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
;
