Thursday, 14 November 2013

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

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 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

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 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

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

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;

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
;



SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 1000 FEEDBACK OFF

-- Top 10 
DECLARE
  CURSOR query_cursor IS
    SELECT
      ROWNUM AS rank,
      v.sql_id,
      SUBSTR(v.sql_text, 1, 120) AS truncated_sql,
      TO_CHAR(v.elapsed_time / 1000000, '999999999999.9') AS elapsed_seconds,
      v.executions
    FROM (
      SELECT
        sql_id,
        sql_text,
        elapsed_time,
        executions
      FROM
        gv$sqlstats
		where 1=1
		and CON_ID!=1 --pertain to only the root
		and sql_text not like 'DECLARE job%'
      ORDER BY
        elapsed_time DESC
    ) v
    WHERE ROWNUM <= 10;

  v_rank NUMBER;
  v_sql_id VARCHAR2(13);
  v_truncated_sql VARCHAR2(120);
  v_elapsed_seconds VARCHAR2(20);
  v_executions NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Rank| SQL_ID    |                                        Truncated SQL                                                                    | Elapsed(S)| NoofExec');
  DBMS_OUTPUT.PUT_LINE('--|-------------|-------------------------------------------------------------------------------------------------------------------------|-----------|--------|');

  FOR result IN query_cursor LOOP
    v_rank := result.rank;
    v_sql_id := result.sql_id;
    v_truncated_sql := result.truncated_sql;
    v_elapsed_seconds := result.elapsed_seconds;
    v_executions := result.executions;
    DBMS_OUTPUT.PUT_LINE(
      RPAD(TO_CHAR(v_rank),2) || '|' ||
      RPAD(v_sql_id, 13) || '|' ||
      RPAD(v_truncated_sql, 121) || '|' ||
      RPAD(v_elapsed_seconds, 10) || ' |' ||
      RPAD(TO_CHAR(v_executions), 5)|| '   |'
    );
  END LOOP;
END;
/

set LINESIZE 100 PAGESIZE 1000




set linesize 500 pagesize 300

define v_dbid=NULL;
select &v_dbid from dual;
col f_dbid new_value v_dbid
select &database_id f_dbid from dual;
select &v_dbid from dual;
select nvl(&v_dbid,dbid) f_dbid from v$database;
select &v_dbid from dual;

col PCT_IO_OBJ for a25
col aud_action for a20
with master as  (
   select
        sql_id,
        sql_plan_hash_value,
		 con_id_ash,
        sql_opcode,
        sum(cpu) cpu,
        sum(wait) wait,
        sum(io) io,
        sum(total) total,
        decode(sum(io),0,null, decode(objn,-1,NULL,objn)) objn,  
        row_number() over ( partition by sql_id order by io desc ) seq,
        ratio_to_report( sum(io)) over ( partition by sql_id ) pct
   from (
     select
        ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE 
		,ash.con_id con_id_ash
		, sql_opcode,
        current_obj# objn,
        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(wait_class, 'User I/O',1,0),0)) wait ,
        sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) io ,
        sum(decode(ash.session_state,'ON CPU',1,1)) total
     from dba_hist_active_sess_history ash
     where  SQL_ID is not NULL
       and  dbid=&v_dbid
     group by sql_id, SQL_PLAN_HASH_VALUE , ash.con_id,sql_opcode, current_obj#
   )
group by sql_id, SQL_PLAN_HASH_VALUE , con_id_ash,sql_opcode, objn,io
)
select * from (
select
        sql_id,
        sql_plan_hash_value,
		con_id_ash,
        aud.name aud_action,
        sum(cpu) cpu,
        sum(wait) wait,
        sum(io) io,
        sum(total) total,
        round(max(decode(seq,1,pct,null)),2)*100  pct_io,
        max(decode(seq,1,o.object_name,null)) pct_io_obj
from master,audit_actions aud , DBA_HIST_SEG_STAT_OBJ o
where 1=1
    --and  objn=o.object_id(+) 
     and   objn=o.obj#(+) 
    and sql_opcode=aud.action
group by sql_id,sql_plan_hash_value,con_id_ash,aud.name
order by total desc )
where rownum < 10
/

====


 
 set pause off echo off
set feed on term on

set pagesize 200 linesize 200 trimspool on

col wait_class format a20 head 'WAIT CLASS'
col event format a50 head 'EVENT'

col sql_id format a13 head 'SQL ID'
col session_serial# format 99999999 head 'SESSION|SERIAL#'
col sql_time format 999,999,999 head 'SQL TIME(s)'

with sqldata as (
select distinct
	sql_id
	,session_id
	, session_serial#
	, sql_exec_id
	, event
	, count(*) over (partition by sql_id, session_id, session_serial#, sql_exec_id, event) sql_time
from gv$active_session_history h
where time_waited != 0
and sql_id is not null
--and SAMPLE_TIME > systimestamp - numtodsinterval('1', 'hour')
and SAMPLE_TIME > systimestamp - numtodsinterval('10', 'MINUTE')
--and SAMPLE_TIME > systimestamp - numtodsinterval('1', 'SECOND')
order by 6 desc
)
select sql_id
	, event
	, sql_time
from sqldata
where rownum <= 20
order by sql_time
/




set linesize 400 pagesize 400
col sample_id for 999999999
col sid_ser# for a10
col username for a26
col sql_id for a13
col exec_plan_ln#_obj# for a45
col dT for 9999
col event for a30
col "DB%,CPU%" for a7
col "R,W_IOPS" for a7
col "R,W_MBPS" for a7
col "PGA,TEMP_MB" for a9
col kill for a18
break on sample_id skip 1


select * from (
select 
''''||session_id ||','|| session_serial#||',@'||inst_id ||'''' kill ,con_id,
--sample_id,session_id||' '||session_serial# sid_ser#,
       (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((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       blocking_session, wait_time,time_waited,
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from gv$active_session_history  
where 1=1
-- and sample_time>systimestamp-numtodsinterval(1,'second')
and sample_time > sysdate - interval '5' minute
  --order by "DB%,CPU%"
   order by "R,W_IOPS"
 --  order by  "R,W_MBPS"
 --  order by  "PGA,TEMP_MB"
  )
  where 1=1
  and username not in ('SYS','SYSTEM','DBFS_USER','GGATE')
  and rownum <20
  --order by "DB%,CPU%"
   order by "R,W_IOPS"
 --  order by  "R,W_MBPS"
 --  order by  "PGA,TEMP_MB"
;



set linesize 300
col sample_id for 999999999
col sid_ser# for a10
col username for a26
col sql_id for a13
col top_level_sql_id for a13
col exec_plan_ln#_obj# for a45
col dT for 9999
col event for a30
col "DB%,CPU%" for a8
col "R,W_IOPS" for a8
col "R,W_MBPS" for a8
col "PGA,TEMP_MB" for a11
col kill for a18

select 
''''||session_id ||','|| session_serial#||',@'||inst_id ||'''' kill ,con_id,
       (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,event
from gv$active_session_history  
where 1=1
-- and  inst_id=&1 and session_id=&2
      and rownum=1;

break on sample_id skip 1

select 
sample_id,
''''||session_id ||','|| session_serial#||',@'||inst_id ||'''' kill ,con_id,
       sql_id, top_level_sql_id,
       round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from gv$active_session_history  
where 1=1
-- and inst_id=&1 and session_id=&2 
      -- and sample_time>systimestamp-numtodsinterval(3,'second')
	  and sample_time> sysdate - interval '15' minute
/




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 gv$active_session_history
 where 1=1
 and sample_time> sysdate - interval '15' minute
 )
select sql_id,con_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 1=1
and sample_time> sysdate - interval '15' minute
-- sample_time>systimestamp-numtodsinterval(&1,'second')
group by sql_id,con_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;





set pagesize 300

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 1=1 
 and sample_time> sysdate - interval '15' minute 
 --and 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 1=1
and sample_time> sysdate - interval '15' minute 
--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;



set pagesize 300
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 v$active_session_history
 where 1=1
 and sample_time> sysdate - interval '15' minute 
 )
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 1=1
and sample_time> sysdate - interval '15' minute 
--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

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Thursday, 14 November 2013

Top Sql from Between snap_id

Top Sql from Between snap_id

Oracle top 10 sql waits

Oracle top 10 sql waits

Oracle Rac top 10 sql waits

Oracle DBA

anuj blog Archive