Top Sql from Between snap_id
for snap id !!!
column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999
select * from (
select min(snap_id) as snap_id,
to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
from (
select snap_id, s.instance_number, begin_interval_time start_time,
end_interval_time end_time, snap_level, flush_elapsed,
lag(s.startup_time) over (partition by s.dbid, s.instance_number
order by s.snap_id) prev_startup_time,
s.startup_time
from dba_hist_snapshot s, gv$instance i
where begin_interval_time between trunc(sysdate)-&p_days and sysdate
and s.instance_number = i.instance_number
and s.instance_number = &p_inst
order by snap_id
)
group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24')
order by snap_id, start_time )
pivot
(sum(snap_id)
for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
)
order by dt;
save this sql in file <<<<<<<<< then run @file_name accept BSID prompt "Enter value for begin snap_id:" accept ESID prompt "Enter value for end snap_id:" PROMPT PROMPT TOP Application SQL PROMPT set linesize 200 pagesize 200 column_object_name format a31 column owner format a15 column "%TOT%" format a6 column SQL_TEXT format a80 wrap on select distinct sub.parsing_schema_name,replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT, sub.EXECUTIONS,round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%", sub.DISK_READS,round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%", sub.BUFFER_GETS,round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%", sub.ELAPSED_TIME,round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%", sub.IOWAIT,sub.ROWS_PROCESSED, sub.SEC_PER_EXEC "SEC/EXE", round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK from DBA_HIST_SQLTEXT DHST, ( select distinct SQL_ID, PARSING_SCHEMA_NAME, round(sum(EXECUTIONS_DELTA)) as EXECUTIONS, round(sum(PARSE_CALLS_DELTA)) as PARSE_CALLS, round(sum(DISK_READS_DELTA)) as DISK_READS, round(sum(BUFFER_GETS_DELTA)) as BUFFER_GETS, round(sum(ROWS_PROCESSED_DELTA)) as ROWS_PROCESSED, round(sum(CPU_TIME_DELTA/1000000)) as CPU_TIME, round(sum(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME, round(sum(IOWAIT_DELTA)/1000000) as IOWAIT, sum(ELAPSED_TIME_DELTA/1000000)/decode(sum(EXECUTIONS_DELTA),0,1,sum(EXECUTIONS_DELTA)) SEC_PER_EXEC from dba_hist_snapshot natural join dba_hist_sqlstat DHS natural join dba_hist_sql_plan DHSP where snap_id between &&BSID and &&ESID and parsing_schema_name not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP') and object_owner not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP') group by SQL_ID,PARSING_SCHEMA_NAME ) sub, ( select decode(round(sum(EXECUTIONS_DELTA)),0,1,round(sum(EXECUTIONS_DELTA))) as EXECUTIONS_TOTAL, decode(round(sum(DISK_READS_DELTA)),0,1,round(sum(DISK_READS_DELTA))) as DISK_READS_TOTAL, decode(round(sum(BUFFER_GETS_DELTA)),0,1,round(sum(BUFFER_GETS_DELTA))) as BUFFER_GETS_TOTAL, decode(round(sum(ELAPSED_TIME_DELTA/1000000)),0,1,round(sum(ELAPSED_TIME_DELTA/1000000))) as ELAPSED_TIME_TOTAL from dba_hist_snapshot natural join dba_hist_sqlstat DHS natural join dba_hist_sql_plan DHSP where snap_id between &&BSID and &&ESID and parsing_schema_name not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP') and object_owner not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP') ) sub2 where DHST.sql_id = sub.sql_id order by RANK Desc /
================
with sql_id
accept BSID prompt "Enter value for begin snap_id:"
accept ESID prompt "Enter value for end snap_id:"
PROMPT
PROMPT TOP Application SQL
PROMPT
set linesize 300 pagesize 200
column_object_name for a31
column owner for a15
column "%TOT%" for a6
column SQL_TEXT for a80 wrap on
col parsing_schema_name for A12
select distinct sql_id1,sub.parsing_schema_name,
-- replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT,
sub.EXECUTIONS,round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%",
sub.DISK_READS,round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%",
sub.BUFFER_GETS,round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%",
sub.ELAPSED_TIME,round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%",
sub.IOWAIT,sub.ROWS_PROCESSED,
sub.SEC_PER_EXEC "SEC/EXE",
round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK ,
replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT
from DBA_HIST_SQLTEXT DHST,
(
select distinct
SQL_ID sql_id1,
PARSING_SCHEMA_NAME,
round(sum(EXECUTIONS_DELTA)) as EXECUTIONS,
round(sum(PARSE_CALLS_DELTA)) as PARSE_CALLS,
round(sum(DISK_READS_DELTA)) as DISK_READS,
round(sum(BUFFER_GETS_DELTA)) as BUFFER_GETS,
round(sum(ROWS_PROCESSED_DELTA)) as ROWS_PROCESSED,
round(sum(CPU_TIME_DELTA/1000000)) as CPU_TIME,
round(sum(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME,
round(sum(IOWAIT_DELTA)/1000000) as IOWAIT,
sum(ELAPSED_TIME_DELTA/1000000)/decode(sum(EXECUTIONS_DELTA),0,1,sum(EXECUTIONS_DELTA)) SEC_PER_EXEC
from
dba_hist_snapshot
natural join
dba_hist_sqlstat DHS
natural join
dba_hist_sql_plan DHSP
where
snap_id between &&BSID and &&ESID
and parsing_schema_name not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
and object_owner not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
group by SQL_ID,PARSING_SCHEMA_NAME
) sub,
(
select decode(round(sum(EXECUTIONS_DELTA)),0,1,round(sum(EXECUTIONS_DELTA))) as EXECUTIONS_TOTAL,
decode(round(sum(DISK_READS_DELTA)),0,1,round(sum(DISK_READS_DELTA))) as DISK_READS_TOTAL,
decode(round(sum(BUFFER_GETS_DELTA)),0,1,round(sum(BUFFER_GETS_DELTA))) as BUFFER_GETS_TOTAL,
decode(round(sum(ELAPSED_TIME_DELTA/1000000)),0,1,round(sum(ELAPSED_TIME_DELTA/1000000))) as ELAPSED_TIME_TOTAL
from
dba_hist_snapshot
natural join
dba_hist_sqlstat DHS
natural join
dba_hist_sql_plan DHSP
where
snap_id between &&BSID and &&ESID
and parsing_schema_name not in
('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
and object_owner not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
) sub2
where DHST.sql_id = sub.sql_id1
order by RANK Desc
/
====
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set line 999
set pagesize 999
set verify off
BREAK ON REPORT
COLUMN DUMMY HEADING ''
compute sum of PCT_CPU on REPORT
with total_cpu as
(select count(1)*10 from dba_hist_active_sess_history
where snap_id >= :BgnSnap and snap_id < :EndSnap
and SESSION_TYPE != 'BACKGROUND'
and session_state = 'ON CPU')
select module,count(1)*10 "module_cpu",(select * from total_cpu) "total_cpu",round((count(1)*10/(select * from total_cpu))*100,2) PCT_CPU
from dba_hist_active_sess_history
where snap_id >= :BgnSnap and snap_id < :EndSnap
and SESSION_TYPE != 'BACKGROUND'
and session_state = 'ON CPU'
group by module order by PCT_CPU desc
set line 999
set pagesize 999
set verify off
BREAK ON REPORT
COLUMN DUMMY HEADING ''
compute sum of PCT_CPU on REPORT
with total_cpu as
(select count(1)*10
from dba_hist_active_sess_history
where snap_id >= :BgnSnap and snap_id < :EndSnap
and SESSION_TYPE = 'FOREGROUND')
select module,
count(1)*10 "module_cpu",
(select * from total_cpu) "total_cpu",
round((count(1)*10/(select * from total_cpu))*100,2) PCT_CPU
from dba_hist_active_sess_history
where snap_id >= :BgnSnap and snap_id < :EndSnap
and SESSION_TYPE = 'FOREGROUND'
group by module order by PCT_CPU desc
--- awr_top_waits.txt
VARIABLE INST NUMBER
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select sys_context ('userenv','INSTANCE') into :INST from v$instance ;
/
set termout off feedback off verify off linesize 200
set termout on serveroutput on size 1000000 format wrapped
DECLARE
v_low NUMBER;
v_high NUMBER;
v_end_t VARCHAR2(30);
v_output VARCHAR2(200);
type srec is record (
instance_number NUMBER,
event_name VARCHAR2(64),
waits NUMBER,
time_s NUMBER,
avg_ms NUMBER,
pct NUMBER);
type stab is table of srec index by pls_integer;
v_stab stab;
CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS
WITH db_time AS (
SELECT DISTINCT INSTANCE_NUMBER,
ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER))/1000000,0) db_t
FROM DBA_HIST_SYS_TIME_MODEL
WHERE STAT_NAME='DB time' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
),
event_time AS (
SELECT instance_number,event_name,waits,time_s,avg_ms,pos
FROM (
SELECT instance_number,event_name,waits,time_s,
DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
ROW_NUMBER() OVER (PARTITION BY instance_number ORDER BY time_s desc) pos
FROM (
SELECT DISTINCT INSTANCE_NUMBER, EVENT_NAME,
MAX(TOTAL_WAITS) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME) - MIN(TOTAL_WAITS) over (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME) waits,
ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY dbid,INSTANCE_NUMBER,EVENT_NAME) - MIN(TIME_WAITED_MICRO) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME))/1000000) time_s
FROM DBA_HIST_SYSTEM_EVENT
WHERE WAIT_CLASS<>'Idle' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
UNION
SELECT DISTINCT INSTANCE_NUMBER, 'CPU time', NULL,
ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID, INSTANCE_NUMBER))/1000000)
FROM DBA_HIST_SYS_TIME_MODEL
WHERE STAT_NAME='DB CPU' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
)
)
WHERE pos<6
)
SELECT db_time.instance_number,event_name,nvl(waits,0),time_s,nvl(avg_ms,0),ROUND(time_s*100/db_t ,1) pct
FROM db_time, event_time
WHERE db_time.instance_number=event_time.instance_number
ORDER BY db_time.instance_number,pos;
BEGIN
FOR i IN :BgnSnap...:EndSnap LOOP
v_low:=i;
v_high:=i+1;
IF v_high > :EndSnap THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('');
SELECT TO_CHAR(END_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
FROM DBA_HIST_SNAPSHOT
WHERE INSTANCE_NUMBER=:INST AND SNAP_ID=v_high;
DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t);
IF c1%isopen THEN
CLOSE c1;
END IF;
OPEN c1(:INST, v_low, v_high);
FETCH c1 bulk collect into v_stab;
v_output:='';
v_output:=v_output||rpad('Instance',9,' ');
v_output:=v_output||lpad('Averge',76, ' ');
v_output:=v_output||lpad('% Total',34,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
v_output:='';
v_output:=v_output||rpad('Number',9,' ');
v_output:=v_output||lpad('Event Name',64,' ');
v_output:=v_output||lpad('Waits',12,' ');
v_output:=v_output||lpad('Time (s)', 12,' ');
v_output:=v_output||lpad('Wait (ms)',12, ' ');
v_output:=v_output||lpad('Call Time',12,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));
FOR j IN 1..v_stab.count LOOP
v_output:='';
v_output:=v_output||rpad(to_char(v_stab(j).instance_number), 9,' ');
v_output:=v_output||lpad(v_stab(j).event_name, 64,' ');
IF v_stab(j).waits = 0 THEN
v_output:=v_output||lpad(' ', 12,' ');
ELSE
v_output:=v_output||lpad(to_char(v_stab(j).waits,'999,999,999'), 12,' ');
END IF;
v_output:=v_output||lpad(to_char(v_stab(j).time_s), 10,' ');
IF v_stab(j).avg_ms = 0 THEN
v_output:=v_output||lpad(' ', 10,' ');
ELSE
v_output:=v_output||lpad(to_char(v_stab(j).avg_ms), 10,' ');
END IF;
v_output:=v_output||lpad(to_char(v_stab(j).pct), 12,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
END LOOP;
IF c1%isopen THEN
CLOSE c1;
END IF;
END LOOP;
END;
/
Instance Averge % Total
Number Event Name Waits Time (s) Wait (ms) Call Time
-----------------------------------------------------------------------------------------------------------------------------
1 db file sequential read 6,993,273 2235 17.8
1 CPU time 2136 17
1 gc buffer busy acquire 3,473,824 2101 1 16.7
1 read by other session 2,885,443 997 7.9
1 gc cr disk read 3,288,611 716 5.7
SQ
set termout off feedback off verify off linesize 200
VARIABLE INST NUMBER
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select sys_context ('userenv','INSTANCE') into :INST from v$instance ;
/
set termout on serveroutput on size 1000000 format wrapped
DECLARE
v_low NUMBER;
v_high NUMBER;
v_end_t VARCHAR2(30);
v_output VARCHAR2(200);
type srec is record (
sql_id VARCHAR2(13),
plan_hash_value NUMBER,
elapsed_seconds NUMBER,
cpu_seconds NUMBER,
rows_processed NUMBER,
buffer_gets NUMBER,
disk_reads NUMBER,
executions NUMBER,
parses NUMBER );
type stab is table of srec index by pls_integer;
v_stab stab;
CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS
WITH sql_stats_delta AS
( SELECT SNAP_ID, SQL_ID, PLAN_HASH_VALUE,
ELAPSED_TIME_DELTA,
CPU_TIME_DELTA,
ROWS_PROCESSED_DELTA,
BUFFER_GETS_DELTA,
DISK_READS_DELTA,
EXECUTIONS_DELTA,
PARSE_CALLS_DELTA
FROM DBA_HIST_SQLSTAT
WHERE INSTANCE_NUMBER=inst_number AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id
),
sql_stats_delta_rollup AS
( SELECT sql_id, plan_hash_value,
ROUND(SUM(elapsed_time_delta) /1000000) elapsed_seconds,
ROUND(SUM(cpu_time_delta) /1000000) cpu_seconds,
DENSE_RANK() OVER (ORDER BY SUM(cpu_time_delta) DESC) rank_by_cpu_seconds,
SUM(rows_processed_delta) rows_processed,
SUM(buffer_gets_delta) buffer_gets,
SUM(disk_reads_delta) disk_reads,
SUM(executions_delta) executions,
SUM(parse_calls_delta) parses
FROM sql_stats_delta
GROUP BY sql_id,plan_hash_value
)
SELECT sql_id,plan_hash_value,elapsed_seconds,cpu_seconds,rows_processed,buffer_gets,disk_reads,executions,parses
FROM sql_stats_delta_rollup
WHERE rank_by_cpu_seconds <= 10
ORDER BY rank_by_cpu_seconds;
BEGIN
FOR i IN :BgnSnap...:EndSnap LOOP
v_low:=i;
v_high:=i+1;
IF v_high > :EndSnap THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('');
SELECT TO_CHAR(END_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
FROM DBA_HIST_SNAPSHOT
WHERE INSTANCE_NUMBER=:INST AND SNAP_ID=v_high;
DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t);
IF c1%isopen THEN
CLOSE c1;
END IF;
OPEN c1(:INST, v_low, v_high);
FETCH c1 bulk collect into v_stab;
v_output:='';
v_output:=v_output||lpad('Elapsed', 43,' ');
v_output:=v_output||lpad('CPU', 10,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
v_output:='';
v_output:=v_output||rpad('Sql Id',15,' ');
v_output:=v_output||lpad('Plan Hash Value',16,' ');
v_output:=v_output||lpad('Seconds',12,' ');
v_output:=v_output||lpad('Seconds', 12,' ');
v_output:=v_output||lpad('Rows',13,' ');
v_output:=v_output||lpad('Buffer Gets',15,' ');
v_output:=v_output||lpad('Disk Reads',15,' ');
v_output:=v_output||lpad('Executions', 12,' ');
v_output:=v_output||lpad('Parses', 12,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));
FOR j IN 1..v_stab.count LOOP
v_output:='';
v_output:=v_output||rpad(v_stab(j).sql_id, 15,' ');
v_output:=v_output||to_char(v_stab(j).plan_hash_value, 999999999999);
v_output:=v_output||to_char(v_stab(j).elapsed_seconds ,'999,999,999');
v_output:=v_output||to_char(v_stab(j).cpu_seconds , '999,999,999');
v_output:=v_output||to_char(v_stab(j).rows_processed , '999,999,999,999');
v_output:=v_output||to_char(v_stab(j).buffer_gets , '999,999,999,999');
v_output:=v_output||to_char(v_stab(j).disk_reads , '999,999,999,999');
v_output:=v_output||to_char(v_stab(j).executions , '999,999,999');
v_output:=v_output||to_char(v_stab(j).parses , '999,999,999');
DBMS_OUTPUT.PUT_LINE(v_output);
END LOOP;
IF c1%isopen THEN
CLOSE c1;
END IF;
END LOOP;
END;
/
Elapsed CPU
Sql Id Plan Hash Value Seconds Seconds Rows Buffer Gets Disk Reads Executions Parses
-----------------------------------------------------------------------------------------------------------------------------
8cnh50qfgwg73 3673574621 14,418 4,175 187 42,314,024 13,727,079 187 6
7z5abdb0vs5dz 0 7,215 2,091 0 43,114,637 6,884,128 3 3
5aa3zr7sxythy 0 7,214 2,088 0 43,109,039 6,851,546 3 3
1n1bm7xa28vtq 1605285479 448 177 29,421,022 43,392,786 0 59,985 58,409
1y4f4wtu63797 2541923104 28 26 37 242,622 1,003 37 60
fhf8upax5cxsz 0 26 24 37 369,434 1,004 37 37
0w26sk6t6gq98 1388734953 25 23 37 300,454 1,003 37 37
0smupm8p2dhq7 3018276022 16 16 1 5,109,549 0 1 1
8mdz49zkajhw3 2609763588 10 10 3,663 1,819 216 37 19
5aa4qy7098w5k 923387779 13 10 36,640 146,762 383 36,640 36,640
==========================================================================================================================================
Top Resource Consuming Sql
define minutes=100
select
ash.session_id,
ash.session_serial#,ash.sql_id,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash,v$event_name en
where en.event# = ash.event# and SAMPLE_TIME > SYSDATE - (&minutes/(24*60))
group by sql_id,session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1));
=============
Top Resource Consuming Queries
set linesize 300 pagesize 300
set numf 9999999.99
define topN=20
VARIABLE beg_snap NUMBER
VARIABLE end_snap NUMBER
exec select max(snap_id) -1 into :beg_snap from dba_hist_snapshot ;
exec select max(snap_id) into :end_snap from dba_hist_snapshot ;
col SQLMODULE for a25
col SCHEMA_NAME for a20
WITH sqt AS
(SELECT elap, cput, EXEC,norm_val, iowt,SCHEMA_NAME, sql_id, module, rnum
FROM (SELECT sql_id, module, elap,norm_val, cput, EXEC, iowt,PARSING_SCHEMA_NAME schema_name, rownum rnum
FROM (SELECT sql_id,
MAX(module) module,
SUM(elapsed_time_delta) elap,
(100 * (SUM(elapsed_time_delta) /NULLIF((SELECT (SUM(e.VALUE) - SUM(b.VALUE)) /1000000 / 60 FROM DBA_HIST_SYS_TIME_MODEL e,
DBA_HIST_SYS_TIME_MODEL b
WHERE e.SNAP_ID = :beg_snap
AND e.STAT_NAME = 'DB time'
AND b.SNAP_ID = :end_snap
AND b.STAT_NAME = 'DB time'),0))) norm_val,
sum(cpu_time_delta) cput,
sum(executions_delta) exec,
sum(iowait_delta) iowt,
parsing_schema_name
from dba_hist_sqlstat
WHERE 1=1
and snap_id=:beg_snap
and snap_id<=:end_snap
and PARSING_SCHEMA_NAME NOT IN ('APEX_030200','SYSMAN','SYS','MDSYS','EXFSYS','DBSNMP','UTILITY')
/*
and (SELECT snap_id FROM dba_hist_snapshot WHERE BEGIN_INTERVAL_TIME =(SELECT Min(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot
WHERE To_Char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD')=To_Char(&endtime,'YYYY-MM-DD'))) < snap_id
AND snap_id <= (SELECT snap_id FROM dba_hist_snapshot WHERE BEGIN_INTERVAL_TIME =(SELECT max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot
WHERE To_Char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD')=To_Char(starttime,'YYYY-MM-DD')))
*/
GROUP BY sql_id,PARSING_SCHEMA_NAME
ORDER BY nvl(SUM(elapsed_time_delta), -1) DESC, sql_id))
WHERE rnum < &topN
)
SELECT /*+ NO_MERGE(sqt) */
nvl((sqt.elap / 1000000), to_number(NULL)) ela_Time,sqt.EXEC EXEC,
decode(sqt.EXEC, 0, to_number(NULL), (sqt.elap / sqt.EXEC / 1000000)) elapexec,
decode(sqt.elap, 0, to_number(NULL), (100 * (sqt.cput / sqt.elap))) CPU,------ to show CPU intensive queries
decode(sqt.elap, 0, to_number(NULL), (100 * (sqt.iowt / sqt.elap))) IO, ------ to show IP intensive queries
sqt.SCHEMA_NAME,
sqt.sql_id SQLId,
to_clob(decode(sqt.module, NULL, NULL, 'Module: ' || sqt.module)) SQLModule,
nvl(substr(st.sql_text, 0, 200), to_clob('** SQL Text Not Available **')) SQLText
FROM sqt, dba_hist_sqltext st
WHERE st.sql_id(+) = sqt.sql_id
ORDER BY sqt.rnum
;====--- top event
set termout off feedback off verify off linesize 200 numf 9999999999999999999999
define v_inst_number="1"
set termout on serveroutput on size 1000000 format wrapped
DECLARE
v_low NUMBER;
v_high NUMBER;
v_end_t VARCHAR2(30);
v_output VARCHAR2(200);
type srec is record (
inst_id NUMBER,
event VARCHAR2(64),
waits NUMBER,
time_s NUMBER,
avg_ms NUMBER,
pct NUMBER);
type stab is table of srec index by pls_integer;
v_stab stab;
CURSOR c1 (inst_number NUMBER) IS
WITH db_time AS (
SELECT DISTINCT INST_ID,
ROUND((MAX(VALUE) OVER (PARTITION BY (SELECT DBID FROM V$DATABASE),INST_ID))/1000000,0) db_t
FROM gv$SYS_TIME_MODEL
WHERE STAT_NAME='DB time' AND INST_ID=inst_number
),
event_time AS (
SELECT inst_id,event,waits,time_s,avg_ms,pos
FROM (
SELECT inst_id,event,waits,time_s,
DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY time_s desc) pos
FROM (
SELECT DISTINCT INST_ID, EVENT,
MAX(TOTAL_WAITS) OVER (PARTITION BY (select DBID from v$database),INST_ID, EVENT) waits,
ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY (select dbid from v$database),INST_ID,EVENT))/1000000) time_s
FROM gv$SYSTEM_EVENT
WHERE WAIT_CLASS<>'Idle' and INST_ID=inst_number
UNION
SELECT DISTINCT INST_ID, 'CPU time', NULL,
ROUND((MAX(VALUE) OVER (PARTITION BY (select DBID from v$database),inst_id))/1000000)
FROM gv$SYS_TIME_MODEL
WHERE STAT_NAME='DB CPU' and inst_id=inst_number
)
)
WHERE pos<6
)
SELECT db_time.inst_id,event,nvl(waits,0),time_s,nvl(avg_ms,0),ROUND(time_s*100/db_t ,1) pct
FROM db_time, event_time
WHERE db_time.inst_id=event_time.inst_id
ORDER BY db_time.inst_id,pos;
BEGIN
DBMS_OUTPUT.PUT_LINE('');
IF c1%isopen THEN
CLOSE c1;
END IF;
OPEN c1(&v_inst_number);
FETCH c1 bulk collect into v_stab;
v_output:='';
v_output:=v_output||rpad('Instance',9,' ');
v_output:=v_output||lpad('Averge',76, ' ');
v_output:=v_output||lpad('% Total',34,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
v_output:='';
v_output:=v_output||rpad('Number',9,' ');
v_output:=v_output||lpad('Event Name',64,' ');
v_output:=v_output||lpad('Waits',12,' ');
v_output:=v_output||lpad('Time (s)', 12,' ');
v_output:=v_output||lpad('Wait (ms)',12, ' ');
v_output:=v_output||lpad('Call Time',12,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));
FOR j IN 1..v_stab.count LOOP
v_output:='';
v_output:=v_output||rpad(to_char(v_stab(j).inst_id), 9,' ');
v_output:=v_output||lpad(v_stab(j).event, 64,' ');
IF v_stab(j).waits = 0 THEN
v_output:=v_output||lpad(' ', 12,' ');
ELSE
v_output:=v_output||lpad(to_char(v_stab(j).waits,'999,999,999,999,999'), 12,' ');
END IF;
v_output:=v_output||lpad(to_char(v_stab(j).time_s), 10,' ');
IF v_stab(j).avg_ms = 0 THEN
v_output:=v_output||lpad(' ', 10,' ');
ELSE
v_output:=v_output||lpad(to_char(v_stab(j).avg_ms), 10,' ');
END IF;
v_output:=v_output||lpad(to_char(v_stab(j).pct), 12,' ');
DBMS_OUTPUT.PUT_LINE(v_output);
END LOOP;
IF c1%isopen THEN
CLOSE c1;
END IF;
END;
/
Instance Averge % Total
Number Event Name Waits Time (s) Wait (ms) Call Time
-----------------------------------------------------------------------------------------------------------------------------
1 CPU time 13397752 35.6
1 enq: TX - row lock contention 127 10497842 82 27.9
1 log file sync 2,365 8382373 4 22.3
1 Redo Transport MISC 2,659 6174759 2 16.4
1 SYNC Remote Write 2,659 5830027 2 15.5
Elapsed: 00:00:00.03
***************
Oracle AWR Report Script top sqls' waits
set numf 9999999999999999999999.99
VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;
set linesize 300 pagesize 300
select dbid,
db_name,
instance_number,
inst_name,
begin_snap_id,
end_snap_id,
elapsed,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM DBA_HIST_SYSSTAT B,
DBA_HIST_SYSSTAT E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
AND B.STAT_NAME in( 'physical reads') ) as physical_reads,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM DBA_HIST_SYSSTAT B,
DBA_HIST_SYSSTAT E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
AND B.STAT_NAME in( 'session logical reads') ) as logical_reads,
(SELECT SUM(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0))
FROM DBA_HIST_SYSTEM_EVENT B,
DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID(+) = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
AND B.DBID(+) = E.DBID
AND B.INSTANCE_NUMBER(+) = E.INSTANCE_NUMBER
AND E.INSTANCE_NUMBER = base_info.instance_number
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.WAIT_CLASS = 'User I/O') as UIOW_TIME,--STAT_UIOW_TIME
(SELECT e.VALUE-b.value as diff_value
FROM DBA_HIST_SYS_TIME_MODEL B,
DBA_HIST_SYS_TIME_MODEL E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
AND B.STAT_NAME = 'DB time'
) as db_time,
(SELECT sum(E.VALUE)-sum(B.VALUE) as STAT_TXN
FROM DBA_HIST_SYSSTAT B,
DBA_HIST_SYSSTAT E
WHERE b.dbid = e.dbid
and b.instance_number = e.instance_number
and b.STAT_ID = e.STAT_ID
AND E.DBID = base_info.dbid
and e.instance_number = base_info.instance_number
and b.snap_id = base_info.begin_snap_id
and e.snap_id = base_info.end_snap_id
AND e.STAT_NAME in ('user rollbacks','user commits')
) as transaction_count
from (with db_info as (select d.dbid dbid,
d.name db_name,
i.instance_number instance_number,
i.instance_name inst_name
from v$database d, v$instance i),
snap_info as (select c.*,
EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
+ EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
+ EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
+ EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
from (select min(snap_id) begin_snap_id,
max(snap_id) end_snap_id,
min(END_INTERVAL_TIME) as min_end_interval_time,
max(END_INTERVAL_TIME) as max_end_interval_time
from dba_hist_snapshot sn
where sn.begin_interval_time >= trunc(sysdate) - 1
and sn.begin_interval_time < sysdate) c
)
select * from db_info, snap_info) base_info;
DBID DB_NAME INSTANCE_NUMBER INST_NAME BEGIN_SNAP_ID END_SNAP_ID ELAPSED PHYSICAL_READS LOGICAL_READS UIOW_TIME DB_TIME TRANSACTION_COUNT
-------------------------- --------- -------------------------- ---------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
1222414252.00 XXRAC 1.00 xxrac1 12280.00 12312.00 115204.08 41865968.00 392896011.00 14553390318.00 39638901871.00 5463.00
AWR SQL ordered by Reads
set linesize 600 pagesize 300
col "SQL Text" for a70 wrap
define phyr='41865968.00'
select *
from (select sqt.dskr as "Physical Reads",
sqt.exec as "Executions",
round(decode(sqt.exec, 0, to_number(null),(sqt.dskr / sqt.exec)),2) as "Reads per Exec",
round(decode(&phyr, 0, to_number(null),(100 * sqt.dskr)/&phyr),2) as "%Total",
round(nvl((sqt.elap/1000000), to_number(null)),2) as "Elapsed Time (s)",
round(decode(sqt.elap, 0, to_number(null),(100 * (sqt.cput / sqt.elap))),2) as "%CPU",
round(decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))),2) as "%IO",
sqt.sql_id as "SQL Id",
decode(sqt.module, null,null, 'Module: ' || sqt.module) as "SQL Module",
nvl(dbms_lob.substr(st.sql_text,4000,1), to_clob('** SQL Text Not Available **')) as "SQL Text"
from (select sql_id, max(module) module,
sum(disk_reads_delta) dskr, sum(executions_delta) exec,
sum(cpu_time_delta) cput, sum(elapsed_time_delta) elap,
sum(iowait_delta) uiot
from dba_hist_sqlstat
where 1=1
and instance_number = :INSTANCE_NUMBER
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and &phyr > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 60 and (rownum <= 10 or "%Total" > 0.1)
Physical Reads Executions Reads per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------------------------------------------------------------ ----------------------------------------------------------------------
bytes = :25,
other_tag = :26,
partition_start = :
27, partition_s
top = :28, par
tition_id = :29,
other = :30,
distribution = :31,
cpu_cost = :32,
SQL statements Top SQL
set linesize 500 pagesize 300
VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;
Logical read TOP 10
select *
from (select sqt.dskr,
sqt.exec,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)),
(100 * sqt.dskr) / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER
AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') norm_val,
nvl((sqt.cput / 1000000), to_number(null)),
nvl((sqt.elap / 1000000), to_number(null)),
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = :INSTANCE_NUMBER
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER
AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65
and(rownum <= 10
or norm_val > 1)
DSKR EXEC DECODE(SQT.EXEC,0,TO_NUMBER(NULL),(SQT.DSKR/SQT.EXEC)) NORM_VAL NVL((SQT.CPUT/1000000),TO_NUMBER(NULL)) NVL((SQT.ELAP/1000000),TO_NUMBER(NULL)) SQL_ID DECODE(SQT.MODULE,NULL,NULL,'MODULE:'||SQT.MODULE) NVL(ST.SQL_TEXT,TO_CLOB('**SQLTEXTNOTAVAILABLE**'))
---------- ---------- ------------------------------------------------------ ---------- --------------------------------------- --------------------------------------- ------------- ------------------------------------------------------------------------ --------------------------------------------------------------------------------
1303904 384 3395.58333 99.8915974 256.457058 715.109381 8cnh50qfgwg73 Module: DBMS_SCHEDULER SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1
Physical reading TOP 10
select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);
Physical reading TOP 10
select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);
PHYSICAL_READS EXECUTIONS READS_PER_EXEC TOTAL_RATE CPU_TIME_S ELAPSED_TIME_S SQL_ID SQL_MODULE SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
1303904.00 384.00 3395.58 99.89 256.46 715.11 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
ULER = :B1
CPU consumption TOP 10
col SQL_MODULE for a18
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);
ELAPSED_TIME_S CPU_TIME_S EXECUTIONS ELAP_PER_EXEC_S TOTAL_DB_TIME_RATE SQL_ID SQL_MODULE SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
715.11 256.46 384.00 1.86 309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
ULER = :B1
Execution time TOP 10
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);
ELAPSED_TIME_S CPU_TIME_S EXECUTIONS ELAP_PER_EXEC_S TOTAL_DB_TIME_RATE SQL_ID SQL_MODULE SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
715.11 256.46 384.00 1.86 309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
ULER = :B1
No comments:
Post a Comment