How to Gather Dictionary and Fixed Object Statistics in Oracle Database 19c

Oracle uses optimizer statistics to choose execution plans. Alongside application table statistics, dictionary and fixed object statistics support queries against database metadata and internal structures.

This post explains how to gather dictionary statistics in a pluggable database (PDB) and fixed object statistics at the container database (CDB) level.

Replace APP_PDB with your PDB name.

1. Connect to the Database

Log in as the Oracle software owner, set the database environment, and connect using SQL*Plus:

sqlplus / as sysdba

Check the current container and available PDBs:

SHOW CON_NAME;
SHOW PDBS;

Example output:

CON_ID  CON_NAME    OPEN MODE   RESTRICTED
------  ---------- ----------- ----------
2       PDB$SEED    READ ONLY   NO
3       APP_PDB     READ WRITE  NO

2. Gather Dictionary Statistics in the PDB

Dictionary statistics cover objects owned by SYS, SYSTEM, and other Oracle component schemas. Use the dedicated GATHER_DICTIONARY_STATS procedure for this purpose.

Switch to the required PDB:

ALTER SESSION SET CONTAINER = APP_PDB;

SHOW CON_NAME;

SET TIMING ON;

Gather dictionary statistics:

BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Expected output:

PL/SQL procedure successfully completed.

Gather Only Stale or Missing Dictionary Statistics

To limit collection to dictionary objects with stale or missing statistics:

BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS(
    options => 'GATHER STALE'
  );
END;
/

Choose either the default collection or the stale-only collection according to your maintenance requirement.

Is a Separate SYS Schema Collection Required?

For general dictionary statistics maintenance, use GATHER_DICTIONARY_STATS. A separate call to GATHER_SCHEMA_STATS('SYS', ...) is normally unnecessary because dictionary collection already covers the relevant SYS objects.

ALTER SESSION SET CONTAINER = APP_PDB;

SHOW CON_NAME

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'SYS',
    options          => 'GATHER STALE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE
  );
END;
/

3. Gather Fixed Object Statistics

Fixed objects are internal structures, including the underlying X$ tables used by many dynamic performance views.

Gather fixed object statistics when the database has a representative workload. Collecting them immediately after startup, before normal activity has populated the internal structures, can produce unrepresentative statistics.

For CDB-level maintenance, switch to the root container:

ALTER SESSION SET CONTAINER = CDB$ROOT;

SHOW CON_NAME;

SET TIMING ON;

Run the collection:

BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

Expected output:

PL/SQL procedure successfully completed.

Gathering dictionary statistics in one PDB does not replace dictionary maintenance in other containers. Plan collection separately for the containers that require it.

4. Review Statistics Collection History

Run the following query in the container where the collection was performed:

SET LINESIZE 200;
SET PAGESIZE 100;

COLUMN operation FORMAT A35;
COLUMN target FORMAT A30;

SELECT operation,
       target,
       start_time,
       end_time
FROM   dba_optstat_operations
WHERE  start_time > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY start_time DESC;

Review the operation and its start and end times. Also check the SQL*Plus output for any reported errors.

Practical Considerations

• Dictionary statistics collection can consume CPU and I/O; schedule it according to the database workload.

• Gather fixed object statistics under representative runtime conditions.

• Execution time depends on database size, workload, and available resources.

• These procedures collect optimizer statistics; they do not purge application data or reclaim storage.

• For Oracle E-Business Suite application schemas, follow the applicable EBS statistics gathering procedures separately.

Suggested Blogspot labels: Oracle 19c, Oracle DBA, Multitenant, PDB, DBMS_STATS, Dictionary Statistics, Fixed Object Statistics

Continue

 ================================================================================

 RT6 - SQL IN THE CURSOR CACHE

================================================================================

 

-- RT6.1  Per-execution profile of SQL currently being executed

-- Note : GV$SQL values are cumulative since the cursor was loaded.

SELECT q.inst_id, q.sql_id, q.child_number, q.plan_hash_value, q.executions, q.users_executing,

       ROUND(q.elapsed_time/1e6/NULLIF(q.executions,0),2)       AS ela_pe_s,

       ROUND(q.cpu_time/1e6/NULLIF(q.executions,0),2)           AS cpu_pe_s,

       ROUND(q.user_io_wait_time/1e6/NULLIF(q.executions,0),2)  AS io_pe_s,

       ROUND(q.buffer_gets/NULLIF(q.executions,0))              AS gets_pe,

       ROUND(q.disk_reads/NULLIF(q.executions,0))               AS reads_pe,

       ROUND(q.rows_processed/NULLIF(q.executions,0),1)         AS rows_pe,

       q.module, q.last_active_time

FROM   gv$sql q

WHERE  (q.inst_id, q.sql_id, q.child_number) IN

       (SELECT inst_id, sql_id, sql_child_number

        FROM   gv$session

        WHERE  type = 'USER' AND status = 'ACTIVE' AND sql_id IS NOT NULL)

ORDER  BY ela_pe_s DESC NULLS LAST;

 

 

-- RT6.2  Top SQL active in the last hour by total elapsed (cumulative stats)

SELECT inst_id, sql_id, plan_hash_value, executions,

       ROUND(elapsed_time/1e6)                             AS ela_s,

       ROUND(cpu_time/1e6)                                 AS cpu_s,

       ROUND(elapsed_time/1e6/NULLIF(executions,0),2)      AS ela_pe_s,

       ROUND(buffer_gets/NULLIF(executions,0))             AS gets_pe,

       ROUND(disk_reads/NULLIF(executions,0))              AS reads_pe,

       last_active_time

FROM   gv$sqlstats

WHERE  last_active_time > SYSDATE - 1/24

ORDER  BY elapsed_time DESC

FETCH FIRST 20 ROWS ONLY;

 

 

-- RT6.3  One SQL_ID: all children and their per-execution cost

SELECT inst_id, sql_id, child_number, plan_hash_value, executions, users_executing,

       ROUND(elapsed_time/1e6/NULLIF(executions,0),3)  AS ela_pe_s,

       ROUND(buffer_gets/NULLIF(executions,0))         AS gets_pe,

       ROUND(disk_reads/NULLIF(executions,0))          AS reads_pe,

       ROUND(rows_processed/NULLIF(executions,0),1)    AS rows_pe,

       optimizer_cost, is_bind_sensitive, is_bind_aware, is_shareable,

       sql_profile, sql_plan_baseline, last_active_time

FROM   gv$sql

WHERE  sql_id = :sql_id

ORDER  BY inst_id, child_number;

 

 

-- RT6.4  Plan with predicates and peeked binds (run on the instance holding the cursor)

-- Note : ALLSTATS / A-Rows only exist if STATISTICS_LEVEL=ALL or the

--        GATHER_PLAN_STATISTICS hint was used. For actual rows use RT7.2.

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, :child_no,

                                       'TYPICAL +PEEKED_BINDS +PREDICATE +OUTLINE'));

 

 

-- RT6.5  Captured binds

SELECT inst_id, sql_id, child_number, position, name, datatype_string, last_captured, value_string

FROM   gv$sql_bind_capture

WHERE  sql_id = :sql_id

ORDER  BY inst_id, child_number, position;

 

 

-- RT6.6  SQL with many child cursors (cursor-sharing problems)

SELECT inst_id, sql_id, COUNT(*) AS children, SUM(executions) AS execs,

       SUBSTR(MAX(sql_text),1,120) AS sql_text

FROM   gv$sql

GROUP  BY inst_id, sql_id

HAVING COUNT(*) > 20

ORDER  BY children DESC

FETCH FIRST 20 ROWS ONLY;

 

 

-- RT6.7  Why children were not shared (look for 'Y' columns)

SELECT *

FROM   gv$sql_shared_cursor

WHERE  sql_id = :sql_id

AND    ROWNUM <= 20;

 

 

-- RT6.8  Optimizer settings that differ between children of one SQL_ID

SELECT inst_id, name,

       COUNT(DISTINCT value) AS distinct_values,

       LISTAGG(child_number || '=' || value, ', ') WITHIN GROUP (ORDER BY child_number) AS child_values

FROM   gv$sql_optimizer_env

WHERE  sql_id = :sql_id

GROUP  BY inst_id, name

HAVING COUNT(DISTINCT value) > 1

ORDER  BY inst_id, name;

 

 

================================================================================

 RT7 - SQL MONITOR (TUNING PACK) AND LONGOPS

================================================================================

 

-- RT7.1  Monitored SQL executing now

SELECT inst_id, sid, session_serial#, sql_id, sql_exec_id, sql_exec_start, status,

       username, module, px_servers_allocated,

       ROUND(elapsed_time/1e6)              AS ela_s,

       ROUND(cpu_time/1e6)                  AS cpu_s,

       ROUND(user_io_wait_time/1e6)         AS io_s,

       ROUND(application_wait_time/1e6)     AS app_s,

       ROUND(concurrency_wait_time/1e6)     AS conc_s,

       ROUND(cluster_wait_time/1e6)         AS cluster_s,

       buffer_gets, disk_reads, physical_read_requests

FROM   gv$sql_monitor

WHERE  status = 'EXECUTING'

ORDER  BY elapsed_time DESC;

 

 

-- RT7.2  Actual rows / starts / reads per plan line for one execution

-- Read : E-rows vs A-rows gaps = cardinality problem; huge starts on an

--        index line with few rows returned higher up = excessive probing.

SELECT plan_line_id,

       LPAD(' ', plan_depth) || plan_operation || ' ' || plan_options AS operation,

       plan_object_owner, plan_object_name,

       plan_cardinality                      AS e_rows,

       starts,

       output_rows                           AS a_rows,

       physical_read_requests,

       ROUND(physical_read_bytes/1048576)    AS read_mb,

       ROUND(workarea_mem/1048576)           AS wa_mem_mb,

       ROUND(workarea_tempseg/1048576)       AS wa_temp_mb

FROM   gv$sql_plan_monitor

WHERE  sql_id      = :sql_id

AND    sql_exec_id = :sql_exec_id

AND    inst_id     = :inst_id

ORDER  BY plan_line_id;

 

 

-- RT7.3  Where the time goes inside that execution (ASH by plan line)

SELECT sql_plan_line_id, sql_plan_operation, sql_plan_options,

       NVL(event,'ON CPU') AS event, COUNT(*) AS samples

FROM   gv$active_session_history

WHERE  sql_id      = :sql_id

AND    sql_exec_id = :sql_exec_id

GROUP  BY sql_plan_line_id, sql_plan_operation, sql_plan_options, event

ORDER  BY samples DESC;

 

 

-- RT7.4  Text SQL Monitor report for one execution

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id       => :sql_id,

                                       sql_exec_id  => :sql_exec_id,

                                       type         => 'TEXT',

                                       report_level => 'ALL')

FROM   dual;

 

 

-- RT7.5  Long operations in progress (instrumented operations only)

-- Note : an empty result does NOT mean the session is idle.

SELECT l.inst_id, l.sid, l.serial#, s.module, l.opname, l.target, l.sofar, l.totalwork, l.units,

       ROUND(l.sofar/NULLIF(l.totalwork,0)*100,1) AS pct_done,

       l.elapsed_seconds, l.time_remaining, l.sql_id

FROM   gv$session_longops l

JOIN   gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid AND s.serial# = l.serial#

WHERE  l.totalwork > 0

AND    l.sofar < l.totalwork

ORDER  BY l.elapsed_seconds DESC;

 

 

================================================================================

 RT8 - I/O LATENCY NOW

================================================================================

 

-- RT8.1  Per-datafile read latency in the last metric interval

-- Read : average_read_time is in centiseconds -> *10 = ms. One file or

--        diskgroup much slower than the rest = storage hotspot.

SELECT f.inst_id, d.tablespace_name, d.file_name,

       f.physical_reads,

       ROUND(f.average_read_time * 10, 2)   AS avg_read_ms,

       f.physical_writes,

       ROUND(f.average_write_time * 10, 2)  AS avg_write_ms,

       TO_CHAR(f.begin_time,'HH24:MI:SS')   AS from_t,

       TO_CHAR(f.end_time,'HH24:MI:SS')     AS to_t

FROM   gv$filemetric f

JOIN   dba_data_files d ON d.file_id = f.file_id

WHERE  f.physical_reads > 0

ORDER  BY f.physical_reads DESC

FETCH FIRST 25 ROWS ONLY;

 

 

-- RT8.2  I/O wait profile by event, last 15 minutes (live ASH)

SELECT event, COUNT(*) AS samples,

       COUNT(DISTINCT inst_id || ':' || session_id || ',' || session_serial#) AS sessions,

       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100,1) AS pct

FROM   gv$active_session_history

WHERE  sample_time > SYSDATE - 15/1440

AND    wait_class = 'User I/O'

GROUP  BY event

ORDER  BY samples DESC;

 

 

================================================================================

 RT9 - TEMP, PGA, WORKAREAS

================================================================================

 

-- RT9.1  TEMP consumers (real block size, not assumed 8 KB)

SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.sql_id AS current_sql,

       u.sql_id AS temp_sql_id, u.tablespace, u.segtype,

       ROUND(u.blocks * t.block_size / 1048576) AS temp_mb

FROM   gv$tempseg_usage u

JOIN   gv$session s

       ON s.inst_id = u.inst_id AND s.saddr = u.session_addr

JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace

ORDER  BY u.blocks DESC

FETCH FIRST 20 ROWS ONLY;

 

 

-- RT9.2  TEMP tablespace headroom

SELECT tablespace_name,

       ROUND(tablespace_size/1048576)  AS size_mb,

       ROUND(allocated_space/1048576)  AS allocated_mb,

       ROUND(free_space/1048576)       AS free_mb

FROM   dba_temp_free_space;

 

 

-- RT9.3  Active sort/hash workareas (spills in flight)

-- Read : number_passes > 0 or large tempseg_mb = workarea spilling to TEMP.

SELECT inst_id, sid, sql_id, operation_type, policy,

       ROUND(actual_mem_used/1048576) AS mem_mb,

       ROUND(max_mem_used/1048576)    AS max_mem_mb,

       number_passes,

       ROUND(tempseg_size/1048576)    AS tempseg_mb

FROM   gv$sql_workarea_active

ORDER  BY tempseg_size DESC NULLS LAST, actual_mem_used DESC;

 

 

-- RT9.4  PGA overview per instance

SELECT inst_id, name, ROUND(value/1048576) AS mb_or_count

FROM   gv$pgastat

WHERE  name IN ('aggregate PGA target parameter','total PGA allocated',

                'total PGA inuse','maximum PGA allocated','over allocation count')

ORDER  BY inst_id, name;

 

 

================================================================================

 RT10 - UNDO AND OPEN TRANSACTIONS

================================================================================

 

-- RT10.1  Open transactions by undo used, with owning session

-- Read : large used_ublk on an INACTIVE session = long uncommitted transaction.

SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.action, s.status,

       s.last_call_et, s.sql_id, s.prev_sql_id,

       t.start_time, t.used_ublk, t.used_urec,

       ROUND(t.used_ublk * TO_NUMBER(p.value) / 1048576) AS undo_mb,

       t.status AS txn_status

FROM   gv$transaction t

JOIN   gv$session s ON s.inst_id = t.inst_id AND s.taddr = t.addr

JOIN   gv$parameter p ON p.inst_id = t.inst_id AND p.name = 'db_block_size'

ORDER  BY t.used_ublk DESC;

 

 

-- RT10.2  Undo extent status

SELECT tablespace_name, status, ROUND(SUM(bytes)/1048576) AS mb

FROM   dba_undo_extents

GROUP  BY tablespace_name, status

ORDER  BY tablespace_name, status;

 

 

================================================================================

 RT11 - DRILL INTO ONE SESSION

================================================================================

 

-- RT11.1  Session identity and OS process

SELECT s.inst_id, s.sid, s.serial#, s.audsid, s.username, s.osuser, s.machine,

       s.program, s.module, s.action, s.client_identifier,

       p.spid AS os_pid, s.logon_time, s.status, s.last_call_et

FROM   gv$session s

JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.inst_id = :inst_id

AND    s.sid     = :sid;

 

 

-- RT11.2  Session wait totals since logon

SELECT event, wait_class, total_waits,

       ROUND(time_waited_micro/1e6,1)                                   AS waited_s,

       ROUND(time_waited_micro/1e3/NULLIF(total_waits,0),2)             AS avg_ms

FROM   gv$session_event

WHERE  inst_id = :inst_id

AND    sid     = :sid

AND    wait_class <> 'Idle'

ORDER  BY time_waited_micro DESC;

 

 

-- RT11.3  Key session statistics since logon

SELECT n.name, st.value

FROM   gv$sesstat st

JOIN   gv$statname n ON n.inst_id = st.inst_id AND n.statistic# = st.statistic#

WHERE  st.inst_id = :inst_id

AND    st.sid     = :sid

AND    n.name IN ('CPU used by this session','session logical reads',

                  'physical reads','physical read IO requests',

                  'parse count (hard)','execute count','user commits',

                  'redo size','session pga memory','session pga memory max')

ORDER  BY n.name;

 

 

-- RT11.4  What this session did in the last 30 minutes (live ASH)

SELECT sql_id, NVL(event,'ON CPU') AS event, COUNT(*) AS samples,

       MIN(sample_time) AS first_seen, MAX(sample_time) AS last_seen

FROM   gv$active_session_history

WHERE  inst_id         = :inst_id

AND    session_id      = :sid

AND    session_serial# = :serial

AND    sample_time > SYSDATE - 30/1440

GROUP  BY sql_id, event

ORDER  BY samples DESC;

 

 

================================================================================

 RT12 - RAC AND RESOURCE LIMITS

================================================================================

 

-- RT12.1  Cluster waits in the last 15 minutes by event and object

SELECT h.inst_id, h.event, o.owner, o.object_name, COUNT(*) AS samples

FROM   gv$active_session_history h

LEFT JOIN dba_objects o ON o.object_id = h.current_obj#

WHERE  h.sample_time > SYSDATE - 15/1440

AND    h.wait_class = 'Cluster'

GROUP  BY h.inst_id, h.event, o.owner, o.object_name

ORDER  BY samples DESC

FETCH FIRST 20 ROWS ONLY;

 

 

-- RT12.2  Sessions / processes vs limits

SELECT inst_id, resource_name, current_utilization, max_utilization,

       initial_allocation, limit_value

FROM   gv$resource_limit

WHERE  resource_name IN ('processes','sessions','transactions','parallel_max_servers')

ORDER  BY inst_id, resource_name;

 

 

================================================================================

 QUICK TRIAGE ORDER

================================================================================

 1. RT1.1 / RT1.3   -> CPU, I/O, locks or RAC?  Where is the load?

 2. RT4.1           -> If anyone is blocked, the blocker is the RCA first.

 3. RT3.1 / RT3.3   -> Which EBS requests are running / stuck pending?

 4. RT2.1 / RT2.3   -> Which SQL and modules dominate the last 15 minutes?

 5. RT6.1 / RT7.2   -> Per-exec cost and actual rows for the top SQL.

 6. RT8.1           -> Is storage latency abnormal right now?

 7. RT9 / RT10      -> TEMP spills, long transactions, undo.

================================================================================

 END OF PACK

================================================================================

REAL-TIME PERFORMANCE QUERY PACK

================================================================================

 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;