set serverout on size 1000000
set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc;
begin
dbms_output.put_line('Reads'||' '||' 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
Reads Text
----- --------------------------------------------------------------------
633257 call dbms_space.auto_space_advisor_job_proc ( )
631665 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')
" )
452927 call dbms_stats.gather_database_stats_job_proc ( )
61758 select name||','||to_char(first_time,'YYYY-MM')||','||totGB||','||
" | (totGB-freeGB)||','||count(*)||','||count(*)*logsize fr
" om v$database,v$log_history, (select bytes/1024/1024 lo
" gsize from v$log where rownum=1) l, (select round(sum(byt
" es/1024/1024/1024),3) totGB from (select bytes from dba_d
" ata_files union all select bytes from dba_temp_files)) s,
" (select round(sum(bytes/1024/1024/1024),3) freeGB from dba_free
" _space) f --where LOG_MODE='ARCHIVELOG' group by name,t
" o_char(first_time,'YYYY-MM'),totGB,(totGB-freeGB),logsize
53255 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME
" E ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(
" 30) := :job_name; job_subname VARCHAR2(30) := :job_subname; jo
" b_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TI
" ME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
" ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME
" ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :
" window_end; BEGIN BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.collect_
" config_metrics('ORACLE_OCM_CONFIG_DIR'); END; :mydate := next_d
" ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;