Oracle top 10 sql waits
Oracle Rac top 10 sql waits
@?/rdbms/admin/sqltrpt
top awr event http://anuj-singh.blogspot.com/2021/12/awr-top-events.htmlhttp://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
=======
--top N SQLs ordered by elapsed time
col "Buffer Gets" for 9999999999999
col "Physical Reads" for 9999999999999
col "Executions" for 999999999999999
col gets_per_row for 999999999999
col rows1 for 999999999999999999
col gets_per_exec for 999999999999
col prds_per_row for 9999999999999
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
column module format a20
set lines 300 pagesize 300
define rnum=20
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 :BgnSnap and :EndSnap
group by sql_id, module
order by 3 desc
) where rownum <= &rnum
;
set linesize 200
column executions format 999,999,999 heading "Executions"
column disk_reads format 999,999,999 heading "Block Reads"
column buffer_gets format 999,999,999,999 heading "Buffer Reads"
column sql_text format a90 heading "Statement"
break on executions on buffer_gets skip 1
--we want the 2 largest consumers
variable max_buffer_read number
begin
select max(buffer_gets) into :max_buffer_read from v$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' )
and parsing_user_id >0
;
select max(buffer_gets) into :max_buffer_read
from v$sqlarea
where parsing_user_id >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' )
and buffer_gets < :max_buffer_read;
end;
/
col PARSING_SCHEMA_NAME for a20
select PARSING_SCHEMA_NAME,a.con_id,a.sql_id,a.executions,a.buffer_gets,b.sql_text
from v$sqlarea a,v$sqltext b
where a.buffer_gets >= :max_buffer_read
and a.address = b.address
and a.parsing_user_id > 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 a.buffer_gets desc, piece asc;
set linesize 300 pagesize 1000
SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null
--and USERNAME='USER'
and sql_id='2qwmxpwhknnb1'
) ;
https://anuj-singh.blogspot.com/2011/07/oracle-session-doing-what.html
set linesize 1000
col p1 format 9999999999999
col p2 format 9999999999999
col program format a15
col event format a20
col event form a25 trunc head "Event| Waiting For"
col command format a15
col sid format 9999
col machine format a22
col username format a10
col kill for a17
col osuser for a12
select /*+ RULE */ ''''||a.sid ||','|| b.serial#||',@'||a.inst_id ||'''' kill,
a.con_id,
substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,
decode(command,0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
305,'ALTER PUBLIC DATABASE LINK',command)
command,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL ,
a.event,a.p1,a.p2,a.p3,a.p1raw,b.sql_id,prev_sql_id,b.sql_hash_value
from gv$session_wait a,gv$session b
where 1=1
and b.sid=a.sid
AND a.inst_id = b.inst_id
and a.event not in('SQL*Net message from client','SQL*Net message to client','smon timer','pmon timer')
and username is not null
AND b.status = 'ACTIVE'
and USERNAME not in ('GGATE','SYS','SYSTEM','DEPLOYMENT','DBSNMP')
and USERNAME not like ('DEPLOYMENT%')
order by 7;
https://anuj-singh.blogspot.com/2017/09/object-list-via-file-and-block-id.html
set linesize 500
DEF _swp_print='noprint'
DEF _swp_print='print'
DEF _swp_sid=5762 ----- change sid
DEF _swp_p123=e3
DEF _swp_samples=1000000
col sw_event head EVENT for a35 truncate
col sw_p1transl head P1TRANSL for a42
col sw_sid head SID for 999999
col swp_p1 head P1 for a26 word_wrap
col swp_p2 head P2 for a16 word_wrap &_swp_print
col swp_p3 head P3 for a16 word_wrap &_swp_print
col swp_seq# head SEQ# &_swp_print
col pct_total_samples head "% Total|Time" format 999.99
col waitprof_total_ms head "Total Event|Time ms" format 9999999.999
col dist_events head Distinct|Events
col average_samples head Average|Samples
col waitprof_avg_ms head "Avg time|ms/Event" format 99999.999
WITH
t1 AS (SELECT hsecs FROM v$timer),
samples AS (
SELECT /*+ ORDERED NO_MERGE USE_NL(sw.gv$session_wait.s) */
--s.sid sw_sid,
CASE WHEN sw.state = 'WAITING' THEN 'WAITING' ELSE 'WORKING' END AS state,
CASE WHEN sw.state = 'WAITING' THEN event ELSE 'On CPU / runqueue' END AS sw_event,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%1%' THEN sw.p1text || '= ' || CASE WHEN (LOWER(sw.p1text) LIKE '%addr%' OR sw.p1 >= 536870912) THEN RAWTOHEX(sw.p1raw) ELSE TO_CHAR(sw.p1) END ELSE NULL END swp_p1,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%2%' THEN sw.p2text || '= ' || CASE WHEN (LOWER(sw.p2text) LIKE '%addr%' OR sw.p2 >= 536870912) THEN RAWTOHEX(sw.p2raw) ELSE TO_CHAR(sw.p2) END ELSE NULL END swp_p2,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%3%' THEN sw.p3text || '= ' || CASE WHEN (LOWER(sw.p3text) LIKE '%addr%' OR sw.p3 >= 536870912) THEN RAWTOHEX(sw.p3raw) ELSE TO_CHAR(sw.p3) END ELSE NULL END swp_p3,
CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN sw.seq# ELSE NULL END seq#,
COUNT(*) total_samples,
COUNT(DISTINCT seq#) dist_events,
TRUNC(COUNT(*)/COUNT(DISTINCT seq#)) average_samples
FROM
( SELECT /*+ NO_MERGE */ TO_NUMBER(&_swp_sid) sid FROM
(SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) a,
(SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) b,
(SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) c
WHERE ROWNUM <= &_swp_samples
) s,
v$session_wait sw
WHERE
s.sid = sw.sid
GROUP BY
s.sid,
CASE WHEN sw.state = 'WAITING' THEN 'WAITING' ELSE 'WORKING' END,
CASE WHEN sw.state = 'WAITING' THEN event ELSE 'On CPU / runqueue' END,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%1%' THEN sw.p1text || '= ' || CASE WHEN (LOWER(sw.p1text) LIKE '%addr%' OR sw.p1 >= 536870912) THEN RAWTOHEX(sw.p1raw) ELSE TO_CHAR(sw.p1) END ELSE NULL END,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%2%' THEN sw.p2text || '= ' || CASE WHEN (LOWER(sw.p2text) LIKE '%addr%' OR sw.p2 >= 536870912) THEN RAWTOHEX(sw.p2raw) ELSE TO_CHAR(sw.p2) END ELSE NULL END,
CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%3%' THEN sw.p3text || '= ' || CASE WHEN (LOWER(sw.p3text) LIKE '%addr%' OR sw.p3 >= 536870912) THEN RAWTOHEX(sw.p3raw) ELSE TO_CHAR(sw.p3) END ELSE NULL END,
CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN sw.seq# ELSE NULL END
ORDER BY
CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN -seq# ELSE total_samples END DESC
),
t2 AS (SELECT hsecs FROM v$timer)
SELECT /*+ ORDERED */
s.sw_sid,
s.state,
s.sw_event,
s.swp_p1,
s.swp_p2,
s.swp_p3,
s.seq# swp_seq#,
s.total_samples / &_swp_samples * 100 pct_total_samples,
(t2.hsecs - t1.hsecs) * 10 * s.total_samples / &_swp_samples waitprof_total_ms,
s.dist_events,
-- s.average_samples,
(t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_events / &_swp_samples waitprof_avg_ms
FROM
t1,
samples s,
t2
/
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