Sunday, May 4, 2025
http://blog.tanelpoder.com/files/scripts/ash/ashtop.sql sql_id,u.username,event "sql_plan_operation='TABLE ACCESS' and sql_plan_options='FULL'" sysdate-1/24 sysdate
@ashtop sql_id,p1,p2,p3 "event like '%TM%'" sysdate-1/24 sysdate
with stats as (
select /*+ materialize */
* from (
select parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
select s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,'iw')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by sql_id, plan_hash_value,parsing_schema_name
)t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
rownum <= 50 )
select * from stats s
where parsing_schema_name not like 'SYS%'
and s.sql_text not like '%/* DS_SVC */%'
and s.sql_text not like '%_job_proc%'
and s.sql_text not like '%SQL Analyze%'
and lower(s.sql_text) not like '%dbms_feature%'
and s.sql_text not like '%SYS_DBA_SEGS%'
and lower(s.sql_text) not like '%v$sys%'
and lower(s.sql_text) not like '%dba_audit_trail%'
and lower(s.sql_text) not like '%no_index(mytab)%';
select t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
select min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > 5
group by sql_id, plan_hash_value
)t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs desc;
select cast(min (ash.SAMPLE_TIME) as date) as start#
,round (24*60*(cast (max(ash.SAMPLE_TIME) as date) - cast(min (ash.SAMPLE_TIME) as date) ),2) as duration#
,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION as B_SID,ash.BLOCKING_SESSION_SERIAL# as b_serial#
,ash2.SQL_EXEC_ID b_sql_exec_id
,ash.event,do.object_name
,sum(decode(ash.session_state,'ON CPU',1,0)) "CPU"
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0)) "WAIT"
,sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0)) "IO"
,sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
,du.username,ash2.SQL_EXEC_ID,
dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj
,ash2.machine as blocking_machine
from dba_hist_active_sess_history ash
left join dba_objects do on do.object_id=ash.CURRENT_OBJ#
join dba_hist_active_sess_history ash2 on ash.BLOCKING_SESSION=ash2.session_id and ash.BLOCKING_SESSION_SERIAL#=ash2.session_serial# and ash.SNAP_ID=ash2.SNAP_ID
join dba_users du on du.USER_ID=ash2.USER_ID
left join dba_procedures dp on dp.object_id=ash2.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
where ash.SQL_ID is not NULL
and ash.SAMPLE_TIME > trunc(sysdate)
group by ash.SQL_EXEC_ID,ash2.SQL_EXEC_ID, ash2.machine, ash.session_id,ash.session_serial#,ash.event,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION,ash.BLOCKING_SESSION_SERIAL#, ash2.sql_id ,du.username,
dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null)
,do.object_name
having sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0)) >0
and max(ash.SAMPLE_TIME) - min (ash.SAMPLE_TIME) > interval '3' minute
order by 1,ash2.sql_exec_id;
col object_name for a35
col cnt for 99999
SELECT
cnt, object_name, object_type,file#, dbablk, obj, tch, hladdr
FROM (
select count(*) cnt, rfile, block from (
SELECT /*+ ORDERED USE_NL(l.x$ksuprlat) */
--l.laddr, u.laddr, u.laddrx, u.laddrr,
dbms_utility.data_block_address_file(to_number(object,'XXXXXXXX')) rfile,
dbms_utility.data_block_address_block(to_number(object,'XXXXXXXX')) block
FROM
(SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 100000) s,
(SELECT ksuprlnm LNAME, ksuprsid sid, ksuprlat laddr,
TO_CHAR(ksulawhy,'XXXXXXXXXXXXXXXX') object
FROM x$ksuprlat) l,
(select indx, kslednam from x$ksled ) e,
(SELECT
indx
, ksusesqh sqlhash
, ksuseopc
, ksusep1r laddr
FROM x$ksuse) u
WHERE LOWER(l.Lname) LIKE LOWER('%cache buffers chains%')
AND u.laddr=l.laddr
AND u.ksuseopc=e.indx
AND e.kslednam like '%cache buffers chains%'
)
group by rfile, block
) objs,
x$bh bh,
dba_objects o
WHERE
bh.file#=objs.rfile
and bh.dbablk=objs.block
and o.object_id=bh.obj
order by cnt
;
SET PAGESIZE 100
SET LINESIZE 200
COLUMN begin_time FORMAT A20
COLUMN instance_name FORMAT A20
COLUMN cpu_count FORMAT 99
COLUMN aas FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === DATABASE LOAD - AAS BASED ON DELTA DB TIME (CORRECTED) ===
WITH db_time_data AS (
SELECT s.snap_id,
s.instance_number,
MIN(s.begin_interval_time) AS begin_time,
MAX(s.end_interval_time) AS end_time,
SUM(tm.value) AS db_time
FROM dba_hist_sys_time_model tm
JOIN dba_hist_snapshot s
ON tm.snap_id = s.snap_id
AND tm.instance_number = s.instance_number
WHERE tm.stat_name = 'DB time'
AND s.begin_interval_time > SYSDATE - &DAYS_AGO
GROUP BY s.snap_id, s.instance_number
),
cpu_cores AS (
SELECT instance_number,
MAX(VALUE) AS cpu_count
FROM dba_hist_osstat
WHERE stat_name = 'NUM_CPUS'
GROUP BY instance_number
),
instance_names AS (
SELECT DISTINCT instance_number, instance_name FROM gv$instance
)
SELECT TO_CHAR(d.begin_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
i.instance_name,
c.cpu_count,
ROUND((d.db_time / 1000000) /
((CAST(d.end_time AS DATE) - CAST(d.begin_time AS DATE)) * 24 * 60 * 60), 2) AS aas,
CASE
WHEN ROUND((d.db_time / 1000000) /
((CAST(d.end_time AS DATE) - CAST(d.begin_time AS DATE)) * 24 * 60 * 60), 2) > c.cpu_count * 1.0 THEN 'CRITICAL'
WHEN ROUND((d.db_time / 1000000) /
((CAST(d.end_time AS DATE) - CAST(d.begin_time AS DATE)) * 24 * 60 * 60), 2) > c.cpu_count * 0.75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM db_time_data d
JOIN cpu_cores c ON d.instance_number = c.instance_number
LEFT JOIN instance_names i ON d.instance_number = i.instance_number
ORDER BY d.begin_time DESC, i.instance_name;
##
58_rac_iops_trend.sql
PROMPT === 1-DAY IOPS TREND PER INSTANCE (AWR: DBA_HIST_SYSSTAT) ===
SET PAGESIZE 100
SET LINESIZE 200
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;
PROMPT === REAL-TIME IOPS PER INSTANCE (GV$SYSSTAT) ===
SET PAGESIZE 100
SET LINESIZE 200
COLUMN inst_id FORMAT 99
COLUMN name FORMAT A40
COLUMN value FORMAT 999999999
COLUMN iops FORMAT 999999
SELECT inst_id,
'IOPS (Read+Write)' AS name,
reads + writes AS value,
ROUND((reads + writes) / 60) AS iops -- assuming ~60 sec snapshot
FROM (
SELECT inst_id,
SUM(CASE WHEN name LIKE 'physical read IO requests' THEN value ELSE 0 END) AS reads,
SUM(CASE WHEN name LIKE 'physical write IO requests' THEN value ELSE 0 END) AS writes
FROM gv$sysstat
WHERE name IN ('physical read IO requests', 'physical write IO requests')
GROUP BY inst_id
);
File Name Description
53_rac_wait_skew.sql RAC node wait skew detection from ASH
PROMPT === TOP WAIT EVENTS PER INSTANCE (RAC SKEW CHECK) ===
SET PAGESIZE 100
SET LINESIZE 200
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;
54_exadata_smart_scan_stats.sql Smart scan stats from V$SYSSTAT
PROMPT === EXADATA SMART SCAN UTILIZATION (V$SYSSTAT) ===
SET PAGESIZE 100
SET LINESIZE 200
COLUMN name FORMAT A50
COLUMN value FORMAT 999999999
SELECT name,
value
FROM v$sysstat
WHERE name LIKE 'cell smart%scan%';
55_interconnect_latency.sql Exadata interconnect performance
PROMPT === INTERCONNECT LATENCY (GV$CELL_GLOBAL_STATISTICS) ===
SET PAGESIZE 100
SET LINESIZE 200
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;
56_gc_contention.sql Global cache contention (gc waits)
PROMPT === RAC GLOBAL CACHE CONTENTION EVENTS (GV$EVENT_HISTOGRAM) ===
SET PAGESIZE 100
SET LINESIZE 200
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;
57_exadata_offload_ratio.sql Offload vs logical I/O ratio from AWR
PROMPT === EXADATA OFFLOAD SQL STATS (AWR: DBA_HIST_SQLSTAT) ===
SET PAGESIZE 100
SET LINESIZE 200
COLUMN sql_id FORMAT A15
COLUMN offload_gb FORMAT 999999.99
COLUMN logical_gb FORMAT 999999.99
COLUMN offload_ratio FORMAT 9.99
SELECT sql_id,
SUM(cell_offload_elig_bytes) / 1024 / 1024 / 1024 AS offload_gb,
SUM(cell_uncompressed_bytes) / 1024 / 1024 / 1024 AS logical_gb,
ROUND(SUM(cell_offload_elig_bytes) / NULLIF(SUM(cell_uncompressed_bytes), 0), 2) AS offload_ratio
FROM dba_hist_sqlstat
WHERE snap_id IN (
SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 1
)
GROUP BY sql_id
ORDER BY offload_ratio DESC NULLS LAST;
SET PAGESIZE 100
SET PAGESIZE 100
SET LINESIZE 200
COLUMN begin_time FORMAT A20
COLUMN instance_name FORMAT A20
COLUMN cpu_count FORMAT 99
COLUMN db_time FORMAT 999999999
COLUMN db_time_mins FORMAT 999999.99
COLUMN aas FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === DATABASE LOAD (AAS PER MINUTE FROM AWR DELTAS) - ORACLE RAC ===
WITH dbtime_deltas AS (
SELECT s.instance_number,
s.snap_id,
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 - &DAYS_AGO
),
cpu_cores AS (
SELECT instance_number,
MAX(value) AS cpu_count
FROM dba_hist_osstat
WHERE stat_name = 'NUM_CPUS'
GROUP BY instance_number
),
instance_names AS (
SELECT DISTINCT inst_id AS instance_number, instance_name FROM gv$instance
),
load_data AS (
SELECT d.begin_interval_time,
d.end_interval_time,
i.instance_name,
c.cpu_count,
d.db_time,
ROUND(d.db_time / 1e6, 2) AS db_time_mins,
ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) AS aas,
CASE
WHEN ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) > c.cpu_count THEN 'CRITICAL'
WHEN ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) > c.cpu_count * 0.75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM dbtime_deltas d
JOIN cpu_cores c ON d.instance_number = c.instance_number
LEFT JOIN instance_names i ON d.instance_number = i.instance_number
WHERE d.db_time IS NOT NULL
)
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
instance_name,
cpu_count,
db_time,
db_time_mins,
aas,
status
FROM load_data
ORDER BY begin_interval_time DESC, instance_name;
PROMPT
PROMPT === TOTAL AAS PER MINUTE ACROSS ALL INSTANCES ===
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
ROUND(SUM(aas), 2) AS total_aas
FROM load_data
GROUP BY begin_interval_time
ORDER BY begin_interval_time DESC;
PROMPT
PROMPT === TOTAL AAS PER MINUTE ACROSS ALL INSTANCES ===
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
ROUND(SUM(aas), 2) AS total_aas
FROM load_data
GROUP BY begin_interval_time
ORDER BY begin_interval_time DESC;
SET PAGESIZE 100
SET LINESIZE 200
COLUMN begin_time FORMAT A20
COLUMN instance_name FORMAT A20
COLUMN cpu_count FORMAT 99
COLUMN db_time_mins FORMAT 999999.99
COLUMN aas FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === DATABASE LOAD (AAS PER MINUTE FROM AWR) - ORACLE RAC ===
WITH db_time_data AS (
SELECT s.snap_id,
s.instance_number,
s.begin_interval_time,
s.end_interval_time,
MAX(CASE WHEN tm.stat_name = 'DB time' THEN tm.value END) AS db_time
FROM dba_hist_sys_time_model tm
JOIN dba_hist_snapshot s
ON tm.snap_id = s.snap_id AND tm.instance_number = s.instance_number
WHERE s.begin_interval_time > SYSDATE - &DAYS_AGO
AND tm.stat_name = 'DB time'
GROUP BY s.snap_id, s.instance_number, s.begin_interval_time, s.end_interval_time
),
cpu_cores AS (
SELECT instance_number,
MAX(value) AS cpu_count
FROM dba_hist_osstat
WHERE stat_name = 'NUM_CPUS'
GROUP BY instance_number
),
instance_names AS (
SELECT DISTINCT inst_id AS instance_number, instance_name FROM gv$instance
)
SELECT TO_CHAR(d.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
i.instance_name,
c.cpu_count,
ROUND(d.db_time / 1e6, 2) AS db_time_mins,
ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) AS aas,
CASE
WHEN ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) > c.cpu_count THEN 'CRITICAL'
WHEN ROUND((d.db_time / 1e6) /
((CAST(d.end_interval_time AS DATE) - CAST(d.begin_interval_time AS DATE)) * 24 * 60), 2) > c.cpu_count * 0.75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM db_time_data d
JOIN cpu_cores c ON d.instance_number = c.instance_number
LEFT JOIN instance_names i ON d.instance_number = i.instance_number
ORDER BY d.begin_interval_time DESC, i.instance_name;
is_exadata() {
local db_connect="$1"
# Query V$VERSION or V$PARAMETER to detect Exadata features
local result
result=$(sqlplus -s "$db_connect" < 50 THEN 'CRITICAL'
ELSE 'OK'
END AS status
FROM gv$active_session_history
WHERE event LIKE 'ges%'
AND sample_time > SYSDATE - (&HOURS_AGO / 24)
GROUP BY event
ORDER BY samples DESC;
47
SET PAGESIZE 100
SET LINESIZE 200
COLUMN inst_id FORMAT 99
COLUMN name FORMAT A40
COLUMN mb FORMAT 999999.99
COLUMN status FORMAT A10
PROMPT === HIGH INTERCONNECT ACTIVITY (GV$SYSSTAT) ===
SELECT inst_id,
name,
ROUND(value / 1024 / 1024, 2) AS mb,
CASE
WHEN value > 500000000 THEN 'CRITICAL'
ELSE 'OK'
END AS status
FROM gv$sysstat
WHERE name IN (
'gc current blocks received',
'gc cr blocks received',
'gc current blocks served',
'gc cr blocks served'
)
ORDER BY inst_id, name;
46
SET PAGESIZE 100
SET LINESIZE 200
COLUMN blocking_session FORMAT 99999
COLUMN blocking_inst_id FORMAT 99
COLUMN blocks FORMAT 9999
COLUMN first_seen FORMAT A20
COLUMN last_seen FORMAT A20
COLUMN status FORMAT A10
PROMPT === GES BLOCKING EVENTS (GV$ACTIVE_SESSION_HISTORY) ===
SELECT blocking_session, blocking_inst_id,
COUNT(*) AS blocks,
TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI') AS first_seen,
TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI') AS last_seen,
CASE
WHEN COUNT(*) > 20 THEN 'CRITICAL'
ELSE 'OK'
END AS status
FROM gv$active_session_history
WHERE blocking_session IS NOT NULL
AND sample_time > SYSDATE - (&HOURS_AGO / 24)
GROUP BY blocking_session, blocking_inst_id
ORDER BY blocks DESC;
45
SET PAGESIZE 100
SET LINESIZE 200
COLUMN inst_id FORMAT 99
COLUMN event FORMAT A40
COLUMN count FORMAT 99999
COLUMN status FORMAT A10
PROMPT === GC WAITS BY INSTANCE (GV$ACTIVE_SESSION_HISTORY) ===
SELECT inst_id,
event,
COUNT(*) AS count,
CASE
WHEN COUNT(*) > 500 THEN 'CRITICAL'
WHEN COUNT(*) > 200 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM gv$active_session_history
WHERE event LIKE 'gc%' AND sample_time > SYSDATE - (&HOURS_AGO / 24)
GROUP BY inst_id, event
ORDER BY inst_id, count DESC;45
44
SET PAGESIZE 100
SET LINESIZE 200
COLUMN event FORMAT A40
COLUMN samples FORMAT 99999
COLUMN pct FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === TOP GLOBAL CACHE WAITS (GV$ACTIVE_SESSION_HISTORY) ===
SELECT event,
COUNT(*) AS samples,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS pct,
CASE
WHEN event LIKE 'gc%' AND COUNT(*) > 100 THEN 'CRITICAL'
ELSE 'OK'
END AS status
FROM gv$active_session_history
WHERE event LIKE 'gc%'
AND sample_time > SYSDATE - (&HOURS_AGO / 24)
GROUP BY event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;
43
SET PAGESIZE 100
SET LINESIZE 200
COLUMN inst_id FORMAT 99
COLUMN cpu_busy_secs FORMAT 999999.99
COLUMN total_cpu_secs FORMAT 999999.99
COLUMN cpu_util_pct FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === REAL-TIME CPU UTILIZATION (GV$OSSTAT) - ORACLE 19C RAC ===
WITH os_stat AS (
SELECT inst_id,
MAX(CASE WHEN stat_name = 'BUSY_TIME' THEN value END) AS busy_time,
MAX(CASE WHEN stat_name = 'IDLE_TIME' THEN value END) AS idle_time
FROM gv$osstat
WHERE stat_name IN ('BUSY_TIME', 'IDLE_TIME')
GROUP BY inst_id
)
SELECT inst_id,
ROUND(busy_time / 100, 2) AS cpu_busy_secs,
ROUND((busy_time + idle_time) / 100, 2) AS total_cpu_secs,
ROUND((busy_time / (busy_time + idle_time)) * 100, 2) AS cpu_util_pct,
CASE
WHEN (busy_time / (busy_time + idle_time)) * 100 > 90 THEN 'CRITICAL'
WHEN (busy_time / (busy_time + idle_time)) * 100 > 75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM os_stat
ORDER BY inst_id;
42
SET PAGESIZE 100
SET LINESIZE 200
COLUMN inst_id FORMAT 99
COLUMN cpu_count FORMAT 99
COLUMN aas FORMAT 999.99
COLUMN status FORMAT A10
PROMPT === REAL-TIME AAS (GV$ACTIVE_SESSION_HISTORY, LAST &MINUTES_AGO MINUTES) ===
WITH active_sessions AS (
SELECT inst_id,
COUNT(*) / (&MINUTES_AGO * 60) AS aas
FROM gv$active_session_history
WHERE sample_time > SYSDATE - (&MINUTES_AGO / 1440)
AND session_type = 'FOREGROUND'
GROUP BY inst_id
),
cpu_cores AS (
SELECT instance_number AS inst_id, MAX(value) AS cpu_count
FROM dba_hist_osstat
WHERE stat_name = 'NUM_CPUS'
GROUP BY instance_number
)
SELECT a.inst_id,
c.cpu_count,
ROUND(a.aas, 2) AS aas,
CASE
WHEN a.aas > c.cpu_count THEN 'CRITICAL'
WHEN a.aas > c.cpu_count * 0.75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM active_sessions a
JOIN cpu_cores c ON a.inst_id = c.inst_id
ORDER BY a.inst_id;
SET PAGESIZE 100
SET LINESIZE 200
COLUMN service_name FORMAT A25
COLUMN inst_id FORMAT 99
COLUMN session_type FORMAT A10
COLUMN active_count FORMAT 99999
COLUMN cpu_count FORMAT 99
COLUMN status FORMAT A10
PROMPT === ACTIVE SESSIONS PER INSTANCE (SCALED TO CPU CORES) - ORACLE 19C RAC ===
WITH session_stats AS (
SELECT inst_id,
service_hash,
session_type,
COUNT(CASE WHEN session_state IN ('ON CPU','WAITING') THEN 1 END) AS active_count
FROM gv$active_session_history
WHERE sample_time > SYSDATE - &DAYS_AGO
GROUP BY inst_id, service_hash, session_type
),
cpu_cores AS (
SELECT instance_number AS inst_id, MAX(value) AS cpu_count
FROM dba_hist_osstat
WHERE stat_name = 'NUM_CPUS'
GROUP BY instance_number
),
services AS (
SELECT name_hash, name AS service_name FROM gv$services
)
SELECT NVL(s.service_name, 'Unknown') AS service_name,
ss.inst_id,
ss.session_type,
ss.active_count,
c.cpu_count,
CASE
WHEN ss.active_count > c.cpu_count THEN 'CRITICAL'
WHEN ss.active_count > c.cpu_count * 0.75 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM session_stats ss
JOIN cpu_cores c ON ss.inst_id = c.inst_id
LEFT JOIN services s ON ss.service_hash = s.name_hash
ORDER BY ss.inst_id, service_name;
#####
# Example modules
run_sql_file_report "DB Load (AAS)" "$SQL_DIR/01_db_load.sql" "$DB_CONNECT_STRING"
run_sql_file_report "Long Running Sessions" "$SQL_DIR/06_long_running_sessions.sql" "$DB_CONNECT_STRING"
run_sql_file_report "IO Response Time" "$SQL_DIR/07_io_response_time.sql" "$DB_CONNECT_STRING"
if is_exadata; then
run_sql_file_report "Exadata Offload Efficiency" "$SQL_DIR/35_exadata_offload_efficiency.sql" "$DB_CONNECT_STRING"
run_sql_file_report "Exadata Cell Interconnect Waits" "$SQL_DIR/36_exadata_cell_interconnect_waits.sql" "$DB_CONNECT_STRING"
run_sql_file_report "Exadata Flash Cache Stats" "$SQL_DIR/37_exadata_flashcache_stats.sql" "$DB_CONNECT_STRING"
run_sql_file_report "Exadata Smart Scan Usage" "$SQL_DIR/38_exadata_smart_scan_usage.sql" "$DB_CONNECT_STRING"
run_sql_file_report "ASM Diskgroup Status" "$SQL_DIR/39_exadata_asm_diskgroup_status.sql" "$DB_CONNECT_STRING"
run_sql_file_report "Exadata Wait Class Usage" "$SQL_DIR/40_exadata_wait_class_usage.sql" "$DB_CONNECT_STRING"
run_sql_file_report "IORM Plan Check" "$SQL_DIR/41_exadata_iorm_plan.sql" "$DB_CONNECT_STRING"
fi
echo "