================================================================================
ORACLE EBS 12.2 / DATABASE 19c - REAL-TIME PERFORMANCE QUERY PACK (GV$ / V$)
Read-only. Built from the "Performance Issue RCA - Deep Dive Query Workbook"
view list, with the review fixes applied:
- CPU vs wait separated using STATE (not WAIT_CLASS alone)
- ASH object attribution only for waiting samples
- INST_ID included in every distinct-session count (RAC)
- Hot blocks from ROW_WAIT_* (no P1/P2 misreads for latches)
- Real block size used for TEMP / undo MB
- Pending requests limited to runnable ones; CM target vs actual
================================================================================
LICENSING
---------
- GV$ACTIVE_SESSION_HISTORY (live ASH) requires the Diagnostics Pack.
- GV$SQL_MONITOR, GV$SQL_PLAN_MONITOR and DBMS_SQLTUNE.REPORT_SQL_MONITOR
require the Tuning Pack.
- Confirm metric-view licensing (GV$SYSMETRIC*, GV$FILEMETRIC) against your
Oracle licensing guide.
USAGE
-----
- Run as a DBA user with SELECT on GV$ and DBA_ views; FND queries need
access to the APPS objects (run as APPS or via synonyms/grants).
- Bind variables (set before running):
:inst_id :sid :serial :sql_id :sql_exec_id :child_no :request_id
- Recommended SQL*Plus settings:
SET LINESIZE 400 PAGESIZE 5000 TRIMSPOOL ON LONG 1000000 LONGCHUNKSIZE 1000000
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
(ALTER SESSION changes only your own session display format.)
================================================================================
RT0 - CONTEXT: INSTANCES, CPU CAPACITY, ASH COVERAGE
================================================================================
-- RT0.1 Instances
SELECT inst_id, instance_name, host_name, status, startup_time, version
FROM gv$instance
ORDER BY inst_id;
-- RT0.2 CPU capacity (yardstick for "sessions on CPU")
SELECT inst_id, stat_name, value
FROM gv$osstat
WHERE stat_name IN ('NUM_CPUS','NUM_CPU_CORES','LOAD')
ORDER BY inst_id, stat_name;
-- RT0.3 How far back live ASH reaches on each instance (varies with load)
SELECT inst_id, MIN(sample_time) AS oldest_sample, MAX(sample_time) AS newest_sample,
ROUND((CAST(MAX(sample_time) AS DATE) - CAST(MIN(sample_time) AS DATE)) * 24, 1) AS hours_covered
FROM gv$active_session_history
GROUP BY inst_id
ORDER BY inst_id;
================================================================================
RT1 - WHAT IS THE DATABASE DOING RIGHT NOW
================================================================================
-- RT1.1 Current system metrics (last 60-second interval)
-- Read : CPU Usage Per Sec / 100 = CPU cores used by the DB.
-- Host CPU high but DB CPU low -> load is outside the database.
-- Single-block read latency is the live storage-latency check.
SELECT inst_id, metric_name, ROUND(value,2) AS value, metric_unit,
TO_CHAR(begin_time,'HH24:MI:SS') AS from_t, TO_CHAR(end_time,'HH24:MI:SS') AS to_t
FROM gv$sysmetric
WHERE group_id = 2
AND metric_name IN ('Host CPU Utilization (%)',
'CPU Usage Per Sec',
'Average Active Sessions',
'Database Time Per Sec',
'Average Synchronous Single-Block Read Latency',
'Physical Read Total IO Requests Per Sec',
'Physical Read Total Bytes Per Sec',
'Executions Per Sec',
'Hard Parse Count Per Sec',
'User Transaction Per Sec',
'Redo Generated Per Sec',
'Session Count',
'Global Cache Average CR Get Time',
'Global Cache Average Current Get Time')
ORDER BY metric_name, inst_id;
-- RT1.2 Same metrics minute-by-minute for the last hour (trend)
SELECT inst_id, TO_CHAR(begin_time,'HH24:MI') AS minute, metric_name, ROUND(value,2) AS value
FROM gv$sysmetric_history
WHERE group_id = 2
AND begin_time > SYSDATE - 1/24
AND metric_name IN ('Host CPU Utilization (%)','CPU Usage Per Sec',
'Average Active Sessions',
'Average Synchronous Single-Block Read Latency')
ORDER BY metric_name, inst_id, begin_time;
-- RT1.3 Active-session mix now: CPU vs each wait class (STATE-aware)
-- Read : on_cpu near NUM_CPUS = CPU saturation; large user_io = I/O bound;
-- application = locks; cluster = RAC gc; concurrency = latches/buffer busy.
SELECT inst_id,
COUNT(*) AS active_sess,
SUM(CASE WHEN state <> 'WAITING' THEN 1 ELSE 0 END) AS on_cpu,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'User I/O' THEN 1 ELSE 0 END) AS user_io,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Application' THEN 1 ELSE 0 END) AS application,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Concurrency' THEN 1 ELSE 0 END) AS concurrency,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Cluster' THEN 1 ELSE 0 END) AS cluster_w,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Commit' THEN 1 ELSE 0 END) AS commit_w,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Configuration' THEN 1 ELSE 0 END) AS configuration,
SUM(CASE WHEN state = 'WAITING' AND wait_class = 'Scheduler' THEN 1 ELSE 0 END) AS resmgr_sched,
SUM(CASE WHEN state = 'WAITING' AND wait_class NOT IN
('User I/O','Application','Concurrency','Cluster','Commit','Configuration','Scheduler')
THEN 1 ELSE 0 END) AS other_w
FROM gv$session
WHERE type = 'USER'
AND status = 'ACTIVE'
AND (state <> 'WAITING' OR wait_class <> 'Idle')
GROUP BY inst_id
ORDER BY inst_id;
-- RT1.4 Active sessions in detail (STATE-aware activity)
SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.action, s.machine,
s.sql_id, s.sql_child_number,
CASE WHEN s.state = 'WAITING' THEN s.event ELSE 'ON CPU' END AS activity,
CASE WHEN s.state = 'WAITING' THEN s.wait_class ELSE 'CPU' END AS act_class,
CASE WHEN s.state = 'WAITING'
THEN ROUND(s.wait_time_micro/1e6,1)
ELSE ROUND(s.time_since_last_wait_micro/1e6,1) END AS secs_in_state,
ROUND((SYSDATE - s.sql_exec_start) * 86400) AS sql_exec_sec,
s.last_call_et,
s.blocking_instance, s.blocking_session,
s.final_blocking_instance, s.final_blocking_session,
s.row_wait_obj#
FROM gv$session s
WHERE s.type = 'USER'
AND s.status = 'ACTIVE'
AND (s.state <> 'WAITING' OR s.wait_class <> 'Idle')
ORDER BY s.last_call_et DESC;
-- RT1.5 Average active sessions per minute, last 30 minutes (live ASH, 1-s samples)
-- Read : aas_cpu vs NUM_CPUS; aas_cpu_queued > 0 = Resource Manager CPU throttling.
-- The current (partial) minute will read low.
SELECT inst_id,
TO_CHAR(TRUNC(sample_time,'MI'),'HH24:MI') AS minute,
ROUND(COUNT(*)/60,1) AS aas,
ROUND(SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END)/60,1) AS aas_cpu,
ROUND(SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END)/60,1) AS aas_user_io,
ROUND(SUM(CASE WHEN wait_class = 'Application' THEN 1 ELSE 0 END)/60,1) AS aas_app,
ROUND(SUM(CASE WHEN wait_class = 'Cluster' THEN 1 ELSE 0 END)/60,1) AS aas_cluster,
ROUND(SUM(CASE WHEN event = 'resmgr:cpu quantum' THEN 1 ELSE 0 END)/60,1) AS aas_cpu_queued
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 30/1440
GROUP BY inst_id, TRUNC(sample_time,'MI')
ORDER BY TRUNC(sample_time,'MI'), inst_id;
================================================================================
RT2 - TOP CONSUMERS IN THE LAST 15 MINUTES (LIVE ASH)
================================================================================
-- RT2.1 Top SQL (aas = average active sessions over the 15-min window)
SELECT sql_id, sql_plan_hash_value,
COUNT(*) AS samples,
ROUND(COUNT(*)/900,2) AS aas,
SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu_samples,
SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END) AS io_samples,
COUNT(DISTINCT inst_id || ':' || session_id || ',' || session_serial#) AS sessions,
MAX(module) AS sample_module,
ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100,1) AS pct
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 15/1440
AND sql_id IS NOT NULL
GROUP BY sql_id, sql_plan_hash_value
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
-- RT2.2 Top wait events (and CPU)
SELECT NVL(wait_class,'CPU') AS wait_class, NVL(event,'ON CPU') AS event,
COUNT(*) AS samples,
ROUND(COUNT(*)/900,2) AS aas,
ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100,1) AS pct
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 15/1440
GROUP BY wait_class, event
ORDER BY samples DESC
FETCH FIRST 25 ROWS ONLY;
-- RT2.3 Top modules / programs on CPU
SELECT module, action, program, machine,
COUNT(*) AS cpu_samples,
ROUND(COUNT(*)/900,2) AS aas_cpu,
ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100,1) AS pct_of_cpu
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 15/1440
AND session_state = 'ON CPU'
GROUP BY module, action, program, machine
ORDER BY cpu_samples DESC
FETCH FIRST 20 ROWS ONLY;
-- RT2.4 Top objects for I/O / contention waits (waiting samples only)
SELECT o.owner, o.object_name, o.object_type, o.subobject_name, h.event,
COUNT(*) AS samples,
COUNT(DISTINCT h.current_block#) AS distinct_blocks
FROM gv$active_session_history h
JOIN dba_objects o ON o.object_id = h.current_obj#
WHERE h.sample_time > SYSDATE - 15/1440
AND h.session_state = 'WAITING'
AND h.wait_class IN ('User I/O','Cluster','Concurrency','Application')
AND h.current_obj# > 0
GROUP BY o.owner, o.object_name, o.object_type, o.subobject_name, h.event
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
-- RT2.5 Top PL/SQL entry points (which package is driving the load)
SELECT p.owner, p.object_name, p.procedure_name,
COUNT(*) AS samples,
SUM(CASE WHEN h.session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu_samples,
ROUND(COUNT(*)/900,2) AS aas
FROM gv$active_session_history h
JOIN dba_procedures p
ON p.object_id = h.plsql_entry_object_id
AND p.subprogram_id = h.plsql_entry_subprogram_id
WHERE h.sample_time > SYSDATE - 15/1440
GROUP BY p.owner, p.object_name, p.procedure_name
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
================================================================================
RT3 - EBS CONCURRENT PROCESSING (LIVE)
================================================================================
-- RT3.1 Running requests -> DB session, current activity, CPU so far
SELECT fcr.request_id,
fcp.concurrent_program_name,
fcpt.user_concurrent_program_name,
fu.user_name,
ROUND((SYSDATE - fcr.actual_start_date) * 1440,1) AS run_min,
s.inst_id, s.sid, s.serial#, s.sql_id,
CASE WHEN s.state = 'WAITING' THEN s.event ELSE 'ON CPU' END AS activity,
s.final_blocking_instance, s.final_blocking_session,
ROUND(st.value/100) AS cpu_sec_so_far,
fcr.argument_text
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_programs fcp
ON fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcp.application_id = fcr.program_application_id
JOIN apps.fnd_concurrent_programs_tl fcpt
ON fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcpt.language = USERENV('LANG')
LEFT JOIN apps.fnd_user fu ON fu.user_id = fcr.requested_by
LEFT JOIN gv$session s
ON s.audsid = fcr.oracle_session_id
AND fcr.oracle_session_id > 0
LEFT JOIN gv$sesstat st
ON st.inst_id = s.inst_id
AND st.sid = s.sid
AND st.statistic# = (SELECT statistic# FROM v$statname
WHERE name = 'CPU used by this session')
WHERE fcr.phase_code = 'R'
ORDER BY run_min DESC;
-- Note: a running request with no session row may be a Java / host / OPP
-- program that runs in other sessions - use RT3.2 or match by MODULE.
-- RT3.2 Fallback mapping via OS process id (for one request)
SELECT fcr.request_id, s.inst_id, s.sid, s.serial#, p.spid, s.sql_id, s.module, s.action,
CASE WHEN s.state = 'WAITING' THEN s.event ELSE 'ON CPU' END AS activity
FROM apps.fnd_concurrent_requests fcr
JOIN gv$process p ON p.spid = fcr.oracle_process_id
JOIN gv$session s ON s.paddr = p.addr AND s.inst_id = p.inst_id
WHERE fcr.request_id = :request_id;
-- RT3.3 Runnable pending requests and how long they have waited
-- Read : long waiting_min with idle DB = manager capacity / incompatibility,
-- not database performance.
SELECT fcr.request_id,
fcpt.user_concurrent_program_name,
DECODE(fcr.status_code,'I','Normal','Q','Standby','R','Normal',
'Z','Waiting','F','Scheduled',fcr.status_code) AS pending_status,
fcr.hold_flag,
fcr.requested_start_date,
ROUND((SYSDATE - GREATEST(fcr.request_date, fcr.requested_start_date)) * 1440,1) AS waiting_min,
fu.user_name
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_programs_tl fcpt
ON fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.application_id = fcr.program_application_id
AND fcpt.language = USERENV('LANG')
LEFT JOIN apps.fnd_user fu ON fu.user_id = fcr.requested_by
WHERE fcr.phase_code = 'P'
AND fcr.hold_flag = 'N'
AND fcr.requested_start_date <= SYSDATE
ORDER BY waiting_min DESC;
-- RT3.4 Concurrent managers: target vs actual processes vs running requests
-- Read : actual_active < target = manager processes missing;
-- running_reqs = actual_active with long pending queue = saturated.
SELECT q.concurrent_queue_name,
q.target_node,
q.max_processes AS target_processes,
q.running_processes AS fnd_running_processes,
(SELECT COUNT(*)
FROM apps.fnd_concurrent_processes p
WHERE p.queue_application_id = q.application_id
AND p.concurrent_queue_id = q.concurrent_queue_id
AND p.process_status_code = 'A') AS actual_active,
(SELECT COUNT(*)
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_processes p
ON p.concurrent_process_id = r.controlling_manager
WHERE r.phase_code = 'R'
AND p.queue_application_id = q.application_id
AND p.concurrent_queue_id = q.concurrent_queue_id) AS running_reqs
FROM apps.fnd_concurrent_queues q
WHERE q.enabled_flag = 'Y'
AND q.max_processes > 0
ORDER BY running_reqs DESC, q.concurrent_queue_name;
================================================================================
RT4 - BLOCKING, LOCKS AND WAIT CHAINS
================================================================================
-- RT4.1 Waiters with their FINAL blocker (and the blocker's EBS request, if any)
-- Read : blocker INACTIVE with high last_call_et = uncommitted session (often
-- an idle Forms session). Fix the holder, not the waiter.
SELECT w.inst_id, w.sid, w.serial#, w.username, w.module AS waiter_module, w.sql_id,
w.event, ROUND(w.wait_time_micro/1e6) AS wait_s,
o.owner || '.' || o.object_name AS waited_object,
w.final_blocking_instance AS blk_inst, w.final_blocking_session AS blk_sid,
b.serial# AS blk_serial, b.username AS blk_user, b.module AS blk_module,
b.action AS blk_action, b.machine AS blk_machine,
b.status AS blk_status, b.last_call_et AS blk_last_call_et,
b.sql_id AS blk_sql_id, b.prev_sql_id AS blk_prev_sql_id,
CASE WHEN b.state = 'WAITING' THEN b.event ELSE 'ON CPU' END AS blk_activity,
r.request_id AS blk_request_id
FROM gv$session w
LEFT JOIN gv$session b
ON b.inst_id = w.final_blocking_instance
AND b.sid = w.final_blocking_session
LEFT JOIN dba_objects o ON o.object_id = w.row_wait_obj#
LEFT JOIN apps.fnd_concurrent_requests r
ON r.oracle_session_id = b.audsid
AND r.phase_code = 'R'
AND b.audsid > 0
WHERE w.blocking_session IS NOT NULL
ORDER BY wait_s DESC;
-- RT4.2 Full wait chains (cluster-wide)
SELECT chain_id, chain_is_cycle, instance, sid, sess_serial#,
blocker_instance, blocker_sid, blocker_sess_serial#,
wait_event_text, in_wait_secs, num_waiters, row_wait_obj#
FROM v$wait_chains
ORDER BY chain_id, num_waiters DESC;
-- RT4.3 Lock holders and requesters (TM locks decoded to the object)
SELECT l.inst_id, l.sid, s.serial#, s.username, s.module, l.type, l.id1, l.id2,
DECODE(l.lmode,0,'None',1,'Null',2,'RowS',3,'RowX',4,'Share',5,'SRowX',6,'Excl',l.lmode) AS held,
DECODE(l.request,0,'None',1,'Null',2,'RowS',3,'RowX',4,'Share',5,'SRowX',6,'Excl',l.request) AS requested,
l.ctime AS secs_in_mode, l.block,
CASE WHEN l.type = 'TM'
THEN (SELECT owner || '.' || object_name FROM dba_objects WHERE object_id = l.id1)
END AS tm_object
FROM gv$lock l
JOIN gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid
WHERE l.request > 0 OR l.block > 0
ORDER BY l.block DESC, l.ctime DESC;
-- RT4.4 Locked objects with the owning session
SELECT lo.inst_id, lo.session_id AS sid, s.serial#, lo.oracle_username, s.module, s.status,
s.last_call_et, o.owner, o.object_name, o.object_type,
DECODE(lo.locked_mode,0,'None',1,'Null',2,'RowS',3,'RowX',4,'Share',5,'SRowX',6,'Excl',lo.locked_mode) AS locked_mode
FROM gv$locked_object lo
JOIN dba_objects o ON o.object_id = lo.object_id
JOIN gv$session s ON s.inst_id = lo.inst_id AND s.sid = lo.session_id
ORDER BY s.last_call_et DESC;
================================================================================
RT5 - HOT BLOCKS / BUFFER CONTENTION
================================================================================
-- RT5.1 Sessions waiting on the same object/block (uses ROW_WAIT_*)
SELECT s.inst_id, s.event, o.owner, o.object_name, o.object_type,
s.row_wait_file#, s.row_wait_block#, COUNT(*) AS sessions
FROM gv$session s
JOIN dba_objects o ON o.object_id = s.row_wait_obj#
WHERE s.state = 'WAITING'
AND s.event IN ('buffer busy waits','read by other session',
'gc buffer busy acquire','gc buffer busy release',
'gc cr block busy','gc current block busy',
'enq: TX - row lock contention','enq: TX - index contention')
GROUP BY s.inst_id, s.event, o.owner, o.object_name, o.object_type,
s.row_wait_file#, s.row_wait_block#
ORDER BY sessions DESC;
-- RT5.2 Most contended cache-buffers-chains latch children (cumulative since startup)
-- Note : mapping a latch child to blocks requires X$BH (SYS only). Use RT2.4 /
-- RT2.1 to find the SQL and object instead.
SELECT inst_id, addr, child#, gets, misses, sleeps
FROM gv$latch_children
WHERE name = 'cache buffers chains'
ORDER BY sleeps DESC
FETCH FIRST 10 ROWS ONLY;
