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;