Search This Blog

Total Pageviews

Thursday 14 November 2013

Top Sql from Between snap_id

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



Oracle top 10 sql waits

Oracle top 10 sql waits 

Oracle Rac top 10 sql waits

top awr event 
http://anuj-singh.blogspot.com/2021/12/awr-top-events.html


	
http://anuj-singh.blogspot.com/2023/     Oracle AWR Report Script top sqls' waits ..     

Prompt Top Elapsed
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, avg_etime_Sec,cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,sql_id,child_number,sql_text,round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),2) avg_etime_Sec, 
      cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
                                   and USERS_EXECUTING!=0
                                 )
where
elapsed_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Avg Elapsed Time Sec*      :'||sqlcur.avg_etime_Sec);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/



Prompt Top buffer_gets
set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,avg_buf ,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,sql_id,child_number,sql_text,round(buffer_gets/decode(nvl(executions,0),0,1,executions),2) avg_buf, 
      cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY buffer_gets  DESC) AS buffer_gets_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
                                   and USERS_EXECUTING!=0
                                  )
where buffer_gets_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Avg Buffer Gets*           :'||sqlcur.avg_buf);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/

Prompt High cpu 
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,sql_id,child_number,sql_text,EXECUTIONS, round((cpu_time/decode(nvl(executions,0),0,1,executions))/1000000,2) CPU_TIME,
      disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY cpu_time DESC) AS cpu_time_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
                                   and USERS_EXECUTING!=0
                               )
where cpu_time_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('===============================================================================');
dbms_output.put_line('Inst id        :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME      :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID         :'||sqlcur.sql_id);
dbms_output.put_line('Child Number        :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS       :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU (In Seconds) Per Execution*   :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads        :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement       :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==============================================================================');
end;
/

Prompt Top EXECUTIONS
set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,sql_id,child_number,sql_text,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('EXECUTIONS                 :'||sqlcur.EXECUTIONS);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top disk_reads

set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY disk_reads  DESC) AS disk_reads_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where disk_reads_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Disk Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


Prompt Top parse_calls

set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,parse_calls 
from (select inst_id,sql_id,child_number,parse_calls,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY parse_calls  DESC) AS parse_calls_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where parse_calls_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Parse Calls                :'||sqlcur.parse_calls);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/



Prompt Top sharable_mem

set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,sharable_mem  
from (select inst_id,sql_id,child_number,sharable_mem ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sharable_mem   DESC) AS sharable_mem_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS' , 'ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where sharable_mem_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Sharable Mem               :'||sqlcur.sharable_mem);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


Prompt Top VERSIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,OPEN_VERSIONS  
from (select inst_id,sql_id,child_number,OPEN_VERSIONS ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY OPEN_VERSIONS   DESC) AS OPEN_VERSIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where OPEN_VERSIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('OPEN VERSIONS              :'||sqlcur.OPEN_VERSIONS);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/




Sorts ...

set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME,parse_calls 
from (select inst_id,sql_id,child_number,parse_calls,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sorts  DESC) AS sorts_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where sorts_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Parse Calls                :'||sqlcur.parse_calls);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('*sorts                     :'||sqlcur.sorts);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


============================================================
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  		for a30
col ACTION 		for a25
SELECT 
rows_processed,
       buffer_gets, 
       executions, 
	-- direct_writes,
	--  elapsed_time / 1000000 as elapsed_time_secs, 
	-- cpu_time / 1000000 as cpu_time_secs,
        --  last_exec_start_time, 
       parsing_schema_name, 
       module, 
       --  action, 
       sql_id, 
       plan_hash_value, 
       sql_text
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 1000'))
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 1000'))
where 1=1
and parsing_schema_name not in ('SYS','SYSTEM','SYSMAN','DBVISIT7','MON','MDSYS')
ORDER BY buffer_gets desc;



set linesize 300 pagesize 300
col MODULE for a20 
col ACTION for a30 
SELECT s.sql_id,s.rows_processed, s.loads, s.executions, s.buffer_gets,s.disk_reads, t.sql_text,s.module, s.ACTION FROM gv$sql s,gv$sqltext t
WHERE s.address = t.address
and s.inst_id = t.inst_id
AND ((buffer_gets > 10000000) or (disk_reads > 1000000) or (executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/


======



SELECT 'Physical i/o' type,
       ROUND (100 * NVL (top_disk_read / sum_disk_reads, 0), 2) perct, hash_value,  sql_id
  FROM (SELECT   disk_reads top_disk_read, hash_value, sql_id     FROM gv$sqlarea       WHERE disk_reads > 0  ORDER BY 1 DESC),
       (SELECT SUM (disk_reads) sum_disk_reads FROM gv$sqlarea  WHERE disk_reads > 0)
 WHERE ROWNUM < 11
UNION ALL
SELECT 'Logical i/o', ROUND (100 * NVL (top_buff_get / sum_buff_gets, 0), 2) perct, hash_value, sql_id
  FROM (SELECT   buffer_gets top_buff_get, hash_value, sql_id    FROM gv$sqlarea         WHERE buffer_gets > 0  ORDER BY 1 DESC),
       (SELECT SUM (buffer_gets) sum_buff_gets FROM gv$sqlarea   WHERE buffer_gets > 0)
 WHERE ROWNUM < 11
UNION ALL
SELECT 'CPU Time', ROUND (100 * NVL (top_cpu / sum_cpu, 0), 2) perct, hash_value, sql_id
  FROM (SELECT   cpu_time top_cpu, hash_value, sql_id    FROM gv$sqlarea   WHERE cpu_time > 0   ORDER BY 1 DESC),
       (SELECT SUM (cpu_time) sum_cpu   FROM gv$sqlarea  WHERE cpu_time > 0)
 WHERE ROWNUM < 11
UNION ALL
SELECT 'Elapsed Time',
       ROUND (100 * NVL (top_elap_time / sum_elap_time, 0), 2) perct, hash_value, sql_id
  FROM (SELECT   elapsed_time top_elap_time, hash_value, sql_id   FROM gv$sqlarea   WHERE elapsed_time > 0  ORDER BY 1 DESC),
       (SELECT SUM (elapsed_time) sum_elap_time FROM gv$sqlarea
         WHERE elapsed_time > 0)
 WHERE ROWNUM < 11
;

===========


 
set long 99999999999  linesize 300 pagesize 300
select sql_id,sql_text from dba_hist_sqltext 
where sql_id in ( select sql_id from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL/s.executions_total)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL/s.executions_total)) DESC) elapsed_rank
from  dba_hist_sqlstat s, dba_hist_snapshot sn
where  1=1
and sn.begin_interval_time between to_date('28-apr-2021 00:01','dd-mon-yyyy hh24:mi') and to_date('28-apr-2021 06:00','dd-mon-yyyy hh24:mi')
and sn.snap_id=s.snap_id and s.executions_total >0
group by   s.sql_id   ) 
where cpu_rank <=100 and elapsed_rank<=100);
===



prompt "Top SQL by Buffer Gets"
set linesize 300 pagesize 300
col sql for a50 wrap
select * from ( select sql_id,substr(sql_text,1,500) "SQL",      (cpu_time/1000000) "CPU_Seconds",      disk_reads "Disk_Reads",      buffer_gets "Buffer_Gets",      executions "Executions",
      case when rows_processed = 0 then null       
      else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))     
      end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )    and USERS_EXECUTING!=0
order by buffer_gets desc nulls last)
where rownum<11;




 
prompt "Top SQL by Buffer Gets / Rows Proc"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL",   round((cpu_time/1000000),3) "CPU_Seconds",   disk_reads "Disk_Reads",  buffer_gets "Buffer_Gets",   executions "Executions",
           case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )    and USERS_EXECUTING!=0
order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last)
where rownum<11;


 
prompt "Top SQL by Disk Reads"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL",    (cpu_time/1000000) "CPU_Seconds",    disk_reads "Disk_Reads",   buffer_gets "Buffer_Gets",    executions "Executions",
           case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
           round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
           (elapsed_time/1000000) "Elapsed_Seconds",
           module "Module"
 from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )    and USERS_EXECUTING!=0
order by disk_reads desc nulls last)
where rownum<11
;

 
 
prompt "Top SQL by CPU"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL",  (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets",   executions "Executions",
           case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
           round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
           (elapsed_time/1000000) "Elapsed_Seconds",
           module "Module"
 from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )    and USERS_EXECUTING!=0
order by cpu_time desc nulls last)
where rownum<11
;

 
 
prompt "Top SQL by Executions"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL",   (cpu_time/1000000) "CPU_Seconds",  disk_reads "Disk_Reads",  buffer_gets "Buffer_Gets",    executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )    and USERS_EXECUTING!=0
order by executions desc nulls last)
where rownum<11
;

 
set linesize 300 pagesize 300
prompt "Top SQL by Waits"
col sql for a50 wrap
col Module for a20
select INST_ID,   (cpu_time/1000000) "CPU_Seconds",   disk_reads "Disk_Reads",   buffer_gets "Buffer_Gets",  executions "Executions",
          case when rows_processed = 0 then null
          else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
          end "Buffer_gets/rows_proc",
     round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",  (elapsed_time/1000000) "Elapsed_Seconds",
     --round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
     substr(sql_text,1,500) "SQL",
     module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
  (select   sql_id
  ,  CASE  WHEN session_state = 'ON CPU' THEN 'CPU'      
           WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
           ELSE 'WAIT' END state           
    from gv$active_session_history            
    where   session_type IN ( 'FOREGROUND')       
    and sample_time  between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
    group by sql_id, state),
     ranked_sqls AS
(select sql_id,  sum(occur) sql_occur  , rank () over (order by sum(occur)desc) xrank
from sql_class          
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
and rs.xrank <= 11 
order by xrank, sql_id, state )) 
order by elapsed_time desc nulls last; 

=====

top 10 sql 

set linesize 500 pagesize 300
col SQL_TEXT for a50 wrap 
col DURATION for a15
col SQL_OPNAME for a15
select
 * from (
                select instance_number as inst_id,
                       ash.sql_id,
                       sql_opname,
                       to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss')           as SQL_EXEC_START,
                       max(sample_time) - SQL_EXEC_START                         as duration,
                       sql_exec_id,
                       sql_plan_hash_value,
                       module,
                       action,
                       machine,
                       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
                       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
                       max(px_used)                                             as max_px_used
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ') as SQL_TEXT
                  from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, machine,
                               sum(temp_space_allocated)           as temp_space_allocated,
                               sum(pga_allocated)                  as pga_allocated,
                               count(distinct session_serial#) - 1 as px_used,
                               sample_time
                          from dba_hist_active_sess_history ash
                         where snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot 
where 1=1
and begin_interval_time > sysdate - 1 
)
    and sql_exec_id > 0
                         group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, sample_time, machine
                          having sum(temp_space_allocated) is not null) ash
left join dba_hist_sqltext t  on t.sql_id  = ash.sql_id
                group by instance_number, ash.sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, machine
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ')
                --having max(temp_space_allocated) / 1024 / 1024 / 1024 > 100 -- GB
                order by 11 desc
) where rownum <= nvl('&2',10)
/


=============



  TOP 20 RESOURCE-INTENSIVE SQL
  
set linesize 300
select *
FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"
+ "Disk Reads"
+ "Buffer Gets"
+ "Writes"
+ "Sorts"
+ "Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day",
shs.sql_id "Sql id",
REPLACE(CAST(DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
),CHR (10),
''
) "Sql",
SUM (shs.executions_delta) "Execs",
ROUND ( (SUM (shs.elapsed_time_delta) / 1000000)/ SUM (shs.executions_delta),1) "Time Ea Sec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000)/ SUM (shs.executions_delta),1) "CPU Ea Sec",
ROUND ( (SUM (shs.iowait_delta) / 1000000)/ SUM (shs.executions_delta),1) "IO/Wait Ea Sec",
SUM (shs.cpu_time_delta) "CPU Time",
SUM (shs.disk_reads_delta) "Disk Reads",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.direct_writes_delta) "Writes",
SUM (shs.parse_calls_delta) "Parses",
SUM (shs.sorts_delta) "Sorts",
SUM (shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,
TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'),
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
)
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');

==========================

display top 15 resource intensive SQL

SQL> @?/rdbms/admin/sqltrpt.sql

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
b6usrg82hwsa3  11,054.98 call dbms_stats.gather_database_stats_job_proc (  )
b6usrg82hwsa3   2,559.58 call dbms_stats.gather_database_stats_job_proc (  )
22356bkgsdcnh     721.25 SELECT COUNT(*) FROM X$KSPPI A, X$KSPPCV2 B WHERE A.IND
6h1ysp5jm8h8m     492.02 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
6mcpb06rctk0x     467.58 call dbms_space.auto_space_advisor_job_proc (  )
6mcpb06rctk0x     437.91 call dbms_space.auto_space_advisor_job_proc (  )
6h1ysp5jm8h8m     395.80 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
0smupm8p2dhq7     324.44 select count(*) num_windows, sum(case when jobs_started
3zmzdajzvjkfw     281.02 select /*+ no_monitor no_statement_queuing */ job,  nvl
4q607gwyr580r     264.54 MERGE /*+ dynamic_sampling(4) dynamic_sampling_est_cdn
49s332uhbnsma     240.74       declare          vsn  varchar2(20);             b

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
1fvsn5j51ugz3     237.73       begin          dbms_rcvman.resetAll;       end;
5c24r2fm4b9rh     233.07 SELECT /*jskqjobqlod2*/ /*+ no_monitor no_statement_que
5j7xryzqzqcbk     232.36 SELECT /*jskqjobqlod3*//*+ no_monitor no_statement_queu
c9umxngkc3byq     230.66 select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to

15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     (select to_char(substr(replace(st.sql_text,:newl,' '),1,55))
      *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row



Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: b6usrg82hwsa3

Sql Id specified: b6usrg82hwsa3

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_5127
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/06/2021 07:14:03
Completed at       : 05/06/2021 07:14:03

-------------------------------------------------------------------------------
Schema Name   : SYS

Container Name: CDB$ROOT
SQL ID        : b6usrg82hwsa3
SQL Text      : call dbms_stats.gather_database_stats_job_proc (  )

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.

-------------------------------------------------------------------------------


=====================



SET UNDERLINE =
set HEADS '/'
COL separador HEADING '|' FOR A1
COL seconds_since_date HEADING 'Elapsed/Time(s)'
COL execs_since_date HEADING 'Executions'
COL elpased_execution HEADING 'Elapsed/per Exec(s)'
COL gets_since_date HEADING 'Total Gets'
COL pct_db_time HEADING '% DB Time'
COL pct_gets HEADING '% Gets'
COL ranking NOPRINT
select
    sql_id,
    '|' as separador,
    seconds_since_date,
    execs_since_date,
    elpased_execution,
    pct_db_time,
    '|' as separador,
    gets_since_date,
    pct_gets,
    ranking
from
(select
   sub.sql_id,   
   sub.seconds_since_date,
   sub.execs_since_date,
   round(sub.seconds_since_date/DECODE(sub.execs_since_date,0,1,sub.execs_since_date),2) elpased_execution,
   round(sub.seconds_since_date*100/sum(sub.seconds_since_date) over ( order by instance_number), 2) pct_db_time,
   sub.gets_since_date,
   round(sub.gets_since_date*100/(sum(sub.gets_since_date) over ( order by instance_number)), 2) pct_gets,
   rank() over( order by gets_since_date desc) ranking
from
   ( -- sub to sort before rownum
     select
        instance_number,
        sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
        sum(executions_delta) as execs_since_date,
        sum(buffer_gets_delta) as gets_since_date       
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where 1=1
and begin_interval_time >sysdate -1
      --  begin_interval_time >= to_date('&start_yyyy_mm_dd_hh24','YYYY-MM-DD HH24')    and end_interval_time <= to_date('&end_yyyy_mm_dd_hh24','YYYY-MM-DD HH24')
      --  and instance_number = 1     
     group by
        instance_number,
        sql_id
     order by
        2 desc
   ) sub
)where
    ranking <20
order by ranking;


                   Elapsed                Elapsed
SQL_ID        |    Time(s) Executions per Exec(s)  % DB Time | Total Gets     % Gets
============= = ========== ========== =========== ========== = ========== ==========
5j7xryzqzqcbk |          0                                 0 |
0smupm8p2dhq7 |        301         23       13.09       7.81 |  117628461      35.45
0smupm8p2dhq7 |        282         22       12.82       5.06 |  112406699      16.63
34cd4y8mbqvsk |        126       2387         .05       2.26 |   49451325       7.32
34cd4y8mbqvsk |        119       2170         .05       3.09 |   44956061      13.55
b6usrg82hwsa3 |        363         11          33       6.52 |   43243450        6.4
6mcpb06rctk0x |        132          8        16.5       2.37 |   40187372       5.95
f705bwx3q0ydq |         94         23        4.09       2.44 |   38932793      11.73
3n1sg9y1f0xm6 |         84         11        7.64       1.51 |   37679114       5.58
f705bwx3q0ydq |         91         22        4.14       1.63 |   37506332       5.55
b6usrg82hwsa3 |       1094          7      156.29       28.4 |   36664030      11.05
6mcpb06rctk0x |        130          7       18.57       3.37 |   35490187       10.7
3n1sg9y1f0xm6 |         78          9        8.67       2.02 |   30828428       9.29
6wrwqq7jkmv3w |         13         66          .2        .23 |    4229673        .63
6ajkhukk78nsr |         21         23         .91        .38 |    3998010        .59
6wrwqq7jkmv3w |         16         60         .27        .42 |    3845488       1.16
1j6tnz8fcm4c3 |         64         41        1.56       1.66 |    3512258       1.06
6ajkhukk78nsr |         16         21         .76        .42 |    3424585       1.03
6wm3n4d7bnddg |          8         25         .32        .14 |    2807919        .42



select
sub.sql_id,
sub.seconds_since_date,
sub.execs_since_date,
sub.gets_since_date
from
( --sub to sort before rownum
select
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date
from dba_hist_snapshot natural join dba_hist_sqlstat
where 1=1
--begin_interval_time > to_date('2015-05-10′,'YYYY-MM-DD’) –to_date('&&start_YYYYMMDD','YYYY-MM-DD')
group by
sql_id
order by
2 desc
) sub
where
rownum < 30
;


SQL_ID        SECONDS_SINCE_DATE EXECS_SINCE_DATE GETS_SINCE_DATE
------------- ------------------ ---------------- ---------------
b6usrg82hwsa3              21879              570      2255605219
6mcpb06rctk0x               9172              601      3018062105

===========================================




set linesize 200 pagesize 10000
 
column min_date format a20
column max_date format a20
 
select x.sql_id,x.command_type,
       to_char(x.min_date,'dd-mon-yyyy hh24:mi:ss') min_date,
       to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') max_date,
       x.cnt,
       to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss') v_sqlstat_last_active,
       (x.max_date - a.last_active_time) * 60 * 60 * 24 diff_sec ,
       a.executions stat_exe,
       round(a.cpu_time / 1000000,0) cpu_secs
from ( select s.sql_id,
                  s.command_type,
                  min(s.last_active_time) min_date,
                  max(s.last_active_time) max_date,
                  count(*) cnt,
                  sum(s.executions) sql_exe
           from gv$sql s
           group by sql_id,command_type
           --having count(*) > 1 -- remove this because still possible for 1 child cursor row (child_number>0] to have mismatch on last_active_time in v$sqlstats
      ) x,
      gv$sqlstats a
where x.sql_id = a.sql_id
and   to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') != to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss')
and   (x.max_date - a.last_active_time) * 60 * 60 * 24 > 20 -- Filter out any where v$sqlstat last active is less than 20 seconds
order by 7 desc
/




===================
var v_second number;
var v_min number;
-- begin :v_second := 10; end; ----- change 
begin :v_min := 15 end;
/


define v_min=15

set linesize 300 pagesize 300
clear breaks
break on session_id
col KILL for a15
col MODULE for a15
with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where 1=1
 --  and sample_time>systimestamp-numtodsinterval(1,'second')
 --  and sample_time>systimestamp - (:v_second)
and sample_time>systimestamp - INTERVAL '&v_min' MINUTE
 )
select ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''' kill ,PROGRAM,MODULE,
       sql_id,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where 1=1
 --and sample_time>systimestamp-numtodsinterval(1,'second')
 --and sample_time>systimestamp - (:v_second)
 --and sample_time>systimestamp - INTERVAL '60' MINUTE
and sample_time>systimestamp - INTERVAL '&v_min' MINUTE
and SQL_ID is not null 
group by ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''',PROGRAM,MODULE,sql_id,b.samples,b.deltaT
--having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;

============from  gv$sqlarea


set pagesize 200 linesize 300
--set echo off
col parsing_schema_name for a20
col executions      heading "Execs"         for 99999999
col rows_processed  heading "Rows Procd"    for 99999999
col loads           heading "Loads"         for 999999.99
col buffer_gets     heading "Buffer Gets"
col disk_reads      heading "Disk Reads"
col elapsed_time    heading "Elasped Time"
col cpu_time        heading "CPU Time"
col sql_text        heading "SQL Text"      for a120 wrap
col avg_cost        heading "Avg Cost"      for 99999999
col gets_per_exec   heading "Gets Per Exec" for 99999999
col reads_per_exec  heading "Read Per Exec" for 99999999
col rows_per_exec   heading "Rows Per Exec" for 99999999

break on report
compute sum  of rows_processed  on report
compute sum  of executions      on report
compute avg  of avg_cost        on report
compute avg  of gets_per_exec   on report
compute avg  of reads_per_exec  on report
compute avg  of row_per_exec    on report

-- and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 

PROMPT
PROMPT Top 10 most expensive SQL by Elapsed Time...
PROMPT

col sql_text for a70 wrap
select rownum  rank, a.*  from ( select con_id,elapsed_time, executions, buffer_gets, disk_reads, cpu_time,sql_id, hash_value, sql_text
            from  gv$sqlarea
            where elapsed_time > 20000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
  order by elapsed_time desc) a 
  where rownum < 11;


PROMPT
PROMPT Top 10 most expensive SQL by CPU Time...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
  from ( select con_id,elapsed_Time, executions, buffer_gets, disk_reads, cpu_time,PARSING_SCHEMA_NAME,sql_id, hash_value,  sql_text
           from  gv$sqlarea
           where cpu_time > 20000
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
order by cpu_time desc) a
where rownum < 11;


PROMPT
PROMPT Top 10 most expensive SQL by Buffer Gets by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,buffer_gets, executions, buffer_gets/ decode(executions,0,1, executions) gets_per_exec,sql_id,   hash_value, sql_text
        from  gv$sqlarea
        where buffer_gets > 50000
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
and users_executing!=0
order by buffer_gets desc) a
where rownum < 11;


PROMPT
PROMPT Top 10 most expensive SQL by Physical Reads by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,disk_reads, executions,
             disk_reads / decode(executions,0,1, executions) reads_per_exec, sql_id,hash_value, sql_text
       from  gv$sqlarea
       where disk_reads > 10000
   and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
order by disk_reads desc) a
where rownum < 11;


PROMPT
PROMPT Top 10 most expensive SQL by Rows Processed by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,rows_processed, executions,rows_processed / decode(executions,0,1, executions) rows_per_exec, sql_id,hash_value, sql_text
        from  gv$sqlarea
        where rows_processed > 10000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) order by disk_reads desc) a
  where rownum < 11;


PROMPT
PROMPT Top 10 most expensive SQL by Buffer Gets vs Rows Processed...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from ( select con_id,buffer_gets, lpad(rows_processed ||
              decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",   executions, loads,
              (decode(rows_processed,0,1,1)) * buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
              sql_id,sql_text
        from  gv$sqlarea
        where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
order by disk_reads desc) a
--order by 5 desc) a
where rownum < 11;


rem Check to see if there are any candidates for procedures or
rem for using bind variables. Check this by comparing UPPER
rem
rem This May be a candidate application for using the init.ora parameter
rem CURSOR_SHARING = FORCE|SIMILAR

col SQLTEXT for a65 WRAP
select rownum as rank, a.* from (select upper(substr(sql_text, 1, 65)) sqltext, count(*)     from gv$sqlarea
WHERE 1=1
--and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
--order by disk_reads desc
         group by upper(substr(sql_text, 1, 65) )
having count(*) > 1
  order by count(*) desc) a
where rownum < 11;

 =============


VARIABLE bsnap NUMBER
VARIABLE esnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bsnap from dba_hist_snapshot ;
exec select max(snap_id) into :esnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;



select * from (
select ss.snap_id snapid,ss.instance_number inst,ss.sql_id  sqlid
       ,round(sum(ss.elapsed_time_delta)) elapsed
       ,nvl(round(sum(ss.executions_delta)),1) execs
       ,round(sum(ss.buffer_gets_delta)) gets
       ,round(sum(ss.rows_processed_delta)) rowsp
       ,round(sum(ss.disk_reads_delta)) reads
       ,dense_rank() over(partition by snap_id,instance_number order by sum(ss.elapsed_time_delta) desc) sql_rank
from
dba_hist_sqlstat ss
where
ss.dbid = :DID  
and
ss.snap_id between :bsnap and :esnap
group by ss.snap_id,ss.instance_number,ss.sql_id
)
where sql_rank < 11 
and snapid between :bsnap and :esnap
/
====


top sql 

COLUMN session_id                 HEADING "SID"              FORMAT 99999
COLUMN inst_id                    HEADING "I#"               FORMAT 99
COLUMN "session_serial#"          HEADING "Serial#"          FORMAT 999999
COLUMN FORCE_MATCHING_SIGNATURE                              FORMAT 99999999999999999999999
COLUMN sql_plan_hash_value        HEADING "Plan|Hash|Value"  FORMAT 9999999999 
COLUMN sql_exec_start                                        FORMAT a19
COLUMN sql_exec_end               HEADING "MaxSampleTime"    FORMAT a19
COLUMN duration                                              FORMAT a15
COLUMN sql_opname                 HEADING "SQL|Operation"    FORMAT a15 TRUNCATE
COLUMN sql_child_number           HEADING "SQL|Ch#"          FORMAT 999
COLUMN current_dop                HEADING "DOP"              FORMAT 999
COLUMN pga_allocated              HEADING "PGA|(GB)"                  FORMAT 99.00
COLUMN temp_space_allocated       HEADING "Temp|Space|(GB)"           FORMAT 999.00

-- Get the SQL Statements from ASH
SELECT * FROM 
(
SELECT --ash.sql_exec_id,
       --TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
          NVL(ash.qc_session_id,ash.session_id)                                        session_id 
     , NVL(ash.qc_instance_id,ash.inst_id)                                          inst_id     
     , NVL(ash.qc_session_serial#,ash.session_serial#)                              session_serial#
     , TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
     , TO_CHAR(max(ash.sample_time) ,'DD-MON-YY HH24:MI:SS')                    sql_exec_end
     , REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'+00000000','+')          duration
   , ash.sql_opname
     , ash.sql_id
     , ash.sql_child_number
     , ash.sql_plan_hash_value
, max(trunc(ash.px_flags / 2097152)) current_dop
     , ash.force_matching_signature
     , NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id) top_level_sql_id
     , ROUND(MAX(ash.pga_allocated)/power(1024,3),2)              pga_allocated
     , ROUND(MAX(ash.temp_space_allocated)/power(1024,3),2)       temp_space_allocated
  FROM gv$session s
       JOIN gv$active_session_history ash 
                    ON s.inst_id = NVL(ash.qc_instance_id,ash.inst_id)
                   AND s.sid     = NVL(ash.qc_session_id,ash.session_id)
                   AND s.serial# = NVL(ash.qc_session_serial#,ash.session_serial#)
       LEFT OUTER JOIN gv$active_session_history ash_parent
                    ON ash_parent.inst_id                   = ash.qc_instance_id
                   AND ash_parent.session_id                = ash.qc_session_id
                   AND ash_parent.session_serial#           = ash.qc_session_serial#
                   AND CAST(ash_parent.sample_time as DATE) = ash.sql_exec_start
 WHERE 1=1
--s.inst_id    = :INST_ID
  -- AND s.sid        = :SID
   --AND ash.sql_exec_id IS NOT NULL
GROUP BY NVL(ash.qc_session_id,ash.session_id)
       , NVL(ash.qc_instance_id,ash.inst_id)   
       , NVL(ash.qc_session_serial#,ash.session_serial#)
       , ash.sql_exec_id
       , ash.sql_exec_start
       , ash.sql_id
       , ash.sql_child_number
       , ash.sql_plan_hash_value
       , ash.FORCE_MATCHING_SIGNATURE
   , ash.sql_opname
       , NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id)
ORDER BY 
        -- max(ash.sample_time) asc
       --, 
       NVL(ash.sql_exec_start,MIN(ash.sample_time)) DESC   
     , max(ash.sample_time) DESC
)       
WHERE ROWNUM <= 10
ORDER BY sql_exec_end
;

                                                                                                                     Plan                                                       Temp
                                                                           SQL                            SQL        Hash                                                PGA   Space
   SID  I# Serial# SQL_EXEC_START      MaxSampleTime       DURATION        Operation       SQL_ID         Ch#       Value  DOP FORCE_MATCHING_SIGNATURE TOP_LEVEL_SQL   (GB)    (GB)
------ --- ------- ------------------- ------------------- --------------- --------------- ------------- ---- ----------- ---- ------------------------ ------------- ------ -------
  1710   1   17438 14-OCT-21 04:40:38  14-OCT-21 04:40:39  +0 00:00:01.167 SELECT          1n1bm7xa28vtq    0  1605285479          13512506387705464989 5aa3zr7sxythy    .01     .00
  1143   1   32606 14-OCT-21 04:41:35  14-OCT-21 04:41:36  +0 00:00:01.199 SELECT          1n1bm7xa28vtq    0  1605285479                             0 7z5abdb0vs5dz    .00     .00
  1143   1   32606 14-OCT-21 04:41:28  14-OCT-21 04:41:44  +0 00:00:16.204 SELECT          8cnh50qfgwg73    0  3673574621          13512506387705464989 7z5abdb0vs5dz    .00     .00
  1710   1   17438 14-OCT-21 04:41:44  14-OCT-21 04:41:45  +0 00:00:01.205 SELECT          1n1bm7xa28vtq    0  1605285479          13512506387705464989 5aa3zr7sxythy    .01     .00



====

Oracle 12c  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
prompt With con_id

Prompt Top Elapsed
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, avg_etime_Sec,cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,CON_ID,sql_id,child_number,sql_text,round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),2) avg_etime_Sec, 
      cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) 
                                   and USERS_EXECUTING!=0
                                 )
where
elapsed_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Avg Elapsed Time Sec*      :'||sqlcur.avg_etime_Sec);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/




Prompt Top buffer_gets
set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,avg_buf ,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,CON_ID,sql_id,child_number,sql_text,round(buffer_gets/decode(nvl(executions,0),0,1,executions),2) avg_buf, 
      cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY buffer_gets  DESC) AS buffer_gets_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
                                   and USERS_EXECUTING!=0
                                  )
where buffer_gets_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Avg Buffer Gets*           :'||sqlcur.avg_buf);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/




Prompt High cpu 
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,CON_ID,sql_id,child_number,sql_text,EXECUTIONS, round((cpu_time/decode(nvl(executions,0),0,1,executions))/1000000,2) CPU_TIME,
      disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY cpu_time DESC) AS cpu_time_rank 
      FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
                                   and USERS_EXECUTING!=0
                               )
where cpu_time_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('===============================================================================');
dbms_output.put_line('Inst id        :'||sqlcur.inst_id);
 dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME      :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID         :'||sqlcur.sql_id);
dbms_output.put_line('Child Number        :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS       :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU (In Seconds) Per Execution*   :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads        :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement       :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==============================================================================');
end;
/




Prompt Top EXECUTIONS
set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,CON_ID,sql_id,child_number,sql_text,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('EXECUTIONS                 :'||sqlcur.EXECUTIONS);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/



Prompt Top disk_reads

set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads 
from (select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY disk_reads  DESC) AS disk_reads_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where disk_reads_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Disk Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


Prompt Top parse_calls

set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,parse_calls 
from (select inst_id,CON_ID,sql_id,child_number,parse_calls,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY parse_calls  DESC) AS parse_calls_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where parse_calls_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Parse Calls                :'||sqlcur.parse_calls);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/



Prompt Top sharable_mem

set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,sharable_mem  
from (select inst_id,CON_ID,sql_id,child_number,sharable_mem ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sharable_mem   DESC) AS sharable_mem_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS' , 'ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where sharable_mem_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Sharable Mem               :'||sqlcur.sharable_mem);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


Prompt Top VERSIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,OPEN_VERSIONS  
from (select inst_id,CON_ID,sql_id,child_number,OPEN_VERSIONS ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY OPEN_VERSIONS   DESC) AS OPEN_VERSIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where OPEN_VERSIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('OPEN VERSIONS              :'||sqlcur.OPEN_VERSIONS);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('DISK Reads                 :'||sqlcur.disk_reads);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/




Prompt Sorts ...

set linesize 150 pagesize 200  serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME,parse_calls 
from (select inst_id,CON_ID,sql_id,child_number,parse_calls,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sorts  DESC) AS sorts_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where sorts_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
     dbms_output.put_line('Inst id                    :'||sqlcur.inst_id);
  dbms_output.put_line('CON_ID                    :'||sqlcur.CON_ID);
     dbms_output.put_line('PARSING SCHEMA NAME        :'||sqlcur.PARSING_SCHEMA_NAME);
     dbms_output.put_line('SQL ID                     :'||sqlcur.sql_id);
     dbms_output.put_line('Child Number               :'||sqlcur.child_number);
     dbms_output.put_line('Parse Calls                :'||sqlcur.parse_calls);
     dbms_output.put_line('CPU                        :'||sqlcur.cpu_time);
     dbms_output.put_line('*sorts                     :'||sqlcur.sorts);
     dbms_output.put_line('SQL Statement              :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/


Prompt Top EXECUTIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,
case when executions = 0 then 0 else round(elapsed_time/executions, 3) end "ElapsedPerExec(ms)",
sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,elapsed_time,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
 dbms_output.put_line('CON_ID                   :'||sqlcur.CON_ID)
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('Per EXECUTIONS :'||sqlcur."ElapsedPerExec(ms)");
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/

=============================================


top 20 sql !!!


set linesize 500 pagesize 300

VARIABLE dbid NUMBER
exec select DBID into :dbid from v$database;
column DB_BLOCK_SIZE_1 new_value DB_BLOCK_SIZE noprint

with inst as (
select min(instance_number) inst_num
  from dba_hist_snapshot
  where dbid = :dbid
)
SELECT VALUE DB_BLOCK_SIZE_1
FROM DBA_HIST_PARAMETER
WHERE dbid = :dbid
and PARAMETER_NAME = 'db_block_size'
AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_osstat WHERE dbid = :dbid AND instance_number = (select inst_num from inst))
   AND instance_number = (select inst_num from inst);




define SQL_TOP_N=20

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER

exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;



column module format a33
column action format a33
col PARSING_SCHEMA_NAME for a20
select * from(
SELECT s.snap_id,PARSING_SCHEMA_NAME,PLAN_HASH_VALUE plan_hash,substr(regexp_replace(s.module,'([[:alnum:]\.\-])@.+\(TNS.+','\1'),1,30) module,
substr(s.action,1,30) action, 
s.sql_id,
avg(s.optimizer_cost) optimizer_cost,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',
7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',
3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,sum(EXECUTIONS_DELTA) execs,sum(BUFFER_GETS_DELTA) buffer_gets,sum(ROWS_PROCESSED_DELTA) rows_proc,
round(sum(CPU_TIME_DELTA)/1000000,1) cpu_t_s,round(sum(ELAPSED_TIME_DELTA)/1000000,1) elap_s,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024,1) read_mb,round(sum(IOWAIT_DELTA)/1000000,1) io_wait,
DENSE_RANK() OVER (PARTITION BY s.snap_id ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
      CASE WHEN MAX(PLAN_HASH_VALUE) = LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC) 
      OR LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC) = 0 THEN 0
      when count(distinct PLAN_HASH_VALUE) > 1 then 1 else 1 end plan_change,
      count(distinct PLAN_HASH_VALUE) OVER       (PARTITION BY s.snap_id,s.sql_id ) plans,
      round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
      sum(s.px_servers_execs_delta) px_servers_execs,
      round(sum(DIRECT_WRITES_DELTA * &DB_BLOCK_SIZE)/1024/1024/1024) direct_w_gb,
      sum(IOWAIT_DELTA) as iowait_time,
      sum(DISK_READS_DELTA) as PIO
  FROM dba_hist_sqlstat s,dba_hist_sqltext t
  WHERE s.dbid = :dbid  
  AND s.dbid = t.dbid
  AND s.sql_id = t.sql_id
 AND s.snap_id BETWEEN :bid AND :eid
  AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN') 
  GROUP BY s.snap_id, PLAN_HASH_VALUE,t.command_type,PARSING_SCHEMA_NAME,s.module,s.action, s.sql_id)
  WHERE elap_rank <= &SQL_TOP_N --#
  --and sql_id = '2a22s56r25y6d'
  order by snap_id,elap_rank asc nulls last;
  
  
   SNAP_ID PARSING_SCHEMA_NAME   PLAN_HASH MODULE                            ACTION                            SQL_ID        OPTIMIZER_COST COMMAND_NAME       EXECS BUFFER_GETS  ROWS_PROC    CPU_T_S          ELAP_S    READ_MB    IO_WAIT  ELAP_RANK PLAN_CHANGE      PLANS PHY_READ_GB PX_SERVERS_EXECS DIRECT_W_GB IOWAIT_TIME        PIO
---------- -------------------- ---------- --------------------------------- --------------------------------- ------------- -------------- ------------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------- ---------------- ----------- ----------- ----------
     39828 SVCDBEM7MON                   0 DBMS_SCHEDULER                    FIFTEEN_MINUTE_INTERVAL           5aa3zr7sxythy              0 CALLMETHOD             1    21240163          0 1060.6      3548.2    26556.8       1560          2           0          1          26                0           0  1560006226    3399272


====



Ordered by Elapsed time



prompt SQL  ...
set lines 1000 pages 1000
col begin_interval_time for a30
col end_interval_time for a30
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc
/


define rnum=20

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER

exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;


col MODULE for a20
set lines 1000 pages 1000
select * from (
select sql_id, module,
sum(ELAPSED_TIME_DELTA)/1000000 "Elapsed Time(s)",
sum(CPU_TIME_DELTA)/1000000 "CPU Time(s)",
sum(executions_delta) "Executions",
sum(ROWS_PROCESSED_DELTA) rows1,
sum(BUFFER_GETS_DELTA) "Buffer Gets",
sum(DISK_READS_DELTA) "Physical Reads",
sum(iowait_delta)/1000000 "IO Wait",
sum(ccwait_delta)/1000000 cc_wait,
sum(apwait_delta)/1000000 ap_wait,
sum(clwait_delta)/1000000 cl_wait,
sum(BUFFER_GETS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) gets_per_row,
sum(DISK_READS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) prds_per_row,
sum(BUFFER_GETS_DELTA)/decode(sum(executions_delta), 0, 1, sum(executions_delta)) gets_per_exec
from dba_hist_sqlstat
where snap_id between :bid and :eid
group by sql_id, module
order by 3 desc
) where rownum <= &rnum
/


===




 col sql_text for a50 wrap
select * from (
  SELECT
    m.*, TO_CHAR(dbms_lob.substr(v.sql_text, 3900)) SQL_Text
  FROM (
    select distinct
        snap_id,
        sql_id,
        EXECUTIONS_DELTA,
        trunc(max(ELAPSED_TIME_DELTA)OVER(PARTITION BY snap_id, sql_id),0) max_elapsed,
        trunc(max(cpu_time_delta)OVER(PARTITION BY snap_id, sql_id),0) max_cpu
    from   dba_hist_sqlstat t
    WHERE   t.snap_id IN (SELECT MAX(snap_id) FROM dba_hist_sqlstat)
  ) M,dba_hist_sqltext v
  where 1=1
    and v.sql_id(+)=m.sql_id 
    and v.SQL_TEXT not like '%v$%'
  order by max_elapsed desc
) where rownum < 10
;






-- associate sql execution user 


col USERNAME  for a20
 col sql_text for a50 wrap
select * from ( 
  select 
    sqt.sql_id, sqt.max_exec, sqt.max_elapsed, su.username, 
    TO_CHAR(dbms_lob.substr(st.sql_text, 3900)) sql_text 
  from 
    (select 
      sql_id, 
      min(snap_id) snap_id, 
      max(executions_delta) max_exec, 
      max(cpu_time_delta) max_cpu, 
      NVL((MAX(elapsed_time_delta) / 1000000), to_number(null)) max_elapsed 
    from dba_hist_sqlstat 
    where 1=1
--and module = 'XXXXX' -- filter a certain The sql statement executed by the program 
    group by sql_id) sqt, dba_hist_sqltext st, 
    (SELECT sql_id, parsing_schema_name username 
      FROM (
        SELECT t.sql_id,t.parsing_schema_name,row_number() over(partition by t.sql_id order by t.snap_id asc) rn
        FROM dba_hist_sqlstat t
        WHERE 1=1
--and module ='XXXXX'
     ) 
      WHERE rn = 1) su
  where
    st.sql_id(+) = sqt.sql_id and su.sql_id(+) = sqt.sql_id
  order by nvl(sqt.max_elapsed, -1) desc, sqt.sql_id
) where rownum <= 10;






---total duration of a sql


col sql_text for a50 wrap
select * from (
  select
    sqt.sql_id,
    sqt.exec Executions,
    nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
    nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
    decode(sqt.exec,   0, to_number(null), (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
    TO_CHAR(dbms_lob.substr(st.sql_text, 3900)) 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
    group by    sql_id) sqt, dba_hist_sqltext st
  where    st.sql_id(+) = sqt.sql_id and st.sql_text not like '%v$%'
  order by  nvl(sqt.elap, -1) desc, sqt.sql_id
) where rownum < 10;





 
 col sql_text for a50 wrap
 select
  sql_id,
  executions,
  elapsed_time,
  cpu_time,
  (elapsed_time / executions ) Elap_per_Exec,
  TO_CHAR(dbms_lob.substr(sql_fulltext, 3900)) SQL_Text
from
  (select
    sql_id,
    child_number,
    sql_text,
    elapsed_time,
    cpu_time,
    disk_reads,
    sql_fulltext,
    executions,
    rank () over(order by elapsed_time desc) as sql_rank
  from
    gv$sql where sql_fulltext not like '%v$%')
where
  sql_rank < 20;
  
  
  
 
 set linesize 800
 SELECT  
       A.SQL_ID,
       A.SQL_TEXT, 
       A.COMMAND_TYPE, --> IF NEEDED
       B.PLAN_HASH_VALUE,
       B.ID,
       B.OPERATION,
       B.OPTIONS,
       B.OBJECT_OWNER,
       B.OBJECT_NAME,
       B.OBJECT_TYPE,
       B.OPTIMIZER,
       B.PARTITION_START,
       B.PARTITION_STOP,
       B.PARTITION_ID
FROM   DBA_HIST_SQLTEXT A, DBA_HIST_SQL_PLAN B
WHERE  A.DBID = B.DBID
AND    A.SQL_ID = B.SQL_ID
--AND    A.SQL_ID IN('xxxxxxx')  ----!!!!
and    exists
       (select 'e' 
        from  dba_hist_sqlstat c 
        where a.dbid = c.dbid 
        and   a.sql_id = c.sql_id 
        and   b.sql_id = c.sql_id
        and   b.plan_hash_value <> c.plan_hash_value
       -- and   parsing_schema_name  in('SCOTT')
   )
ORDER BY A.DBID, A.SQL_ID, B.PLAN_HASH_VALUE, B.ID;



=====


---ash_top !!!!!!!!!!!!!!!

set linesize 300 pagesize 300

define 1=100  ----100 sec !!!

set verify off

break on sql_id

with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from v$active_session_history
 where sample_time>systimestamp-numtodsinterval(&1,'second')
 )
select sql_id,decode(session_state,'WAITING',event,'CPU') event,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by sql_id,event,b.samples,b.deltaT,session_state
--having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=1
order by 1,2,3 desc nulls last;



col USERNAME for a20
clear breaks
break on username

with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>systimestamp-numtodsinterval(&1,'second')
 )
select (select us.name from sys.user$ us where us.user#=user_id)||case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by user_id,program,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;

clear breaks
break on session_id

with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>systimestamp-numtodsinterval(&1,'second')
 )
select session_id,
       sql_id,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by session_id,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;

clear breaks


====






According to the execution time check sql 



set linesize 700 pagesize 500

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;

set numf 9999999999999

select 
      s.con_id
,s.sql_id
, elapsed_time/1000000   elapsed_time
, cpu_time/1000000       cpu_time
, iowait_time/1000000     iowait_time
, gets
, reads
, rws
, clwait_time/1000000     clwait_time
, execs
, st.sql_text sqt
, elapsed_time/1000000 /decode(execs,0,null,execs)          elpe
from
(select * from
( select 
        con_id
        ,sql_id
, sum(executions_delta)      execs
, sum(buffer_gets_delta)     gets
, sum(disk_reads_delta)      reads
, sum(rows_processed_delta)  rws
, sum(cpu_time_delta)        cpu_time
, sum(elapsed_time_delta)         elapsed_time
, sum(clwait_delta)         clwait_time
, sum(iowait_delta)         iowait_time
from dba_hist_sqlstat
where snap_id  > :bid
and snap_id <= :eid
        and  PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
group by con_id,sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by elapsed_time desc, sql_id;



Prompt By CPU 

select 
s.con_id
,s.sql_id
, cpu_time/1000000       cpu_time
, elapsed_time/1000000   elapsed_time
, iowait_time/1000000    iowait_time
, gets
, reads
, rws
, clwait_time/1000000    clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, '             ' nl
, cpu_time/1000000/decode(execs,0,null,execs)           cppe
, elapsed_time/1000000/decode(execs,0,null,execs)       elpe
, iowait_time/1000000/decode(execs,0,null,execs)        iope
, gets/decode(execs,0,null,execs)                      bpe
, reads/decode(execs,0,null,execs)                     rpe
, rws/decode(execs,0,null,execs)                       rwpe
, clwait_time/1000000/decode(execs,0,null,execs)        clpe
, '          '    ep
--, st.sql_text  sqtn
from
(select * from
( select 
con_id
,sql_id
, sum(executions_delta)      execs
, sum(buffer_gets_delta)     gets
, sum(disk_reads_delta)      reads
, sum(rows_processed_delta)  rws
, sum(cpu_time_delta)        cpu_time
, sum(elapsed_time_delta)         elapsed_time
, sum(iowait_delta)         iowait_time
, sum(clwait_delta)         clwait_time
from dba_hist_sqlstat
where snap_id  > :bid
and snap_id <= :eid
       and  PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
group by con_id, sql_id
order by sum(cpu_time_delta) desc)
where rownum <= 10 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by cpu_time desc, sql_id;






Prompt  Sort by I/O 

select 
s.con_id
,s.sql_id
, iowait_time/1000000    iowait_time
, elapsed_time/1000000   elapsed_time
, cpu_time/1000000       cpu_time
, gets
, reads
, rws
, clwait_time/1000000    clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, '             ' nl
, iowait_time/1000000/decode(execs,0,null,execs)        iope
, elapsed_time/1000000/decode(execs,0,null,execs)       elpe
, cpu_time/1000000/decode(execs,0,null,execs)           cppe
, gets/decode(execs,0,null,execs)                      bpe
, reads/decode(execs,0,null,execs)                     rpe
, rws/decode(execs,0,null,execs)                       rwpe
, clwait_time/1000000/decode(execs,0,null,execs)        clpe
, '          '    ep
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50)   sqtn
from
(select * from
( select con_id,sql_id
, sum(executions_delta)      execs
, sum(buffer_gets_delta)     gets
, sum(disk_reads_delta)      reads
, sum(rows_processed_delta)  rws
, sum(cpu_time_delta)        cpu_time
, sum(elapsed_time_delta)         elapsed_time
, sum(iowait_delta)         iowait_time
, sum(clwait_delta)         clwait_time
from dba_hist_sqlstat
where snap_id  > :bid
and snap_id <= :eid
       and  PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
group by con_id,sql_id
order by sum(iowait_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by iowait_time desc, reads desc, sql_id;




Prompt -- press gets 

select 
s.con_id
,s.sql_id
, gets
, reads
, elapsed_time/1000000   elapsed_time
, cpu_time/1000000       cpu_time
, iowait_time/1000000    iowait_time
, rws
, clwait_time/1000000    clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, '             ' nl
, gets/decode(execs,0,null,execs)                      bpe
, reads/decode(execs,0,null,execs)                     rpe
, elapsed_time/1000000/decode(execs,0,null,execs)       elpe
, cpu_time/1000000/decode(execs,0,null,execs)           cppe
, iowait_time/1000000/decode(execs,0,null,execs)        iope
, rws/decode(execs,0,null,execs)                       rwpe
, clwait_time/1000000/decode(execs,0,null,execs)        clpe
, '          '    ep
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50)   sqtn
from
(select * from
( select 
         con_id
        ,sql_id
, sum(executions_delta)      execs
, sum(buffer_gets_delta)     gets
, sum(disk_reads_delta)      reads
, sum(rows_processed_delta)  rws
, sum(cpu_time_delta)        cpu_time
, sum(elapsed_time_delta)         elapsed_time
, sum(iowait_delta)         iowait_time
, sum(clwait_delta)         clwait_time
from dba_hist_sqlstat
where snap_id  > :bid
and snap_id <= :eid
and  PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
group by con_id,sql_id
order by sum(buffer_gets_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by gets desc, cpu_time desc, sql_id;



Prompt --By execution times 

select 
s.con_id
,s.sql_id
, execs
, elapsed_time/1000000   elapsed_time
, cpu_time/1000000       cpu_time
, iowait_time/1000000    iowait_time
, gets
, reads
, rws
, clwait_time/1000000    clwait_time
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, '             ' nl
, '            '    ep
, elapsed_time/1000000/decode(execs,0,null,execs)       elpe
, cpu_time/1000000/decode(execs,0,null,execs)           cppe
, iowait_time/1000000/decode(execs,0,null,execs)  iope
, gets/decode(execs,0,null,execs)                      bpe
, reads/decode(execs,0,null,execs)                     rpe
, rws/decode(execs,0,null,execs)                       rwpe
, clwait_time/1000000/decode(execs,0,null,execs)  clpe
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50)   sqtn
from
(select * from
( select 
        con_id
        ,sql_id
, sum(executions_delta)      execs
, sum(buffer_gets_delta)     gets
, sum(disk_reads_delta)      reads
, sum(rows_processed_delta)  rws
, sum(cpu_time_delta)        cpu_time
, sum(elapsed_time_delta)    elapsed_time
, sum(iowait_delta)         iowait_time
, sum(clwait_delta)         clwait_time
from dba_hist_sqlstat
where snap_id  > :bid
and snap_id <= :eid
        and  PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
group by con_id,sql_id
order by sum(executions_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by execs desc, sql_id;







create or replace view slow_sql_view as


select  to_char(sysdate-1/24,'yyyy-mm-dd hh24') as snapshot_time,
        v_1.sql_id,
        v_1.elapsed_time,
        v_1.cpu_time,
        v_1.iowait_time,
        v_1.gets,
        v_1.reads,
        v_1.rws,
        v_1.clwait_time,
        v_1.execs,
        v_1.elpe,
        nvl(v_2.machine,'null') as machine,
        nvl(v_2.username,'null') as username,
        to_char(substr(v_1.sqt,1,3000)) as sql
from
  (select s.sql_id,
          round(elapsed_time / 1000000,2) elapsed_time,
          round(cpu_time / 1000000,2) cpu_time,
          round(iowait_time / 1000000,2) iowait_time,
          gets,
          reads,
          rws,
          round(clwait_time / 1000000,2) clwait_time,
          execs,
          st.sql_text sqt,
          round(elapsed_time / 1000000 / decode(execs, 0, null, execs),2) elpe
     from (select *
             from (select sql_id,
                          sum(executions_delta) execs,
                          sum(buffer_gets_delta) gets,
                          sum(disk_reads_delta) reads,
                          sum(rows_processed_delta) rws,
                          sum(cpu_time_delta) cpu_time,
                          sum(elapsed_time_delta) elapsed_time,
                          sum(clwait_delta) clwait_time,
                          sum(iowait_delta) iowait_time
                     from dba_hist_sqlstat
                    where snap_id =(select max(snap_id) from dba_hist_snapshot)
                    group by sql_id
                    order by sum(elapsed_time_delta) desc)
            where rownum <= 20) s,
          dba_hist_sqltext  st
    where st.sql_id = s.sql_id) v_1
    left join
  (select distinct a.sql_id, a.machine, b.username
     from dba_hist_active_sess_history a
     left join dba_users b
       on a.user_id = b.user_id
    where a.snap_id = (select max(snap_id) from dba_hist_snapshot)) v_2
      on v_1.sql_id = v_2.sql_id
      where v_1.elpe >=1  
      order by elpe desc
;


====


set linesize 700

col elaexe              for 99999.99990
col cpu_time            for 99999.99990
col app_wait            for 99999.99990
col plsql_t             for 99999.99990
col java_exec_t         for 99999.99990
col opt_mode            for a12
col P_schema            for a20
col sql_text            for a100 wrap


/*
prompt  2. INSERT;
prompt  3. SELECT;
prompt  6. UPDATE;
prompt  7. DELETE;
prompt 47. PL/SQL EXECUTE;
*/

--accept excludelist prompt "Enter command types for exclude: ";
with top_sql_ids as (
         select--+ no_merge
            sql_id
         from
            (
            select a.sql_id,a.elapsed_time
            from gv$sqlarea a 
            where 1=1
--and a.command_type not in ('INSERT%')
            order by a.elapsed_time desc
            )
         where rownum<=10
)
select
    s.sql_id
   ,s.elapsed_time/1e6                                                 "Elapsed(sec)"
   ,s.executions             
   ,decode(s.executions,0,0, s.ROWS_PROCESSED           /s.executions)  rows_per_exec
   ,decode(s.executions,0,0, s.elapsed_time/1e6/s.executions)           elaexe
   ,decode(s.executions,0,0, s.CPU_TIME/1e6/s.executions)               cpu_time
   ,decode(s.executions,0,0, s.APPLICATION_WAIT_TIME/1e6/s.executions)  app_wait
   ,decode(s.executions,0,0, s.CONCURRENCY_WAIT_TIME/1e6/s.executions)  concurrency
   ,decode(s.executions,0,0, s.USER_IO_WAIT_TIME    /1e6/s.executions)  io_wait
   ,decode(s.executions,0,0, s.PLSQL_EXEC_TIME      /1e6/s.executions)  plsql_t
   ,decode(s.executions,0,0, s.java_exec_time       /1e6/s.executions)  java_exec_t
   ,s.OPTIMIZER_MODE                                                    opt_mode
   ,s.OPTIMIZER_COST                                                    cost
   ,s.OPTIMIZER_ENV_HASH_VALUE                                          env_hash
   ,s.PARSING_SCHEMA_NAME                                               P_schema
   ,substr(s.sql_text,1,150) as sql_text
from top_sql_ids ids
    ,gv$sqlarea s
where ids.sql_id=s.sql_id
/
col elaexe       clear
col cpu_time     clear
col app_wait     clear
col plsql_t      clear
col java_exec_t  clear
col opt_mode     clear
col P_schema     clear
col sql_text     clear



SQL_ID        Elapsed(sec) EXECUTIONS ROWS_PER_EXEC       ELAEXE     CPU_TIME     APP_WAIT CONCURRENCY    IO_WAIT      PLSQL_T  JAVA_EXEC_T OPT_MODE           COST   ENV_HASH P_SCHEMA         SQL_TEXT
------------- ------------ ---------- ------------- ------------ ------------ ------------ ----------- ---------- ------------ ------------ ------------ ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------

6mcpb06rctk0x   9488.43854          2             0   4744.21927   4701.82623       .00365     .002752   .9720005       .11989       .00000 CHOOSE                0 3608716814 SYS 




set lines 1000 pages 200
col sid for 9999 
col serial for 999999
col status for a15
col username         for a10 
col sql_text         for a80
col module for a30
col program         for a70
col SQL_EXEC_START         for a20
col kill for a16

SELECT * FROM        (SELECT ''''||sid ||','|| session_serial#||',@'||inst_id ||''''  as kill,status,username,sql_id,SQL_PLAN_HASH_VALUE,     MODULE,program,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
         ROUND(cpu_time    /1000000)                      AS "CPU (s)",
         substr(sql_text,1,70) sql_text
       FROM gv$sql_monitor 
   where 1=1
   and status='EXECUTING' 
   and module not like '%emagent%' 
       ORDER BY sql_exec_start  desc
       );


===


set linesize 300
SELECT * FROM
(SELECT dhs.sql_id,
        ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs,
        ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs,
        SUM(dhs.disk_reads_delta) disk_reads,
        SUM(dhs.buffer_gets_delta) buffer_gets,
        SUM(dhs.px_servers_execs_delta) px_server_execs,
        SUM(dhs.rows_processed_delta) rows_processed,
        SUM(dhs.executions_delta) executions,
        ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs,
        ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs,
        ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs,
        ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs
 FROM dba_hist_sqlstat  dhs,      gv$database  d,      gv$instance i
 WHERE dhs.dbid = d.dbid
 AND   dhs.instance_number = i.instance_number
-- AND   dhs.snap_id > 10875 AND dhs.snap_id <= 10876
 GROUP BY dhs.sql_id 
ORDER BY 2 DESC
)
WHERE ROWNUM <= 10;

   


============




VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;




col PLAN_TABLE_OUTPUT for a200
select
  s.elapsed_time_delta,  s.buffer_gets_delta,  s.disk_reads_delta,  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
from   dba_hist_sqltext t,  dba_hist_sqlstat s
where 1=1
  and t.dbid = s.dbid
  and t.sql_id = s.sql_id
  and s.snap_id between :BgnSnap and :EndSnap
 -- and t.sql_text like 'select /*+ Anuj */%'
;


======================



set linesize 500 pagesize 500
col SQL for a25
SELECT
    SQL, SQL_ID, CPU_SECONDS_FORM CPU, ELAPSED_SECONDS_FORM ELAPSED, DISK_READS, BUFFER_GETS, EXECUTIONS_FORM EXECS, MODULE, LAST_ACTIVE_TIME_FORM
FROM
    (  SELECT   D.*  ,ROWNUM ROW#
        FROM        (      SELECT
                    D.*       FROM
                    (      SELECT
                            substr(SQL_TEXT, 1, 25) AS SQL
                            ,S.CPU_TIME / 1000000 AS CPU_SECONDS
                                ,CASE WHEN
                                    S.CPU_TIME < 1000
                                THEN
                                    '< 1 ms'
                                WHEN
                                    S.CPU_TIME < 1000000
                                THEN
                                    TO_CHAR(ROUND(S.CPU_TIME / 1000,1) )       ||  ' ms'
                                WHEN
                                    S.CPU_TIME < 60000000
                                THEN
                                    TO_CHAR(ROUND(S.CPU_TIME / 1000000,1) )    ||  ' s'
                                ELSE
                                    TO_CHAR(ROUND(S.CPU_TIME / 60000000,1) )   ||  ' m'
                                END
                            AS CPU_SECONDS_FORM
                            ,DECODE(L.MAX_CPU_TIME,0,0,S.CPU_TIME / L.MAX_CPU_TIME) AS CPU_SECONDS_PROP
                            ,S.ELAPSED_TIME / 1000000 AS ELAPSED_SECONDS
                                ,CASE WHEN
                                    S.ELAPSED_TIME < 1000
                                THEN
                                    '< 1 ms'
                                WHEN
                                    S.ELAPSED_TIME < 1000000
                                THEN
                                    TO_CHAR(ROUND(S.ELAPSED_TIME / 1000,1) )      ||  ' ms'
                                WHEN
                                    S.ELAPSED_TIME < 60000000
                                THEN
                                    TO_CHAR(ROUND(S.ELAPSED_TIME / 1000000,1) )   ||  ' s'
                                ELSE
                                    TO_CHAR(ROUND(S.ELAPSED_TIME / 60000000,1) )  ||  ' m'
                                END
                            AS ELAPSED_SECONDS_FORM
                            ,DECODE(L.MAX_ELAPSED_TIME,0,0,S.ELAPSED_TIME / L.MAX_ELAPSED_TIME) AS ELAPSED_SECONDS_PROP
                            ,S.DISK_READS AS DISK_READS
                                ,CASE WHEN
                                    S.DISK_READS < 1000
                                THEN
                                    TO_CHAR(S.DISK_READS)
                                WHEN
                                    S.DISK_READS < 1000000
                                THEN
                                    TO_CHAR(ROUND(S.DISK_READS / 1000,1) )      ||  'K'
                                WHEN
                                    S.DISK_READS < 1000000000
                                THEN
                                    TO_CHAR(ROUND(S.DISK_READS / 1000000,1) )   ||  'M'
                                ELSE
                                    TO_CHAR(ROUND(S.DISK_READS / 1000000000,1) ) ||  'G'
                                END
                            AS DISK_READS_FORM
                            ,DECODE(L.MAX_DISK_READS,0,0,S.DISK_READS / L.MAX_DISK_READS) AS DISK_READS_PROP
                            ,S.BUFFER_GETS AS BUFFER_GETS
                                ,CASE WHEN
                                    S.BUFFER_GETS < 1000
                                THEN
                                    TO_CHAR(S.BUFFER_GETS)
                                WHEN
                                    S.BUFFER_GETS < 1000000
                                THEN
                                    TO_CHAR(ROUND(S.BUFFER_GETS / 1000,1) )    ||  'K'
                                WHEN
                                    S.BUFFER_GETS < 1000000000
                                THEN
                                    TO_CHAR(ROUND(S.BUFFER_GETS / 1000000,1) )    ||  'M'
                                ELSE
                                    TO_CHAR(ROUND(S.BUFFER_GETS / 1000000000,1) )  ||  'G'
                                END
                            AS BUFFER_GETS_FORM
                            ,DECODE(L.MAX_BUFFER_GETS,0,0,S.BUFFER_GETS / L.MAX_BUFFER_GETS) AS BUFFER_GETS_PROP
                            ,S.EXECUTIONS AS EXECUTIONS
                                ,CASE WHEN
                                    S.EXECUTIONS < 1000
                                THEN
                                    TO_CHAR(S.EXECUTIONS)
                                WHEN
                                    S.EXECUTIONS < 1000000
                                THEN
                                    TO_CHAR(ROUND(S.EXECUTIONS / 1000,1) )       ||  'K'
                                WHEN
                                    S.EXECUTIONS < 1000000000
                                THEN
                                    TO_CHAR(ROUND(S.EXECUTIONS / 1000000,1) )    ||  'M'
                                ELSE
                                    TO_CHAR(ROUND(S.EXECUTIONS / 1000000000,1) ) ||  'G'
                                END
                            AS EXECUTIONS_FORM
                            ,DECODE(L.MAX_EXECUTIONS,0,0,S.EXECUTIONS / L.MAX_EXECUTIONS) AS EXECUTIONS_PROP
                            ,DECODE(S.MODULE,NULL,' ',S.MODULE) AS MODULE
                            ,S.LAST_ACTIVE_TIME AS LAST_ACTIVE_TIME
                            ,DECODE(S.LAST_ACTIVE_TIME,NULL,' ',TO_CHAR(S.LAST_ACTIVE_TIME,'DD-Mon-YYYY HH24:MI:SS') ) AS LAST_ACTIVE_TIME_FORM
                            ,S.SQL_ID AS SQL_ID
                            ,S.CHILD_NUMBER AS CHILD_NUMBER
                            ,S.INST_ID AS INST_ID
                        FROM
                            GV$SQL S
                            ,(
                                SELECT
                                    MAX(CPU_TIME) AS MAX_CPU_TIME
                                    ,MAX(ELAPSED_TIME) AS MAX_ELAPSED_TIME
                                    ,MAX(DISK_READS) AS MAX_DISK_READS
                                    ,MAX(BUFFER_GETS) AS MAX_BUFFER_GETS
                                    ,MAX(EXECUTIONS) AS MAX_EXECUTIONS
                                FROM
                                    GV$SQL
                            ) L
                    ) D
                ORDER BY
                    CPU_SECONDS_PROP DESC
                    ,SQL
                    ,DISK_READS_PROP
                    ,BUFFER_GETS_PROP
                    ,EXECUTIONS_PROP
                    ,ELAPSED_SECONDS_PROP
                    ,MODULE
                    ,LAST_ACTIVE_TIME
            ) D
    ) D
WHERE   ROW# >= 1
AND  ROW# <= 50


====




VARIABLE bid NUMBER
VARIABLE eid NUMBER

VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;

define DB_BLOCK_SIZE=8192
define SQL_TOP_N = 10
define DBID=''


col MODULE for a20
SELECT * FROM(
SELECT substr(REGEXP_REPLACE(s.module,'^(.+?)@.+$','\1'),1,30) module,s.action,s.sql_id,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,
PARSING_SCHEMA_NAME,
DENSE_RANK() OVER      (ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER      (ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER      (ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER      (ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank,
  sum(EXECUTIONS_DELTA) execs,
sum(ELAPSED_TIME_DELTA) elap,
sum(BUFFER_GETS_DELTA) log_reads,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
      count(distinct plan_hash_value) plan_count,
  sum(px_servers_execs_delta) px_servers_execs
 FROM dba_hist_sqlstat s,dba_hist_sqltext t
 WHERE 1=1
 --and s.dbid = &DBID
  AND s.snap_id BETWEEN :bid and :eid
   AND s.dbid = t.dbid
  AND s.sql_id = t.sql_id
  AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN')
  GROUP BY s.module,s.action,s.sql_id,t.command_type,PARSING_SCHEMA_NAME)
WHERE elap_rank <= &SQL_TOP_N
 OR phys_reads_rank <= &SQL_TOP_N
 or log_reads_rank <= &SQL_TOP_N
 or exec_rank <= &SQL_TOP_N
 order by elap_rank asc nulls last;




====



select INST_ID,
     (cpu_time/1000000) "CPU_Seconds",
     disk_reads "Disk_Reads",
     buffer_gets "Buffer_Gets",
     executions "Executions",
     case when rows_processed = 0 then null
          else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
          end "Buffer_gets/rows_proc",
     round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
     (elapsed_time/1000000) "Elapsed_Seconds",
     round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
     substr(sql_text,1,50) "SQL",
     module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
  (select   sql_id
  ,  CASE  WHEN session_state = 'ON CPU' THEN 'CPU'      
           WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
           ELSE 'WAIT' END state           
    from gv$active_session_history            
    where   session_type IN ( 'FOREGROUND')       
    and sample_time  between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
    group by sql_id, state),      ranked_sqls AS
(select sql_id,  sum(occur) sql_occur  , rank () over (order by sum(occur)desc) xrank
from sql_class          
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
and rs.xrank <15 ))


===



 
 
 select  con_id,top_level_Sql_id, module, action, sum(10) ash_Secs from dba_hist_Active_Sess_history h
where sql_id != top_level_sql_id
group by con_id,top_level_sql_id, module, action
order by ash_Secs desc; 
/




set linesize 300 

define sql_id='fhf8upax5cxsz'
col SQL_TEXT for a100 wrap
with x as (
select sql_id, sum(10) ash_Secs
from dba_hist_active_sess_history
where top_level_sql_id = '&sql_id'
group by sql_id
)
select x.* 
, (select sql_text from dba_hist_sqltext where sql_id = x.sql_id and rownum = 1) sql_text
from x order by ash_Secs desc;
 
 



 
-- Summary of SQL plans in AWR DBA_HIST_ACTIVE_SESS_HISTORY data

define sql_id='34cd4y8mbqvsk'
set linesize 500 
col SQL_TEXT for a50 wrap

WITH src AS (
SELECT session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, inst_id
, MAX(sql_plan_hash_value) AS sql_plan_hash_value
, sql_exec_start
, CAST(max(sample_time) AS DATE) AS last_sample_time
, CAST(min(sample_time) AS DATE) AS first_sample_time
FROM gv$active_session_history
WHERE 1=1
and sql_id IN ('&sql_id')
GROUP BY session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, inst_id
, sql_exec_start
)
SELECT session_id
, session_serial#
, sql_exec_id
, src.sql_id
, sql_plan_hash_value
, sql_exec_start
, first_sample_time
, last_sample_time
, ROUND((last_sample_time-sql_exec_start)*86400) AS durn_s
, CAST(SUBSTR(sql_text,1,100) AS VARCHAR2(200)) AS sql_text
FROM src
LEFT JOIN gv$sql sql
ON src.sql_id = sql.sql_id
AND src.sql_child_number = sql.child_number
AND src.inst_id = sql.inst_id
ORDER BY sql_exec_start;






define sql_id='34cd4y8mbqvsk'
SELECT sql_id
, TO_CHAR(sample_time,'yyyy-mm-dd') AS exec_day
, TO_CHAR(MIN(sample_time),'hh24:mi') AS first_exec
, TO_CHAR(MAX(sample_time),'hh24:mi') AS last_exec
, sql_plan_hash_value
, COUNT(*) AS nbr_samples
, COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id) AS execs
, ROUND(10*COUNT(*)/NULLIF(COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id),0)) AS avg_durn_s
, ROUND(SUM(delta_read_io_bytes)/1024/1024/1024/NULLIF(COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id),0),2) avg_read_GB
, SUM(delta_read_io_requests) read_req
, SUM(delta_write_io_requests) write_req
, ROUND(SUM(delta_read_io_bytes)/1024/1024/1024,2) read_GB
, ROUND(SUM(delta_write_io_bytes)/1024/1024/1024,2) write_GB
, ROUND(SUM(delta_interconnect_io_bytes)/1024/1024/1024,2) interconn_GB
, ROUND(SUM(
CASE WHEN tm_delta_cpu_time <= 64*tm_delta_time THEN tm_delta_cpu_time ELSE 0 END/NULLIF(tm_delta_time,0)),2) cpu_s
, ROUND(SUM(
CASE WHEN tm_delta_db_time <= 64*tm_delta_time THEN tm_delta_db_time ELSE 0 END/NULLIF(tm_delta_time,0)),2) dbtime_s
FROM dba_hist_active_sess_history
WHERE sql_id = '&sql_id'
--and top_level_sql_id = '&sql_id'
--AND sql_plan_hash_value <> 0
GROUP BY sql_id
, sql_plan_hash_value
, TO_CHAR(sample_time,'yyyy-mm-dd')
ORDER BY 2,3,1;


SQL_ID        EXEC_DAY   FIRST LAST_ SQL_PLAN_HASH_VALUE NBR_SAMPLES      EXECS AVG_DURN_S AVG_READ_GB   READ_REQ  WRITE_REQ    READ_GB   WRITE_GB INTERCONN_GB      CPU_S   DBTIME_S
------------- ---------- ----- ----- ------------------- ----------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
34cd4y8mbqvsk 2022-11-07 23:29 23:59                   0           2          2         10           0         16                     0                       0        .25        .47
34cd4y8mbqvsk 2022-11-08 01:00 02:41           693498460          34         32         11           0         49                     0                       0      33.35      33.66
34cd4y8mbqvsk 2022-11-08 01:00 01:00          1320773586           1          1         10           0         18                     0                       0        .61        .68
34cd4y8mbqvsk 2022-11-09 00:42 00:42           834029895           1          1         10           0          7                     0                       0        .45        .65
34cd4y8mbqvsk 2022-11-09 01:00 03:43           693498460          67         64         10           0        184                     0                       0      62.56      63.23



— List of individual executions in DBA_HIST_ACTIVE_SESS_HISTORY

define nbr_days_history=1

define sql_id='34cd4y8mbqvsk'
WITH src AS (
SELECT session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, instance_number
, MAX(sql_plan_hash_value) AS sql_plan_hash_value
, sql_exec_start
, CAST(max(sample_time) AS DATE) AS last_sample_time
, CAST(min(sample_time) AS DATE) AS first_sample_time
, SUM(delta_read_io_bytes) read_bytes
, SUM(tm_delta_cpu_time) cpu_time
FROM dba_hist_active_sess_history
WHERE sql_id IN ('&sql_id')
--AND sample_time >= sysdate – &nbr_days_history.
AND sql_exec_id IS NOT NULL
GROUP BY session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, instance_number
, sql_exec_start
)
SELECT session_id
, session_serial#
, instance_number
, sql_exec_id
, src.sql_id
, sql_plan_hash_value
, sql_exec_start
, first_sample_time
, last_sample_time
, ROUND((last_sample_time-sql_exec_start)*86400) AS durn_s
, ROUND(read_bytes/1024/1024,2) read_MB
, ROUND(src.cpu_time/1e6,2) cpu_s
, CAST(SUBSTR(sql_text,1,100) AS VARCHAR2(200)) AS sql_text
FROM src
LEFT JOIN gv$sql sql
ON src.sql_id = sql.sql_id
AND src.sql_child_number = sql.child_number
AND src.instance_number = sql.inst_id
ORDER BY sql_exec_start, first_sample_time;



SESSION_ID SESSION_SERIAL# INSTANCE_NUMBER SQL_EXEC_ID SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_START FIRST_SAMPLE_T LAST_SAMPLE_TI     DURN_S    READ_MB      CPU_S SQL_TEXT
---------- --------------- --------------- ----------- ------------- ------------------- -------------- -------------- -------------- ---------- ---------- ---------- --------------------------------------------------
       394           27416               1    16873452 34cd4y8mbqvsk           693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00          4                  9.59
       394           27416               1    16873453 34cd4y8mbqvsk           693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00          8        .01       9.94
       394           27416               1    16873454 34cd4y8mbqvsk           693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00          6                  9.95
  


— Wait events and objects for a single execution from DBA_HIST_ACTIVE_SESS_HISTORY



set linesize 500 pagesize 300
define sql_id='34cd4y8mbqvsk'
define sid=394
define serial=27416
define sql_exec_id=16873452
define instance_number=1
col FIRST_SAMPLE for a27                                                               
col LAST_SAMPLE  for a27   

col OWNER for a15
col OBJECT_NAME for a20
col EVENT for a20
SELECT sql_id
, sql_plan_hash_value
, sql_exec_id
, NVL(event,session_state) AS event
, sql_plan_line_id
, object_type
, owner
, current_obj#
, object_name
, COUNT(DISTINCT subobject_name) nbr_sub_objects
, SUM(10) s_in_wait
, MIN(sample_time) first_sample
, MAX(sample_time) last_sample
FROM dba_hist_active_sess_history
LEFT JOIN dba_objects
ON current_obj# = object_id
WHERE sql_id = '&sql_id'
--AND sql_exec_id = '&sql_exec_id'
AND instance_number = '&instance_number'
AND session_id = '&sid'
AND session_serial# = '&serial'
GROUP BY sql_id
, sql_plan_hash_value
, sql_exec_id
, NVL(event,session_state)
, sql_plan_line_id
, object_type
, owner
, current_obj#
, object_name
ORDER BY s_in_wait DESC
, event;



SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_ID EVENT                SQL_PLAN_LINE_ID OBJECT_TYPE             OWNER           CURRENT_OBJ# OBJECT_NAME          NBR_SUB_OBJECTS  S_IN_WAIT FIRST_SAMPLE                LAST_SAMPLE
------------- ------------------- ----------- -------------------- ---------------- ----------------------- --------------- ------------ -------------------- --------------- ---------- --------------------------- ---------------------------
34cd4y8mbqvsk           693498460    16873459 ON CPU                            238 INDEX                   SYS                      830 I_LOBFRAG$_FRAGOBJ$                0         10 08-NOV-22 01.00.58.472 AM   08-NOV-22 01.00.58.472 AM
34cd4y8mbqvsk          1320773586             ON CPU                                                                                  -1                                    0         10 08-NOV-22 01.00.08.471 AM   08-NOV-22 01.00.08.471 AM
34cd4y8mbqvsk           693498460    16873454 ON CPU                            128                                                   -1                                    0         10 08-NOV-22 01.00.38.471 AM   08-NOV-22 01.00.38.471 AM
34cd4y8mbqvsk           693498460    16873453 ON CPU                            257                                                   -1                                    0         10 08-NOV-22 01.00.28.471 AM   08-NOV-22 01.00.28.471 AM
34cd4y8mbqvsk           693498460    16873459 ON CPU                            114 INDEX                   SYS                      830 I_LOBFRAG$_FRAGOBJ$                0         10 08-NOV-22 01.00.48.472 AM   08-NOV





— List of wait events per execution from GV$ACTIVE_SESSION_HISTORY

define sql_id='34cd4y8mbqvsk'
WITH t1 AS (
SELECT sql_id
, session_id
, session_serial#
, sql_exec_start
, sql_exec_id
, sql_plan_hash_value
, sql_plan_line_id
, sql_plan_operation
, sql_plan_options
, event
, COUNT(*) AS samples
FROM gv$active_session_history
WHERE sql_id='&sql_id'
GROUP BY sql_id
, session_id
, session_serial#
, sql_exec_start
, sql_exec_id
, sql_plan_hash_value
, sql_plan_line_id
, sql_plan_operation
, sql_plan_options
, event
)
SELECT * FROM t1
ORDER BY sql_exec_start DESC, samples DESC;

 
 
 
SQL_ID        SESSION_ID SESSION_SERIAL# SQL_EXEC_START SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION             SQL_PLAN_OPTIONS               EVENT                   SAMPLES
------------- ---------- --------------- -------------- ----------- ------------------- ---------------- ------------------------------ ------------------------------ -------------------- ----------
34cd4y8mbqvsk          5           34822                                     2593769849                                                                                                              2
34cd4y8mbqvsk          5           16271                                              0                                                                                                              2
34cd4y8mbqvsk        292           60379                                              0                                                                                                              2
34cd4y8mbqvsk        199           57266                                              0                                                                                                              2
34cd4y8mbqvsk        776           61802                                              0                                                                                                              2
34cd4y8mbqvsk        776           12672                                              0                                                                                                              2
34cd4y8mbqvsk          5           28388                                              0                                                                                                              2
34cd4y8mbqvsk        200           34854                                      665610596                                                                                                              1
34cd4y8mbqvsk        483           51658                                     1673451505                                                                                acknowledge over PGA          1
                                                                                                                                                                        limit


 
 ===
 

  
  set linesize 500
  
  VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

  set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BgnSnap;
v_end_snap_id number := :EndSnap;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
--,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id
and PARSING_SCHEMA_NAME Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
 )
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5 
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');

for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;

for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;

for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/


SQL=1fasdju62v6a7 was top SQL 1 times
snapshot = 115356 ** execs = 4 ** gets_per_exec = 71908924 ** rows_per_exec 12.5 ** ela_per_exec 188.85
snapshot = 115627 ** execs = 1 ** gets_per_exec = 149567 ** rows_per_exec 10 ** ela_per_exec .38
snapshot = 115627 ** execs = 2 ** gets_per_exec = 67338241.5 ** rows_per_exec 10 ** ela_per_exec 172.28
snapshot = 116005 ** execs = 1 ** gets_per_exec = 148350 ** rows_per_exec 10 ** ela_per_exec .43
snapshot = 116005 ** execs = 2 ** gets_per_exec = 67316735 ** rows_per_exec 10 ** ela_per_exec 174.86
snapshot = 116204 ** execs = 3 ** gets_per_exec = 76098606 ** rows_per_exec 16.67 ** ela_per_exec 197.58
snapshot = 116203 ** execs = 1 ** gets_per_exec = 149129 ** rows_per_exec 10 ** ela_per_exec .45
snapshot = 116203 ** execs = 6 ** gets_per_exec = 62077108.17 ** rows_per_exec 6.67 ** ela_per_exec 162.82
snapshot = 116788 ** execs = 1 ** gets_per_exec = 149540 ** rows_per_exec 10 ** ela_per_exec .42





  VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BgnSnap;
v_end_snap_id number := :EndSnap;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
--,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id)
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5 
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');

for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;

for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs
|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;

for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name
|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/






set linesize 400 pagesize 300


col GETS_SQL for a27                                             
col CPU_SQL  for a27                                                  
col ELAPSED_SQL   for a27 
col EXECUTIONS_SQL for a27

  VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

with awr_ranks as
(
select snap_id, sql_id,
                           buffer_gets_delta,
                           dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
                           cpu_time_delta,
                           dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
                           elapsed_time_delta,
                           dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank,
                           executions_delta,
                           dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
                     from sys.wrh$_sqlstat
), rank as
(
       select level rank from dual connect by level <= 5
)
select snap_id,
                           rank,
                           max(case gets_rank when rank then to_char(buffer_gets_delta)||': '||sql_id end) gets_sql,
                           max(case cpu_rank when rank then to_char(round(cpu_time_delta/1000000,2))||': '||sql_id end) cpu_sql,
                           max(case elapsed_rank when rank then to_char(round(elapsed_time_delta/1000000,2))||': '||sql_id end) elapsed_sql,
                           max(case executions_rank when rank then  to_char(executions_delta)||': '||sql_id end) executions_sql
              from awr_ranks, rank
              where snap_id between :BgnSnap and :EndSnap
              group by snap_id, rank
              order by snap_id, rank;


   SNAP_ID       RANK GETS_SQL                                                CPU_SQL                                                 ELAPSED_SQL                                          EXECUTIONS_SQL
---------- ---------- ------------------------------------------------------- ------------------------------------------------------- ------------------------------------------------------- -------------------------------------------------------
      4421          2 671550216: drq81bs3crpat                                1602.21: 9px0f4mv0tn0t                                  1606.38: 9px0f4mv0tn0t                               472475: 97ajm1fqw1bbu
      4421          3 660785900: ghvcahvbqmccz                                1506.73: 499ds2y15j9zf                                  1538.66: 499ds2y15j9zf                               471521: 41770y3h89crb
      4421          4 465180962: 57h83vb5f41ht                                1481.32: f0qkr7vnva09k                                  1522.38: 3swzhzyxqjfw3                               466932: 2jfqzrxhrm93b
      4421          5 410826590: 5gqv37qghnh9q                                1477.85: 0ksuvynpvkjm7                                  1520.09: f0qkr7vnva09k                               246681: ct1xj8wz8sb0j

15 rows selected.




define sql_id='1fasdju62v6a7'
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
col PARSING_SCHEMA_NAME for a20
set lines 200 pages 200
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b 
where b.snap_id=a.snap_id 
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id 
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , sql_id,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a 
where sql_id in('&sql_id')
order by snap_id, INSTANCE_NUMBER
;



=====




set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT buffer_gets, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
where 1=1
--and last_exec_start_time > sysdate -1
ORDER BY buffer_gets desc;




 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT disk_reads, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 10000000'))
ORDER BY disk_reads desc;





 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
cl sql_text for a50 wrap
SELECT cpu_time / 1000000 as cpu_time_secs, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY cpu_time desc;



 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT elapsed_time / 1000000 as elapsed_time_secs, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY elapsed_time desc;



 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY executions desc;



 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT direct_writes, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 100000'))
ORDER BY direct_writes desc;




 set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE  for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT rows_processed, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 1000000'))
ORDER BY rows_processed desc;






set numf 99999999999999999
col PARSING_SCHEMA_NAME for a20
col sql_text for a20
SELECT  sql_id ,substr(sql_text,1,20) sql_text,disk_reads ,cpu_time ,elapsed_time ,buffer_gets ,parsing_schema_name 
FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(  basic_filter => 'parsing_schema_name <> ''SYS''' ,ranking_measure1 => 'cpu_time' ,result_limit => 10 )); 

 

====




set linesize 500 
col  sql_text for a40 wrap
select
    *
from
(
    select
con_id,
           sql_id,
         plan_hash_value,
        executions,
        case
        when elapsed_time > 0 then
            elapsed_time/1000
        else
            0
        end elapsed_time_ms,
        case
        when executions > 0 then
            round(elapsed_time/nvl(executions, 1)/1000, 2)
        else
            0
        end elapsed_time_per_exec_ms,
        rows_processed,
        px_servers_executions,
        sorts,
        invalidations,
        parse_calls,
        buffer_gets,
        disk_reads,
    VERSION_COUNT,
        sql_text
    from
        gv$sqlstats
    order by
        elapsed_time_per_exec_ms desc
)
where
    rownum <= 50
;





oracle_top_sql_history.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 ;



set linesize 500 pagesize 300

col sql_text for a50
col SQL_PROFILE for a20
col MODULE for a20
select
    *
from
(
    select
        ss.module,
        ss.snap_id,
ss.con_id,
        ss.sql_id,
        ss.plan_hash_value,
        ss.executions_total,
        case
        when ss.elapsed_time_total > 0 then
            ss.elapsed_time_total/1000
        else
            0
        end elapsed_time_ms,
        case
        when ss.executions_total > 0 then
            round(ss.elapsed_time_total/nvl(ss.executions_total, 1)/1000, 2)
        else
            0
        end elapsed_time_per_exec_ms,
        ss.rows_processed_total,
        ss.px_servers_execs_total,
        ss.sorts_total,
        ss.invalidations_total,
        ss.parse_calls_total,
        ss.buffer_gets_total,
        ss.disk_reads_total,
        ss.optimizer_mode,
        ss.sql_profile,
        to_char(substr(st.sql_text,1,50)) sql_text
    from
        dba_hist_sqlstat ss
        inner join
        dba_hist_sqltext st
         on ss.sql_id = st.sql_id
where 1=1
and ss.snap_id between :BgnSnap and :EndSnap
    order by   elapsed_time_per_exec_ms desc
)
where
    rownum <= 50;


set pagesize 300 linesize 300 numf 99999999999999999999 SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY buffer_gets DESC) WHERE rownum <= 5 ; PROMPT >> disk_reads << SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY disk_reads DESC) WHERE rownum <= 5 ; PROMPT >> sorts << SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY sorts DESC) WHERE rownum <= 5 ; PROMPT >> cpu << SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY cpu_min DESC) WHERE rownum <= 5 ; PROMPT >> rows_processed << SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY rows_processed DESC) WHERE rownum <= 5 ; PROMPT >> elapsed_time << SELECT * FROM (SELECT con_id ,sql_id ,buffer_gets ,disk_reads ,sorts ,ROUND(cpu_time/(1e6*60)) cpu_min ,rows_processed ,elapsed_time FROM gv$sqlstats ORDER BY elapsed_time DESC) WHERE rownum <= 5 ;







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 ;



set linesize 200 trimspool on pagesize 60 verify off
column begin_interval_time format a35
column end_interval_time format a35
break on sql_id skip 1 on instance_number
column sdate new_value sdt noprint
select to_char(sysdate, 'DD-MM-YYYY HHMI') sdate from dual;
-- 


define 1=50

prompt
prompt  Historic
prompt
prompt  Elapsed by exec
prompt
select distinct x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time
from
(select instance_number, sql_id, snap_id,
       round((elapsed_time_total/1000000)/(case when executions_total = 0 then 1 else executions_total end),4) time_per_exec,
       round(elapsed_time_total/1000000, 4) elapsed_time_total
from dba_hist_sqlstat) x, (select snap_id,
                                  max(begin_interval_time) begin_interval_time,
                                  max(end_interval_time) end_interval_time
                           from dba_hist_snapshot
						   where 1=1
						   and snap_id between :BgnSnap and :EndSnap
                           group by snap_id) s
where s.snap_id = x.snap_id
and x.time_per_exec > &&1
and x.time_per_exec <> x.elapsed_time_total
order by 2 asc, 3 desc, 6 desc
/




alter session set nls_date_format='dd-mm-YYYY hh24:mi';

clear breaks
prompt
prompt  Elapsed time total
prompt
select inst_id,
       con_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
order by 5 desc
/


prompt
prompt  Elapsed per exec
prompt
select inst_id,
        con_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
order by 5 desc
/





set linesize 500 pagesize 300
var sqlid VARCHAR2(13);
begin :sqlid := 'g0bggfqrddc4w'; end;
/

col sql_text for a20
select inst_id,con_id, sql_id,rank_elap_per_exec, elapsed_per_exec , elapsed_time, executions, cpu_time, applic_wait_time, user_io_wait_time,sql_text
from (
select inst_id,
con_id,
sql_id,
trunc(elapsed_time/1000000,1) 			elapsed_time,
executions,
trunc(cpu_time/1000000,1) 			cpu_time,
trunc(application_wait_time/1000000,1) 		applic_wait_time,
trunc(user_io_wait_time/1000000,1) 		user_io_wait_time,
trunc(concurrency_wait_time/1000000,1) 		concurr_time,
trunc((elapsed_time/decode(executions, 0,1, executions))/1000000,1) elapsed_per_exec,
RANK() OVER (ORDER BY trunc((elapsed_time/decode(executions, 0,1, executions))/1000000,1) desc) rank_elap_per_exec,
substr(sql_text,1,20) sql_text
from gv$sqlstats
WHERE 1=1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
--and inst_id not in (select instance_number FROM v$instance)
-- and sql_id= :sqlid
)
where rank_elap_per_exec<21
order by rank_elap_per_exec
/




set linesize 700 numf 999999999999999999 pagesize 500
col Rank for 9999
SELECT *
FROM (SELECT RANK () OVER 
(PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.* FROM (SELECT TO_CHAR (hs.begin_interval_time, 'dd-MM-YY' ) "Snap Day",
SUM (shs.executions_delta) 		"Execs",
SUM (shs.buffer_gets_delta) 	"Buffer Gets",
SUM (shs.disk_reads_delta) 		"Disk Reads",
ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) 		"Gets/Exec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) 	"CPU/Exec(S)",
ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) 	"IO/Exec(S)",
shs.sql_id 																		"Sql id",
REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'dd-MM-YY'), CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) )
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 50 
AND "Snap Day" = TO_CHAR (SYSDATE, 'dd-MM-YY')
;



define sql_id='7grvag6ayaxn1'
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col avg_cpu_time for 999,999.999
col avg_lio for 999,999,999.9
col avg_pio for 9,999,999.9
col begin_interval_time for a30
col node for 99999
col plan_hash_value for 99999999999999
break on plan_hash_value on startup_time skip 1
select sql_id, plan_hash_value, 
sum(execs) execs, 
-- sum(etime) etime, 
sum(etime)/sum(execs) avg_etime, 
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio, 
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id',sql_id)
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
-- and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5
/

set linesize 300 pagesize 300

VARIABLE BEGIN_SNAP_ID NUMBER
VARIABLE END_SNAP_ID NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -2 into :BEGIN_SNAP_ID from dba_hist_snapshot ;
exec select max(snap_id) into :END_SNAP_ID from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;



set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BEGIN_SNAP_ID;
v_end_snap_id number := :END_SNAP_ID;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id)
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5 
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');

for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;

for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs
|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;

for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name
|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/




-- active_sql_workarea.sql
set linesize 500 pagesize 400
col sidser	format a18  		head 'Sid,Serial'
col operid	format 999		head 'Oper|Id'
col oper	format a14  		head 'Oper Type'	trunc
col esizek	format 9,999,999  	head 'EstSizeK '	
col csizek	format 99,999,999  	head 'CurSizeK '
col msizek	format 9,999,999  	head 'MaxSizeK'
col p		format 9		
col tsizem	format 999,999		head 'TempSizeM'
col sqlid_c	format a17		head 'SqlId:Child'
col qcsid	format 9999		head 'QC'
col sexecs	format a05		head 'Start'
col minago	format 99999999		head 'Min|Ago'
col module	format a30		head 'Module'		trunc
col event	format a30		head 'Event'		trunc
col ts		format a08		head 'TempTS'		trunc
col kill for a18
break on module on sql_id on qcsid skip 1 on sidser on report
compute sum of csizek on module
compute sum of csizek on report
compute sum of tsizem on report

select	 se.module			module
	,wa.sql_id||':'||se.sql_child_number			sqlid_c
	,wa.qcsid			qcsid
	-- ,lpad(se.sid,4,' ')||','||lpad(se.serial#,5,' ')	sidser
	,''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||''''  sidser
	,se.con_id
	,to_char(wa.sql_exec_start,'HH24:MI')			sexecs
	,(sysdate - wa.sql_exec_start)*24*60			minago
	,se.event
       	,trunc(ACTUAL_MEM_USED/1024) 	csizeK
	,operation_id			operid
       	,operation_type 		oper
       	,trunc(EXPECTED_SIZE/1024) 	esizeK
       	,trunc(MAX_MEM_USED/1024) 	msizeK
       	,NUMBER_PASSES 			p
	,tablespace			ts
       	,trunc(TEMPSEG_SIZE/1024/1024) 	tsizeM
from 	 gv$sql_workarea_active 	wa
	,gv$session		se
where	 wa.sid		= se.sid
and wa.inst_id		= se.inst_id
order by module
	,sqlid_c
	,operid
	,oper
	,sidser
;





SELECT * 
FROM 
   (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"+"Disk Reads"+"Buffer Gets"+"Writes"+"Sorts"+"Parses" DESC) AS "Rank", 
           i1.* 
    FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day", 
                 shs.sql_id "Sql id", 
                 REPLACE(CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)),CHR (10),'') "Sql", 
                 SUM(shs.executions_delta) "Execs",ROUND((SUM(shs.elapsed_time_delta)/1000000)/SUM (shs.executions_delta),1) "Time Ea Sec", 
                 ROUND((SUM(shs.cpu_time_delta)/1000000)/SUM (shs.executions_delta),1) "CPU Ea Sec", 
                 ROUND((SUM(shs.iowait_delta)/1000000)/SUM (shs.executions_delta),1) "IO/Wait Ea Sec", 
                 SUM(shs.cpu_time_delta) "CPU Time", 
                 SUM(shs.disk_reads_delta) "Disk Reads", 
                 SUM(shs.buffer_gets_delta) "Buffer Gets", 
                 SUM(shs.direct_writes_delta) "Writes", 
                 SUM(shs.parse_calls_delta) "Parses", 
                 SUM(shs.sorts_delta) "Sorts", 
                 SUM(shs.elapsed_time_delta) "Elapsed" 
            FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht 
                 ON (sht.sql_id = shs.sql_id) 
                 INNER JOIN dba_hist_snapshot hs 
                 ON (shs.snap_id = hs.snap_id) 
				 and hs.begin_interval_time >sysdate - interval '60' minute
            HAVING SUM (shs.executions_delta) > 0 
            GROUP BY shs.sql_id,TO_CHAR(hs.begin_interval_time,'MM/DD/YY'),CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)) 
            ORDER BY "Snap Day" DESC) i1 
ORDER BY "Snap Day" DESC) 
WHERE "Rank" <= 20 
 	; 
 

====

set linesize 500 pagesize 300 numf 999999999999999
col sql_fulltext for a100 wrap


-- sql with high io and memory consumption
select * from (  select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
                    , elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext 
                from gv$sqlarea   
                where buffer_gets > 10000000 or disk_reads > 1000000   
                order by buffer_gets + 100 * disk_reads desc
            )where rownum<20 ;


-- sql accounted for io
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
                    , elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext 
               from gv$sqlarea   
               order by disk_reads desc  
            )where rownum<20 ;


 --average execution slow sql
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
                    , elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext 
               from gv$sqlarea   
               order by cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) desc   
            )where rownum<20 ;


-- total time-consuming sql
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
                    , elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext 
               from gv$sqlarea   
               order by elapsed_time desc   
            )where rownum<20 ;
            


-- sql accounted for cpu
select * from (  select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
                    , elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext 
               from gv$sqlarea   
               order by cpu_time desc   
            )where rownum<20 ;



====

find_sql_awr.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;

set long 32000  lines 300
col sql_text format a40 
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio, sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, nvl(executions_delta,0) execs,elapsed_time_delta/1000000 etime,(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and ss.snap_id between nvl(:BgnSnap,999999999) and nvl(:EndSnap,999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
--and sql_text like nvl('&sql_text',sql_text)
--and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/


***********************



SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF 

COL pct FOR A10 JUST RIGHT
COL cpu_pct FOR 999.9
COL io_pct FOR 999.9
col OWNER for a20
col OBJECT_NAME    for a20                                                                                                                   
col PROCEDURE_NAME for a20 

BREAK ON day SKIP 1

DEF days=1

PROMPT Displaying daily top SQL for last &days days...

WITH ash AS (
    SELECT 
        day
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value
      , total_seconds
      , io_seconds
      , cpu_seconds
      , LPAD(TRIM(TO_CHAR(RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100, '999.9'))||'%', 10) pct
      , RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100 pct_num
    FROM (
        SELECT
            TO_CHAR(sample_time, 'YYYY-MM-DD') day
          , sql_id
          , sql_plan_hash_value 
          , p.owner
          , p.object_name
          , p.procedure_name
          , SUM(10) total_seconds
          , SUM(CASE WHEN wait_class = 'User I/O' THEN 10 ELSE 0 END) io_seconds
          , SUM(CASE WHEN wait_class IS NULL THEN 10 ELSE 0 END) cpu_seconds
        FROM
            dba_hist_active_sess_history a
          , dba_procedures p
        WHERE
            a.plsql_entry_object_id = p.object_id (+)
        AND a.plsql_entry_subprogram_id = p.subprogram_id (+)
        AND sample_time > SYSDATE - &days
        AND session_type != 'BACKGROUND' -- ignore for now
        GROUP BY
            sql_id
          , sql_plan_hash_value 
          , p.owner
          , p.object_name
          , p.procedure_name
          , TO_CHAR(sample_time, 'YYYY-MM-DD')
    )
)
, sqlstat AS (
    SELECT /*+ MATERIALIZE */ 
        TO_CHAR(begin_interval_time, 'YYYY-MM-DD') day
      , sql_id
      , plan_hash_value
      , SUM(executions_delta) executions
      , SUM(rows_processed_delta) rows_processed
      , SUM(disk_reads_delta) blocks_read
      , SUM(disk_reads_delta)*8/1024 mb_read
      , SUM(buffer_gets_delta) buffer_gets
      , SUM(iowait_delta)/1000000 awr_iowait_seconds
      , SUM(cpu_time_delta)/1000000 awr_cpu_seconds 
      , SUM(elapsed_time_delta)/1000000 awr_elapsed_seconds
    FROM
        dba_hist_snapshot
      NATURAL JOIN
        dba_hist_sqlstat
    WHERE
        begin_interval_time > SYSDATE - &days
    GROUP BY
        TO_CHAR(begin_interval_time, 'YYYY-MM-DD') 
      , sql_id
      , plan_hash_value
)
SELECT /*+ MONITOR */
        day
      , pct
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value plan_hash
      , ROUND(total_seconds / 3600,1) total_hours
      , total_seconds
      , executions
      , ROUND(total_seconds / NULLIF(executions,0),2) seconds_per_exec
      , io_pct
      , cpu_pct
      , mb_read
      , ROUND(mb_read / NULLIF(executions,0),2) mb_per_exec
      , buffer_gets
      , ROUND(buffer_gets / NULLIF(executions,0),2) bufget_per_exec
      , CASE WHEN sql_id IS NOT NULL THEN 
            'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('''||sql_id||''','||CASE WHEN sql_plan_hash_value = 0 THEN 'NULL' ELSE TO_CHAR(sql_plan_hash_value) END||', format=>''ADVANCED''));'
        END extract_plan_from_awr
FROM (
    SELECT
        day
      , pct
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value
      , total_seconds
      , io_seconds/total_seconds*100 io_pct
      , cpu_seconds/total_seconds*100 cpu_pct
      , (SELECT executions FROM sqlstat s  WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) executions
      , (SELECT mb_read FROM sqlstat s     WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) mb_read
      , (SELECT buffer_gets FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) buffer_gets
    FROM 
        ash
    WHERE 
        ash.pct_num >= 1
)
ORDER BY
    day DESC
  , total_seconds DESC
/




DAY               PCT OWNER                OBJECT_NAME          PROCEDURE_NAME       SQL_ID         PLAN_HASH TOTAL_HOURS TOTAL_SECONDS EXECUTIONS SECONDS_PER_EXEC IO_PCT CPU_PCT    MB_READ MB_PER_EXEC BUFFER_GETS BUFGET_PER_EXEC EXTRACT_PLAN_FROM_AWR
---------- ---------- -------------------- -------------------- -------------------- ------------- ---------- ----------- ------------- ---------- ---------------- ------ ------- ---------- ----------- ----------- --------------- --------------------------------------------------------------------------------------------------------------------------
2023-08-12      78.6%                                                                ctyc1af1abg5u  200298931          91        327510                                 .0   100.0                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
                 4.4%                                                                                       0         5.1         18340                               22.3    72.7
                 4.2%                                                                3xjw1ncw5vh27 2487977020         4.9         17500                               88.3    11.7                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3xjw1ncw5vh27',2487977020, format=>'ADVANCED'));
                 4.2%                                                                c7ngymcfjtb1c 1658157256         4.8         17450                               43.8    56.2                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('c7ngymcfjtb1c',1658157256, format=>'ADVANCED'));

2023-08-11      55.1%                                                                ctyc1af1abg5u  200298931        55.9        201130                                 .0   100.0                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));




                 8.7%                                                                1zuyhdtsmmx5s 3008400300         8.8         31680    





define sql_id='ctyc1af1abg5u'

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


COL exec_per_sec    FOR 99999990
COL ela_ms_per_sec  FOR 99999990
COL rows_per_sec    FOR 99999990
COL lios_per_sec    FOR 99999990
COL blkrd_per_sec   FOR 99999990
COL cpu_ms_per_sec  FOR 99999990
COL iow_ms_per_sec  FOR 99999990
COL clw_ms_per_sec  FOR 99999990
COL apw_ms_per_sec  FOR 99999990
COL ccw_ms_per_sec  FOR 99999990


SELECT
    CAST(begin_interval_time AS DATE) begin_interval_time
,st.con_id
  , sql_id
  , plan_hash_value
  , ROUND(SUM(executions_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
  , ROUND(SUM(elapsed_time_delta  ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
  , ROUND(SUM(rows_processed_delta)        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
  , ROUND(SUM(buffer_gets_delta   )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
  , ROUND(SUM(disk_reads_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
  , ROUND(SUM(cpu_time_delta      ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
  , ROUND(SUM(iowait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
  , ROUND(SUM(clwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
  , ROUND(SUM(apwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
  , ROUND(SUM(ccwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time   <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
    CAST(begin_interval_time AS DATE)
,st.con_id
  , CAST(end_interval_time AS DATE)
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/           



connnect to pdbs

col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));


*************************************************



define sql_id='88gm0z813qnht'

set long 30000 pagesize 500 linesize 300
col frm         heading from 
select * from (select 'gv$sql' frm ,  sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
               union all
	select 'gv$sqlstats' frm ,  sql_fulltext sql_text from gv$sqlstats where sql_id='&&sql_id'
	union all
          select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id' 
			   )
			 where 1=1
			 and rownum<2
			 ;








****$ORACLE_HOME/rdbms/admin/sqltrpt.sql <<<<<< to display 15 Most expensive SQL





$ORACLE_HOME/rdbms/admin/sqltrpt.sql <<<<<< to display 15 Most expensive SQL





set linesize 300 pagesize 300
column elapsed format 999999999999999
variable newl varchar2(64);

begin
  :newl := ' ';
end;
/


col sql_text_fragment for a60
select * from (
 select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,:newl,' '),1,55) as sql_text_fragment
 from   V$SQLSTATS
 order by elapsed_time desc
) where ROWNUM <= 15;

prompt
prompt 15 Most expensive SQL in the workload repository
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


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;


select * from (
 select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed,
     (select to_char(substr(replace(st.sql_text,:newl,' '),1,55))
     from dba_hist_sqltext st
     where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment
 from dba_hist_sqlstat stat, dba_hist_sqltext text
 where stat.sql_id = text.sql_id and
       stat.dbid   = text.dbid
and stat.snap_id between nvl(:BgnSnap,999999999) and nvl(:EndSnap,999999999)
 group by stat.dbid, stat.sql_id
 order by elapsed desc
) where ROWNUM <= 15;




***

 
 
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 ;


col GETS_SQL    for a25                                            
col CPU_SQL      for a25                                           
col ELAPSED_SQL      for a25                       
col EXECUTIONS_SQL for a25

with awr_ranks as
(
select snap_id, sql_id,
                           buffer_gets_delta,
                           dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
                           cpu_time_delta,
                           dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
                           elapsed_time_delta,
                           dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank,
                           executions_delta,
                           dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
                    -- from sys.wrh$_sqlstat
                   from DBA_HIST_SQLSTAT
), rank as
(       select level rank from dual connect by level <= 5
)
select snap_id,             rank,
                           max(case gets_rank when rank then to_char(buffer_gets_delta)||': '||sql_id end) gets_sql,
                           max(case cpu_rank when rank then to_char(round(cpu_time_delta/1000000,2))||': '||sql_id end) cpu_sql,
                           max(case elapsed_rank when rank then to_char(round(elapsed_time_delta/1000000,2))||': '||sql_id end) elapsed_sql,
                           max(case executions_rank when rank then  to_char(executions_delta)||': '||sql_id end) executions_sql
              from awr_ranks, rank
              where snap_id between :BgnSnap and :EndSnap
              group by snap_id, rank
              order by snap_id, rank;





***************************************************************************






set linesize 2000
var order_by varchar2(10); 

-- begin :order_by := 'ELAP'; end;
--/

 -- begin :order_by := 'IO'; end;
 --/


 --begin :order_by := 'CPU'; end;
 -- /

 --begin :order_by := 'GET'; end;
 -- /

 begin :order_by := 'READ'; end;
 /

 --begin :order_by := 'EXEC'; end;
 -- /

 --begin :order_by := 'PARSE'; end;
  --/

  --begin :order_by := 'MEM'; end;
 /


 --begin :order_by := 'VERS'; end;
 -- /
 
 
 --begin :order_by := 'ELAP_EXEC'; end;
  --/

 --begin :order_by := 'SNAP'; end;
 -- /

set numf 9999999999999999999
col "Tot Wait"  		for 99999999999999999   
col IOWAIT  			for 99999999999999999
col CLWAIT  			for 99999999999999999
col BASELINE_PLAN_NAME 	for a15
col SCHEMA  			for a20
col sql_text 			for a70 wrap
col "Time Per Exec" 	for a10

select ord ord
,  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then 'elapsed_sec' 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then 'cpu_sec'
       when nvl(:order_by, 'GET') in ('IO'   , '3') then 'iowait'
       when nvl(:order_by, 'GET') in ('GET'  , '4') then 'buffer_gets'
       when nvl(:order_by, 'GET') in ('READ' , '5') then 'disk_reads'
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then 'executions'
       when nvl(:order_by, 'GET') in ('PARSE', '7') then 'parse_calls'
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then 'sharable_mem'
       when nvl(:order_by, 'GET') in ('VERS' , '9') then 'version_count' 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 'time_per_exec' 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then 'snap_id' 
       else 'buffer_gets'
  end order_by
, schema  
, sql_id
, plan_hash_value "Plan Hash Value"
,      (select 
        max(to_number(extractvalue(
        xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]'))) plan_hash_2
        from   dba_hist_sql_plan hp
        where  hp.sql_id          = main_query.sql_id
        and    hp.plan_hash_value = main_query.plan_hash_value
        and    hp.other_xml is not null) plan_hash_2
, (select max(last_refresh_time) from gv$sql_monitor sm where sm.sql_id = main_query.sql_id and sm.sql_plan_hash_value = main_query.plan_hash_value) monitor_last_refresh_time
, time_per_exec "Time Per Exec"
, executions "Exec-utions"
, clock_time "Clock Time"
, px_servers_execs "px servers execs"
, sql_text
, buffer_gets "Buffer Gets"
, fetches
, rows_processed "rows processed"
, round(rows_processed / nullif(fetches, 0)) "rows per fetch" 
, end_of_fetch_count "end of fetch count"
, sorts
, disk_reads "disk reads"
, tot_wait "Tot Wait"
, iowait
, clwait
, apwait
, ccwait
, direct_writes "direct writes"
, elapsed_sec "Elap-sed (Sec)"
, cpu_sec "CPU Sec"
, plsql_sec "PL/SQL sec"
, plsexec_time "pls exec time"
, javexec_time "java exec time"
, sharable_mem "shar-able mem"
-- per exec calculations
, case when executions > 0 then buffer_gets/executions else 0 end "Buffer Gets per exec"
, case when executions > 0 then fetches/executions else 0 end  "Fetches Gets per exec"
, case when executions > 0 then rows_processed/executions else 0 end  "rows per exec"
, case when executions > 0 then sorts/executions else 0 end  "sorts per exec"
, case when executions > 0 then disk_reads/executions else 0 end  "disk reads per exec"
, case when executions > 0 then tot_wait/executions else 0 end  "Tot Wait per exec"
, case when executions > 0 then iowait/executions else 0 end  "iowait per exec"
, case when executions > 0 then clwait/executions else 0 end  "clwait  per exec"
, case when executions > 0 then apwait/executions else 0 end  "apwait per exec"
, case when executions > 0 then ccwait/executions else 0 end  "ccwait  per exec"
, case when executions > 0 then direct_writes/executions else 0 end  "direct writes  per exec"
, case when executions > 0 then elapsed_sec/executions else 0 end  "Elap-sed (Sec)  per exec"
, case when executions > 0 then cpu_sec/executions else 0 end  "CPU Sec per exec"
, case when executions > 0 then plsql_sec/executions else 0 end  "PL/SQL sec  per exec"
, case when executions > 0 then plsexec_time/executions else 0 end  "pls exec time  per exec"
, case when executions > 0 then javexec_time/executions else 0 end  "java exec time per exec"
, case when executions > 0 then sharable_mem/executions else 0 end  "shar-able mem per exec"
-- per row calculations
, case when rows_processed > 0 then buffer_gets/rows_processed else 0 end "Buffer Gets per row"
, case when rows_processed > 0 then fetches/rows_processed else 0 end  "Fetches Gets per row"
, case when rows_processed > 0 then rows_processed/rows_processed else 0 end  "rows per row"
, case when rows_processed > 0 then sorts/rows_processed else 0 end  "sorts per row"
, case when rows_processed > 0 then disk_reads/rows_processed else 0 end  "disk reads per row"
, case when rows_processed > 0 then tot_wait/rows_processed else 0 end  "Tot Wait per row"
, case when rows_processed > 0 then iowait/rows_processed else 0 end  "iowait per row"
, case when rows_processed > 0 then clwait/rows_processed else 0 end  "clwait  per row"
, case when rows_processed > 0 then apwait/rows_processed else 0 end  "apwait per row"
, case when rows_processed > 0 then ccwait/rows_processed else 0 end  "ccwait  per row"
, case when rows_processed > 0 then direct_writes/rows_processed else 0 end  "direct writes  per row"
, case when rows_processed > 0 then elapsed_sec/rows_processed else 0 end  "Elap-sed (Sec)  per row"
, case when rows_processed > 0 then cpu_sec/rows_processed else 0 end  "CPU Sec per row"
, case when rows_processed > 0 then plsql_sec/rows_processed else 0 end  "PL/SQL sec  per row"
, case when rows_processed > 0 then plsexec_time/rows_processed else 0 end  "pls exec time  per row"
, case when rows_processed > 0 then javexec_time/rows_processed else 0 end  "java exec time per row"
, case when rows_processed > 0 then sharable_mem/rows_processed else 0 end  "shar-able mem per row"
, loaded_versions "loaded vers-ions" 
, version_count "ver-sion count"
, loads
, invalidations "invalid-ations"
, parse_calls "parse calls"
, module 
, command_type_name
, to_char(min_time, 'dd/mm/yyyy HH24:MI:SS') min_time
, to_char(max_time ,'dd/mm/yyyy HH24:MI:SS') max_time
, min_snap_id "Min Snap Id"
, max_snap_id "Max Snap Id"
, sql_profile
, Baseline_plan_name -- does not work for 10g
from
(
select schema  
   , plan_hash_value
   , sql_id
   , rownum ord
   , sub.elapsed_sec
   , CASE 
     WHEN elapsed_sec > 86399
          THEN elapsed_sec || ' sec' 
     WHEN elapsed_sec <= 86399
          THEN to_char(to_date(round(elapsed_sec) ,'SSSSS'), 'HH24:MI:SS') 
     END as clock_time
   , case when executions <> 0
     then CASE 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) > 86399
          THEN round(elapsed_sec/(executions)*decode(px_servers_execs, 0, 1, px_servers_execs)) || ' sec' 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) <= 86399
          THEN to_char(to_date(round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) ,'SSSSS'), 'HH24:MI:SS') 
     END 
     end as time_per_exec
   , cpu_sec
   , plsql_sec
   , executions
   , buffer_gets
   , sharable_mem
   , loaded_versions
   , version_count
   , module 
   , fetches
   , end_of_fetch_count
   , sorts
   , px_servers_execs
   , loads
   , invalidations
   , parse_calls
   , disk_reads
   , rows_processed
   , iowait
   , clwait
   , apwait
   , ccwait
   , tot_wait
   , direct_writes
   , plsexec_time
   , javexec_time
   , (select max(DBMS_LOB.SUBSTR(sql_text, 3800)) from dba_hist_sqltext st where st.sql_id = sub.sql_id) sql_text
   , (select max(name) from dba_hist_sqltext st, audit_actions aa where st.sql_id = sub.sql_id and aa.action = st.command_type)   command_type_name
   , min_time
   , max_time
   , min_snap_id
   , max_snap_id
   , sql_profile
   , (select nvl(min(sql_plan_baseline), 'none') from v$sql sql where sql.sql_id  = sub.sql_id and sql.plan_hash_value = sub.plan_hash_value) Baseline_plan_name -- does not work for 10g
from
   ( -- sub to sort before rownum
     select
        sql_id
        , plan_hash_value
        , round(sum(elapsed_time_delta)/1000000) as elapsed_sec
        , round(sum(cpu_time_delta)    /1000000) as cpu_sec 
        , round(sum(plsexec_time_delta)/1000000) as plsql_sec 
        , sum(executions_delta) as executions
        , sum(buffer_gets_delta) as buffer_gets      
        , sum(sharable_mem) as sharable_mem
        , sum(loaded_versions) as loaded_versions
        , sum(version_count) as version_count
        , max(module) as module 
        , sum(fetches_delta) as fetches
        , sum(end_of_fetch_count_delta) as end_of_fetch_count
        , sum(sorts_delta) as sorts
        , sum(px_servers_execs_delta) as px_servers_execs
        , sum(loads_delta) as loads
        , sum(invalidations_delta) as invalidations
        , sum(parse_calls_delta) as parse_calls
        , sum(disk_reads_delta) as disk_reads
        , sum(rows_processed_delta) as rows_processed
        , sum(iowait_delta) as iowait
        , sum(clwait_delta) as clwait
        , sum(apwait_delta) as apwait
        , sum(ccwait_delta) as ccwait
        , sum(iowait_delta) + sum(clwait_delta) + sum(apwait_delta) + sum(ccwait_delta) as tot_wait
        , sum(direct_writes_delta) as direct_writes
        , sum(plsexec_time_delta) as plsexec_time
        , sum(javexec_time_delta) as javexec_time
        , max(parsing_schema_name) as schema
        , max(snap.end_INTERVAL_TIME) max_time
        , min(snap.end_INTERVAL_TIME) min_time
        , min(stat.snap_id) min_snap_id
        , max(stat.snap_id) max_snap_id
        , min(nvl(sql_profile, 'none')) sql_profile     
     from
        dba_hist_snapshot snap
        , dba_hist_sqlstat stat
     where 1=1
          and nvl(:order_by, 'GET') like '%' 
          and snap.dbid = stat.dbid
          and snap.instance_number = stat.instance_number
          and snap.snap_id = stat.snap_id
        --  and snap.snap_id between nvl(:start_snap_id, snap.snap_id) and nvl(:end_snap_id, snap.snap_id)
        --  and nvl(parsing_schema_name,'%') like nvl(upper(:username), nvl(parsing_schema_name,'%')  )
        --  and sql_id = nvl(:sql_id, sql_id)
        --  and nvl(plan_hash_value,0) = nvl(:plan_hash_value, nvl(plan_hash_value,0))
        --   and nvl(module,'x') like nvl(:module, nvl(module,'x'))
        --  and stat.instance_number = nvl(:inst_id, stat.instance_number)
        --  and decode(:days_back_only_Y_N,'Y', end_INTERVAL_TIME, trunc(sysdate-:days_back) ) >= trunc(sysdate-:days_back)
        --  and (trunc(begin_INTERVAL_TIME, 'MI') >=  to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI, to_char(begin_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
        --  and trunc(end_interval_time, 'MI') <= to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI, to_char(end_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI'))
        --  and (to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour, 0) and  nvl(:end_hour, 24) 
        --  or to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour2, nvl(:begin_hour, 0)) and  nvl(:end_hour2, nvl(:end_hour, 24)))
group by sql_id, plan_hash_value --, force_matching_signature  -- , stat.instance_number
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets 
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 
	   case when executions <> 0 
	   then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) 
	   else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
   ) sub
where 1=1
  and rownum <= 10
) main_query
where 1=1
 -- and nvl(upper(sql_text), '%') like nvl(upper(:sql_text), '%')
 -- and nvl(command_type_name, 'x') like nvl(:command_type_name, nvl(command_type_name, 'x'))
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets  -- essentially an overall workload ordering
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then case when executions <> 0 then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
;













Oracle DBA

anuj blog Archive