ORACLE event to Sql ....
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 ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
col "Begin Snap" for a27
col "End Snap" for a27
col Event for a25
col "Wait Class" for a15
select case wait_rank when 1 then inst_id end "Inst Num",
case wait_rank when 1 then snap_id end "Snap Id",
case wait_rank when 1 then begin_snap end "Begin Snap",
case wait_rank when 1 then end_snap end "End Snap",
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
round((time_waited/db_time)*100, 2) "% DB time",
substr(wait_class, 1, 15) "Wait Class"
from (
select
inst_id,
snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
to_char(end_snap, 'hh24:mi:ss') end_snap,
event_name,
wait_class,
total_waits,
time_waited,
dense_rank() over (partition by inst_id, snap_id order by time_waited desc)-1 wait_rank,
max(time_waited) over (partition by inst_id, snap_id) db_time
from (
select
s.instance_number inst_id,
s.snap_id,
s.begin_interval_time begin_snap,
s.end_interval_time end_snap,
event_name,
wait_class,
total_waits-lag(total_waits, 1, total_waits) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) total_waits,
time_waited-lag(time_waited, 1, time_waited) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) time_waited,
min(s.snap_id) over (partition by s.startup_time, s.instance_number, stats.event_name) min_snap_id
from (
select dbid, instance_number, snap_id, event_name, wait_class, total_waits_fg total_waits, round(time_waited_micro_fg/1000000, 2) time_waited
from dba_hist_system_event
where wait_class not in ('Idle', 'System I/O')
union all
select dbid, instance_number, snap_id, stat_name event_name, null wait_class, null total_waits, round(value/1000000, 2) time_waited
from dba_hist_sys_time_model
where stat_name in ('DB CPU', 'DB time')
) stats, dba_hist_snapshot s
where stats.instance_number=s.instance_number
and stats.snap_id=s.snap_id
and stats.dbid=s.dbid
and s.instance_number = :INST_NUMBER
and stats.snap_id between :BgnSnap and :EndSnap
and stats.dbid = :DID
) where snap_id > min_snap_id and nvl(total_waits,1) > 0
) where event_name!='DB time' and wait_rank <= 5
order by inst_id, snap_id;
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set lines 1000 pages 1000
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between :BgnSnap and :EndSnap
group by wait_class_id, wait_class
order by 3;
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between :BgnSnap and :EndSnap and wait_class_id=3386400367 ---<<<< from above
group by event_id, event
order by 3;
select sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between :BgnSnap and :EndSnap
and event_id in ( 1328744198 ) --- from above
group by sql_id
-- having count(*)> 100
order by 2
col sql_count for a30
with topsql as (
select distinct sql_id, count(sql_id) over (partition by sql_id ) sql_id_count
from dba_hist_active_sess_history h
join dba_hist_snapshot s on s.snap_id = h.snap_id
and s.dbid = h.dbid
and s.instance_number = h.instance_number
where s.begin_interval_time >= systimestamp - interval '1' day
order by 2 desc
)
select sql_id ||','|| sql_id_count sql_count
from topsql
where rownum <= 100
/
SQL_COUNT
------------------------------
1rn43zb7tm2jg,7943
1zh2ms1aymubn,5902
720143cwrpuju,5510
87a333ujtm7q3,5243
9783wcf3s3634,3367
cmcuudhryjkkk,2378
cdmqjc2dtv99z,1638
9wr7pd23cxbb6,1629
select sql_text from gv$sql where sql_id='1456sjks32zqrb3';
==
define 3="TIMESTAMP'2023-07-20 02:10:00'"
define 4="TIMESTAMP'2023-07-20 02:30:00'"
set linesize 500 pagesize 300
col WHAT for a45
col OBJ for a30
col EVENT for a35
select * from (
SELECT
COUNT(*) count1,
sql_id,
event,
session_state,
sql_plan_operation
|| ' '
|| sql_plan_options AS what,
CASE
WHEN wait_class IN(
'Application',
'Cluster',
'Concurrency',
'User I/O'
)THEN object_name
ELSE 'undef'
END AS obj
FROM
gv$active_session_history ash,
dba_objects o
WHERE
ash.current_obj# = o.object_id
--AND sql_id = '4ws10ggwp4npf'
-- AND sample_time BETWEEN &3 AND &4
AND sample_time >sysdate -1
GROUP BY
sql_id,
event,
session_state,
sql_plan_operation
|| ' '
|| sql_plan_options,
CASE
WHEN wait_class IN(
'Application',
'Cluster',
'Concurrency',
'User I/O'
)THEN object_name
ELSE 'undef'
END
ORDER BY 1 desc
--and having COUNT(*)>10
)
where 1=1
and COUNT1>10
;===
define 3="TIMESTAMP'2023-07-20 02:10:00'"
define 4="TIMESTAMP'2023-07-20 02:30:00'"
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
select min(sample_time) from V$ACTIVE_SESSION_HISTORY
-- top events
select
/*+PARALLEL(8)*/ event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
group by event
order by count(*) desc;
-- top sql
select /*+PARALLEL(8)*/ sql_id,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
group by sql_id
order by count(*) desc;
-- see specific samples
select /*+PARALLEL(8)*/ sample_time,user_id,sql_id,event from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
--and user_id>0
--and session_id=371
order by sample_time;
-- look for hot buffers
select p1,p2,p3,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
and event='buffer busy waits'
group by p1,p2,p3
order by count(*)
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = 1 AND 231928 BETWEEN BLOCK_ID AND
BLOCK_ID + BLOCKS - 1;
-- top SQL waiting for a specific events
select /*+PARALLEL(8)*/ sql_id,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
and event is null
group by sql_id
order by count(*)
-- top programs waiting for a specific events
select /*+PARALLEL(8)*/ program,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
and event='buffer busy waits'
group by program
order by count(*)
/
-- top users waiting for a specific events
select user_id,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
and event='buffer busy waits'
group by user_id
order by count(*) ;
-- Everyone waiting for specific event
select /*+PARALLEL(8)*/ sample_time,user_id,sql_id,event,p1,blocking_session from gV$ACTIVE_SESSION_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and event like 'library%'
;
-- Who is waiting for specific event the most:
select /*+PARALLEL(8)*/ SESSION_ID,user_id,sql_id,round(sample_time,'hh'),count(*) from V$ACTIVE_SESSION_HISTORY
where event like 'log file sync'
AND sample_time BETWEEN &3 AND &4
group by SESSION_ID,user_id,sql_id,round(sample_time,'hh')
order by count(*) desc
select /*+PARALLEL(8)*/ event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
group by event
order by count(*) desc
select /*+PARALLEL(8)*/ to_char(trunc(sample_time, 'hh24') + round((cast(sample_time as date)- trunc(cast(sample_time as date), 'hh24'))*60*24/5)*5/60/24, 'dd/mm/yyyy hh24:mi'),count(*) from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
--and user_id=209
and event='buffer busy waits'
group by to_char(trunc(sample_time, 'hh24') + round((cast(sample_time as date)- trunc(cast(sample_time as date), 'hh24'))*60*24/5)*5/60/24, 'dd/mm/yyyy hh24:mi')
order by count(*)
select sql_id,count(*) from gV$ACTIVE_SESSION_HISTORY
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
and user_id>0
group by sql_id
order by count(*) desc
select * from dba_views where view_name like 'DBA_HIST%'
select /*+PARALLEL(8)*/ sh.sample_time,sh.SESSION_ID,user_id,sh.sql_id,event,p1,blocking_session,PROGRAM,sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY sh
left outer join DBA_HIST_SQLTEXT sq on sq.sql_id=sh.sql_id
where 1=1
-- and sample_time> sysdate-1/24
AND sample_time BETWEEN &3 AND &4
--and user_id=61
and sh.sql_id='cdmqjc2dtv99z'
order by sample_time
/
select trunc(sample_time),
sum(case when INSTANCE_NUMBER=1 then 1 else 0 end) inst1,
sum(case when INSTANCE_NUMBER=2 then 1 else 0 end) inst2
from DBA_HIST_ACTIVE_SESS_HISTORY sh
where 1=1
and user_id=61
group by trunc(sample_time)
order by trunc(sample_time)
;
set long 5000
select SQL_TEXT from DBA_HIST_SQLTEXT where sql_id='cdmqjc2dtv99z'
--and dbms_lob.instr(sql_text, 'GLOBAL',1,1) > 0
;
define 3="TIMESTAMP'2023-07-20 07:10:00'"
define 4="TIMESTAMP'2023-07-20 07:30:00'"
COL wait_class FOR a15
COL event FOR a35
COL time_range HEAD "WAIT_TIM_BUCKET_US+" FOR A26 JUST RIGHT
COL avg_wait_us HEAD "AVG_WAIT_IN_BKT_US" FOR 9,999,999,999
COL pct_event FOR a9
COL pct_event_vis FOR a13
COL pct_total_vis FOR a13
COL pct_total FOR a9
BREAK ON event SKIP 1 NODUPLICATES ON state ON wait_class
-- TODO: ignore latest sample (0 waits)
SELECT /* (hint disabled) LEADING(@"SEL$4" "S"@"SEL$4" "A"@"SEL$4") USE_HASH(@"SEL$4" "A"@"SEL$4") PARALLEL(8)*/
session_state state
, wait_class
, event
, ROUND(AVG(time_waited)) avg_wait_us
, LPAD(REPLACE(TO_CHAR(POWER(2,TRUNC(LOG(2,CASE WHEN time_waited < 1 THEN NULL ELSE time_waited END))),'9,999,999,999')||' ..'||TO_CHAR(POWER(2,TRUNC(LOG(2,CASE WHEN time_waited < 1 THEN NULL ELSE time_waited END)))*2, '9,999,999,999'), ' ',''), 26) time_range
, COUNT(*) samples
, LPAD(TO_CHAR(TO_NUMBER(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER (PARTITION BY session_state, wait_class, event) * 100, 1), 999.9))||' %',8) pct_event
, '|'||RPAD( NVL( LPAD('#', ROUND(RATIO_TO_REPORT(COUNT(*)) OVER (PARTITION BY session_state, wait_class, event) * 10), '#'), ' '), 10,' ')||'|' pct_event_vis
, LPAD(TO_CHAR(TO_NUMBER(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1), 999.9))||' %',8) pct_total
, '|'||RPAD( NVL( LPAD('#', ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 10), '#'), ' '), 10,' ')||'|' pct_total_vis
FROM
gv$active_session_history
WHERE
1=1
AND sample_time BETWEEN &3 AND &4
--AND sample_time BETWEEN sysdate-1/24 AND sysdate
--AND sample_time BETWEEN TIMESTAMP'2012-04-29 19:30:00' AND TIMESTAMP'2012-04-29 19:30:59'
--AND (REGEXP_LIKE(wait_class, '&1') OR REGEXP_LIKE(event, '&1'))
GROUP BY
session_state
, wait_class
, event
, POWER(2,TRUNC(LOG(2,CASE WHEN time_waited < 1 THEN NULL ELSE time_waited END)))
ORDER BY
session_state
, wait_class
, event
, time_range NULLS FIRST
, samples DESC
/
STATE WAIT_CLASS EVENT AVG_WAIT_IN_BKT_US WAIT_TIM_BUCKET_US+ SAMPLES PCT_EVENT PCT_EVENT_VIS PCT_TOTAL PCT_TOTAL_VIS
------- --------------- ----------------------------------- ------------------ -------------------------- ---------- --------- ------------- --------- -------------
ON CPU 0 .. 9979 100 % |##########| 26.7 % |### |
WAITING Commit log file sync 0 .. 52 .9 % | | .1 % | |
3,780 2,048..4,096 76 1.3 % | | .2 % | |
6,529 4,096..8,192 2361 41 % |#### | 6.3 % |#
--desc DBA_HIST_ACTIVE_SESS_HISTORY
--EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
select sample_time,user_id,sql_id,event,p1,blocking_session from gV$ACTIVE_SESSION_HISTORY
where event like 'library%'
AND sample_time BETWEEN &3 AND &4
/
===
set linesize 600
select
sql_id,
plan_hash_value,
END_INTERVAL_TIME,
executions_delta,
ELAPSED_TIME_DELTA/(nonzeroexecutions*1000) "Elapsed Average ms",
CPU_TIME_DELTA/(nonzeroexecutions*1000) "CPU Average ms",
IOWAIT_DELTA/(nonzeroexecutions*1000) "IO Average ms",
CLWAIT_DELTA/(nonzeroexecutions*1000) "Cluster Average ms",
APWAIT_DELTA/(nonzeroexecutions*1000) "Application Average ms",
CCWAIT_DELTA/(nonzeroexecutions*1000) "Concurrency Average ms",
BUFFER_GETS_DELTA/nonzeroexecutions "Average buffer gets",
DISK_READS_DELTA/nonzeroexecutions "Average disk reads",
trunc(PHYSICAL_WRITE_BYTES_DELTA/(1024*1024*nonzeroexecutions)) "Average disk write megabytes",
ROWS_PROCESSED_DELTA/nonzeroexecutions "Average rows processed"
from
(select
ss.snap_id,
ss.sql_id,
ss.plan_hash_value,
sn.END_INTERVAL_TIME,
ss.executions_delta,
case ss.executions_delta when 0 then 1 else ss.executions_delta end nonzeroexecutions,
ELAPSED_TIME_DELTA,
CPU_TIME_DELTA,
IOWAIT_DELTA,
CLWAIT_DELTA,
APWAIT_DELTA,
CCWAIT_DELTA,
BUFFER_GETS_DELTA,
DISK_READS_DELTA,
PHYSICAL_WRITE_BYTES_DELTA,
ROWS_PROCESSED_DELTA
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where 1=1
and ss.sql_id = '01gmsncqrjrfr'
and ss.snap_id=sn.snap_id
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER)
where ELAPSED_TIME_DELTA > 0
order by snap_id,sql_id
;
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set linesize 500 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 :BgnSnap and :EndSnap
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 :BgnSnap and :EndSnap
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
/
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set linesize 180
set pagesize 9999
column sql_id format a15
column num_versions format 999999
column sql_text format a70
column cpu_seconds format 999999999.99
column cpus_taken format 999.99
column pct_db_cpu alias "% DB CPU" format 99.99
column executions format 999999999999
with btime as (select begin_interval_time time from dba_hist_snapshot where snap_id = :BgnSnap),
etime as (select end_interval_time time from dba_hist_snapshot where snap_id = :EndSnap),
diff as (select (select time from etime) - (select time from btime) diff from dual),
elapsed as (select 24*60*60*extract(day from diff) + 60*60*extract(hour from diff)+60*extract(minute from diff)+extract(second from diff) seconds from diff),
osstat as (select value num_cpus from DBA_HIST_OSSTAT where snap_id = :EndSnap and stat_name= 'NUM_CPUS')
select i2.*
from
( select inline.sql_id,
num_versions,
inline.cpu/1e6 cpu_seconds,
(100*(case total.cpu when 0 then null else inline.cpu/total.cpu end)) pct_db_cpu,
executions,
inline.cpu/1e6/elapsed.seconds cpus_taken,
dbms_lob.substr(replace(replace(sql_text, chr(10), ' '), chr(13)), 200) sql_text
from
elapsed,
osstat,
( select decode(force_matching_signature, 0, dbms_lob.substr(replace(replace(sql_text, chr(10), ' '), chr(13)), 100), force_matching_signature) signature, min(st.sql_id) sql_id, sum(cpu_time_delta) cpu, count(distinct st.sql_id) num_versions, sum(executions_delta) executions
from dba_hist_sqlstat st,
dba_hist_sqltext txt
where st.sql_id = txt.sql_id
and snap_id between :BgnSnap and :EndSnap
group by decode(force_matching_signature, 0, dbms_lob.substr(replace(replace(sql_text, chr(10), ' '), chr(13)), 100), force_matching_signature)
) inline,
(select sum(cpu) cpu from (select snap_id, (value-lag(value) over (partition by stat_name order by snap_id)) cpu from dba_hist_SYS_TIME_MODEL where stat_name = 'DB CPU' )
where snap_id between :BgnSnap and :EndSnap) total,
dba_hist_sqltext txt
where inline.sql_id = txt.sql_id
) i2
order by cpu_seconds desc;
