|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select sql_id AS "SQL ID" ,nvl(replace(replace(replace(replace(to_char(substr(replace(sql_text,',','_'),1,2500)),chr(9),' '),chr(10),' '),chr(13),' '),',','_'), '** Not Found **') AS "SQL TEXT" ,cnt As "version Count" ,executions as "Exec (Max)" ,sharable_mem as "Used Memory (bytes)" ,module as "Representative Client" from ( select substr(replace(sql_text,',','_'),1,60) sql_partial ,max(to_char(substr(sql_fulltext,1,2500))) sql_text ,count(*) cnt ,sum(sharable_mem) sharable_mem ,max(hash_value) hash_value ,max(sql_id) sql_id ,max(executions) executions ,max(module) module from v$sql where executions > 0 and executions < 5 group by substr(replace(sql_text,',','_'),1,60) having count(*) >= 10 order by cnt desc, sharable_mem desc ) where rownum <= 123 / |