Most frequently executed query

[ganesh]

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.

[helpme]

hi,
I dunno if i am rite
but try in this table

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

[bmycroft]

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