from https://github.com/tanelpoder/tpt-oracle/blob/master/ash/ashtopsum.sql
set linesize 700 pagesize 300
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"
COL event FOR A42 WORD_WRAP
COL event2 FOR A46 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2 FOR A40 TRUNCATE
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
SELECT * FROM (
WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
10 * COUNT(*) "TotalSeconds"
, ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, username,sql_id
-- , 10 * SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
-- , 10 * SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
-- , 10 * SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
-- , 10 * SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
-- , 10 * SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
-- , 10 * SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
-- , 10 * SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
-- , 10 * SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
-- , 10 * SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
-- , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
-- , 10 * SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
-- , 10 * SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, NVL(event, session_state)||
CASE
WHEN event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null
END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM dba_hist_active_sess_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND session_type='FOREGROUND'
AND a.sample_time BETWEEN sysdate-2/24 AND sysdate
AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
--AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning
AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn
WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning
GROUP BY
username,sql_id
ORDER BY
"TotalSeconds" DESC
, username,sql_id
)
WHERE
ROWNUM <= 20
/
Total
Seconds AAS %This USERNAME SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------- -------------------
20 .0 22% SYS 9w52tbp4nqgtt 2022-11-18 02:33:56 2022-11-18 03:13:56
10 .0 11% SYS 1xj8zsb8qxa1n 2022-11-18 03:03:56 2022-11-18 03:03:56
10 .0 11% SYS 34cd4y8mbqvsk 2022-11-18 03:03:56 2022-11-18 03:03:56
10 .0 11% SYS 5j3ram21g5s8r 2022-11-18 03:13:56 2022-11-18 03:13:56
10 .0 11% SYS 8xh3c4x40pnrz 2022-11-18 03:11:46 2022-11-18 03:11:46
10 .0 11% SYS afwv4sqa4vyt1 2022-11-18 02:53:56 2022-11-18 02:53:56
10 .0 11% SYS b6usrg82hwsa3 2022-11-18 02:23:56 2022-11-18 02:23:56
10 .0 11% SYS cf1ur00hw58mh 2022-11-18 02:33:56 2022-11-18 02:33:56
8 rows selected.
=====
--- change the date based on your requirement
set pagesize 300 linesize 500
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
VARIABLE bdate VARCHAR2(35)
VARIABLE edate VARCHAR2(35)
exec SELECT CAST('25-MAR-2023 07:25' AS DATE) into :Bdate FROM dual;
exec SELECT CAST('25-MAR-2023 08:00' AS DATE) into :edate FROM dual;
-- @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
--
-- Other:
-- This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use
-- @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
--
--------------------------------------------------------------------------------
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL event FOR A40 WORD_WRAP
--set linesize 200
col USERNAME for a20
col min_sample_time for a27
col max_sample_time for a27
SELECT * FROM (
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, abs(ROUND(COUNT(*) / ((CAST(:Bdate AS DATE) - CAST(:edate AS DATE)) * 86400), 1)) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
, &1
, SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
, SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
, SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
, SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
, SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
, SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
, SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
, SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
, SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
, SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
, SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
, SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
, SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, MIN(sample_time) min_sample_time
, MAX(sample_time) max_sample_time
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
-- FROM gv$active_session_history a) a
from DBA_HIST_ACTIVE_SESS_HISTORY a) a
-- , dba_users u
, cdb_users u
WHERE
a.user_id = u.user_id (+)
AND &2
AND sample_time BETWEEN CAST(:Bdate AS DATE) AND CAST(:edate AS DATE) --ww
GROUP BY
&1
ORDER BY
TotalSeconds DESC
, &1
)
WHERE
ROWNUM <= 20
/
Total
Seconds AAS %This USERNAME SQL_ID CPU User I/O Application Concurrency Commit Configuration Cluster Idle Network System I/O Scheduler Administrative Queueing Other MIN_SAMPLE_TIME MAX_SAMPLE_TIME
--------- ------- ------- -------------------- ------------- ---------- ---------- ----------- ----------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- --------------------------- ---------------------------
6128 2.9 52% | SYS 10 0 0 0 5852 0 0 0 0 0 0 0 0 266 25-MAR-23 07.25.09.649 AM 25-MAR-23 07.35.00.991 AM
set pagesize 300 linesize 500
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
VARIABLE bdate VARCHAR2(35)
VARIABLE edate VARCHAR2(35)
exec SELECT CAST('25-MAR-2025 07:25' AS DATE) into :Bdate FROM dual;
exec SELECT CAST('25-MAR-2025 08:00' AS DATE) into :edate FROM dual;
DEF 3="TIMESTAMP'2025-04-04 04:00:00'"
DEF 4="TIMESTAMP'2025-04-04 06:30:00'"
-- @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
--
-- Other:
-- This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use
-- @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
--
--------------------------------------------------------------------------------
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL event FOR A40 WORD_WRAP
--set linesize 200
col USERNAME for a20
col min_sample_time for a27
col max_sample_time for a27
SELECT * FROM (
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, abs(ROUND(COUNT(*) / ((CAST(:Bdate AS DATE) - CAST(:edate AS DATE)) * 86400), 1)) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
, &1
, SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
, SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
, SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
, SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
, SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
, SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
, SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
, SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
, SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
, SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
, SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
, SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
, SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, MIN(sample_time) min_sample_time
, MAX(sample_time) max_sample_time
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
-- FROM gv$active_session_history a) a
from DBA_HIST_ACTIVE_SESS_HISTORY a) a
-- , dba_users u
, cdb_users u
WHERE
a.user_id = u.user_id (+)
AND &2
-- AND sample_time BETWEEN CAST(:Bdate AS DATE) AND CAST(:edate AS DATE) --ww
AND sample_time BETWEEN &3 and &4
GROUP BY
&1
ORDER BY
TotalSeconds DESC
, &1
)
WHERE
ROWNUM <= 20
/
=====
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 ;
--@dashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
set linesize 500
set pages 9999
COL "%This" FOR A6
COL "UserI/O" FOR 999999
COL "SystemI/O" FOR 999999
col USERNAME for a20
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
SELECT * FROM (
SELECT /*+ LEADING(a) USE_HASH(u) */
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, &1
, 10 * COUNT(*) "TotalSeconds"
, 10 * SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
, 10 * SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "UserI/O"
, 10 * SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
, 10 * SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
, 10 * SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
, 10 * SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
, 10 * SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
, 10 * SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
, 10 * SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
, 10 * SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "SystemI/O"
, 10 * SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
, 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
, 10 * SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, 10 * SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, MIN(sample_time)
, MAX(sample_time)
FROM
dba_hist_active_sess_history a
, dba_users u
WHERE
a.user_id = u.user_id (+)
AND &2
AND snap_id BETWEEN :BgnSnap AND :EndSnap
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE snap_id BETWEEN :BgnSnap AND :EndSnap) -- for partition pruning
GROUP BY
&1
ORDER BY
"TotalSeconds" DESC
, &1
)
WHERE
ROWNUM <= 20
/
%This USERNAME SQL_ID TotalSeconds CPU UserI/O Application Concurrency Commit Configuration Cluster Idle Network SystemI/O Scheduler Administrative Queueing Other MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
------ -------------------- ------------- ------------ ---------- ------- ----------- ----------- ---------- ------------- ---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
43% 9260 200 10 0 0 0 10 0 0 9040 0 0 0 0 0 24-APR-23 03.00.23.959 AM 24-APR-23 05.00.35.449 AM
14% 7grvag6ayaxn1 3010 3010 0 0 0 0 0 0 0 0 0 0 0 0 0 24-APR-23 03.00.33.975 AM 24-APR-23 05.00.15.417 AM
11% 9px0f4mv0tn0t 2340 2190 150 0 0 0 0 0 0 0 0 0 0 0 0 24-APR-23 03.00.23.959 AM 24-APR-23 05.00.35.449 AM
10% ga5m3ggy97233 2240 2240 0 0 0 0 0 0 0 0 0 0 0 0 0 24-APR-23 03.02.04.120 AM 24-APR-23 05.00.35.449 AM
6% 1m8z8cvabtqyf 1200 300 0 0 880 0 0 0 0 0 0 0 0 0 20 24-APR-23 03.03.04.232 AM 24-APR-23 04.57.35.163 AM
2% 5y8u6k6fkzsqk 540 540 0 0 0 0 0 0 0 0 0 0 0 0 0 24-APR-23 03.05.04.411 AM 24-APR-23 04.40.43.542 AM
2% SYS 430 340 0 0 0 70 20 0 0 0 0 0 0 0 0 24-APR-23 03.01.14.043 AM 24-APR-23 04.42.23.685 AM
2% gtxhhbn2gw777 370 70 0 0 0 0 0 0 0 300 0 0 0 0 0 24-APR-23 03.00.33.975 AM 24-APR-23 04.58.25.238
-- select SQL_ID, CHILD_NUMBER from gv$sql where rownum<10;
define sql_id='5j7xryzqzqcbk'
define child_number=1
set linesize 500 pagesize 300
alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
define 1='session_id,sql_id'
--define 2='event is null'
define 2='event is not null'
define 3='sysdate-10/24/60'
define 4='sysdate'
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL event FOR A40 WORD_WRAP
COL event2 FOR A40 WORD_WRAP
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
SELECT * FROM (
WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
, &1
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
, COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, NVL(event, session_state)||
CASE WHEN a.event IN ('buffer busy waits', 'gc buffer busy', 'gc buffer busy acquire', 'gc buffer busy release')
--THEN ' ['||CASE WHEN (SELECT class FROM bclass WHERE r = a.p3) IS NULL THEN ||']' ELSE null END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
FROM gv$active_session_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND &2
AND sample_time BETWEEN &3 AND &4
GROUP BY
&1
ORDER BY
TotalSeconds DESC
, &1
)
WHERE
ROWNUM <= 20
/
===
==========================
https://github.com/tanelpoder/tpt-oracle/blob/master/ash/ash_wait_chains.sql
-- ash_wait_chains.sql with sql and con_id
set linesize 300 pagesize 300
DEFINE 1="con_id||username||':'||program2||event2"
DEFINE 2="session_type='FOREGROUND'"
DEFINE 3="sysdate-1/24"
DEFINE 4="sysdate"
col SQL_TEXT_SHORT for a50
col WAIT_CHAIN for a70
col "%This" for a6
WITH
bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat),
ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */
a.*, CAST(a.sample_time AS DATE) sample_time_s, o.obj, o.objt, u.username,
CASE WHEN a.session_type = 'BACKGROUND' AND a.program LIKE '%(DBW%)' THEN '(DBWn)'
WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2,
NVL(a.event||CASE WHEN event like 'enq%' AND session_state = 'WAITING' THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU') || ' ' event2
FROM gv$active_session_history a, cdb_users u,
(SELECT object_id, owner||'.'||object_name obj, owner||'.'||object_name||' ['||object_type||']' objt FROM cdb_objects) o
WHERE a.user_id = u.user_id (+) AND a.current_obj# = o.object_id(+)
AND a.sample_time BETWEEN &3 AND &4
),
ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_time_s FROM ash),
ash_data AS (SELECT /*+ INLINE */ * FROM ash),
chains AS (
SELECT /*+ INLINE */ d.sample_time_s ts, level lvl, session_id sid,
REPLACE(SYS_CONNECT_BY_PATH(username||':'||program2||event2, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path,
CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids, CONNECT_BY_ISLEAF isleaf, d.*
FROM ash_samples s, ash_data d
WHERE s.sample_time_s = d.sample_time_s
CONNECT BY NOCYCLE (PRIOR d.blocking_session = d.session_id AND PRIOR d.blocking_inst_id = d.inst_id AND PRIOR s.sample_time_s = d.sample_time_s)
START WITH &2
)
SELECT * FROM (
SELECT
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This",
c.con_id,
c.sql_id,
COUNT(*) seconds,
path wait_chain,
SUBSTR(q.sql_text, 1, 50) sql_text_short
FROM chains c
LEFT JOIN gv$sqlstats q ON c.sql_id = q.sql_id AND c.inst_id = q.inst_id AND c.con_id = q.con_id
WHERE isleaf = 1
GROUP BY c.con_id, c.sql_id, path, SUBSTR(q.sql_text, 1, 50)
ORDER BY COUNT(*) DESC
) WHERE ROWNUM <= 30
;
alter session set container='' ;
set linesize 700 pagesize 300
define 1='username,sql_id'
DEFINE 2="session_type='FOREGROUND'"
define 3="TIMESTAMP'2023-01-20 07:00:00'"
define 4="TIMESTAMP'2023-01-20 08:00:00'"
set linesize 300 pagesize 300
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL dop FOR 99
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999
COL dist_timestamps HEAD "Distinct|Tstamps" FOR 999999
COL event FOR A42 WORD_WRAP
COL event2 FOR A42 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2 FOR A40 TRUNCATE
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
COL rd_rq FOR 999,999,999
COL wr_rq FOR 999,999,999
COL rd_mb FOR 999,999,999
COL wr_mb FOR 999,999,999
COL pgamem_mb FOR 9,999,999
COL tempspc_mb FOR 99,999,999
PROMPT This is an experimental script as some documentation/explanation is needed.
PROMPT The ASH "delta" metrics are not tied to individual SQL_IDs or wait events,
PROMPT They are valid in the session scope (and related mostly static attributes,
PROMPT like PROGRAM, MACHINE, MODULE). Which SQL_ID/operation/event happens to be
PROMPT active when ASH samples its data is just matter of luck.
SELECT
*
FROM (
WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
, SUM(delta_read_io_requests) rd_rq
, SUM(delta_write_io_requests) wr_rq
, SUM(delta_read_io_bytes)/1048576 rd_mb
, SUM(delta_write_io_bytes)/1048576 wr_mb
--, SUM(delta_interconnect_io_bytes)
--, SUM(delta_read_mem_bytes)
, MAX(pga_allocated)/1048576 pgamem_mb
, MAX(temp_space_allocated)/1048576 tempspc_mb
, &1
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
, COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
, COUNT(DISTINCT sample_time) dist_timestamps
FROM
(SELECT
a.*
, session_id sid
, session_serial# serial
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, TRUNC(px_flags / 2097152) dop
, NVL(a.event, a.session_state)||
CASE
WHEN a.event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null
END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM gv$active_session_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND &2
-- AND sample_time BETWEEN &3 AND &4
and sample_time > sysdate - interval '30' minute
GROUP BY
&1
ORDER BY
TotalSeconds DESC
, &1
)
WHERE
ROWNUM <= 20
/
********
set pagesize 300 linesize 500
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
define 3='sysdate-10/24/60'
define 4 ='sysdate'
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL dop FOR 99
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999
COL dist_timestamps HEAD "Distinct|Tstamps" FOR 999999
COL event FOR A42 WORD_WRAP
COL event2 FOR A42 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2 FOR A40 TRUNCATE
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
SELECT
*
FROM (
WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
, &1
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
, COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
, COUNT(DISTINCT sample_time) dist_timestamps
FROM
(SELECT
a.*
, session_id sid
, session_serial# serial
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, TRUNC(px_flags / 2097152) dop
, NVL(a.event, a.session_state)||
CASE
WHEN a.event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null
END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM gv$active_session_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND &2
-- AND sample_time BETWEEN &3 AND &4
GROUP BY
&1
ORDER BY
TotalSeconds DESC
, &1
)
WHERE
ROWNUM <= 15
/
============= set linesize 300 col TIME for a30 def sql_id="5j7xryzqzqcbk" def days_history="10" def interval_hours="1" select hss.instance_number inst, to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)- (trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'dd.mm.yyyy hh24:mi:ss') time, plan_hash_value, hss.sql_id, sum(hss.executions_delta) executions, round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s, round(sum(hss.cpu_time_delta)/1000000,3) cpu_time_s, round(sum(hss.iowait_delta)/1000000,3) iowait_s, round(sum(hss.clwait_delta)/1000000,3) clwait_s, round(sum(hss.apwait_delta)/1000000,3) apwait_s, round(sum(hss.ccwait_delta)/1000000,3) ccwait_s, round(sum(hss.rows_processed_delta),3) rows_processed, round(sum(hss.buffer_gets_delta),3) buffer_gets, round(sum(hss.disk_reads_delta),3) disk_reads, round(sum(hss.direct_writes_delta),3) direct_writes from dba_hist_sqlstat hss, dba_hist_snapshot hs where hss.sql_id='&sql_id' and hss.snap_id=hs.snap_id and hs.begin_interval_time>=trunc(sysdate)-&days_history+1 group by hss.sql_id,hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,plan_hash_value having sum(hss.executions_delta)>0 order by hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24, 4 desc;
with con_idhttps://github.com/LucaCanali/Oracle_DBA_scripts/blob/master/ash_top.sql set linesize 500 pagesize 300 col event for a27 define sec=10 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 sample_time>sysdate - interval '&sec' second ) select con_id,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>sysdate - interval '&sec' second group by con_id,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; clear breaks break on username col username for a20 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>sysdate - interval '&sec' 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, con_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> sysdate - interval '&sec' second group by con_id,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>sysdate - interval '&sec' second ) select con_id, 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>sysdate - interval '&sec' second group by con_id,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;======col inst_sid_ser# for a12 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 a8 col "R,W_IOPS" for a8 col "R,W_MBPS" for a8 col "PGA,TEMP_MB" for a8 col kill for a17 break on sample_id skip 1 --select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=1 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=2 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=3 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1) and sample_time > sysdate - interval '5' second / -- 11G set linesize 500 pagesize 500 col inst_sid_ser# for a12 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 a8 col "R,W_IOPS" for a8 col "R,W_MBPS" for a8 col "PGA,TEMP_MB" for a8 col kill for a17 break on sample_id skip 1 --select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=1 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=2 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=3 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1) and sample_time > sysdate - interval '5' second / --12c set linesize 500 pagesize 500 col inst_sid_ser# for a12 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 a8 col "R,W_IOPS" for a8 col "R,W_MBPS" for a8 col "PGA,TEMP_MB" for a8 col kill for a17 break on sample_id skip 1 --select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill,con_id, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=1 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill,con_id, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=2 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1) and sample_time > sysdate - interval '5' second UNION ALL --select inst_id||'_'||session_id||' '||session_serial# sid_ser#, select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||'''' kill,con_id, (select us.username from dba_users us where us.user_id=a.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 (select * from gv$active_session_history where inst_id=3 and rownum<300) a where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1) and sample_time > sysdate - interval '5' second / set pagesize 300 linesize 500 DEFINE 1="wait_class,event" -- DEFINE 1="inst_id,wait_class,event,username,sql_id" -- DEFINE 1="inst_id,session_id,username,program,sql_id,sql_plan_hash_value,plsql_entry_object_id,blocking_session,event" -- DEFINE 1="inst_id,session_id,username,program,sql_id,sql_plan_hash_value,plsql_entry_object_id,sql_plan_operation,sql_plan_options,sql_plan_line_id" DEFINE 2="session_type='FOREGROUND'"=====================================================set linesize 300 ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; VARIABLE bdate VARCHAR2(35) VARIABLE edate VARCHAR2(35) exec SELECT CAST('12-JUL-2023 04:00' AS DATE) into :Bdate FROM dual; exec SELECT CAST('12-JUL-2023 05:37' AS DATE) into :edate FROM dual; --username,sql_id session_type='FOREGROUND' define 1='username,sql_id' define 2="session_type='FOREGROUND'" COL "%This" FOR A7 --COL p1 FOR 99999999999999 --COL p2 FOR 99999999999999 --COL p3 FOR 99999999999999 COL p1text FOR A30 word_wrap COL p2text FOR A30 word_wrap COL p3text FOR A30 word_wrap COL p1hex FOR A17 COL p2hex FOR A17 COL p3hex FOR A17 COL AAS FOR 9999.9 COL totalseconds HEAD "Total|Seconds" FOR 99999999 COL event FOR A40 WORD_WRAP --set linesize 200 col USERNAME for a20 col min_sample_time for a27 col max_sample_time for a27 SELECT * FROM ( SELECT /*+ LEADING(a) USE_HASH(u) +PARALLEL(8) */ COUNT(*) totalseconds , abs(ROUND(COUNT(*) / ((CAST(:Bdate AS DATE) - CAST(:edate AS DATE)) * 86400), 1)) AAS , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This" , &1 , SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU" , SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O" , SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application" , SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency" , SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit" , SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration" , SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster" , SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle" , SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network" , SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O" , SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler" , SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative" , SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing" , SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other" , MIN(sample_time) min_sample_time , MAX(sample_time) max_sample_time -- , MAX(sql_exec_id) - MIN(sql_exec_id) FROM (SELECT a.* , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex FROM gv$active_session_history a) a -- from DBA_HIST_ACTIVE_SESS_HISTORY a) a , dba_users u --, cdb_users u WHERE a.user_id = u.user_id (+) -- AND &2 --AND sample_time BETWEEN CAST(:Bdate AS DATE) AND CAST(:edate AS DATE) --ww AND sample_time > sysdate - interval '50' minute GROUP BY &1 ORDER BY TotalSeconds DESC , &1 ) WHERE ROWNUM <= 20 /https://github.com/tanelpoder/tpt-oracle/blob/master/ash/dashtop.sql username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate define 1='username,sql_id' define 2="session_type='FOREGROUND'" --define 1='sql_id,event,top_level_call_name,SQL_OPNAME,username,IN_CONNECTION_MGMT,p1text,p1' --define 2="event like 'library%'" DEF 3="TIMESTAMP'2025-04-27 07:15:00'" DEF 4="TIMESTAMP'2025-04-27 09:15:00'" COL "%This" FOR A7 --COL p1 FOR 99999999999999 --COL p2 FOR 99999999999999 --COL p3 FOR 99999999999999 COL p1text FOR A30 word_wrap COL p2text FOR A30 word_wrap COL p3text FOR A30 word_wrap COL p1hex FOR A17 COL p2hex FOR A17 COL p3hex FOR A17 COL AAS FOR 9999.9 COL totalseconds HEAD "Total|Seconds" FOR 99999999 COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999 COL event FOR A42 WORD_WRAP COL event2 FOR A42 WORD_WRAP COL time_model_name FOR A50 WORD_WRAP COL program2 FOR A40 TRUNCATE COL username FOR A20 wrap COL obj FOR A30 COL objt FOR A50 COL sql_opname FOR A20 COL top_level_call_name FOR A30 COL wait_class FOR A15 set linesize 500 pagesize 300 SELECT * FROM ( WITH bclass AS (SELECT class, ROWNUM r from v$waitstat) SELECT /*+ LEADING(a) USE_HASH(u) */ COUNT(*) totalseconds , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This" , &1 , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen FROM (SELECT a.* , session_id sid , session_serial# serial , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex , NVL(event, session_state)|| CASE WHEN event like 'enq%' AND session_state = 'WAITING' THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']' WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass) THEN (SELECT class FROM bclass WHERE r = a.p3) ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual) END ||']' ELSE null END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU) , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n') ELSE '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')' END || ' ' program2 FROM gv$active_session_history a) a , cdb_users u , (SELECT object_id,data_object_id,owner,object_name,subobject_name,object_type , owner||'.'||object_name obj , owner||'.'||object_name||' ['||object_type||']' objt FROM cdb_objects) o WHERE a.user_id = u.user_id (+) AND a.current_obj# = o.object_id(+) AND &2 AND sample_time BETWEEN &3 AND &4 GROUP BY &1 ORDER BY TotalSeconds DESC , &1 ) WHERE ROWNUM <= 50 / DEF 3="TIMESTAMP'2025-04-27 07:15:00'" DEF 4="TIMESTAMP'2025-04-27 09:15:00'" col COMMAND_NAME for a15 SELECT a.top_level_call# , a.top_level_call_name , a.top_level_sql_opcode , s.command_name , COUNT(*) FROM gv$active_session_history a , v$sqlcommand s WHERE a.top_level_sql_opcode = s.command_type AND sample_time BETWEEN &3 AND &4 GROUP BY a.top_level_call# , a.top_level_call_name , a.top_level_sql_opcode , s.command_name ORDER BY COUNT(*) DESC /
