Oracle 10g Top_5sql

Oracle top 5sql
Oracle top sql



SQL> select sysdate from dual;

SYSDATE
---------
24-FEB-11



exec top_stmt5('21-feb-11','2');


for two days



from web

http://www.evdbt.com/top_stmt5_10g.sql



set echo on feedback on timing on

spool top_stmt5_10g

create or replace procedure top_stmt5
(
in_start_date in date,
in_nbr_days in number,
in_top_count in integer default 10,
in_instance_nbr in number DEFAULT NULL,
in_max_disk_reads in integer default 10000,
in_max_buffer_gets in integer default 100000
) is
--
cursor get_top_stmts(in_dr in integer, in_bg in integer,
in_dbid in integer, in_inst_nbr in integer,
in_begin_time in timestamp, in_end_time in timestamp)
is
select sq.sql_id,
sq.module,
st.command_type,
sum(sq.disk_reads_delta) disk_reads,
sum(sq.buffer_gets_delta) buffer_gets,
sum(sq.cpu_time_delta)/1000000 cpu_time,
sum(sq.elapsed_time_delta)/1000000 elapsed_time,
sum(sq.executions_delta) executions,
(1 - (sum(sq.disk_reads_delta) / sum(sq.buffer_gets_delta)))*100 bchr,
sum(sq.disk_reads_delta) / sum(sq.executions_delta) dr_per_exe,
sum(sq.buffer_gets_delta) / sum(sq.executions_delta) bg_per_exe,
sum(sq.cpu_time_delta)/1000000 / sum(sq.executions_delta) cpu_per_exe,
sum(sq.elapsed_time_delta)/1000000 / sum(sq.executions_delta) ela_per_exe,
((sum(sq.disk_reads_delta)*100)+sum(sq.buffer_gets_delta))/100 factor
from dba_hist_sqltext st,
dba_hist_sqlstat sq,
dba_hist_snapshot ss
where ss.dbid = in_dbid
and ss.instance_number = nvl(in_inst_nbr, ss.instance_number)
and ss.begin_interval_time between in_begin_time and in_end_time
and sq.dbid = ss.dbid
and sq.instance_number = ss.instance_number
and sq.snap_id = ss.snap_id
and st.sql_id = sq.sql_id
group by sq.sql_id,
sq.module,
st.command_type
having (sum(sq.disk_reads_delta) > in_dr
or sum(sq.buffer_gets_delta) > in_bg)
and sum(sq.buffer_gets_delta) > 0
and sum(sq.executions_delta) > 0
order by factor desc;
--
cursor get_sql_plan(in_dbid in number,
in_sql_id in varchar2,
in_begin_time in timestamp,
in_end_time in timestamp)
is
select distinct plan_hash_value, timestamp
from dba_hist_sql_plan
where dbid = in_dbid
and sql_id = in_sql_id
and timestamp between in_begin_time and in_end_time
order by 2;
--
v_text_lines integer;
v_sql_text clob;
v_sql_text_len integer;
n integer;
v_tot_logr integer;
v_tot_phyr integer;
v_sql_tot_cnt integer := 0;
v_sql_tot_dr integer := 0;
v_sql_tot_bg integer := 0;
v_sql_tot_cpu integer := 0;
v_sql_tot_ela integer := 0;
v_plsql_tot_cnt integer := 0;
v_plsql_tot_dr integer := 0;
v_plsql_tot_bg integer := 0;
v_plsql_tot_cpu integer := 0;
v_plsql_tot_ela integer := 0;
v_dbid integer;
v_instance_nbr integer;
v_begin_snapshot timestamp;
v_end_snapshot timestamp;
v_nbr_snapshots integer;
v_nbr_instances integer;
v_plan_id integer;
--
v_errcontext varchar2(100);
v_errmsg varchar2(512);
v_save_module varchar2(48);
v_save_action varchar2(32);
--
begin
--
dbms_application_info.read_module(v_save_module, v_save_action);
v_errcontext := 'query dba_hist_database_instance';
dbms_application_info.set_module('TOP_STMT5', v_errcontext);
select h.dbid, decode(in_instance_nbr, null, null, i.instance_number), count(distinct i.instance_number)
into v_dbid, v_instance_nbr, v_nbr_instances
from dba_hist_database_instance h,
gv$instance i
where h.instance_number = i.instance_number
and i.instance_number = nvl(in_instance_nbr, i.instance_number)
group by h.dbid, decode(in_instance_nbr, null, null, i.instance_number);
--
v_errcontext := 'query dba_hist_snapshot';
dbms_application_info.set_action(v_errcontext);
select min(begin_interval_time),
max(end_interval_time),
count(*)
into v_begin_snapshot,
v_end_snapshot,
v_nbr_snapshots
from dba_hist_snapshot
where begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and dbid = v_dbid
and instance_number = nvl(v_instance_nbr, instance_number);
--
v_errcontext := 'query dba_hist_sysstat';
dbms_application_info.set_action(v_errcontext);
select sum(cg.value_delta+dbg.value_delta),
sum(p.value_delta)
into v_tot_logr,
v_tot_phyr
from (select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'consistent gets') cg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'db block gets') dbg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'physical reads') p,
dba_hist_snapshot s
where s.begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and s.dbid = v_dbid
and s.instance_number = nvl(v_instance_nbr, s.instance_number)
and cg.snap_id = s.snap_id
and cg.dbid = s.dbid
and cg.instance_number = s.instance_number
and dbg.snap_id = s.snap_id
and dbg.dbid = s.dbid
and dbg.instance_number = s.instance_number
and p.snap_id = s.snap_id
and p.dbid = s.dbid
and p.instance_number = s.instance_number;
--
v_errcontext := 'open/fetch get_top_stmts';
dbms_application_info.set_action(v_errcontext);
for a in get_top_stmts(in_max_disk_reads, in_max_buffer_gets,
v_dbid, v_instance_nbr,
v_begin_snapshot, v_end_snapshot) loop
--
if get_top_stmts%rowcount > in_top_count then
--
exit;
--
end if;
--
v_errcontext := 'put_line formfeed';
dbms_application_info.set_action(v_errcontext);
if get_top_stmts%rowcount > 1 then
--
dbms_output.put_line(chr(12));
--
end if;
--
v_errcontext := 'put_line statement header';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line(rpad('Beginning Snap Time: ',30) ||
to_char(v_begin_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Page ' ||
to_char(get_top_stmts%rowcount,'990'),60));
dbms_output.put_line(rpad('Ending Snap Time : ',30) ||
to_char(v_end_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Snapshots: ' ||
to_char(v_nbr_snapshots,'990'),60));
dbms_output.put_line(rpad('Date of Report : ',30) ||
to_char(sysdate, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Instances: ' ||
to_char(v_nbr_instances,'990'),60));
dbms_output.put_line(rpad('Total Logical Reads: ', 23) ||
to_char(v_tot_logr,'999,999,999,999,999,990') ||
lpad('Total Physical Reads: ' ||
to_char(v_tot_phyr,'999,999,999,999,999,990'), 60));
dbms_output.put_line('.');
--
if a.module is not null then
v_errcontext := 'display module';
dbms_output.put_line('Module: "' || a.module || '"');
dbms_output.put_line('.');
end if;
--
dbms_output.put_line('SQL Statement Text (SQL ID=' || a.sql_id || ')');
dbms_output.put_line('-------------------------------' || rpad('-', length(trim(to_char(a.sql_id))), '-') || '-');
--
v_errcontext := 'get sql_text from dba_hist_sqltext';
dbms_application_info.set_action(v_errcontext);
select sql_text,
dbms_lob.getlength(sql_text) len
into v_sql_text,
v_sql_text_len
from dba_hist_sqltext
where sql_id = a.sql_id;
v_text_lines := 1;
n := 1;
while n < v_sql_text_len loop
--
dbms_output.put_line(rpad(to_char(v_text_lines),6) ||
replace(dbms_lob.substr(v_sql_text, 100, n),chr(10),null));
n := n + 100;
v_text_lines := v_text_lines + 1;
--
v_errcontext := 'fetch/close get_text';
--
end loop;
--
v_errcontext := 'put_line statement totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',16) ||
lpad('Cache Hit',10) ||
lpad(' ',11) ||
lpad('DR Per',12) ||
lpad('BG Per',12) ||
lpad('CPU Per',15) ||
lpad('Ela Per',15));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',16) ||
lpad('Ratio',10) ||
lpad('Runs',11) ||
lpad('Run',12) ||
lpad('Run',12) ||
lpad('Run',15) ||
lpad('Run',15));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',16) ||
lpad('---------',10) ||
lpad('----',11) ||
lpad('------',12) ||
lpad('------',12) ||
lpad('------',15) ||
lpad('------',15));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(a.disk_reads,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.buffer_gets,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.bchr,'990.00')||'%'),10) ||
lpad(ltrim(to_char(a.executions,'99,999,990')),11) ||
lpad(ltrim(to_char(a.dr_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.bg_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.cpu_per_exe,'999,999,990.00')),15) ||
lpad(ltrim(to_char(a.ela_per_exe,'999,999,990.00')),15));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((a.disk_reads/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((a.buffer_gets/v_tot_logr)*100,3),
'990.000'))||'%)',16));
--
v_errcontext := 'open/fetch get_sql_plan';
dbms_application_info.set_action(v_errcontext);
for p in get_sql_plan(v_dbid, a.sql_id, v_begin_snapshot, v_end_snapshot) loop
--
v_text_lines := 0;
v_errcontext := 'open/fetch get_xplan';
dbms_application_info.set_action(v_errcontext);
for s in (select plan_table_output
from table(dbms_xplan.display_awr(a.sql_id, p.plan_hash_value, v_dbid, 'ALL'))) loop
--
if s.plan_table_output like 'Plan hash value: %' then
v_text_lines := 1;
end if;
--
if v_text_lines = 1 then
dbms_output.put_line('.');
dbms_output.put_line('. SQL execution plan from "'||
to_char(p.timestamp,'MM/DD/YY HH24:MI:SS') || '"');
end if;
--
if v_text_lines >= 1 then
dbms_output.put_line(s.plan_table_output);
v_text_lines := v_text_lines + 1;
end if;
--
end loop;
--
v_errcontext := 'fetch/close get_sql_plan';
--
end loop;
--
if a.command_type = 47 then
--
v_plsql_tot_cnt := v_plsql_tot_cnt + 1;
v_plsql_tot_dr := v_plsql_tot_dr + a.disk_reads;
v_plsql_tot_bg := v_plsql_tot_bg + a.buffer_gets;
v_plsql_tot_cpu := v_plsql_tot_cpu + a.cpu_time;
v_plsql_tot_ela := v_plsql_tot_ela + a.elapsed_time;
--
else
--
v_sql_tot_cnt := v_sql_tot_cnt + 1;
v_sql_tot_dr := v_sql_tot_dr + a.disk_reads;
v_sql_tot_bg := v_sql_tot_bg + a.buffer_gets;
v_sql_tot_cpu := v_sql_tot_cpu + a.cpu_time;
v_sql_tot_ela := v_sql_tot_ela + a.elapsed_time;
--
end if;
--
v_errcontext := 'fetch/close get_top_stmt';
dbms_application_info.set_action(v_errcontext);
--
end loop;
--
if v_sql_tot_cnt > 0 then
--
v_errcontext := 'put_line SQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_sql_tot_cnt||' "TOP ' || in_top_count || '" SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_sql_tot_dr,'999,999,999,990')),16) ||
lpad(ltrim(to_char(v_sql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_sql_tot_dr/v_sql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_sql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_sql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_sql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((v_sql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
if v_plsql_tot_cnt > 0 then
--
v_errcontext := 'put_line PLSQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_plsql_tot_cnt||' "TOP '||in_top_count||'" PL/SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',20) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',20) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_plsql_tot_dr,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_plsql_tot_dr/v_plsql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_plsql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_plsql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',20) ||
lpad('('||ltrim(to_char(round((v_plsql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
rollback;
--
dbms_application_info.set_module(v_save_module, v_save_action);
--
exception
when others then
v_errmsg := sqlerrm;
dbms_application_info.set_module(v_save_module, v_save_action);
rollback;
raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
end top_stmt5;
/
show errors
spool off