Monday, November 16, 2026

Essential Oracle EBS R12.2 Health Check Queries: Context Files & Edition Ports


Managing an Oracle E-Business Suite (EBS) R12.2 environment requires keeping close tabs on your application tier configuration, especially across the Run and Patch editions. Whether you are preparing for an Online Patching (adop) cycle, troubleshooting clone issues, or performing routine DBA health checks, verifying the status of your context files and managed server ports directly from the database can save hours of manual file-system digging.

Below are three essential SQL queries targeting the apps.fnd_oam_context_files table to validate your context file versions and verify port allocations across both editions.

1. Check Context File Status and Versions in the Database

Before running AutoConfig or starting an adop phase, ensure there are no invalid or stale context files registered in the database. This query filters out template/metadata entries and flags any context files where the status is NULL or not marked as VALID.

SQL
SELECT 
    path,
    status,
    serial_number,
    version 
FROM 
    apps.fnd_oam_context_files 
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA') 
    AND (status IS NULL OR status != 'VALID')
ORDER BY 
    path, version;

What to look for:

  • Ideally, this query should return no rows (or only historical/expected entries), confirming that active context files across all nodes are valid and synchronized.

  • If you see duplicate paths with conflicting versions, you may need to clean up fnd_oam_context_files and re-run AutoConfig.

2. Check Managed Server Ports in the Run Edition

In Oracle EBS R12.2, the Run and Patch editions run on distinct port pools. Rather than logging into every application node and grepping through XML files, you can extract the exact WebLogic managed server ports (oacore, forms, oafm, forms-c4ws, and oaea) directly from the XML stored in the database for the Run Edition.

SQL
SELECT 
    EXTRACTVALUE(XMLType(TEXT), '//host[@oa_var="s_hostname"]') AS hostname,
    EXTRACTVALUE(XMLType(TEXT), '//oacore_server_ports') AS oacore_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms_server_ports') AS forms_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oafm_server_ports') AS oafm_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms-c4ws_server_ports') AS forms_c4ws_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oaea_server_ports') AS oaea_ports
FROM 
    apps.fnd_oam_context_files
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA')
    AND (status IS NULL OR status != 'H')
    AND EXTRACTVALUE(XMLType(TEXT), '//file_edition_type') = 'run'
    AND CTX_TYPE = 'A';

Query Breakdown:

  • CTX_TYPE = 'A' filters specifically for Application Tier context files.

  • status != 'H' excludes historical context file records.

  • file_edition_type = 'run' isolates the currently active Run file system.

3. Check Managed Server Ports in the Patch Edition

To verify that your Patch Edition is properly configured on its alternate port pool—and ensure there are no port conflicts with the Run Edition—run the exact same XML extraction targeting the Patch Edition:

SQL
SELECT 
    EXTRACTVALUE(XMLType(TEXT), '//host[@oa_var="s_hostname"]') AS hostname,
    EXTRACTVALUE(XMLType(TEXT), '//oacore_server_ports') AS oacore_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms_server_ports') AS forms_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oafm_server_ports') AS oafm_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms-c4ws_server_ports') AS forms_c4ws_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oaea_server_ports') AS oaea_ports
FROM 
    apps.fnd_oam_context_files
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA')
    AND (status IS NULL OR status != 'H')
    AND EXTRACTVALUE(XMLType(TEXT), '//file_edition_type') = 'patch'
    AND CTX_TYPE = 'A';

Pro Tip: Compare the output of Queries 2 and 3 side-by-side after a fresh fs_clone or Rapid Clone to guarantee that the port pools shifted cleanly between your fs1 and fs2 file systems.

Suggested Blogspot Labels: Oracle EBS, EBS R12.2, Oracle Apps DBA, SQL Scripts, ADOP, AutoConfig

Monday, November 9, 2026

Servicece

 Act as an experienced Oracle EBS Operations Lead and Service Transition Manager. Create a centralized Excel workbook to track operational knowledge, handover completion, support ownership and pending actions for our EBS landscape.

Systems in scope: Oracle EBS R12.2 and Oracle Database 19c, ECC, Viziya, Ariba, AventX, UI Apps, WebCenter Imaging, webMethods, OEM and ServiceNow.

Objective: For every system, we must know how to connect, operate, monitor, troubleshoot, escalate, perform monthly outages, patch and clone it. We must also know what handover we received, what our team can perform independently and what is still pending.

1. System overview and end-to-end flow

Capture each system’s business purpose, environments, versions, servers, URLs, architecture reference, EBS integration, source and destination systems, data flow, schedules and dependencies. Provide fields for an end-to-end flow diagram or document link.

2. Detailed operational process register

Create separate entries for each applicable process under every system:

  • How to connect: URLs, SSH or console access, required roles, access request steps and access approver.

  • Daily operations: processing steps, jobs, interfaces, schedules and success checks.

  • Monitoring: health checks, log locations, alerts and monitoring ownership.

  • Troubleshooting: common failures, diagnosis, recovery, restart and safe reprocessing.

  • Escalation: when to escalate, whom to contact, assignment group, vendor route and evidence required.

  • Monthly outage: preparation, approvals, dependency-based shutdown/startup, interface pause/resume, validation, rollback and business sign-off.

  • Patching: prerequisites, patch source, execution procedure, ownership, validation and rollback.

  • Cloning/refresh: source/target, prerequisites, execution, post-clone configuration, integration isolation/re-enablement and validation.

  • Backup and recovery: scope, schedule, owner, restore procedure and recovery verification.

  • Certificates, service accounts and other recurring maintenance, where applicable.

For each process, record prerequisites, step-by-step instructions or a runbook link, expected results, executor, approver, escalation contact and supporting evidence.

3. Handover and practical readiness

Track each process individually using:

Process ID, system, environment, process name, outgoing SME, receiving owner, KT status, KT date, runbook received, recording link, access availability, demonstration completed, performed by our team, verification result, verifier/date, acceptance owner/date, pending information, next action, action owner, target date and blocker.

Keep document receipt, KT attendance, practical verification and final acceptance as separate fields. Do not mark handover complete merely because a document was received.

4. Onsite/offshore contacts and important email DLs

For each system, capture:

  • Onsite primary and backup contacts.

  • Offshore primary and backup contacts.

  • Technical SME, application owner, business owner and vendor contact.

  • Name, role, email, approved phone/contact method, location, time zone and support hours.

  • After-hours/on-call contact and roster link.

  • Support DL, business DL, outage/change notification DL, incident escalation DL and vendor support email.

  • Escalation levels and triggers.

  • Contact details last verified date and verified by.

Identify who executes, who supports, who approves and which DL must be notified for outages, patching, cloning and incidents.

5. Monthly outage execution history

Provide a reusable checklist with a unique Outage ID and change reference for each maintenance window. Track system, activity, predecessor/dependency, owner, planned and actual timestamps, time zone, status, evidence, issue/action reference, rollback decision and sign-off.

Retain previous months’ records. Treat the shutdown/startup order as environment-specific and requiring confirmation.

6. Pending actions

Maintain one consolidated action register linked to System and Process IDs. Include gap, priority, owner, opened date, due date, next follow-up, status, blocker, ServiceNow reference, resolution and closure date.

7. Dashboard and Excel usability

Provide a dashboard showing system-wise process totals, KT received, access pending, practical verification pending, accepted handovers, blocked items, overdue actions and outage preparation gaps.

Use structured Excel tables, filters, dropdowns, frozen headers, readable formatting, automatic formulas and conditional highlighting. Clearly distinguish editable fields from calculated fields. Use stable IDs to link records across sheets and explain how to add new records without breaking formulas.

Organize the workbook into practical sheets such as Dashboard, Systems & Flows, Process & Handover Register, Contacts & DLs, Monthly Outages, Pending Actions and Instructions. Avoid separate copies of the same tracker for every tool.

Prefill relevant process checklist items for all listed systems. Leave unknown configuration, contact and handover details as “To confirm.” Do not invent completed activities, commands, contact details or approved procedures. Store credential vault references only, never passwords.

Deliver a ready-to-use .xlsx workbook with brief instructions for initial population and weekly review.

Tuesday, November 3, 2026

SQL Capture

 

Query 0: CAPTURE_INFO (environment header)

sql
/* Q0 CAPTURE_INFO - read-only */
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM') AS capture_ts,
       d.name                          AS db_name,
       d.db_unique_name,
       TO_CHAR(d.dbid)                 AS dbid,
       d.cdb,
       SYS_CONTEXT('USERENV','CON_NAME')             AS connected_container,
       SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') AS session_edition,
       d.platform_name, d.database_role, d.open_mode,
       TO_CHAR(i.inst_id)              AS inst_id,
       i.instance_name, i.host_name, i.version_full,
       TO_CHAR(i.startup_time,'YYYY-MM-DD HH24:MI:SS') AS instance_startup,
       (SELECT value FROM nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET')         AS db_charset,
       (SELECT x.value FROM gv$parameter x
         WHERE x.inst_id = i.inst_id AND x.name = 'optimizer_features_enable') AS optimizer_features_enable,
       (SELECT x.value FROM gv$parameter x
         WHERE x.inst_id = i.inst_id AND x.name = 'cursor_sharing')            AS cursor_sharing
FROM   gv$instance i
CROSS JOIN v$database d
ORDER  BY i.inst_id;

Query 1: SQL_INVENTORY (one row per child cursor per RAC instance)

Every identifier is wrapped in TO_CHAR, so it is exported as text. Metric columns stay numeric so you can calculate with them, and each metric column name ends with its unit.

sql
/* Q1 SQL_INVENTORY - read-only */
WITH p AS (SELECT 32000 AS chunk_chars FROM dual)
SELECT /* EBS_MIG_SQL_INVENTORY */
       TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM')  AS capture_ts,
       d.name                                         AS db_name,
       d.db_unique_name,
       TO_CHAR(d.dbid)                                AS dbid,
       NVL(c.name, SYS_CONTEXT('USERENV','CON_NAME')) AS pdb_name,
       TO_CHAR(s.con_id)                              AS con_id,
       TO_CHAR(s.inst_id)                             AS inst_id,
       i.instance_name,
       i.host_name,
       /* identifiers - all TEXT */
       s.sql_id,
       TO_CHAR(s.hash_value)                          AS hash_value,
       TO_CHAR(s.old_hash_value)                      AS old_hash_value,
       TO_CHAR(s.plan_hash_value)                     AS plan_hash_value,
       TO_CHAR(s.full_plan_hash_value)                AS full_plan_hash_value,
       TO_CHAR(s.child_number)                        AS child_number,
       TO_CHAR(s.exact_matching_signature)            AS exact_matching_signature,
       TO_CHAR(s.force_matching_signature)            AS force_matching_signature,
       TO_CHAR(s.optimizer_env_hash_value)            AS optimizer_env_hash_value,
       /* context */
       s.parsing_schema_name, s.module, s.action, s.service,
       s.sql_profile, s.sql_plan_baseline, s.sql_patch,
       s.is_bind_sensitive, s.is_bind_aware, s.is_shareable, s.object_status,
       s.optimizer_mode, s.optimizer_cost,
       s.first_load_time, s.last_load_time,
       TO_CHAR(s.last_active_time,'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
       /* cumulative counters since this child cursor was loaded */
       s.executions             AS executions_cnt,
       s.px_servers_executions  AS px_servers_executions_cnt,
       s.parse_calls            AS parse_calls_cnt,
       s.loads                  AS loads_cnt,
       s.invalidations          AS invalidations_cnt,
       s.fetches                AS fetches_cnt,
       s.rows_processed         AS rows_processed_cnt,
       s.buffer_gets            AS buffer_gets_blocks,
       s.disk_reads             AS disk_reads_blocks,
       s.cpu_time               AS cpu_time_us,
       s.elapsed_time           AS elapsed_time_us,
       s.user_io_wait_time      AS user_io_wait_us,
       s.cluster_wait_time      AS cluster_wait_us,
       s.concurrency_wait_time  AS concurrency_wait_us,
       s.application_wait_time  AS application_wait_us,
       s.sharable_mem           AS sharable_mem_bytes,
       /* derived */
       ROUND(s.elapsed_time / 1e6, 3)                          AS elapsed_time_sec,
       ROUND(s.cpu_time     / 1e6, 3)                          AS cpu_time_sec,
       ROUND(s.elapsed_time / NULLIF(s.executions,0) / 1e3, 3) AS elapsed_ms_per_exec,
       ROUND(s.cpu_time     / NULLIF(s.executions,0) / 1e3, 3) AS cpu_ms_per_exec,
       ROUND(s.buffer_gets  / NULLIF(s.executions,0), 1)       AS buffer_gets_per_exec,
       ROUND(s.disk_reads   / NULLIF(s.executions,0), 1)       AS disk_reads_per_exec,
       ROUND(s.rows_processed / NULLIF(s.executions,0), 1)     AS rows_per_exec,
       /* pointer to SQL_TEXT sheet */
       DBMS_LOB.GETLENGTH(s.sql_fulltext)                      AS sql_text_length_chars,
       GREATEST(1, CEIL(NVL(DBMS_LOB.GETLENGTH(s.sql_fulltext),0) / p.chunk_chars)) AS sql_text_chunks,
       REPLACE(REPLACE(SUBSTR(s.sql_text,1,200),CHR(10),' '),CHR(13),' ')         AS sql_text_preview
FROM   gv$sql s
JOIN   gv$instance i       ON i.inst_id = s.inst_id
LEFT JOIN gv$containers c  ON c.inst_id = s.inst_id AND c.con_id = s.con_id
CROSS JOIN v$database d
CROSS JOIN p
ORDER  BY s.elapsed_time DESC;

Query 2: SQL_TEXT (full text in ordered chunks, no truncation)

sql
/* Q2 SQL_TEXT - read-only; one row per <=32,000-char chunk, one SQL_ID stored once */
WITH p AS (SELECT 32000 AS chunk_chars FROM dual),
     n AS (SELECT LEVEL AS chunk_no FROM dual CONNECT BY LEVEL <= 2048),
     t AS (SELECT con_id, sql_id, sql_fulltext,
                  NVL(DBMS_LOB.GETLENGTH(sql_fulltext),0) AS total_len,
                  CASE WHEN DBMS_LOB.INSTR(sql_fulltext, CHR(13)) > 0
                       THEN 'Y' ELSE 'N' END              AS contains_cr
           FROM (SELECT s.con_id, s.sql_id, s.sql_fulltext,
                        ROW_NUMBER() OVER (PARTITION BY s.con_id, s.sql_id
                                           ORDER BY s.inst_id, s.child_number) AS rn
                 FROM gv$sql s)
           WHERE rn = 1)
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM') AS capture_ts,
       TO_CHAR(t.con_id)                                   AS con_id,
       t.sql_id,
       n.chunk_no,
       GREATEST(1, CEIL(t.total_len / p.chunk_chars))      AS chunk_count,
       t.total_len                                         AS total_length_chars,
       GREATEST(0, LEAST(p.chunk_chars,
                t.total_len - (n.chunk_no-1)*p.chunk_chars)) AS chunk_length_chars,
       t.contains_cr,
       SUBSTR(t.sql_fulltext, (n.chunk_no-1)*p.chunk_chars + 1, p.chunk_chars) AS chunk_text
FROM   t
CROSS JOIN p
JOIN   n ON n.chunk_no <= GREATEST(1, CEIL(t.total_len / p.chunk_chars))
ORDER  BY t.con_id, t.sql_id, n.chunk_no;

The 32,000-character chunk size stays safely under Excel’s hard limit of 32,767 characters per cell. To rebuild a statement, concatenate its chunks in CHUNK_NO order. CHUNK_LENGTH_CHARS lets you prove that nothing was cut off.

Friday, October 23, 2026

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

 

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

Thursday, October 1, 2026

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;