Most frequently executed query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Most frequently executed query

  1. #1
    Join Date
    Sep 2000
    Posts
    17
    How do I find the query that is executed for many number of times.
    Using V$SQLTEXT , I am able to find the queries executed but do not have an idea of the number of times.



  2. #2
    Join Date
    Jun 2001
    Posts
    316
    hi,
    I dunno if i am rite
    but try in this table

    select * from V$SQL
    i hope it helps...

  3. #3

    Try this

    Hi there,
    bung this into a .sql file and run it... It will show you the top ten queries by executions. Also its easy to change to show the same infor for "top 10 buffer gets" or "top ten by IO"

    The query works in both 7 and 8.

    column sql_text format a80 word_wrap
    set long 160
    set linesize 160
    set pagesize 40
    SELECT /*+ RULE */
    substr(rownum,1,2) as "No",
    sql_text,
    executions,
    buffer_gets,
    disk_reads,
    rows_processed
    FROM v$sql st, dual
    WHERE -1*st.executions = DECODE(dual.dummy(+), 'X',NULL, NULL)
    AND rownum <= 10
    ORDER BY st.executions DESC
    /


    -Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width