---------------------------------------------------------------------------------------
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/
--------------------------------------------------------------------------------------------------------------------
-- File name: prashantpoormanscript1.sql
-- Version: V1.1 (12-08-2021) Fancy Version
-- Purpose: This script can be used on any Oracle DB to know what all running and for how long and waiting
-- Also provides details on SQL and SESSION level.
-- Author: Prashant Dixit The Fatdba www.fatdba.com
--------------------------------------------------------------------------------------------------------------------
set linesize 400
set pagesize 400
col ACTION for a22
col USERNAME for a9
col SQL_ID for a16
col EVENT for a20
col OSUSER for a10
col PROCESS for a8
col MACHINE for a15
col OSUSER for a8
col PROGRAM for a15
col module for a20
col BLOCKING_INSTANCE for a20
select
'InstID .............................................: '||x.inst_id,
'SID ................................................: '||x.sid,
'Serial .............................................: '||x.serial#,
'Username ...........................................: '||x.username,
'SQLID ..............................................: '||x.sql_id,
'PHV ................................................: '||plan_hash_value,
'DISK_READS .........................................: '||sqlarea.DISK_READS,
'BUFFER_GETS ........................................: '||sqlarea.BUFFER_GETS,
'ROWS_PROCESSED ..... ...............................: '||sqlarea.ROWS_PROCESSED,
'Event .............................................: '||x.event,
'OSUser .............................................: '||x.osuser,
'Status .............................................: '||x.status,
'BLOCKING_SESSION_STATUS ............................: '||x.BLOCKING_SESSION_STATUS,
'BLOCKING_INSTANCE ..................................: '||x.BLOCKING_INSTANCE,
'BLOCKING_SESSION ...................................: '||x.BLOCKING_SESSION,
'PROCESS ............................................: '||x.process,
'MACHINE ............................................: '||x.machine,
'PROGRAM ............................................: '||x.program,
'MODULE .............................................: '||x.module,
'ACTION .............................................: '||x.action,
'LOGONTIME ..........................................: '||TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime,
'LAST_CALL_ET .......................................: '||x.LAST_CALL_ET,
'SECONDS_IN_WAIT ....................................: '||x.SECONDS_IN_WAIT,
'STATE ..............................................: '||x.state,
'RUNNING_SINCE ......................................: '||ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':' || ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':' || ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE,
'SQLTEXT ............................................: '||sql_text
from gv$sqlarea sqlarea
,gv$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value%'
and sql_text not like '%select :"SYS_B_00"||x.inst_id, :"SYS_B_01"||x.sid, :"SYS_B_02"||x.serial#,%'
and x.status='ACTIVE'
and x.USERNAME is not null
and x.SQL_ADDRESS = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/
SET MARKUP HTML ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 100
SET LINESIZE 200
SPOOL 63_top_fullscan_high_io.html
PROMPT
Top 10 Full Table Scan SQLs (Non-SYS) with High Logical Reads
SELECT *
FROM (
SELECT
ss.sql_id,
u.username,
ROUND(ss.buffer_gets_total / DECODE(ss.executions_total, 0, 1, ss.executions_total)) AS avg_buffer_gets,
ss.executions_total,
ss.buffer_gets_total,
CASE
WHEN ROUND(ss.buffer_gets_total / DECODE(ss.executions_total, 0, 1, ss.executions_total)) > 100000 THEN 'CRITICAL'
WHEN ROUND(ss.buffer_gets_total / DECODE(ss.executions_total, 0, 1, ss.executions_total)) > 50000 THEN 'WARNING'
ELSE 'OK'
END AS status,
st.sql_text
FROM dba_hist_sqlstat ss
JOIN dba_hist_sqltext st ON ss.sql_id = st.sql_id
JOIN dba_users u ON ss.parsing_schema_id = u.user_id
WHERE ss.snap_id > (
SELECT MAX(snap_id) - 48 FROM dba_hist_snapshot
)
AND u.username NOT IN ('SYS', 'SYSTEM')
AND EXISTS (
SELECT 1
FROM dba_hist_sql_plan p
WHERE p.sql_id = ss.sql_id
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
)
ORDER BY ss.buffer_gets_total DESC
)
WHERE ROWNUM <= 10;
SPOOL OFF;
SPOOL OFF;
SPOOL OFF;
SPOOL OFF;
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: Informations about cursor usage in the database
-- Date: 08.2013
--==============================================================================
set linesize 130 pagesize 300
column user_name format a25
ttitle left "Open Cursor used summary" skip 2
select inst_id
, user_name
, count(*)
from gv$open_cursor
where user_name is not null -- and user_name not in ( 'SYS' )
group by rollup (inst_id,user_name)
/
ttitle left "Open Cursor used by session" skip 2
select inst_id
, sid
, user_name
, count(*)
from gv$open_cursor
where user_name is not null -- and user_name not in ( 'SYS' )
group by inst_id,user_name,sid
order by 4,1,3
/
column name format a30 heading "Statistic|Name"
column value format 999G999G999G999 heading "Statistic|value"
ttitle left "Open Cursor used by session over the statistic" skip 2
select a.value
, s.username
, s.sid
, s.serial#
from v$sesstat a
, v$statname b
, v$session s
where a.statistic# = b.statistic#
and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username is not null
/
ttitle left "Open Cursor Statistic " skip 2
column execute_count format 999G999G999G999G999 heading "SQL Execution"
column parse_count format 999G999G999G999G999 heading "Parse Count"
column cursor_hits format 999G999G999G999G999 heading "Cursor Hits"
column hit_percentage_parse format 99D990 heading "Parse| % Total"
column hit_percentage_cursor format 99D990 heading "Cursor Cache | % Total"
select inst_id
, execute_count
, parse_count
, round(parse_count/(execute_count/100),3) as hit_percentage_parse
, cursor_hits
, round(cursor_hits/(execute_count/100),3) as hit_percentage_cursor
from ( select name
, value
, inst_id
from gv$sysstat
where name in ('session cursor cache hits','parse count (total)','execute count')
)
pivot (
max (value)
FOR name
IN ( 'session cursor cache hits' AS cursor_hits
, 'parse count (total)' as parse_count
, 'execute count' as execute_count
)
)
/
prompt ... 11g Syntax if you hid an error on 10g!
prompt ... if Cursor Cache % Total is a relatively low percentage
prompt ... you should increate the DB Parameter session_cached_cursors
ttitle left "Cursor Settings init.ora " skip 2
show parameter cursor
ttitle left "Session cached Cursor Usage " skip 2
--
-- see also
-- SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage (Doc ID 208857.1)
--
select a.inst_id
, 'session_cached_cursors' parameter
, lpad(value, 5) value
, decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select max(s.value) used , inst_id
from v$statname n
, gv$sesstat s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic#
group by inst_id
) a,
( select value,inst_id
from gv$parameter
where name = 'session_cached_cursors'
) b
where a.inst_id=b.inst_id
union all
select c.inst_id
, 'open_cursors'
, lpad(value, 5)
, to_char(100 * used / value, '990') || '%'
from
( select s.inst_id , max((s.value)) used
from v$statname n
, gv$sesstat s
where n.name in ('opened cursors current')
and s.statistic# = n.statistic#
group by s.inst_id
) c,
( select value,inst_id
from gv$parameter
where name = 'open_cursors'
) d
where c.inst_id=d.inst_id
order by 1,2
/
ttitle off
prompt ... if usage percentage is a near 100%
prompt ... you should increate the DB Parameter session_cached_cursors
###
63_top_fullscan_high_io.sql
SET MARKUP HTML ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 100
SET LINESIZE 200
SPOOL 63_top_fullscan_high_io.html
PROMPT
Top 10 Full Table Scan SQLs (Non-SYS) with High I/O Waits
SELECT *
FROM (
SELECT
s.sql_id,
u.username,
ROUND(ss.disk_reads / DECODE(ss.executions, 0, 1, ss.executions)) AS avg_disk_reads,
ss.executions,
ss.buffer_gets,
CASE
WHEN ROUND(ss.disk_reads / DECODE(ss.executions, 0, 1, ss.executions)) > 10000 THEN 'CRITICAL'
WHEN ROUND(ss.disk_reads / DECODE(ss.executions, 0, 1, ss.executions)) > 5000 THEN 'WARNING'
ELSE 'OK'
END AS status,
st.sql_text
FROM dba_hist_sqlstat ss
JOIN dba_hist_sqltext st ON ss.sql_id = st.sql_id
JOIN dba_users u ON ss.parsing_schema_id = u.user_id
WHERE ss.snap_id > (
SELECT MAX(snap_id) - 48 FROM dba_hist_snapshot
)
AND u.username NOT IN ('SYS', 'SYSTEM')
AND EXISTS (
SELECT 1
FROM dba_hist_sql_plan p
WHERE p.sql_id = ss.sql_id
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
)
ORDER BY ss.disk_reads DESC
)
WHERE ROWNUM <= 10;
SPOOL OFF;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN inst_id FORMAT 99
COLUMN name FORMAT A60
COLUMN value_mb FORMAT 999999999.99
SELECT inst_id,
name,
ROUND(value / 1024 / 1024, 2) AS value_mb
FROM gv$cell_global_statistics
WHERE name IN (
'CLO read retries due to stalling',
'IO bytes sent via Smart Interconnect to cells',
'IO bytes sent via non-Smart Interconnect to cells'
)
ORDER BY inst_id, name;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN inst_id FORMAT 99
COLUMN event FORMAT A50
COLUMN wait_time_milli FORMAT 999
COLUMN wait_count FORMAT 999999
SELECT inst_id,
event,
wait_time_milli,
wait_count
FROM gv$event_histogram
WHERE event IN (
'gc buffer busy acquire',
'gc buffer busy release',
'gc cr block busy'
)
AND wait_count > 0
ORDER BY inst_id, event, wait_time_milli;
SET ECHO OFF FEEDBACK OFF PAGES 50 LINES 167 TRIMSPOOL ON
SET MARKUP HTML ON
SPOOL 59_gc_contention_19c.html
SELECT inst_id AS "Inst#", event AS "Event",
total_waits AS "# Waits",
ROUND(time_waited*10) AS "Time (ms)",
ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 1) AS "Avg Wait (ms)",
CASE
WHEN ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 1) > 5 THEN 'CRITICAL'
WHEN ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 1) > 2 THEN 'WARNING'
ELSE 'OK'
END AS "Status"
FROM gv$system_event
WHERE event IN ('gc buffer busy acquire','gc buffer busy release','gc cr block busy')
ORDER BY inst_id, time_waited DESC;
SPOOL OFF;
60_interconnect_19c.sql
-- 60_interconnect_19c.sql - Monitor RAC interconnect usage and latency
SET ECHO OFF FEEDBACK OFF PAGES 100 LINES 200 TRIMSPOOL ON
SET MARKUP HTML ON
SET ECHO OFF FEEDBACK OFF PAGES 100 LINES 200 TRIMSPOOL ON
SET MARKUP HTML ON
SPOOL 60_interconnect_19c.html
SELECT inst_id AS "Inst#",
ROUND( MAX(CASE WHEN name='gc cr block receive time' THEN value END)
/ GREATEST(MAX(CASE WHEN name='gc cr blocks received' THEN value END), 1) * 10, 2)
AS "Avg CR (ms)",
ROUND( MAX(CASE WHEN name='gc current block receive time' THEN value END)
/ GREATEST(MAX(CASE WHEN name='gc current blocks received' THEN value END), 1) * 10, 2)
AS "Avg CUR (ms)",
CASE
WHEN ROUND( MAX(CASE WHEN name='gc cr block receive time' THEN value END)
/ GREATEST(MAX(CASE WHEN name='gc cr blocks received' THEN value END), 1) * 10, 2) > 5 THEN 'CRITICAL'
WHEN ROUND( MAX(CASE WHEN name='gc cr block receive time' THEN value END)
/ GREATEST(MAX(CASE WHEN name='gc cr blocks received' THEN value END), 1) * 10, 2) > 2 THEN 'WARNING'
ELSE 'OK'
END AS "Status"
FROM gv$sysstat
WHERE name IN (
'gc cr block receive time','gc cr blocks received',
'gc current block receive time','gc current blocks received'
)
GROUP BY inst_id
ORDER BY inst_id;
SPOOL OFF;
61_iops_trend_19c.sql
SET ECHO OFF FEEDBACK OFF PAGES 200 LINES 200 TRIMSPOOL ON
SET MARKUP HTML ON
SPOOL 61_iops_trend_19c.html
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS "Snap Time",
instance_number,
ROUND(SUM(iops), 2) AS "IOPS",
CASE
WHEN ROUND(SUM(iops), 2) > 10000 THEN 'CRITICAL'
WHEN ROUND(SUM(iops), 2) > 5000 THEN 'WARNING'
ELSE 'OK'
END AS "Status"
FROM (
SELECT s.begin_interval_time, t.instance_number,
CASE WHEN t.metric_name = 'Physical Read Total IO Requests Per Sec' THEN t.average
WHEN t.metric_name = 'Physical Write Total IO Requests Per Sec' THEN t.average
WHEN t.metric_name = 'Redo Writes Per Sec' THEN t.average
END AS iops
FROM dba_hist_sysmetric_summary t
JOIN dba_hist_snapshot s
ON t.snap_id = s.snap_id AND t.dbid = s.dbid AND t.instance_number = s.instance_number
WHERE t.metric_name IN (
'Physical Read Total IO Requests Per Sec',
'Physical Write Total IO Requests Per Sec',
'Redo Writes Per Sec'
)
AND s.begin_interval_time >= SYSDATE - 1
)
GROUP BY TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI'), instance_number
ORDER BY "Snap Time", instance_number;
SPOOL OFF;
SPOOL OFF;
SET ECHO OFF FEEDBACK OFF PAGES 100 LINES 180 TRIMSPOOL ON
SET MARKUP HTML ON
SPOOL 62_top_waits_19c.html
SELECT inst_id AS "Inst#",
event AS "Wait Event",
ROUND(time_waited/100, 2) AS "Time (s)",
total_waits AS "# Waits",
ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 2) AS "Avg Wait (ms)",
CASE
WHEN ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 2) > 50 THEN 'CRITICAL'
WHEN ROUND((time_waited*10)/DECODE(total_waits, 0, 1, total_waits), 2) > 20 THEN 'WARNING'
ELSE 'OK'
END AS "Status"
FROM (
SELECT e.inst_id, e.event, e.time_waited, e.total_waits,
RANK() OVER (PARTITION BY e.inst_id ORDER BY e.time_waited DESC) AS rk
FROM gv$system_event e
WHERE e.event NOT IN (
SELECT name FROM v$event_name WHERE wait_class = 'Idle'
)
)
WHERE rk <= 5
ORDER BY inst_id, "Time (s)" DESC;
SPOOL OFF;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN inst_id FORMAT 99
COLUMN event FORMAT A50
COLUMN wait_count FORMAT 9999999
SELECT inst_id,
event,
COUNT(*) AS wait_count
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 5/1440
AND session_state = 'WAITING'
GROUP BY inst_id, event
ORDER BY inst_id, wait_count DESC;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN snap_time FORMAT A20
COLUMN instance_number FORMAT 99
COLUMN iops FORMAT 999999.99
WITH io_stats AS (
SELECT s.snap_id,
s.instance_number,
TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
MAX(CASE WHEN ss.stat_name = 'physical read IO requests' THEN ss.value END) AS read_io,
MAX(CASE WHEN ss.stat_name = 'physical write IO requests' THEN ss.value END) AS write_io,
s.begin_interval_time,
s.end_interval_time
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s
ON ss.snap_id = s.snap_id AND ss.instance_number = s.instance_number
WHERE ss.stat_name IN ('physical read IO requests', 'physical write IO requests')
AND s.begin_interval_time > SYSDATE - 1
GROUP BY s.snap_id, s.instance_number, s.begin_interval_time, s.end_interval_time
),
deltas AS (
SELECT snap_time,
instance_number,
(read_io + write_io) -
LAG(read_io + write_io) OVER (PARTITION BY instance_number ORDER BY begin_interval_time) AS total_io,
(CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 24 * 60 * 60 AS elapsed_seconds
FROM io_stats
)
SELECT snap_time,
instance_number,
ROUND(total_io / NULLIF(elapsed_seconds, 0), 2) AS iops
FROM deltas
WHERE total_io IS NOT NULL
ORDER BY snap_time DESC, instance_number;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN begin_time FORMAT A20
COLUMN instance_number FORMAT 99
COLUMN db_time_mins FORMAT 999999.99
WITH dbtime_deltas AS (
SELECT
s.snap_id,
s.instance_number,
s.begin_interval_time,
s.end_interval_time,
tm.value - LAG(tm.value) OVER (PARTITION BY s.instance_number ORDER BY s.snap_id) AS db_time
FROM
dba_hist_snapshot s
JOIN dba_hist_sys_time_model tm
ON s.snap_id = tm.snap_id AND s.instance_number = tm.instance_number
WHERE
tm.stat_name = 'DB time'
AND s.begin_interval_time > SYSDATE - 1
),
load_data AS (
SELECT
begin_interval_time,
instance_number,
ROUND(db_time / 1e6, 2) AS db_time_mins
FROM
dbtime_deltas
WHERE
db_time IS NOT NULL
)
SELECT
TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
instance_number,
db_time_mins
FROM
load_data
ORDER BY
db_time_mins DESC
FETCH FIRST 30 ROWS ONLY;
SET LINESIZE 200
SET PAGESIZE 100
COLUMN begin_time FORMAT A20
COLUMN instance_number FORMAT 99
COLUMN db_time_mins FORMAT 999999.99
WITH dbtime_deltas AS (
SELECT
s.snap_id,
s.instance_number,
s.begin_interval_time,
s.end_interval_time,
tm.value - LAG(tm.value) OVER (PARTITION BY s.instance_number ORDER BY s.snap_id) AS db_time
FROM
dba_hist_snapshot s
JOIN dba_hist_sys_time_model tm
ON s.snap_id = tm.snap_id AND s.instance_number = tm.instance_number
WHERE
tm.stat_name = 'DB time'
AND s.begin_interval_time > SYSDATE - 1
),
load_data AS (
SELECT
begin_interval_time,
instance_number,
ROUND(db_time / 1e6, 2) AS db_time_mins
FROM
dbtime_deltas
WHERE
db_time IS NOT NULL
)
SELECT
TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
instance_number,
db_time_mins
FROM
load_data
ORDER BY
db_time_mins DESC
FETCH FIRST 30 ROWS ONLY;
SET LINESIZE 155
COL dbtime FOR 999,999.99
COL begin_timestamp FOR A40
SELECT *
FROM (
SELECT
begin_snap,
end_snap,
begin_timestamp,
inst,
ROUND(dbtime_microsecs / 1e6 / 60, 2) AS dbtime
FROM (
SELECT
e.snap_id AS end_snap,
LAG(e.snap_id) OVER (PARTITION BY e.instance_number ORDER BY e.snap_id) AS begin_snap,
LAG(s.end_interval_time) OVER (PARTITION BY e.instance_number ORDER BY e.snap_id) AS begin_timestamp,
s.instance_number AS inst,
NVL(e.value - LAG(e.value) OVER (PARTITION BY e.instance_number ORDER BY e.snap_id), 0) AS dbtime_microsecs
FROM
dba_hist_sys_time_model e
JOIN dba_hist_snapshot s ON e.snap_id = s.snap_id AND e.instance_number = s.instance_number
WHERE
e.stat_name = 'DB time'
AND TO_CHAR(e.instance_number) LIKE NVL('&instance_number', TO_CHAR(e.instance_number))
)
WHERE
begin_snap BETWEEN NVL('&begin_snap_id', 0) AND NVL('&end_snap_id', 99999999)
AND begin_snap = end_snap - 1
ORDER BY
dbtime DESC
)
WHERE ROWNUM < 31;
is_rac() {
local db_connect="$1"
local result
result=$(sqlplus -s "$db_connect" <
1 THEN 'RAC' ELSE 'SINGLE' END
FROM gv\\$instance;
EXIT;
EOF
)
if [[ "$result" == "RAC" ]]; then
return 0 # true: RAC
else
return 1 # false: Single instance
fi
}
if is_rac "$DB_CONNECT_STRING"; then
echo "✅ RAC Database detected"
else
echo "ℹ️ Single Instance Database"
fi
COL wait_chain FOR A300 WORD_WRAP
COL "%This" FOR A6
PROMPT
PROMPT -- Display ASH Wait Chain Signatures script v0.8 by Tanel Poder ( https://tanelpoder.com )
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 -- round timestamp to 1 sec boundary for matching across RAC nodes
, o.*
, 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
, 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
, 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
, 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 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(&1, '->'), '->', ' -> ')||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 -- there's a reason why I'm doing this
--, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path
-- , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND LEVEL > 1 THEN ' [sid='||session_id||' seq#='||TO_CHAR(seq#)||']' ELSE NULL END path -- there's a reason why I'm doing this
--, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 :)
, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids
, CONNECT_BY_ISLEAF isleaf
, CONNECT_BY_ISCYCLE iscycle
, d.*
FROM
ash_samples s
, ash_data d
WHERE
s.sample_time_s = d.sample_time_s
AND d.sample_time BETWEEN &3 AND &4
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 -- Different RAC nodes have sample_id drift (assuming that clocks are synced enough)
)
START WITH &2
)
SELECT * FROM (
SELECT
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, COUNT(*) seconds
, ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, path wait_chain
, 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
, COUNT(DISTINCT sids) num_sids
, MIN(sids)
, MAX(sids)
FROM
chains
WHERE
isleaf = 1
GROUP BY
&1
, path
ORDER BY
COUNT(*) DESC
)
WHERE
ROWNUM <= 30
/
