Oracle top 10 sql waits
Oracle Rac top 10 sql waits
top awr event http://anuj-singh.blogspot.com/2021/12/awr-top-events.html
http://anuj-singh.blogspot.com/2023/ Oracle AWR Report Script top sqls' waits ..
Prompt Top Elapsed
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, avg_etime_Sec,cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,sql_id,child_number,sql_text,round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),2) avg_etime_Sec,
cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where
elapsed_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Avg Elapsed Time Sec* :'||sqlcur.avg_etime_Sec);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top buffer_gets
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,avg_buf ,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,sql_id,child_number,sql_text,round(buffer_gets/decode(nvl(executions,0),0,1,executions),2) avg_buf,
cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY buffer_gets DESC) AS buffer_gets_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where buffer_gets_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Avg Buffer Gets* :'||sqlcur.avg_buf);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt High cpu
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,sql_id,child_number,sql_text,EXECUTIONS, round((cpu_time/decode(nvl(executions,0),0,1,executions))/1000000,2) CPU_TIME,
disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY cpu_time DESC) AS cpu_time_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where cpu_time_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('===============================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU (In Seconds) Per Execution* :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==============================================================================');
end;
/
Prompt Top EXECUTIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,sql_id,child_number,sql_text,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top disk_reads set linesize 150 pagesize 200 serveroutput on declare cursor sqltext_cur is select inst_id,sql_id,child_number,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads from (select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY disk_reads DESC) AS disk_reads_rank FROM gv$sql where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ) where disk_reads_rank <= 11; begin for sqlcur in sqltext_cur loop dbms_output.put_line('====================================================================='); dbms_output.put_line('Inst id :'||sqlcur.inst_id); dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME); dbms_output.put_line('SQL ID :'||sqlcur.sql_id); dbms_output.put_line('Child Number :'||sqlcur.child_number); dbms_output.put_line('Disk Reads :'||sqlcur.disk_reads); dbms_output.put_line('CPU :'||sqlcur.cpu_time); dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads); dbms_output.put_line('SQL Statement :'||sqlcur.sql_text); end loop; dbms_output.put_line('=================================================================='); end; / Prompt Top parse_calls set linesize 150 pagesize 200 serveroutput on declare cursor sqltext_cur is select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,parse_calls from (select inst_id,sql_id,child_number,parse_calls,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY parse_calls DESC) AS parse_calls_rank FROM gv$sql where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) ) where parse_calls_rank <= 11; begin for sqlcur in sqltext_cur loop dbms_output.put_line('====================================================================='); dbms_output.put_line('Inst id :'||sqlcur.inst_id); dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME); dbms_output.put_line('SQL ID :'||sqlcur.sql_id); dbms_output.put_line('Child Number :'||sqlcur.child_number); dbms_output.put_line('Parse Calls :'||sqlcur.parse_calls); dbms_output.put_line('CPU :'||sqlcur.cpu_time); dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads); dbms_output.put_line('SQL Statement :'||sqlcur.sql_text); end loop; dbms_output.put_line('=================================================================='); end; / Prompt Top sharable_mem set linesize 150 pagesize 200 serveroutput on declare cursor sqltext_cur is select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,sharable_mem from (select inst_id,sql_id,child_number,sharable_mem ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sharable_mem DESC) AS sharable_mem_rank FROM gv$sql where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS' , 'ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM')) where sharable_mem_rank <= 11; begin for sqlcur in sqltext_cur loop dbms_output.put_line('====================================================================='); dbms_output.put_line('Inst id :'||sqlcur.inst_id); dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME); dbms_output.put_line('SQL ID :'||sqlcur.sql_id); dbms_output.put_line('Child Number :'||sqlcur.child_number); dbms_output.put_line('Sharable Mem :'||sqlcur.sharable_mem); dbms_output.put_line('CPU :'||sqlcur.cpu_time); dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads); dbms_output.put_line('SQL Statement :'||sqlcur.sql_text); end loop; dbms_output.put_line('=================================================================='); end; / Prompt Top VERSIONS set linesize 150 pagesize 200 serveroutput on declare cursor sqltext_cur is select inst_id,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,OPEN_VERSIONS from (select inst_id,sql_id,child_number,OPEN_VERSIONS ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY OPEN_VERSIONS DESC) AS OPEN_VERSIONS_rank FROM gv$sql where PARSING_SCHEMA_NAME Not in( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM')) where OPEN_VERSIONS_rank <= 11; begin for sqlcur in sqltext_cur loop dbms_output.put_line('====================================================================='); dbms_output.put_line('Inst id :'||sqlcur.inst_id); dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME); dbms_output.put_line('SQL ID :'||sqlcur.sql_id); dbms_output.put_line('Child Number :'||sqlcur.child_number); dbms_output.put_line('OPEN VERSIONS :'||sqlcur.OPEN_VERSIONS); dbms_output.put_line('CPU :'||sqlcur.cpu_time); dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads); dbms_output.put_line('SQL Statement :'||sqlcur.sql_text); end loop; dbms_output.put_line('=================================================================='); end; /
Sorts ...
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,sql_id,child_number,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME,parse_calls
from (select inst_id,sql_id,child_number,parse_calls,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sorts DESC) AS sorts_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where sorts_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Parse Calls :'||sqlcur.parse_calls);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('*sorts :'||sqlcur.sorts);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
============================================================
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
SELECT
rows_processed,
buffer_gets,
executions,
-- direct_writes,
-- elapsed_time / 1000000 as elapsed_time_secs,
-- cpu_time / 1000000 as cpu_time_secs,
-- last_exec_start_time,
parsing_schema_name,
module,
-- action,
sql_id,
plan_hash_value,
sql_text
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
--FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 1000'))
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 1000'))
where 1=1
and parsing_schema_name not in ('SYS','SYSTEM','SYSMAN','DBVISIT7','MON','MDSYS')
ORDER BY buffer_gets desc;
set linesize 300 pagesize 300
col MODULE for a20
col ACTION for a30
SELECT s.sql_id,s.rows_processed, s.loads, s.executions, s.buffer_gets,s.disk_reads, t.sql_text,s.module, s.ACTION FROM gv$sql s,gv$sqltext t
WHERE s.address = t.address
and s.inst_id = t.inst_id
AND ((buffer_gets > 10000000) or (disk_reads > 1000000) or (executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/
======
SELECT 'Physical i/o' type,
ROUND (100 * NVL (top_disk_read / sum_disk_reads, 0), 2) perct, hash_value, sql_id
FROM (SELECT disk_reads top_disk_read, hash_value, sql_id FROM gv$sqlarea WHERE disk_reads > 0 ORDER BY 1 DESC),
(SELECT SUM (disk_reads) sum_disk_reads FROM gv$sqlarea WHERE disk_reads > 0)
WHERE ROWNUM < 11
UNION ALL
SELECT 'Logical i/o', ROUND (100 * NVL (top_buff_get / sum_buff_gets, 0), 2) perct, hash_value, sql_id
FROM (SELECT buffer_gets top_buff_get, hash_value, sql_id FROM gv$sqlarea WHERE buffer_gets > 0 ORDER BY 1 DESC),
(SELECT SUM (buffer_gets) sum_buff_gets FROM gv$sqlarea WHERE buffer_gets > 0)
WHERE ROWNUM < 11
UNION ALL
SELECT 'CPU Time', ROUND (100 * NVL (top_cpu / sum_cpu, 0), 2) perct, hash_value, sql_id
FROM (SELECT cpu_time top_cpu, hash_value, sql_id FROM gv$sqlarea WHERE cpu_time > 0 ORDER BY 1 DESC),
(SELECT SUM (cpu_time) sum_cpu FROM gv$sqlarea WHERE cpu_time > 0)
WHERE ROWNUM < 11
UNION ALL
SELECT 'Elapsed Time',
ROUND (100 * NVL (top_elap_time / sum_elap_time, 0), 2) perct, hash_value, sql_id
FROM (SELECT elapsed_time top_elap_time, hash_value, sql_id FROM gv$sqlarea WHERE elapsed_time > 0 ORDER BY 1 DESC),
(SELECT SUM (elapsed_time) sum_elap_time FROM gv$sqlarea
WHERE elapsed_time > 0)
WHERE ROWNUM < 11
;
===========
set long 99999999999 linesize 300 pagesize 300
select sql_id,sql_text from dba_hist_sqltext
where sql_id in ( select sql_id from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL/s.executions_total)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL/s.executions_total)) DESC) elapsed_rank
from dba_hist_sqlstat s, dba_hist_snapshot sn
where 1=1
and sn.begin_interval_time between to_date('28-apr-2021 00:01','dd-mon-yyyy hh24:mi') and to_date('28-apr-2021 06:00','dd-mon-yyyy hh24:mi')
and sn.snap_id=s.snap_id and s.executions_total >0
group by s.sql_id )
where cpu_rank <=100 and elapsed_rank<=100);
===
prompt "Top SQL by Buffer Gets"
set linesize 300 pagesize 300
col sql for a50 wrap
select * from ( select sql_id,substr(sql_text,1,500) "SQL", (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) and USERS_EXECUTING!=0
order by buffer_gets desc nulls last)
where rownum<11;
prompt "Top SQL by Buffer Gets / Rows Proc"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL", round((cpu_time/1000000),3) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) and USERS_EXECUTING!=0
order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last)
where rownum<11;
prompt "Top SQL by Disk Reads"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL", (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) and USERS_EXECUTING!=0
order by disk_reads desc nulls last)
where rownum<11
;
prompt "Top SQL by CPU"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL", (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) and USERS_EXECUTING!=0
order by cpu_time desc nulls last)
where rownum<11
;
prompt "Top SQL by Executions"
set linesize 300 pagesize 300
col sql for a50 wrap
col Module for a20
select * from (select sql_id,substr(sql_text,1,500) "SQL", (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from gv$sql s
where 1=1
and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) and USERS_EXECUTING!=0
order by executions desc nulls last)
where rownum<11
;
set linesize 300 pagesize 300
prompt "Top SQL by Waits"
col sql for a50 wrap
col Module for a20
select INST_ID, (cpu_time/1000000) "CPU_Seconds", disk_reads "Disk_Reads", buffer_gets "Buffer_Gets", executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions", (elapsed_time/1000000) "Elapsed_Seconds",
--round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
substr(sql_text,1,500) "SQL",
module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
(select sql_id
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
ELSE 'WAIT' END state
from gv$active_session_history
where session_type IN ( 'FOREGROUND')
and sample_time between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
group by sql_id, state),
ranked_sqls AS
(select sql_id, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank
from sql_class
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
and rs.xrank <= 11
order by xrank, sql_id, state ))
order by elapsed_time desc nulls last;
=====
top 10 sql
set linesize 500 pagesize 300
col SQL_TEXT for a50 wrap
col DURATION for a15
col SQL_OPNAME for a15
select
* from (
select instance_number as inst_id,
ash.sql_id,
sql_opname,
to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
machine,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ') as SQL_TEXT
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, machine,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history ash
where snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot
where 1=1
and begin_interval_time > sysdate - 1
)
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, sample_time, machine
having sum(temp_space_allocated) is not null) ash
left join dba_hist_sqltext t on t.sql_id = ash.sql_id
group by instance_number, ash.sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, machine
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ')
--having max(temp_space_allocated) / 1024 / 1024 / 1024 > 100 -- GB
order by 11 desc
) where rownum <= nvl('&2',10)
/
=============
TOP 20 RESOURCE-INTENSIVE SQL
set linesize 300
select *
FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"
+ "Disk Reads"
+ "Buffer Gets"
+ "Writes"
+ "Sorts"
+ "Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day",
shs.sql_id "Sql id",
REPLACE(CAST(DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
),CHR (10),
''
) "Sql",
SUM (shs.executions_delta) "Execs",
ROUND ( (SUM (shs.elapsed_time_delta) / 1000000)/ SUM (shs.executions_delta),1) "Time Ea Sec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000)/ SUM (shs.executions_delta),1) "CPU Ea Sec",
ROUND ( (SUM (shs.iowait_delta) / 1000000)/ SUM (shs.executions_delta),1) "IO/Wait Ea Sec",
SUM (shs.cpu_time_delta) "CPU Time",
SUM (shs.disk_reads_delta) "Disk Reads",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.direct_writes_delta) "Writes",
SUM (shs.parse_calls_delta) "Parses",
SUM (shs.sorts_delta) "Sorts",
SUM (shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,
TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'),
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
)
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');
==========================
display top 15 resource intensive SQL
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
b6usrg82hwsa3 11,054.98 call dbms_stats.gather_database_stats_job_proc ( )
b6usrg82hwsa3 2,559.58 call dbms_stats.gather_database_stats_job_proc ( )
22356bkgsdcnh 721.25 SELECT COUNT(*) FROM X$KSPPI A, X$KSPPCV2 B WHERE A.IND
6h1ysp5jm8h8m 492.02 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
6mcpb06rctk0x 467.58 call dbms_space.auto_space_advisor_job_proc ( )
6mcpb06rctk0x 437.91 call dbms_space.auto_space_advisor_job_proc ( )
6h1ysp5jm8h8m 395.80 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
0smupm8p2dhq7 324.44 select count(*) num_windows, sum(case when jobs_started
3zmzdajzvjkfw 281.02 select /*+ no_monitor no_statement_queuing */ job, nvl
4q607gwyr580r 264.54 MERGE /*+ dynamic_sampling(4) dynamic_sampling_est_cdn
49s332uhbnsma 240.74 declare vsn varchar2(20); b
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
1fvsn5j51ugz3 237.73 begin dbms_rcvman.resetAll; end;
5c24r2fm4b9rh 233.07 SELECT /*jskqjobqlod2*/ /*+ no_monitor no_statement_que
5j7xryzqzqcbk 232.36 SELECT /*jskqjobqlod3*//*+ no_monitor no_statement_queu
c9umxngkc3byq 230.66 select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(select to_char(substr(replace(st.sql_text,:newl,' '),1,55))
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: b6usrg82hwsa3
Sql Id specified: b6usrg82hwsa3
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_5127
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/06/2021 07:14:03
Completed at : 05/06/2021 07:14:03
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : b6usrg82hwsa3
SQL Text : call dbms_stats.gather_database_stats_job_proc ( )
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.
-------------------------------------------------------------------------------
=====================
SET UNDERLINE =
set HEADS '/'
COL separador HEADING '|' FOR A1
COL seconds_since_date HEADING 'Elapsed/Time(s)'
COL execs_since_date HEADING 'Executions'
COL elpased_execution HEADING 'Elapsed/per Exec(s)'
COL gets_since_date HEADING 'Total Gets'
COL pct_db_time HEADING '% DB Time'
COL pct_gets HEADING '% Gets'
COL ranking NOPRINT
select
sql_id,
'|' as separador,
seconds_since_date,
execs_since_date,
elpased_execution,
pct_db_time,
'|' as separador,
gets_since_date,
pct_gets,
ranking
from
(select
sub.sql_id,
sub.seconds_since_date,
sub.execs_since_date,
round(sub.seconds_since_date/DECODE(sub.execs_since_date,0,1,sub.execs_since_date),2) elpased_execution,
round(sub.seconds_since_date*100/sum(sub.seconds_since_date) over ( order by instance_number), 2) pct_db_time,
sub.gets_since_date,
round(sub.gets_since_date*100/(sum(sub.gets_since_date) over ( order by instance_number)), 2) pct_gets,
rank() over( order by gets_since_date desc) ranking
from
( -- sub to sort before rownum
select
instance_number,
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date
from
dba_hist_snapshot natural join dba_hist_sqlstat
where 1=1
and begin_interval_time >sysdate -1
-- begin_interval_time >= to_date('&start_yyyy_mm_dd_hh24','YYYY-MM-DD HH24') and end_interval_time <= to_date('&end_yyyy_mm_dd_hh24','YYYY-MM-DD HH24')
-- and instance_number = 1
group by
instance_number,
sql_id
order by
2 desc
) sub
)where
ranking <20
order by ranking;
Elapsed Elapsed
SQL_ID | Time(s) Executions per Exec(s) % DB Time | Total Gets % Gets
============= = ========== ========== =========== ========== = ========== ==========
5j7xryzqzqcbk | 0 0 |
0smupm8p2dhq7 | 301 23 13.09 7.81 | 117628461 35.45
0smupm8p2dhq7 | 282 22 12.82 5.06 | 112406699 16.63
34cd4y8mbqvsk | 126 2387 .05 2.26 | 49451325 7.32
34cd4y8mbqvsk | 119 2170 .05 3.09 | 44956061 13.55
b6usrg82hwsa3 | 363 11 33 6.52 | 43243450 6.4
6mcpb06rctk0x | 132 8 16.5 2.37 | 40187372 5.95
f705bwx3q0ydq | 94 23 4.09 2.44 | 38932793 11.73
3n1sg9y1f0xm6 | 84 11 7.64 1.51 | 37679114 5.58
f705bwx3q0ydq | 91 22 4.14 1.63 | 37506332 5.55
b6usrg82hwsa3 | 1094 7 156.29 28.4 | 36664030 11.05
6mcpb06rctk0x | 130 7 18.57 3.37 | 35490187 10.7
3n1sg9y1f0xm6 | 78 9 8.67 2.02 | 30828428 9.29
6wrwqq7jkmv3w | 13 66 .2 .23 | 4229673 .63
6ajkhukk78nsr | 21 23 .91 .38 | 3998010 .59
6wrwqq7jkmv3w | 16 60 .27 .42 | 3845488 1.16
1j6tnz8fcm4c3 | 64 41 1.56 1.66 | 3512258 1.06
6ajkhukk78nsr | 16 21 .76 .42 | 3424585 1.03
6wm3n4d7bnddg | 8 25 .32 .14 | 2807919 .42
select
sub.sql_id,
sub.seconds_since_date,
sub.execs_since_date,
sub.gets_since_date
from
( --sub to sort before rownum
select
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date
from dba_hist_snapshot natural join dba_hist_sqlstat
where 1=1
--begin_interval_time > to_date('2015-05-10′,'YYYY-MM-DD’) –to_date('&&start_YYYYMMDD','YYYY-MM-DD')
group by
sql_id
order by
2 desc
) sub
where
rownum < 30
;
SQL_ID SECONDS_SINCE_DATE EXECS_SINCE_DATE GETS_SINCE_DATE
------------- ------------------ ---------------- ---------------
b6usrg82hwsa3 21879 570 2255605219
6mcpb06rctk0x 9172 601 3018062105
===========================================
set linesize 200 pagesize 10000
column min_date format a20
column max_date format a20
select x.sql_id,x.command_type,
to_char(x.min_date,'dd-mon-yyyy hh24:mi:ss') min_date,
to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') max_date,
x.cnt,
to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss') v_sqlstat_last_active,
(x.max_date - a.last_active_time) * 60 * 60 * 24 diff_sec ,
a.executions stat_exe,
round(a.cpu_time / 1000000,0) cpu_secs
from ( select s.sql_id,
s.command_type,
min(s.last_active_time) min_date,
max(s.last_active_time) max_date,
count(*) cnt,
sum(s.executions) sql_exe
from gv$sql s
group by sql_id,command_type
--having count(*) > 1 -- remove this because still possible for 1 child cursor row (child_number>0] to have mismatch on last_active_time in v$sqlstats
) x,
gv$sqlstats a
where x.sql_id = a.sql_id
and to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') != to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss')
and (x.max_date - a.last_active_time) * 60 * 60 * 24 > 20 -- Filter out any where v$sqlstat last active is less than 20 seconds
order by 7 desc
/
===================
var v_second number;
var v_min number;
-- begin :v_second := 10; end; ----- change
begin :v_min := 15 end;
/
define v_min=15
set linesize 300 pagesize 300
clear breaks
break on session_id
col KILL for a15
col MODULE for a15
with b as (
select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
from gv$active_session_history
where 1=1
-- and sample_time>systimestamp-numtodsinterval(1,'second')
-- and sample_time>systimestamp - (:v_second)
and sample_time>systimestamp - INTERVAL '&v_min' MINUTE
)
select ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''' kill ,PROGRAM,MODULE,
sql_id,
round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where 1=1
--and sample_time>systimestamp-numtodsinterval(1,'second')
--and sample_time>systimestamp - (:v_second)
--and sample_time>systimestamp - INTERVAL '60' MINUTE
and sample_time>systimestamp - INTERVAL '&v_min' MINUTE
and SQL_ID is not null
group by ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''',PROGRAM,MODULE,sql_id,b.samples,b.deltaT
--having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;
============from gv$sqlarea
set pagesize 200 linesize 300
--set echo off
col parsing_schema_name for a20
col executions heading "Execs" for 99999999
col rows_processed heading "Rows Procd" for 99999999
col loads heading "Loads" for 999999.99
col buffer_gets heading "Buffer Gets"
col disk_reads heading "Disk Reads"
col elapsed_time heading "Elasped Time"
col cpu_time heading "CPU Time"
col sql_text heading "SQL Text" for a120 wrap
col avg_cost heading "Avg Cost" for 99999999
col gets_per_exec heading "Gets Per Exec" for 99999999
col reads_per_exec heading "Read Per Exec" for 99999999
col rows_per_exec heading "Rows Per Exec" for 99999999
break on report
compute sum of rows_processed on report
compute sum of executions on report
compute avg of avg_cost on report
compute avg of gets_per_exec on report
compute avg of reads_per_exec on report
compute avg of row_per_exec on report
-- and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
PROMPT
PROMPT Top 10 most expensive SQL by Elapsed Time...
PROMPT
col sql_text for a70 wrap
select rownum rank, a.* from ( select con_id,elapsed_time, executions, buffer_gets, disk_reads, cpu_time,sql_id, hash_value, sql_text
from gv$sqlarea
where elapsed_time > 20000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
order by elapsed_time desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL by CPU Time...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from ( select con_id,elapsed_Time, executions, buffer_gets, disk_reads, cpu_time,PARSING_SCHEMA_NAME,sql_id, hash_value, sql_text
from gv$sqlarea
where cpu_time > 20000
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
order by cpu_time desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL by Buffer Gets by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,buffer_gets, executions, buffer_gets/ decode(executions,0,1, executions) gets_per_exec,sql_id, hash_value, sql_text
from gv$sqlarea
where buffer_gets > 50000
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
and users_executing!=0
order by buffer_gets desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL by Physical Reads by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,disk_reads, executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec, sql_id,hash_value, sql_text
from gv$sqlarea
where disk_reads > 10000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
order by disk_reads desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL by Rows Processed by Executions...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from (select con_id,rows_processed, executions,rows_processed / decode(executions,0,1, executions) rows_per_exec, sql_id,hash_value, sql_text
from gv$sqlarea
where rows_processed > 10000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) order by disk_reads desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL by Buffer Gets vs Rows Processed...
PROMPT
col sql_text for a70 wrap
select rownum as rank, a.*
from ( select con_id,buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed", executions, loads,
(decode(rows_processed,0,1,1)) * buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
sql_id,sql_text
from gv$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
and users_executing!=0
and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
order by disk_reads desc) a
--order by 5 desc) a
where rownum < 11;
rem Check to see if there are any candidates for procedures or
rem for using bind variables. Check this by comparing UPPER
rem
rem This May be a candidate application for using the init.ora parameter
rem CURSOR_SHARING = FORCE|SIMILAR
col SQLTEXT for a65 WRAP
select rownum as rank, a.* from (select upper(substr(sql_text, 1, 65)) sqltext, count(*) from gv$sqlarea
WHERE 1=1
--and parsing_schema_name not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
--order by disk_reads desc
group by upper(substr(sql_text, 1, 65) )
having count(*) > 1
order by count(*) desc) a
where rownum < 11;
=============
VARIABLE bsnap NUMBER
VARIABLE esnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bsnap from dba_hist_snapshot ;
exec select max(snap_id) into :esnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
select * from (
select ss.snap_id snapid,ss.instance_number inst,ss.sql_id sqlid
,round(sum(ss.elapsed_time_delta)) elapsed
,nvl(round(sum(ss.executions_delta)),1) execs
,round(sum(ss.buffer_gets_delta)) gets
,round(sum(ss.rows_processed_delta)) rowsp
,round(sum(ss.disk_reads_delta)) reads
,dense_rank() over(partition by snap_id,instance_number order by sum(ss.elapsed_time_delta) desc) sql_rank
from
dba_hist_sqlstat ss
where
ss.dbid = :DID
and
ss.snap_id between :bsnap and :esnap
group by ss.snap_id,ss.instance_number,ss.sql_id
)
where sql_rank < 11
and snapid between :bsnap and :esnap
/
====
top sql
COLUMN session_id HEADING "SID" FORMAT 99999
COLUMN inst_id HEADING "I#" FORMAT 99
COLUMN "session_serial#" HEADING "Serial#" FORMAT 999999
COLUMN FORCE_MATCHING_SIGNATURE FORMAT 99999999999999999999999
COLUMN sql_plan_hash_value HEADING "Plan|Hash|Value" FORMAT 9999999999
COLUMN sql_exec_start FORMAT a19
COLUMN sql_exec_end HEADING "MaxSampleTime" FORMAT a19
COLUMN duration FORMAT a15
COLUMN sql_opname HEADING "SQL|Operation" FORMAT a15 TRUNCATE
COLUMN sql_child_number HEADING "SQL|Ch#" FORMAT 999
COLUMN current_dop HEADING "DOP" FORMAT 999
COLUMN pga_allocated HEADING "PGA|(GB)" FORMAT 99.00
COLUMN temp_space_allocated HEADING "Temp|Space|(GB)" FORMAT 999.00
-- Get the SQL Statements from ASH
SELECT * FROM
(
SELECT --ash.sql_exec_id,
--TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
NVL(ash.qc_session_id,ash.session_id) session_id
, NVL(ash.qc_instance_id,ash.inst_id) inst_id
, NVL(ash.qc_session_serial#,ash.session_serial#) session_serial#
, TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
, TO_CHAR(max(ash.sample_time) ,'DD-MON-YY HH24:MI:SS') sql_exec_end
, REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'+00000000','+') duration
, ash.sql_opname
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, max(trunc(ash.px_flags / 2097152)) current_dop
, ash.force_matching_signature
, NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id) top_level_sql_id
, ROUND(MAX(ash.pga_allocated)/power(1024,3),2) pga_allocated
, ROUND(MAX(ash.temp_space_allocated)/power(1024,3),2) temp_space_allocated
FROM gv$session s
JOIN gv$active_session_history ash
ON s.inst_id = NVL(ash.qc_instance_id,ash.inst_id)
AND s.sid = NVL(ash.qc_session_id,ash.session_id)
AND s.serial# = NVL(ash.qc_session_serial#,ash.session_serial#)
LEFT OUTER JOIN gv$active_session_history ash_parent
ON ash_parent.inst_id = ash.qc_instance_id
AND ash_parent.session_id = ash.qc_session_id
AND ash_parent.session_serial# = ash.qc_session_serial#
AND CAST(ash_parent.sample_time as DATE) = ash.sql_exec_start
WHERE 1=1
--s.inst_id = :INST_ID
-- AND s.sid = :SID
--AND ash.sql_exec_id IS NOT NULL
GROUP BY NVL(ash.qc_session_id,ash.session_id)
, NVL(ash.qc_instance_id,ash.inst_id)
, NVL(ash.qc_session_serial#,ash.session_serial#)
, ash.sql_exec_id
, ash.sql_exec_start
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, ash.FORCE_MATCHING_SIGNATURE
, ash.sql_opname
, NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id)
ORDER BY
-- max(ash.sample_time) asc
--,
NVL(ash.sql_exec_start,MIN(ash.sample_time)) DESC
, max(ash.sample_time) DESC
)
WHERE ROWNUM <= 10
ORDER BY sql_exec_end
;
Plan Temp
SQL SQL Hash PGA Space
SID I# Serial# SQL_EXEC_START MaxSampleTime DURATION Operation SQL_ID Ch# Value DOP FORCE_MATCHING_SIGNATURE TOP_LEVEL_SQL (GB) (GB)
------ --- ------- ------------------- ------------------- --------------- --------------- ------------- ---- ----------- ---- ------------------------ ------------- ------ -------
1710 1 17438 14-OCT-21 04:40:38 14-OCT-21 04:40:39 +0 00:00:01.167 SELECT 1n1bm7xa28vtq 0 1605285479 13512506387705464989 5aa3zr7sxythy .01 .00
1143 1 32606 14-OCT-21 04:41:35 14-OCT-21 04:41:36 +0 00:00:01.199 SELECT 1n1bm7xa28vtq 0 1605285479 0 7z5abdb0vs5dz .00 .00
1143 1 32606 14-OCT-21 04:41:28 14-OCT-21 04:41:44 +0 00:00:16.204 SELECT 8cnh50qfgwg73 0 3673574621 13512506387705464989 7z5abdb0vs5dz .00 .00
1710 1 17438 14-OCT-21 04:41:44 14-OCT-21 04:41:45 +0 00:00:01.205 SELECT 1n1bm7xa28vtq 0 1605285479 13512506387705464989 5aa3zr7sxythy .01 .00
====
Oracle 12c !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
prompt With con_id
Prompt Top Elapsed
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, avg_etime_Sec,cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),2) avg_etime_Sec,
cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where
elapsed_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Avg Elapsed Time Sec* :'||sqlcur.avg_etime_Sec);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top buffer_gets
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,avg_buf ,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,round(buffer_gets/decode(nvl(executions,0),0,1,executions),2) avg_buf,
cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY buffer_gets DESC) AS buffer_gets_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where buffer_gets_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Avg Buffer Gets* :'||sqlcur.avg_buf);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt High cpu
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,EXECUTIONS, round((cpu_time/decode(nvl(executions,0),0,1,executions))/1000000,2) CPU_TIME,
disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY cpu_time DESC) AS cpu_time_rank
FROM gv$sql
where PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
and USERS_EXECUTING!=0
)
where cpu_time_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('===============================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU (In Seconds) Per Execution* :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==============================================================================');
end;
/
Prompt Top EXECUTIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top disk_reads
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY disk_reads DESC) AS disk_reads_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where disk_reads_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Disk Reads :'||sqlcur.disk_reads);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top parse_calls
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,parse_calls
from (select inst_id,CON_ID,sql_id,child_number,parse_calls,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY parse_calls DESC) AS parse_calls_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where parse_calls_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Parse Calls :'||sqlcur.parse_calls);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top sharable_mem
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,sharable_mem
from (select inst_id,CON_ID,sql_id,child_number,sharable_mem ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sharable_mem DESC) AS sharable_mem_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS' , 'ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where sharable_mem_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Sharable Mem :'||sqlcur.sharable_mem);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top VERSIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME,OPEN_VERSIONS
from (select inst_id,CON_ID,sql_id,child_number,OPEN_VERSIONS ,sql_text, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY OPEN_VERSIONS DESC) AS OPEN_VERSIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS' , 'CTXSYS' , 'ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM'))
where OPEN_VERSIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('OPEN VERSIONS :'||sqlcur.OPEN_VERSIONS);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Sorts ...
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME,parse_calls
from (select inst_id,CON_ID,sql_id,child_number,parse_calls,sql_text, cpu_time,sorts,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY sorts DESC) AS sorts_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN' , 'MDSYS' ,'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' ) )
where sorts_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID);
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('Parse Calls :'||sqlcur.parse_calls);
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('*sorts :'||sqlcur.sorts);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
Prompt Top EXECUTIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_id,CON_ID,sql_id,child_number,EXECUTIONS,
case when executions = 0 then 0 else round(elapsed_time/executions, 3) end "ElapsedPerExec(ms)",
sql_text, cpu_time,PARSING_SCHEMA_NAME,disk_reads
from (select inst_id,CON_ID,sql_id,child_number,sql_text,elapsed_time,EXECUTIONS, cpu_time,disk_reads,PARSING_SCHEMA_NAME, RANK () OVER (ORDER BY EXECUTIONS DESC) AS EXECUTIONS_rank FROM gv$sql
where PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) )
where EXECUTIONS_rank <= 11;
begin
for sqlcur in sqltext_cur
loop
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Inst id :'||sqlcur.inst_id);
dbms_output.put_line('CON_ID :'||sqlcur.CON_ID)
dbms_output.put_line('PARSING SCHEMA NAME :'||sqlcur.PARSING_SCHEMA_NAME);
dbms_output.put_line('SQL ID :'||sqlcur.sql_id);
dbms_output.put_line('Child Number :'||sqlcur.child_number);
dbms_output.put_line('EXECUTIONS :'||sqlcur.EXECUTIONS);
dbms_output.put_line('Per EXECUTIONS :'||sqlcur."ElapsedPerExec(ms)");
dbms_output.put_line('CPU :'||sqlcur.cpu_time);
dbms_output.put_line('DISK Reads :'||sqlcur.disk_reads);
dbms_output.put_line('SQL Statement :'||sqlcur.sql_text);
end loop;
dbms_output.put_line('==================================================================');
end;
/
=============================================
top 20 sql !!!
set linesize 500 pagesize 300
VARIABLE dbid NUMBER
exec select DBID into :dbid from v$database;
column DB_BLOCK_SIZE_1 new_value DB_BLOCK_SIZE noprint
with inst as (
select min(instance_number) inst_num
from dba_hist_snapshot
where dbid = :dbid
)
SELECT VALUE DB_BLOCK_SIZE_1
FROM DBA_HIST_PARAMETER
WHERE dbid = :dbid
and PARAMETER_NAME = 'db_block_size'
AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_osstat WHERE dbid = :dbid AND instance_number = (select inst_num from inst))
AND instance_number = (select inst_num from inst);
define SQL_TOP_N=20
VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;
column module format a33
column action format a33
col PARSING_SCHEMA_NAME for a20
select * from(
SELECT s.snap_id,PARSING_SCHEMA_NAME,PLAN_HASH_VALUE plan_hash,substr(regexp_replace(s.module,'([[:alnum:]\.\-])@.+\(TNS.+','\1'),1,30) module,
substr(s.action,1,30) action,
s.sql_id,
avg(s.optimizer_cost) optimizer_cost,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',
7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',
3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,sum(EXECUTIONS_DELTA) execs,sum(BUFFER_GETS_DELTA) buffer_gets,sum(ROWS_PROCESSED_DELTA) rows_proc,
round(sum(CPU_TIME_DELTA)/1000000,1) cpu_t_s,round(sum(ELAPSED_TIME_DELTA)/1000000,1) elap_s,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024,1) read_mb,round(sum(IOWAIT_DELTA)/1000000,1) io_wait,
DENSE_RANK() OVER (PARTITION BY s.snap_id ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
CASE WHEN MAX(PLAN_HASH_VALUE) = LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC)
OR LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC) = 0 THEN 0
when count(distinct PLAN_HASH_VALUE) > 1 then 1 else 1 end plan_change,
count(distinct PLAN_HASH_VALUE) OVER (PARTITION BY s.snap_id,s.sql_id ) plans,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
sum(s.px_servers_execs_delta) px_servers_execs,
round(sum(DIRECT_WRITES_DELTA * &DB_BLOCK_SIZE)/1024/1024/1024) direct_w_gb,
sum(IOWAIT_DELTA) as iowait_time,
sum(DISK_READS_DELTA) as PIO
FROM dba_hist_sqlstat s,dba_hist_sqltext t
WHERE s.dbid = :dbid
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND s.snap_id BETWEEN :bid AND :eid
AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN')
GROUP BY s.snap_id, PLAN_HASH_VALUE,t.command_type,PARSING_SCHEMA_NAME,s.module,s.action, s.sql_id)
WHERE elap_rank <= &SQL_TOP_N --#
--and sql_id = '2a22s56r25y6d'
order by snap_id,elap_rank asc nulls last;
SNAP_ID PARSING_SCHEMA_NAME PLAN_HASH MODULE ACTION SQL_ID OPTIMIZER_COST COMMAND_NAME EXECS BUFFER_GETS ROWS_PROC CPU_T_S ELAP_S READ_MB IO_WAIT ELAP_RANK PLAN_CHANGE PLANS PHY_READ_GB PX_SERVERS_EXECS DIRECT_W_GB IOWAIT_TIME PIO
---------- -------------------- ---------- --------------------------------- --------------------------------- ------------- -------------- ------------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------- ---------------- ----------- ----------- ----------
39828 SVCDBEM7MON 0 DBMS_SCHEDULER FIFTEEN_MINUTE_INTERVAL 5aa3zr7sxythy 0 CALLMETHOD 1 21240163 0 1060.6 3548.2 26556.8 1560 2 0 1 26 0 0 1560006226 3399272
====
Ordered by Elapsed time
prompt SQL ...
set lines 1000 pages 1000
col begin_interval_time for a30
col end_interval_time for a30
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc
/
define rnum=20
VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;
col MODULE for a20
set lines 1000 pages 1000
select * from (
select sql_id, module,
sum(ELAPSED_TIME_DELTA)/1000000 "Elapsed Time(s)",
sum(CPU_TIME_DELTA)/1000000 "CPU Time(s)",
sum(executions_delta) "Executions",
sum(ROWS_PROCESSED_DELTA) rows1,
sum(BUFFER_GETS_DELTA) "Buffer Gets",
sum(DISK_READS_DELTA) "Physical Reads",
sum(iowait_delta)/1000000 "IO Wait",
sum(ccwait_delta)/1000000 cc_wait,
sum(apwait_delta)/1000000 ap_wait,
sum(clwait_delta)/1000000 cl_wait,
sum(BUFFER_GETS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) gets_per_row,
sum(DISK_READS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) prds_per_row,
sum(BUFFER_GETS_DELTA)/decode(sum(executions_delta), 0, 1, sum(executions_delta)) gets_per_exec
from dba_hist_sqlstat
where snap_id between :bid and :eid
group by sql_id, module
order by 3 desc
) where rownum <= &rnum
/
===
col sql_text for a50 wrap
select * from (
SELECT
m.*, TO_CHAR(dbms_lob.substr(v.sql_text, 3900)) SQL_Text
FROM (
select distinct
snap_id,
sql_id,
EXECUTIONS_DELTA,
trunc(max(ELAPSED_TIME_DELTA)OVER(PARTITION BY snap_id, sql_id),0) max_elapsed,
trunc(max(cpu_time_delta)OVER(PARTITION BY snap_id, sql_id),0) max_cpu
from dba_hist_sqlstat t
WHERE t.snap_id IN (SELECT MAX(snap_id) FROM dba_hist_sqlstat)
) M,dba_hist_sqltext v
where 1=1
and v.sql_id(+)=m.sql_id
and v.SQL_TEXT not like '%v$%'
order by max_elapsed desc
) where rownum < 10
;
-- associate sql execution user
col USERNAME for a20
col sql_text for a50 wrap
select * from (
select
sqt.sql_id, sqt.max_exec, sqt.max_elapsed, su.username,
TO_CHAR(dbms_lob.substr(st.sql_text, 3900)) sql_text
from
(select
sql_id,
min(snap_id) snap_id,
max(executions_delta) max_exec,
max(cpu_time_delta) max_cpu,
NVL((MAX(elapsed_time_delta) / 1000000), to_number(null)) max_elapsed
from dba_hist_sqlstat
where 1=1
--and module = 'XXXXX' -- filter a certain The sql statement executed by the program
group by sql_id) sqt, dba_hist_sqltext st,
(SELECT sql_id, parsing_schema_name username
FROM (
SELECT t.sql_id,t.parsing_schema_name,row_number() over(partition by t.sql_id order by t.snap_id asc) rn
FROM dba_hist_sqlstat t
WHERE 1=1
--and module ='XXXXX'
)
WHERE rn = 1) su
where
st.sql_id(+) = sqt.sql_id and su.sql_id(+) = sqt.sql_id
order by nvl(sqt.max_elapsed, -1) desc, sqt.sql_id
) where rownum <= 10;
---total duration of a sql
col sql_text for a50 wrap
select * from (
select
sqt.sql_id,
sqt.exec Executions,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
decode(sqt.exec, 0, to_number(null), (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
TO_CHAR(dbms_lob.substr(st.sql_text, 3900)) SQL_Text
from
(select
sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
group by sql_id) sqt, dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id and st.sql_text not like '%v$%'
order by nvl(sqt.elap, -1) desc, sqt.sql_id
) where rownum < 10;
col sql_text for a50 wrap
select
sql_id,
executions,
elapsed_time,
cpu_time,
(elapsed_time / executions ) Elap_per_Exec,
TO_CHAR(dbms_lob.substr(sql_fulltext, 3900)) SQL_Text
from
(select
sql_id,
child_number,
sql_text,
elapsed_time,
cpu_time,
disk_reads,
sql_fulltext,
executions,
rank () over(order by elapsed_time desc) as sql_rank
from
gv$sql where sql_fulltext not like '%v$%')
where
sql_rank < 20;
set linesize 800
SELECT
A.SQL_ID,
A.SQL_TEXT,
A.COMMAND_TYPE, --> IF NEEDED
B.PLAN_HASH_VALUE,
B.ID,
B.OPERATION,
B.OPTIONS,
B.OBJECT_OWNER,
B.OBJECT_NAME,
B.OBJECT_TYPE,
B.OPTIMIZER,
B.PARTITION_START,
B.PARTITION_STOP,
B.PARTITION_ID
FROM DBA_HIST_SQLTEXT A, DBA_HIST_SQL_PLAN B
WHERE A.DBID = B.DBID
AND A.SQL_ID = B.SQL_ID
--AND A.SQL_ID IN('xxxxxxx') ----!!!!
and exists
(select 'e'
from dba_hist_sqlstat c
where a.dbid = c.dbid
and a.sql_id = c.sql_id
and b.sql_id = c.sql_id
and b.plan_hash_value <> c.plan_hash_value
-- and parsing_schema_name in('SCOTT')
)
ORDER BY A.DBID, A.SQL_ID, B.PLAN_HASH_VALUE, B.ID;
=====
---ash_top !!!!!!!!!!!!!!!
set linesize 300 pagesize 300
define 1=100 ----100 sec !!!
set verify off
break on sql_id
with b as (
select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
from v$active_session_history
where sample_time>systimestamp-numtodsinterval(&1,'second')
)
select sql_id,decode(session_state,'WAITING',event,'CPU') event,
round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by sql_id,event,b.samples,b.deltaT,session_state
--having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=1
order by 1,2,3 desc nulls last;
col USERNAME for a20
clear breaks
break on username
with b as (
select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
from gv$active_session_history
where sample_time>systimestamp-numtodsinterval(&1,'second')
)
select (select us.name from sys.user$ us where us.user#=user_id)||case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
sql_id,
round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by user_id,program,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;
clear breaks
break on session_id
with b as (
select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
from gv$active_session_history
where sample_time>systimestamp-numtodsinterval(&1,'second')
)
select session_id,
sql_id,
round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>systimestamp-numtodsinterval(&1,'second')
group by session_id,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;
clear breaks
====
According to the execution time check sql
set linesize 700 pagesize 500
VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;
set numf 9999999999999
select
s.con_id
,s.sql_id
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, st.sql_text sqt
, elapsed_time/1000000 /decode(execs,0,null,execs) elpe
from
(select * from
( select
con_id
,sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(clwait_delta) clwait_time
, sum(iowait_delta) iowait_time
from dba_hist_sqlstat
where snap_id > :bid
and snap_id <= :eid
and PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by con_id,sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by elapsed_time desc, sql_id;
Prompt By CPU
select
s.con_id
,s.sql_id
, cpu_time/1000000 cpu_time
, elapsed_time/1000000 elapsed_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
--, st.sql_text sqtn
from
(select * from
( select
con_id
,sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > :bid
and snap_id <= :eid
and PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by con_id, sql_id
order by sum(cpu_time_delta) desc)
where rownum <= 10 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by cpu_time desc, sql_id;
Prompt Sort by I/O
select
s.con_id
,s.sql_id
, iowait_time/1000000 iowait_time
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, iowait_time/1000000/decode(execs,0,null,execs) iope
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select con_id,sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > :bid
and snap_id <= :eid
and PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by con_id,sql_id
order by sum(iowait_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by iowait_time desc, reads desc, sql_id;
Prompt -- press gets
select
s.con_id
,s.sql_id
, gets
, reads
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select
con_id
,sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > :bid
and snap_id <= :eid
and PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by con_id,sql_id
order by sum(buffer_gets_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by gets desc, cpu_time desc, sql_id;
Prompt --By execution times
select
s.con_id
,s.sql_id
, execs
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, ' ' ep
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
--, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select
con_id
,sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > :bid
and snap_id <= :eid
and PARSING_SCHEMA_NAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by con_id,sql_id
order by sum(executions_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by execs desc, sql_id;
create or replace view slow_sql_view as
select to_char(sysdate-1/24,'yyyy-mm-dd hh24') as snapshot_time,
v_1.sql_id,
v_1.elapsed_time,
v_1.cpu_time,
v_1.iowait_time,
v_1.gets,
v_1.reads,
v_1.rws,
v_1.clwait_time,
v_1.execs,
v_1.elpe,
nvl(v_2.machine,'null') as machine,
nvl(v_2.username,'null') as username,
to_char(substr(v_1.sqt,1,3000)) as sql
from
(select s.sql_id,
round(elapsed_time / 1000000,2) elapsed_time,
round(cpu_time / 1000000,2) cpu_time,
round(iowait_time / 1000000,2) iowait_time,
gets,
reads,
rws,
round(clwait_time / 1000000,2) clwait_time,
execs,
st.sql_text sqt,
round(elapsed_time / 1000000 / decode(execs, 0, null, execs),2) elpe
from (select *
from (select sql_id,
sum(executions_delta) execs,
sum(buffer_gets_delta) gets,
sum(disk_reads_delta) reads,
sum(rows_processed_delta) rws,
sum(cpu_time_delta) cpu_time,
sum(elapsed_time_delta) elapsed_time,
sum(clwait_delta) clwait_time,
sum(iowait_delta) iowait_time
from dba_hist_sqlstat
where snap_id =(select max(snap_id) from dba_hist_snapshot)
group by sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20) s,
dba_hist_sqltext st
where st.sql_id = s.sql_id) v_1
left join
(select distinct a.sql_id, a.machine, b.username
from dba_hist_active_sess_history a
left join dba_users b
on a.user_id = b.user_id
where a.snap_id = (select max(snap_id) from dba_hist_snapshot)) v_2
on v_1.sql_id = v_2.sql_id
where v_1.elpe >=1
order by elpe desc
;
====
set linesize 700
col elaexe for 99999.99990
col cpu_time for 99999.99990
col app_wait for 99999.99990
col plsql_t for 99999.99990
col java_exec_t for 99999.99990
col opt_mode for a12
col P_schema for a20
col sql_text for a100 wrap
/*
prompt 2. INSERT;
prompt 3. SELECT;
prompt 6. UPDATE;
prompt 7. DELETE;
prompt 47. PL/SQL EXECUTE;
*/
--accept excludelist prompt "Enter command types for exclude: ";
with top_sql_ids as (
select--+ no_merge
sql_id
from
(
select a.sql_id,a.elapsed_time
from gv$sqlarea a
where 1=1
--and a.command_type not in ('INSERT%')
order by a.elapsed_time desc
)
where rownum<=10
)
select
s.sql_id
,s.elapsed_time/1e6 "Elapsed(sec)"
,s.executions
,decode(s.executions,0,0, s.ROWS_PROCESSED /s.executions) rows_per_exec
,decode(s.executions,0,0, s.elapsed_time/1e6/s.executions) elaexe
,decode(s.executions,0,0, s.CPU_TIME/1e6/s.executions) cpu_time
,decode(s.executions,0,0, s.APPLICATION_WAIT_TIME/1e6/s.executions) app_wait
,decode(s.executions,0,0, s.CONCURRENCY_WAIT_TIME/1e6/s.executions) concurrency
,decode(s.executions,0,0, s.USER_IO_WAIT_TIME /1e6/s.executions) io_wait
,decode(s.executions,0,0, s.PLSQL_EXEC_TIME /1e6/s.executions) plsql_t
,decode(s.executions,0,0, s.java_exec_time /1e6/s.executions) java_exec_t
,s.OPTIMIZER_MODE opt_mode
,s.OPTIMIZER_COST cost
,s.OPTIMIZER_ENV_HASH_VALUE env_hash
,s.PARSING_SCHEMA_NAME P_schema
,substr(s.sql_text,1,150) as sql_text
from top_sql_ids ids
,gv$sqlarea s
where ids.sql_id=s.sql_id
/
col elaexe clear
col cpu_time clear
col app_wait clear
col plsql_t clear
col java_exec_t clear
col opt_mode clear
col P_schema clear
col sql_text clear
SQL_ID Elapsed(sec) EXECUTIONS ROWS_PER_EXEC ELAEXE CPU_TIME APP_WAIT CONCURRENCY IO_WAIT PLSQL_T JAVA_EXEC_T OPT_MODE COST ENV_HASH P_SCHEMA SQL_TEXT
------------- ------------ ---------- ------------- ------------ ------------ ------------ ----------- ---------- ------------ ------------ ------------ ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
6mcpb06rctk0x 9488.43854 2 0 4744.21927 4701.82623 .00365 .002752 .9720005 .11989 .00000 CHOOSE 0 3608716814 SYS
set lines 1000 pages 200
col sid for 9999
col serial for 999999
col status for a15
col username for a10
col sql_text for a80
col module for a30
col program for a70
col SQL_EXEC_START for a20
col kill for a16
SELECT * FROM (SELECT ''''||sid ||','|| session_serial#||',@'||inst_id ||'''' as kill,status,username,sql_id,SQL_PLAN_HASH_VALUE, MODULE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,70) sql_text
FROM gv$sql_monitor
where 1=1
and status='EXECUTING'
and module not like '%emagent%'
ORDER BY sql_exec_start desc
);
===
set linesize 300
SELECT * FROM
(SELECT dhs.sql_id,
ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs,
ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs,
SUM(dhs.disk_reads_delta) disk_reads,
SUM(dhs.buffer_gets_delta) buffer_gets,
SUM(dhs.px_servers_execs_delta) px_server_execs,
SUM(dhs.rows_processed_delta) rows_processed,
SUM(dhs.executions_delta) executions,
ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs,
ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs,
ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs,
ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs
FROM dba_hist_sqlstat dhs, gv$database d, gv$instance i
WHERE dhs.dbid = d.dbid
AND dhs.instance_number = i.instance_number
-- AND dhs.snap_id > 10875 AND dhs.snap_id <= 10876
GROUP BY dhs.sql_id
ORDER BY 2 DESC
)
WHERE ROWNUM <= 10;
============
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
col PLAN_TABLE_OUTPUT for a200
select
s.elapsed_time_delta, s.buffer_gets_delta, s.disk_reads_delta, cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
from dba_hist_sqltext t, dba_hist_sqlstat s
where 1=1
and t.dbid = s.dbid
and t.sql_id = s.sql_id
and s.snap_id between :BgnSnap and :EndSnap
-- and t.sql_text like 'select /*+ Anuj */%'
;
======================
set linesize 500 pagesize 500
col SQL for a25
SELECT
SQL, SQL_ID, CPU_SECONDS_FORM CPU, ELAPSED_SECONDS_FORM ELAPSED, DISK_READS, BUFFER_GETS, EXECUTIONS_FORM EXECS, MODULE, LAST_ACTIVE_TIME_FORM
FROM
( SELECT D.* ,ROWNUM ROW#
FROM ( SELECT
D.* FROM
( SELECT
substr(SQL_TEXT, 1, 25) AS SQL
,S.CPU_TIME / 1000000 AS CPU_SECONDS
,CASE WHEN
S.CPU_TIME < 1000
THEN
'< 1 ms'
WHEN
S.CPU_TIME < 1000000
THEN
TO_CHAR(ROUND(S.CPU_TIME / 1000,1) ) || ' ms'
WHEN
S.CPU_TIME < 60000000
THEN
TO_CHAR(ROUND(S.CPU_TIME / 1000000,1) ) || ' s'
ELSE
TO_CHAR(ROUND(S.CPU_TIME / 60000000,1) ) || ' m'
END
AS CPU_SECONDS_FORM
,DECODE(L.MAX_CPU_TIME,0,0,S.CPU_TIME / L.MAX_CPU_TIME) AS CPU_SECONDS_PROP
,S.ELAPSED_TIME / 1000000 AS ELAPSED_SECONDS
,CASE WHEN
S.ELAPSED_TIME < 1000
THEN
'< 1 ms'
WHEN
S.ELAPSED_TIME < 1000000
THEN
TO_CHAR(ROUND(S.ELAPSED_TIME / 1000,1) ) || ' ms'
WHEN
S.ELAPSED_TIME < 60000000
THEN
TO_CHAR(ROUND(S.ELAPSED_TIME / 1000000,1) ) || ' s'
ELSE
TO_CHAR(ROUND(S.ELAPSED_TIME / 60000000,1) ) || ' m'
END
AS ELAPSED_SECONDS_FORM
,DECODE(L.MAX_ELAPSED_TIME,0,0,S.ELAPSED_TIME / L.MAX_ELAPSED_TIME) AS ELAPSED_SECONDS_PROP
,S.DISK_READS AS DISK_READS
,CASE WHEN
S.DISK_READS < 1000
THEN
TO_CHAR(S.DISK_READS)
WHEN
S.DISK_READS < 1000000
THEN
TO_CHAR(ROUND(S.DISK_READS / 1000,1) ) || 'K'
WHEN
S.DISK_READS < 1000000000
THEN
TO_CHAR(ROUND(S.DISK_READS / 1000000,1) ) || 'M'
ELSE
TO_CHAR(ROUND(S.DISK_READS / 1000000000,1) ) || 'G'
END
AS DISK_READS_FORM
,DECODE(L.MAX_DISK_READS,0,0,S.DISK_READS / L.MAX_DISK_READS) AS DISK_READS_PROP
,S.BUFFER_GETS AS BUFFER_GETS
,CASE WHEN
S.BUFFER_GETS < 1000
THEN
TO_CHAR(S.BUFFER_GETS)
WHEN
S.BUFFER_GETS < 1000000
THEN
TO_CHAR(ROUND(S.BUFFER_GETS / 1000,1) ) || 'K'
WHEN
S.BUFFER_GETS < 1000000000
THEN
TO_CHAR(ROUND(S.BUFFER_GETS / 1000000,1) ) || 'M'
ELSE
TO_CHAR(ROUND(S.BUFFER_GETS / 1000000000,1) ) || 'G'
END
AS BUFFER_GETS_FORM
,DECODE(L.MAX_BUFFER_GETS,0,0,S.BUFFER_GETS / L.MAX_BUFFER_GETS) AS BUFFER_GETS_PROP
,S.EXECUTIONS AS EXECUTIONS
,CASE WHEN
S.EXECUTIONS < 1000
THEN
TO_CHAR(S.EXECUTIONS)
WHEN
S.EXECUTIONS < 1000000
THEN
TO_CHAR(ROUND(S.EXECUTIONS / 1000,1) ) || 'K'
WHEN
S.EXECUTIONS < 1000000000
THEN
TO_CHAR(ROUND(S.EXECUTIONS / 1000000,1) ) || 'M'
ELSE
TO_CHAR(ROUND(S.EXECUTIONS / 1000000000,1) ) || 'G'
END
AS EXECUTIONS_FORM
,DECODE(L.MAX_EXECUTIONS,0,0,S.EXECUTIONS / L.MAX_EXECUTIONS) AS EXECUTIONS_PROP
,DECODE(S.MODULE,NULL,' ',S.MODULE) AS MODULE
,S.LAST_ACTIVE_TIME AS LAST_ACTIVE_TIME
,DECODE(S.LAST_ACTIVE_TIME,NULL,' ',TO_CHAR(S.LAST_ACTIVE_TIME,'DD-Mon-YYYY HH24:MI:SS') ) AS LAST_ACTIVE_TIME_FORM
,S.SQL_ID AS SQL_ID
,S.CHILD_NUMBER AS CHILD_NUMBER
,S.INST_ID AS INST_ID
FROM
GV$SQL S
,(
SELECT
MAX(CPU_TIME) AS MAX_CPU_TIME
,MAX(ELAPSED_TIME) AS MAX_ELAPSED_TIME
,MAX(DISK_READS) AS MAX_DISK_READS
,MAX(BUFFER_GETS) AS MAX_BUFFER_GETS
,MAX(EXECUTIONS) AS MAX_EXECUTIONS
FROM
GV$SQL
) L
) D
ORDER BY
CPU_SECONDS_PROP DESC
,SQL
,DISK_READS_PROP
,BUFFER_GETS_PROP
,EXECUTIONS_PROP
,ELAPSED_SECONDS_PROP
,MODULE
,LAST_ACTIVE_TIME
) D
) D
WHERE ROW# >= 1
AND ROW# <= 50
====
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
define DB_BLOCK_SIZE=8192
define SQL_TOP_N = 10
define DBID=''
col MODULE for a20
SELECT * FROM(
SELECT substr(REGEXP_REPLACE(s.module,'^(.+?)@.+$','\1'),1,30) module,s.action,s.sql_id,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,
PARSING_SCHEMA_NAME,
DENSE_RANK() OVER (ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER (ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER (ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER (ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank,
sum(EXECUTIONS_DELTA) execs,
sum(ELAPSED_TIME_DELTA) elap,
sum(BUFFER_GETS_DELTA) log_reads,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
count(distinct plan_hash_value) plan_count,
sum(px_servers_execs_delta) px_servers_execs
FROM dba_hist_sqlstat s,dba_hist_sqltext t
WHERE 1=1
--and s.dbid = &DBID
AND s.snap_id BETWEEN :bid and :eid
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN')
GROUP BY s.module,s.action,s.sql_id,t.command_type,PARSING_SCHEMA_NAME)
WHERE elap_rank <= &SQL_TOP_N
OR phys_reads_rank <= &SQL_TOP_N
or log_reads_rank <= &SQL_TOP_N
or exec_rank <= &SQL_TOP_N
order by elap_rank asc nulls last;
====
select INST_ID,
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
substr(sql_text,1,50) "SQL",
module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
(select sql_id
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
ELSE 'WAIT' END state
from gv$active_session_history
where session_type IN ( 'FOREGROUND')
and sample_time between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
group by sql_id, state), ranked_sqls AS
(select sql_id, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank
from sql_class
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
and rs.xrank <15 ))
===
select con_id,top_level_Sql_id, module, action, sum(10) ash_Secs from dba_hist_Active_Sess_history h
where sql_id != top_level_sql_id
group by con_id,top_level_sql_id, module, action
order by ash_Secs desc;
/
set linesize 300
define sql_id='fhf8upax5cxsz'
col SQL_TEXT for a100 wrap
with x as (
select sql_id, sum(10) ash_Secs
from dba_hist_active_sess_history
where top_level_sql_id = '&sql_id'
group by sql_id
)
select x.*
, (select sql_text from dba_hist_sqltext where sql_id = x.sql_id and rownum = 1) sql_text
from x order by ash_Secs desc;
-- Summary of SQL plans in AWR DBA_HIST_ACTIVE_SESS_HISTORY data
define sql_id='34cd4y8mbqvsk'
set linesize 500
col SQL_TEXT for a50 wrap
WITH src AS (
SELECT session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, inst_id
, MAX(sql_plan_hash_value) AS sql_plan_hash_value
, sql_exec_start
, CAST(max(sample_time) AS DATE) AS last_sample_time
, CAST(min(sample_time) AS DATE) AS first_sample_time
FROM gv$active_session_history
WHERE 1=1
and sql_id IN ('&sql_id')
GROUP BY session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, inst_id
, sql_exec_start
)
SELECT session_id
, session_serial#
, sql_exec_id
, src.sql_id
, sql_plan_hash_value
, sql_exec_start
, first_sample_time
, last_sample_time
, ROUND((last_sample_time-sql_exec_start)*86400) AS durn_s
, CAST(SUBSTR(sql_text,1,100) AS VARCHAR2(200)) AS sql_text
FROM src
LEFT JOIN gv$sql sql
ON src.sql_id = sql.sql_id
AND src.sql_child_number = sql.child_number
AND src.inst_id = sql.inst_id
ORDER BY sql_exec_start;
define sql_id='34cd4y8mbqvsk'
SELECT sql_id
, TO_CHAR(sample_time,'yyyy-mm-dd') AS exec_day
, TO_CHAR(MIN(sample_time),'hh24:mi') AS first_exec
, TO_CHAR(MAX(sample_time),'hh24:mi') AS last_exec
, sql_plan_hash_value
, COUNT(*) AS nbr_samples
, COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id) AS execs
, ROUND(10*COUNT(*)/NULLIF(COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id),0)) AS avg_durn_s
, ROUND(SUM(delta_read_io_bytes)/1024/1024/1024/NULLIF(COUNT(DISTINCT session_id||'#'||session_serial#||'#'||sql_exec_id),0),2) avg_read_GB
, SUM(delta_read_io_requests) read_req
, SUM(delta_write_io_requests) write_req
, ROUND(SUM(delta_read_io_bytes)/1024/1024/1024,2) read_GB
, ROUND(SUM(delta_write_io_bytes)/1024/1024/1024,2) write_GB
, ROUND(SUM(delta_interconnect_io_bytes)/1024/1024/1024,2) interconn_GB
, ROUND(SUM(
CASE WHEN tm_delta_cpu_time <= 64*tm_delta_time THEN tm_delta_cpu_time ELSE 0 END/NULLIF(tm_delta_time,0)),2) cpu_s
, ROUND(SUM(
CASE WHEN tm_delta_db_time <= 64*tm_delta_time THEN tm_delta_db_time ELSE 0 END/NULLIF(tm_delta_time,0)),2) dbtime_s
FROM dba_hist_active_sess_history
WHERE sql_id = '&sql_id'
--and top_level_sql_id = '&sql_id'
--AND sql_plan_hash_value <> 0
GROUP BY sql_id
, sql_plan_hash_value
, TO_CHAR(sample_time,'yyyy-mm-dd')
ORDER BY 2,3,1;
SQL_ID EXEC_DAY FIRST LAST_ SQL_PLAN_HASH_VALUE NBR_SAMPLES EXECS AVG_DURN_S AVG_READ_GB READ_REQ WRITE_REQ READ_GB WRITE_GB INTERCONN_GB CPU_S DBTIME_S
------------- ---------- ----- ----- ------------------- ----------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
34cd4y8mbqvsk 2022-11-07 23:29 23:59 0 2 2 10 0 16 0 0 .25 .47
34cd4y8mbqvsk 2022-11-08 01:00 02:41 693498460 34 32 11 0 49 0 0 33.35 33.66
34cd4y8mbqvsk 2022-11-08 01:00 01:00 1320773586 1 1 10 0 18 0 0 .61 .68
34cd4y8mbqvsk 2022-11-09 00:42 00:42 834029895 1 1 10 0 7 0 0 .45 .65
34cd4y8mbqvsk 2022-11-09 01:00 03:43 693498460 67 64 10 0 184 0 0 62.56 63.23
— List of individual executions in DBA_HIST_ACTIVE_SESS_HISTORY
define nbr_days_history=1
define sql_id='34cd4y8mbqvsk'
WITH src AS (
SELECT session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, instance_number
, MAX(sql_plan_hash_value) AS sql_plan_hash_value
, sql_exec_start
, CAST(max(sample_time) AS DATE) AS last_sample_time
, CAST(min(sample_time) AS DATE) AS first_sample_time
, SUM(delta_read_io_bytes) read_bytes
, SUM(tm_delta_cpu_time) cpu_time
FROM dba_hist_active_sess_history
WHERE sql_id IN ('&sql_id')
--AND sample_time >= sysdate – &nbr_days_history.
AND sql_exec_id IS NOT NULL
GROUP BY session_id
, session_serial#
, sql_exec_id
, sql_id
, sql_child_number
, instance_number
, sql_exec_start
)
SELECT session_id
, session_serial#
, instance_number
, sql_exec_id
, src.sql_id
, sql_plan_hash_value
, sql_exec_start
, first_sample_time
, last_sample_time
, ROUND((last_sample_time-sql_exec_start)*86400) AS durn_s
, ROUND(read_bytes/1024/1024,2) read_MB
, ROUND(src.cpu_time/1e6,2) cpu_s
, CAST(SUBSTR(sql_text,1,100) AS VARCHAR2(200)) AS sql_text
FROM src
LEFT JOIN gv$sql sql
ON src.sql_id = sql.sql_id
AND src.sql_child_number = sql.child_number
AND src.instance_number = sql.inst_id
ORDER BY sql_exec_start, first_sample_time;
SESSION_ID SESSION_SERIAL# INSTANCE_NUMBER SQL_EXEC_ID SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_START FIRST_SAMPLE_T LAST_SAMPLE_TI DURN_S READ_MB CPU_S SQL_TEXT
---------- --------------- --------------- ----------- ------------- ------------------- -------------- -------------- -------------- ---------- ---------- ---------- --------------------------------------------------
394 27416 1 16873452 34cd4y8mbqvsk 693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00 4 9.59
394 27416 1 16873453 34cd4y8mbqvsk 693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00 8 .01 9.94
394 27416 1 16873454 34cd4y8mbqvsk 693498460 08-11-22 01:00 08-11-22 01:00 08-11-22 01:00 6 9.95
— Wait events and objects for a single execution from DBA_HIST_ACTIVE_SESS_HISTORY
set linesize 500 pagesize 300
define sql_id='34cd4y8mbqvsk'
define sid=394
define serial=27416
define sql_exec_id=16873452
define instance_number=1
col FIRST_SAMPLE for a27
col LAST_SAMPLE for a27
col OWNER for a15
col OBJECT_NAME for a20
col EVENT for a20
SELECT sql_id
, sql_plan_hash_value
, sql_exec_id
, NVL(event,session_state) AS event
, sql_plan_line_id
, object_type
, owner
, current_obj#
, object_name
, COUNT(DISTINCT subobject_name) nbr_sub_objects
, SUM(10) s_in_wait
, MIN(sample_time) first_sample
, MAX(sample_time) last_sample
FROM dba_hist_active_sess_history
LEFT JOIN dba_objects
ON current_obj# = object_id
WHERE sql_id = '&sql_id'
--AND sql_exec_id = '&sql_exec_id'
AND instance_number = '&instance_number'
AND session_id = '&sid'
AND session_serial# = '&serial'
GROUP BY sql_id
, sql_plan_hash_value
, sql_exec_id
, NVL(event,session_state)
, sql_plan_line_id
, object_type
, owner
, current_obj#
, object_name
ORDER BY s_in_wait DESC
, event;
SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_ID EVENT SQL_PLAN_LINE_ID OBJECT_TYPE OWNER CURRENT_OBJ# OBJECT_NAME NBR_SUB_OBJECTS S_IN_WAIT FIRST_SAMPLE LAST_SAMPLE
------------- ------------------- ----------- -------------------- ---------------- ----------------------- --------------- ------------ -------------------- --------------- ---------- --------------------------- ---------------------------
34cd4y8mbqvsk 693498460 16873459 ON CPU 238 INDEX SYS 830 I_LOBFRAG$_FRAGOBJ$ 0 10 08-NOV-22 01.00.58.472 AM 08-NOV-22 01.00.58.472 AM
34cd4y8mbqvsk 1320773586 ON CPU -1 0 10 08-NOV-22 01.00.08.471 AM 08-NOV-22 01.00.08.471 AM
34cd4y8mbqvsk 693498460 16873454 ON CPU 128 -1 0 10 08-NOV-22 01.00.38.471 AM 08-NOV-22 01.00.38.471 AM
34cd4y8mbqvsk 693498460 16873453 ON CPU 257 -1 0 10 08-NOV-22 01.00.28.471 AM 08-NOV-22 01.00.28.471 AM
34cd4y8mbqvsk 693498460 16873459 ON CPU 114 INDEX SYS 830 I_LOBFRAG$_FRAGOBJ$ 0 10 08-NOV-22 01.00.48.472 AM 08-NOV
— List of wait events per execution from GV$ACTIVE_SESSION_HISTORY
define sql_id='34cd4y8mbqvsk'
WITH t1 AS (
SELECT sql_id
, session_id
, session_serial#
, sql_exec_start
, sql_exec_id
, sql_plan_hash_value
, sql_plan_line_id
, sql_plan_operation
, sql_plan_options
, event
, COUNT(*) AS samples
FROM gv$active_session_history
WHERE sql_id='&sql_id'
GROUP BY sql_id
, session_id
, session_serial#
, sql_exec_start
, sql_exec_id
, sql_plan_hash_value
, sql_plan_line_id
, sql_plan_operation
, sql_plan_options
, event
)
SELECT * FROM t1
ORDER BY sql_exec_start DESC, samples DESC;
SQL_ID SESSION_ID SESSION_SERIAL# SQL_EXEC_START SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS EVENT SAMPLES
------------- ---------- --------------- -------------- ----------- ------------------- ---------------- ------------------------------ ------------------------------ -------------------- ----------
34cd4y8mbqvsk 5 34822 2593769849 2
34cd4y8mbqvsk 5 16271 0 2
34cd4y8mbqvsk 292 60379 0 2
34cd4y8mbqvsk 199 57266 0 2
34cd4y8mbqvsk 776 61802 0 2
34cd4y8mbqvsk 776 12672 0 2
34cd4y8mbqvsk 5 28388 0 2
34cd4y8mbqvsk 200 34854 665610596 1
34cd4y8mbqvsk 483 51658 1673451505 acknowledge over PGA 1
limit
===
set linesize 500
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BgnSnap;
v_end_snap_id number := :EndSnap;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
--,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id
and PARSING_SCHEMA_NAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
)
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');
for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;
for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;
for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/
SQL=1fasdju62v6a7 was top SQL 1 times
snapshot = 115356 ** execs = 4 ** gets_per_exec = 71908924 ** rows_per_exec 12.5 ** ela_per_exec 188.85
snapshot = 115627 ** execs = 1 ** gets_per_exec = 149567 ** rows_per_exec 10 ** ela_per_exec .38
snapshot = 115627 ** execs = 2 ** gets_per_exec = 67338241.5 ** rows_per_exec 10 ** ela_per_exec 172.28
snapshot = 116005 ** execs = 1 ** gets_per_exec = 148350 ** rows_per_exec 10 ** ela_per_exec .43
snapshot = 116005 ** execs = 2 ** gets_per_exec = 67316735 ** rows_per_exec 10 ** ela_per_exec 174.86
snapshot = 116204 ** execs = 3 ** gets_per_exec = 76098606 ** rows_per_exec 16.67 ** ela_per_exec 197.58
snapshot = 116203 ** execs = 1 ** gets_per_exec = 149129 ** rows_per_exec 10 ** ela_per_exec .45
snapshot = 116203 ** execs = 6 ** gets_per_exec = 62077108.17 ** rows_per_exec 6.67 ** ela_per_exec 162.82
snapshot = 116788 ** execs = 1 ** gets_per_exec = 149540 ** rows_per_exec 10 ** ela_per_exec .42
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BgnSnap;
v_end_snap_id number := :EndSnap;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
--,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id)
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');
for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;
for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs
|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;
for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name
|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/
set linesize 400 pagesize 300
col GETS_SQL for a27
col CPU_SQL for a27
col ELAPSED_SQL for a27
col EXECUTIONS_SQL for a27
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
with awr_ranks as
(
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank,
executions_delta,
dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
), rank as
(
select level rank from dual connect by level <= 5
)
select snap_id,
rank,
max(case gets_rank when rank then to_char(buffer_gets_delta)||': '||sql_id end) gets_sql,
max(case cpu_rank when rank then to_char(round(cpu_time_delta/1000000,2))||': '||sql_id end) cpu_sql,
max(case elapsed_rank when rank then to_char(round(elapsed_time_delta/1000000,2))||': '||sql_id end) elapsed_sql,
max(case executions_rank when rank then to_char(executions_delta)||': '||sql_id end) executions_sql
from awr_ranks, rank
where snap_id between :BgnSnap and :EndSnap
group by snap_id, rank
order by snap_id, rank;
SNAP_ID RANK GETS_SQL CPU_SQL ELAPSED_SQL EXECUTIONS_SQL
---------- ---------- ------------------------------------------------------- ------------------------------------------------------- ------------------------------------------------------- -------------------------------------------------------
4421 2 671550216: drq81bs3crpat 1602.21: 9px0f4mv0tn0t 1606.38: 9px0f4mv0tn0t 472475: 97ajm1fqw1bbu
4421 3 660785900: ghvcahvbqmccz 1506.73: 499ds2y15j9zf 1538.66: 499ds2y15j9zf 471521: 41770y3h89crb
4421 4 465180962: 57h83vb5f41ht 1481.32: f0qkr7vnva09k 1522.38: 3swzhzyxqjfw3 466932: 2jfqzrxhrm93b
4421 5 410826590: 5gqv37qghnh9q 1477.85: 0ksuvynpvkjm7 1520.09: f0qkr7vnva09k 246681: ct1xj8wz8sb0j
15 rows selected.
define sql_id='1fasdju62v6a7'
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
col PARSING_SCHEMA_NAME for a20
set lines 200 pages 200
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , sql_id,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a
where sql_id in('&sql_id')
order by snap_id, INSTANCE_NUMBER
;
=====
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT buffer_gets,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
where 1=1
--and last_exec_start_time > sysdate -1
ORDER BY buffer_gets desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT disk_reads,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 10000000'))
ORDER BY disk_reads desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
cl sql_text for a50 wrap
SELECT cpu_time / 1000000 as cpu_time_secs,
executions,
last_exec_start_time,
parsing_schema_name,
module, action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY cpu_time desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT elapsed_time / 1000000 as elapsed_time_secs,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY elapsed_time desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT executions,
last_exec_start_time,
parsing_schema_name,
module, action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY executions desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT direct_writes,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 100000'))
ORDER BY direct_writes desc;
set linesize 300 pagesize 200
col PARSING_SCHEMA_NAME for a20
col MODULE for a30
col ACTION for a25
col sql_text for a50 wrap
SELECT rows_processed,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 1000000'))
ORDER BY rows_processed desc;
set numf 99999999999999999
col PARSING_SCHEMA_NAME for a20
col sql_text for a20
SELECT sql_id ,substr(sql_text,1,20) sql_text,disk_reads ,cpu_time ,elapsed_time ,buffer_gets ,parsing_schema_name
FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter => 'parsing_schema_name <> ''SYS''' ,ranking_measure1 => 'cpu_time' ,result_limit => 10 ));
====
set linesize 500
col sql_text for a40 wrap
select
*
from
(
select
con_id,
sql_id,
plan_hash_value,
executions,
case
when elapsed_time > 0 then
elapsed_time/1000
else
0
end elapsed_time_ms,
case
when executions > 0 then
round(elapsed_time/nvl(executions, 1)/1000, 2)
else
0
end elapsed_time_per_exec_ms,
rows_processed,
px_servers_executions,
sorts,
invalidations,
parse_calls,
buffer_gets,
disk_reads,
VERSION_COUNT,
sql_text
from
gv$sqlstats
order by
elapsed_time_per_exec_ms desc
)
where
rownum <= 50
;
oracle_top_sql_history.sql
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set linesize 500 pagesize 300
col sql_text for a50
col SQL_PROFILE for a20
col MODULE for a20
select
*
from
(
select
ss.module,
ss.snap_id,
ss.con_id,
ss.sql_id,
ss.plan_hash_value,
ss.executions_total,
case
when ss.elapsed_time_total > 0 then
ss.elapsed_time_total/1000
else
0
end elapsed_time_ms,
case
when ss.executions_total > 0 then
round(ss.elapsed_time_total/nvl(ss.executions_total, 1)/1000, 2)
else
0
end elapsed_time_per_exec_ms,
ss.rows_processed_total,
ss.px_servers_execs_total,
ss.sorts_total,
ss.invalidations_total,
ss.parse_calls_total,
ss.buffer_gets_total,
ss.disk_reads_total,
ss.optimizer_mode,
ss.sql_profile,
to_char(substr(st.sql_text,1,50)) sql_text
from
dba_hist_sqlstat ss
inner join
dba_hist_sqltext st
on ss.sql_id = st.sql_id
where 1=1
and ss.snap_id between :BgnSnap and :EndSnap
order by elapsed_time_per_exec_ms desc
)
where
rownum <= 50;
set pagesize 300 linesize 300 numf 99999999999999999999
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY buffer_gets DESC)
WHERE rownum <= 5
;
PROMPT >> disk_reads <<
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5
;
PROMPT >> sorts <<
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY sorts DESC)
WHERE rownum <= 5
;
PROMPT >> cpu <<
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY cpu_min DESC)
WHERE rownum <= 5
;
PROMPT >> rows_processed <<
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY rows_processed DESC)
WHERE rownum <= 5
;
PROMPT >> elapsed_time <<
SELECT *
FROM
(SELECT
con_id
,sql_id
,buffer_gets
,disk_reads
,sorts
,ROUND(cpu_time/(1e6*60)) cpu_min
,rows_processed
,elapsed_time
FROM gv$sqlstats
ORDER BY elapsed_time DESC)
WHERE rownum <= 5
;
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
--exec select DBID into :DID from v\$database;
--exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
set linesize 200 trimspool on pagesize 60 verify off
column begin_interval_time format a35
column end_interval_time format a35
break on sql_id skip 1 on instance_number
column sdate new_value sdt noprint
select to_char(sysdate, 'DD-MM-YYYY HHMI') sdate from dual;
--
define 1=50
prompt
prompt Historic
prompt
prompt Elapsed by exec
prompt
select distinct x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time
from
(select instance_number, sql_id, snap_id,
round((elapsed_time_total/1000000)/(case when executions_total = 0 then 1 else executions_total end),4) time_per_exec,
round(elapsed_time_total/1000000, 4) elapsed_time_total
from dba_hist_sqlstat) x, (select snap_id,
max(begin_interval_time) begin_interval_time,
max(end_interval_time) end_interval_time
from dba_hist_snapshot
where 1=1
and snap_id between :BgnSnap and :EndSnap
group by snap_id) s
where s.snap_id = x.snap_id
and x.time_per_exec > &&1
and x.time_per_exec <> x.elapsed_time_total
order by 2 asc, 3 desc, 6 desc
/
alter session set nls_date_format='dd-mm-YYYY hh24:mi';
clear breaks
prompt
prompt Elapsed time total
prompt
select inst_id,
con_id,
sql_id,
executions,
round(elapsed_time/1000000, 6) elapsed_sec,
round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
order by 5 desc
/
prompt
prompt Elapsed per exec
prompt
select inst_id,
con_id,
sql_id,
executions,
round(elapsed_time/1000000, 6) elapsed_sec,
round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
order by 5 desc
/
set linesize 500 pagesize 300
var sqlid VARCHAR2(13);
begin :sqlid := 'g0bggfqrddc4w'; end;
/
col sql_text for a20
select inst_id,con_id, sql_id,rank_elap_per_exec, elapsed_per_exec , elapsed_time, executions, cpu_time, applic_wait_time, user_io_wait_time,sql_text
from (
select inst_id,
con_id,
sql_id,
trunc(elapsed_time/1000000,1) elapsed_time,
executions,
trunc(cpu_time/1000000,1) cpu_time,
trunc(application_wait_time/1000000,1) applic_wait_time,
trunc(user_io_wait_time/1000000,1) user_io_wait_time,
trunc(concurrency_wait_time/1000000,1) concurr_time,
trunc((elapsed_time/decode(executions, 0,1, executions))/1000000,1) elapsed_per_exec,
RANK() OVER (ORDER BY trunc((elapsed_time/decode(executions, 0,1, executions))/1000000,1) desc) rank_elap_per_exec,
substr(sql_text,1,20) sql_text
from gv$sqlstats
WHERE 1=1
and LAST_ACTIVE_TIME > sysdate - interval '15' minute
--and inst_id not in (select instance_number FROM v$instance)
-- and sql_id= :sqlid
)
where rank_elap_per_exec<21
order by rank_elap_per_exec
/
set linesize 700 numf 999999999999999999 pagesize 500 col Rank for 9999 SELECT * FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.* FROM (SELECT TO_CHAR (hs.begin_interval_time, 'dd-MM-YY' ) "Snap Day", SUM (shs.executions_delta) "Execs", SUM (shs.buffer_gets_delta) "Buffer Gets", SUM (shs.disk_reads_delta) "Disk Reads", ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) "Gets/Exec", ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "CPU/Exec(S)", ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "IO/Exec(S)", shs.sql_id "Sql id", REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql" FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht ON (sht.sql_id = shs.sql_id) INNER JOIN dba_hist_snapshot hs ON (shs.snap_id = hs.snap_id) HAVING SUM (shs.executions_delta) > 0 GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'dd-MM-YY'), CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ) ORDER BY "Snap Day" DESC) i1 ORDER BY "Snap Day" DESC) WHERE "Rank" <= 50 AND "Snap Day" = TO_CHAR (SYSDATE, 'dd-MM-YY') ; define sql_id='7grvag6ayaxn1' col execs for 999,999,999 col etime for 999,999,999.9 col avg_etime for 999,999.999 col avg_cpu_time for 999,999.999 col avg_lio for 999,999,999.9 col avg_pio for 9,999,999.9 col begin_interval_time for a30 col node for 99999 col plan_hash_value for 99999999999999 break on plan_hash_value on startup_time skip 1 select sql_id, plan_hash_value, sum(execs) execs, -- sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(cpu_time)/sum(execs) avg_cpu_time, sum(lio)/sum(execs) avg_lio, sum(pio)/sum(execs) avg_pio from ( select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, elapsed_time_delta/1000000 etime, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, buffer_gets_delta lio, disk_reads_delta pio, cpu_time_delta/1000000 cpu_time, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id',sql_id) and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number -- and executions_delta > 0 ) group by sql_id, plan_hash_value order by 5 /
set linesize 300 pagesize 300
VARIABLE BEGIN_SNAP_ID NUMBER
VARIABLE END_SNAP_ID NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -2 into :BEGIN_SNAP_ID from dba_hist_snapshot ;
exec select max(snap_id) into :END_SNAP_ID from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;
set serveroutput on
declare
clobsqltext CLOB;
v_start_snap_id number := :BEGIN_SNAP_ID;
v_end_snap_id number := :END_SNAP_ID;
begin
for v_sql_id in (select sql_id,count(*) cnt from (
select snap_id, sql_id,
buffer_gets_delta,
dense_rank() over (partition by snap_id order by buffer_gets_delta desc) gets_rank,
cpu_time_delta,
dense_rank() over (partition by snap_id order by cpu_time_delta desc) cpu_rank,
elapsed_time_delta,
dense_rank() over (partition by snap_id order by elapsed_time_delta desc) elapsed_rank
--,executions_delta
,dense_rank() over (partition by snap_id order by executions_delta desc) executions_rank
from sys.wrh$_sqlstat
where snap_id>=v_start_snap_id and snap_id<=v_end_snap_id)
where gets_rank<=5 or cpu_rank<=5 or elapsed_rank<=5
--or executions_rank<=5
group by sql_id
order by cnt desc)
loop
dbms_output.put_line('SQL='||v_sql_id.sql_id || ' was top SQL ' || v_sql_id.cnt || ' times');
for line in (select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR(v_sql_id.sql_id)))
loop
dbms_output.put_line(line.plan_table_output);
END LOOP;
for line in (select snap_id,executions_delta execs,
round(buffer_gets_delta/executions_delta,2) gets_per_exec,
round(rows_processed_delta/executions_delta,2) rows_per_exec,
round((elapsed_time_delta/1000000)/executions_delta,2) ela_per_exec
from sys.wrh$_sqlstat
where sql_id=v_sql_id.sql_id
and executions_delta > 0)
loop
dbms_output.put_line('snapshot = ' || line.snap_id || ' ** execs = ' || line.execs
|| ' ** gets_per_exec = ' || line.gets_per_exec || ' ** rows_per_exec ' || line.rows_per_exec || ' ** ela_per_exec '|| line.ela_per_exec);
end loop;
for line in ( select snap_id,name, position, datatype_string, value_string from dba_hist_sqlbind where sql_id=v_sql_id.sql_id)
loop
if line.value_string is not null then
dbms_output.put_line('**** bind variables for snapshot = ' || line.snap_id || ' name=' || line.name
|| ' position=' || line.position || ' datatype=' || line.datatype_string || ' value=' || line.value_string);
end if;
end loop;
end loop;
END;
/
-- active_sql_workarea.sql
set linesize 500 pagesize 400
col sidser format a18 head 'Sid,Serial'
col operid format 999 head 'Oper|Id'
col oper format a14 head 'Oper Type' trunc
col esizek format 9,999,999 head 'EstSizeK '
col csizek format 99,999,999 head 'CurSizeK '
col msizek format 9,999,999 head 'MaxSizeK'
col p format 9
col tsizem format 999,999 head 'TempSizeM'
col sqlid_c format a17 head 'SqlId:Child'
col qcsid format 9999 head 'QC'
col sexecs format a05 head 'Start'
col minago format 99999999 head 'Min|Ago'
col module format a30 head 'Module' trunc
col event format a30 head 'Event' trunc
col ts format a08 head 'TempTS' trunc
col kill for a18
break on module on sql_id on qcsid skip 1 on sidser on report
compute sum of csizek on module
compute sum of csizek on report
compute sum of tsizem on report
select se.module module
,wa.sql_id||':'||se.sql_child_number sqlid_c
,wa.qcsid qcsid
-- ,lpad(se.sid,4,' ')||','||lpad(se.serial#,5,' ') sidser
,''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||'''' sidser
,se.con_id
,to_char(wa.sql_exec_start,'HH24:MI') sexecs
,(sysdate - wa.sql_exec_start)*24*60 minago
,se.event
,trunc(ACTUAL_MEM_USED/1024) csizeK
,operation_id operid
,operation_type oper
,trunc(EXPECTED_SIZE/1024) esizeK
,trunc(MAX_MEM_USED/1024) msizeK
,NUMBER_PASSES p
,tablespace ts
,trunc(TEMPSEG_SIZE/1024/1024) tsizeM
from gv$sql_workarea_active wa
,gv$session se
where wa.sid = se.sid
and wa.inst_id = se.inst_id
order by module
,sqlid_c
,operid
,oper
,sidser
;
SELECT *
FROM
(SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"+"Disk Reads"+"Buffer Gets"+"Writes"+"Sorts"+"Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day",
shs.sql_id "Sql id",
REPLACE(CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)),CHR (10),'') "Sql",
SUM(shs.executions_delta) "Execs",ROUND((SUM(shs.elapsed_time_delta)/1000000)/SUM (shs.executions_delta),1) "Time Ea Sec",
ROUND((SUM(shs.cpu_time_delta)/1000000)/SUM (shs.executions_delta),1) "CPU Ea Sec",
ROUND((SUM(shs.iowait_delta)/1000000)/SUM (shs.executions_delta),1) "IO/Wait Ea Sec",
SUM(shs.cpu_time_delta) "CPU Time",
SUM(shs.disk_reads_delta) "Disk Reads",
SUM(shs.buffer_gets_delta) "Buffer Gets",
SUM(shs.direct_writes_delta) "Writes",
SUM(shs.parse_calls_delta) "Parses",
SUM(shs.sorts_delta) "Sorts",
SUM(shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
and hs.begin_interval_time >sysdate - interval '60' minute
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,TO_CHAR(hs.begin_interval_time,'MM/DD/YY'),CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40))
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20
;
====
set linesize 500 pagesize 300 numf 999999999999999
col sql_fulltext for a100 wrap
-- sql with high io and memory consumption
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext
from gv$sqlarea
where buffer_gets > 10000000 or disk_reads > 1000000
order by buffer_gets + 100 * disk_reads desc
)where rownum<20 ;
-- sql accounted for io
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext
from gv$sqlarea
order by disk_reads desc
)where rownum<20 ;
--average execution slow sql
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext
from gv$sqlarea
order by cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) desc
)where rownum<20 ;
-- total time-consuming sql
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext
from gv$sqlarea
order by elapsed_time desc
)where rownum<20 ;
-- sql accounted for cpu
select * from ( select optimizer_cost, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,sql_id,hash_value, parsing_user_id,sorts,command_type,sql_fulltext
from gv$sqlarea
order by cpu_time desc
)where rownum<20 ;
====
find_sql_awr.sql
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
set long 32000 lines 300
col sql_text format a40
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio, sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, nvl(executions_delta,0) execs,elapsed_time_delta/1000000 etime,(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.snap_id between nvl(:BgnSnap,999999999) and nvl(:EndSnap,999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
--and sql_text like nvl('&sql_text',sql_text)
--and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/
***********************
SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF
COL pct FOR A10 JUST RIGHT
COL cpu_pct FOR 999.9
COL io_pct FOR 999.9
col OWNER for a20
col OBJECT_NAME for a20
col PROCEDURE_NAME for a20
BREAK ON day SKIP 1
DEF days=1
PROMPT Displaying daily top SQL for last &days days...
WITH ash AS (
SELECT
day
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds
, cpu_seconds
, LPAD(TRIM(TO_CHAR(RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100, '999.9'))||'%', 10) pct
, RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100 pct_num
FROM (
SELECT
TO_CHAR(sample_time, 'YYYY-MM-DD') day
, sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, SUM(10) total_seconds
, SUM(CASE WHEN wait_class = 'User I/O' THEN 10 ELSE 0 END) io_seconds
, SUM(CASE WHEN wait_class IS NULL THEN 10 ELSE 0 END) cpu_seconds
FROM
dba_hist_active_sess_history a
, dba_procedures p
WHERE
a.plsql_entry_object_id = p.object_id (+)
AND a.plsql_entry_subprogram_id = p.subprogram_id (+)
AND sample_time > SYSDATE - &days
AND session_type != 'BACKGROUND' -- ignore for now
GROUP BY
sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, TO_CHAR(sample_time, 'YYYY-MM-DD')
)
)
, sqlstat AS (
SELECT /*+ MATERIALIZE */
TO_CHAR(begin_interval_time, 'YYYY-MM-DD') day
, sql_id
, plan_hash_value
, SUM(executions_delta) executions
, SUM(rows_processed_delta) rows_processed
, SUM(disk_reads_delta) blocks_read
, SUM(disk_reads_delta)*8/1024 mb_read
, SUM(buffer_gets_delta) buffer_gets
, SUM(iowait_delta)/1000000 awr_iowait_seconds
, SUM(cpu_time_delta)/1000000 awr_cpu_seconds
, SUM(elapsed_time_delta)/1000000 awr_elapsed_seconds
FROM
dba_hist_snapshot
NATURAL JOIN
dba_hist_sqlstat
WHERE
begin_interval_time > SYSDATE - &days
GROUP BY
TO_CHAR(begin_interval_time, 'YYYY-MM-DD')
, sql_id
, plan_hash_value
)
SELECT /*+ MONITOR */
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value plan_hash
, ROUND(total_seconds / 3600,1) total_hours
, total_seconds
, executions
, ROUND(total_seconds / NULLIF(executions,0),2) seconds_per_exec
, io_pct
, cpu_pct
, mb_read
, ROUND(mb_read / NULLIF(executions,0),2) mb_per_exec
, buffer_gets
, ROUND(buffer_gets / NULLIF(executions,0),2) bufget_per_exec
, CASE WHEN sql_id IS NOT NULL THEN
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('''||sql_id||''','||CASE WHEN sql_plan_hash_value = 0 THEN 'NULL' ELSE TO_CHAR(sql_plan_hash_value) END||', format=>''ADVANCED''));'
END extract_plan_from_awr
FROM (
SELECT
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds/total_seconds*100 io_pct
, cpu_seconds/total_seconds*100 cpu_pct
, (SELECT executions FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) executions
, (SELECT mb_read FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) mb_read
, (SELECT buffer_gets FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) buffer_gets
FROM
ash
WHERE
ash.pct_num >= 1
)
ORDER BY
day DESC
, total_seconds DESC
/
DAY PCT OWNER OBJECT_NAME PROCEDURE_NAME SQL_ID PLAN_HASH TOTAL_HOURS TOTAL_SECONDS EXECUTIONS SECONDS_PER_EXEC IO_PCT CPU_PCT MB_READ MB_PER_EXEC BUFFER_GETS BUFGET_PER_EXEC EXTRACT_PLAN_FROM_AWR
---------- ---------- -------------------- -------------------- -------------------- ------------- ---------- ----------- ------------- ---------- ---------------- ------ ------- ---------- ----------- ----------- --------------- --------------------------------------------------------------------------------------------------------------------------
2023-08-12 78.6% ctyc1af1abg5u 200298931 91 327510 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
4.4% 0 5.1 18340 22.3 72.7
4.2% 3xjw1ncw5vh27 2487977020 4.9 17500 88.3 11.7 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3xjw1ncw5vh27',2487977020, format=>'ADVANCED'));
4.2% c7ngymcfjtb1c 1658157256 4.8 17450 43.8 56.2 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('c7ngymcfjtb1c',1658157256, format=>'ADVANCED'));
2023-08-11 55.1% ctyc1af1abg5u 200298931 55.9 201130 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
8.7% 1zuyhdtsmmx5s 3008400300 8.8 31680
define sql_id='ctyc1af1abg5u'
SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON
COL exec_per_sec FOR 99999990
COL ela_ms_per_sec FOR 99999990
COL rows_per_sec FOR 99999990
COL lios_per_sec FOR 99999990
COL blkrd_per_sec FOR 99999990
COL cpu_ms_per_sec FOR 99999990
COL iow_ms_per_sec FOR 99999990
COL clw_ms_per_sec FOR 99999990
COL apw_ms_per_sec FOR 99999990
COL ccw_ms_per_sec FOR 99999990
SELECT
CAST(begin_interval_time AS DATE) begin_interval_time
,st.con_id
, sql_id
, plan_hash_value
, ROUND(SUM(executions_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
, ROUND(SUM(elapsed_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
, ROUND(SUM(rows_processed_delta) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
, ROUND(SUM(buffer_gets_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
, ROUND(SUM(disk_reads_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
, ROUND(SUM(cpu_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
, ROUND(SUM(iowait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
, ROUND(SUM(clwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
, ROUND(SUM(apwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
, ROUND(SUM(ccwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
dba_hist_snapshot sn
, dba_hist_sqlstat st
WHERE
sn.snap_id = st.snap_id
AND sn.dbid = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
CAST(begin_interval_time AS DATE)
,st.con_id
, CAST(end_interval_time AS DATE)
, sql_id
, plan_hash_value
ORDER BY
begin_interval_time
, sql_id
, plan_hash_value
/
connnect to pdbs
col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
*************************************************
define sql_id='88gm0z813qnht'
set long 30000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
union all
select 'gv$sqlstats' frm , sql_fulltext sql_text from gv$sqlstats where sql_id='&&sql_id'
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id'
)
where 1=1
and rownum<2
;
****$ORACLE_HOME/rdbms/admin/sqltrpt.sql <<<<<< to display 15 Most expensive SQL
.
$ORACLE_HOME/rdbms/admin/sqltrpt.sql <<<<<< to display 15 Most expensive SQL
set linesize 300 pagesize 300column elapsed format 999999999999999variable newl varchar2(64);
begin :newl := ' ';end;/
col sql_text_fragment for a60select * 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;
promptprompt 15 Most expensive SQL in the workload repositoryprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
VARIABLE BgnSnap NUMBERVARIABLE EndSnap NUMBERVARIABLE DID NUMBERVARIABLE INST_NUMBER numberVARIABLE 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.dbidand 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
9 comments:
SELECT sql_text FROM gv$sqlarea WHERE sql_id = '&sql_id';
-- Shared Pool
set linesize 300
select * from table(dbms_xplan.display_cursor('&sql_id',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
-- AWR
set linesize 300
select * from table(dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
or
select * from TABLE(dbms_xplan.display_awr('&sql_id'));
per execution !!
Prompt Top EXECUTIONS
set linesize 150 pagesize 200 serveroutput on
declare
cursor sqltext_cur is
select inst_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,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('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;
/
select
sql_id "SQL_ID",
min(snap_id) "MIN_SNAP_ID",
max(snap_id) "MAX_SNAP_ID",
case when sum(executions_delta) = 0 then 0
else round(sum(elapsed_time_delta)/sum(executions_delta), 3)
end "ElapsedPerExec(ms)",
sum(elapsed_time_delta) "ElapsedTime (ms)",
sum(executions_delta) "Executions"
from
dba_hist_sqlstat
where
sql_id = 'f0yx3svkt9t2y'
group by
sql_id
set linesize 300
col "SQL Text" for a70 wrap
col parsing_schema_name for a15
SELECT PARSING_SCHEMA_NAME,sql_id,SUBSTR(sql_text, 1, 60) "SQL Text",rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM gv$sqlarea
WHERE sql_text not LIKE 'INSERT INTO%'
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 open_versions > 0 AND rows_processed > 0;
http://anuj-singh.blogspot.com/2017/08/oracle-active-session-report-ash-report.html
set linesize 300 pagesize 300
var sqlid VARCHAR2(13);
begin :sqlid := 'g0bggfqrddc4w'; end;
/
select inst_id, sql_id,rank_elap_per_exec, elapsed_per_exec , elapsed_time, executions, cpu_time, applic_wait_time, user_io_wait_time
from (
select inst_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
from gv$sql
WHERE 1=1
--and inst_id not in (select instance_number FROM v$instance)
-- and sql_id= :sqlid
)
where rank_elap_per_exec<11
order by rank_elap_per_exec
/
alter session set NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF';
col BEGIN_INTERVAL_TIME for a40
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE
, b.executions_delta
, round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2) tijd
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and b.sql_id=:sqlid
order by a.BEGIN_INTERVAL_TIME
/
Top Sql from Between snap_id
http://anuj-singh.blogspot.com/2013/11/top-sql-from-between-snapid.html
Rac waits
http://anuj-singh.blogspot.com/2011_10_16_archive.html
http://anuj-singh.blogspot.com/2021/ Object waits with event ..
http://anuj-singh.blogspot.com/2011/07/oracle-top-sql-spent-more-on-cpuwaitio.html
Post a Comment