set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select decode(executions,0,1,round(disk_reads/executions)),substr(sql_text,1,4000)
from v$sqlarea
order by 1 desc;
begin
dbms_output.put_line('Reads/Exec'||' '||' 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
26 Reads/Exec Text
--------- --------------------------------------------------------------------
8016 call dbms_space.auto_space_advisor_job_proc ( )
5733 call dbms_stats.gather_database_stats_job_proc ( )
3998 insert into wri$_adv_objspace_trend_data select timepoint, space_
" _usage, space_alloc, quality from table(dbms_space.object_growt
" h_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')
" )
2474 select /*+ no_parallel_index(t,"I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST") d
" dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampl
" ing(0) no_monitoring no_expand index(t,"I_WRI$_OPTSTAT_H_OBJ#_IC
" OL#_ST") */ count(*) as nrw,count(distinct sys_op_lbid(4226,'L',
" t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("OBJ#")|
" |sys_op_descend("INTCOL#")||sys_op_descend(SYS_EXTRACT_UTC("SAVT
" IME")))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
" from "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY" t where "OBJ#" is not
" null or "INTCOL#" is not null or SYS_EXTRACT_UTC("SAVTIME") is n
" ot null
1073 select /*+ no_parallel_index(t,"I_WRI$_OPTSTAT_H_ST") dbms_stats c
" cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_m
" onitoring no_expand index(t,"I_WRI$_OPTSTAT_H_ST") */ count(*) a
" s nrw,count(distinct sys_op_lbid(4227,'L',t.rowid)) as nlb,count
" (distinct SYS_EXTRACT_UTC("SAVTIME")) as ndk,sys_op_countchg(sub
" strb(t.rowid,1,15),1) as clf from "SYS"."WRI$_OPTSTAT_HISTGRM_HI
" STORY" t where SYS_EXTRACT_UTC("SAVTIME") is not null