set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select executions,substr(sql_text,1,4000)
from v$sqlarea
order by executions desc;
begin
dbms_output.put_line('Executions'||' '||' Text');
dbms_output.put_line ('---------'||' '||'--------------------------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/
Sample Output:
Code: Select all
Executions Text
--------- --------------------------------------------------------------------
2554 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, times
" stamp#, sample_size, minimum, maximum, distcnt, lowval, hival, d
" ensity, col#, spare1, spare2, avgcln from hist_head$ where obj#=
" :1 and intcol#=:2
2246 select privilege#,level from sysauth$ connect by grantee#=prior pr
" rivilege# and privilege#>0 start with grantee#=:1 and privilege#
" >0
754 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= nex
" xt_date) and (next_date <= :2)) or ((last_date is null) and
" (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
" ) and (this_date is null) and ((dbms_logstdby.db_is_logstdby = 0
" and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and
" job >= 1000000000)) order by next_date, job
692 select count(*) from sys.job$ where (next_date > sysdate) and (nex
" xt_date < (sysdate+5/86400)) and ((dbms_logstdby.db_is_logstdby
" = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1
" and job >= 1000000000))
689 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ w
" where obj#=:1 and intcol#=:2 and row#=:3 order by bucket