Thursday, September 24, 2026

Step By Step

-- =====================================================================

-- GL ARCHIVE AND PURGE - READ-ONLY DIAGNOSTIC SQL

-- SQL_ID 6vhqjj8bwpga6 | EBS 12.2 / DB 19c | DEV03

-- =====================================================================

SET LINESIZE 250 PAGESIZE 200 LONG 1000000 LONGCHUNKSIZE 1000000

SET SERVEROUTPUT ON SIZE UNLIMITED

COL prog FORMAT A40

COL argument_text FORMAT A60

COL object_name FORMAT A30

COL event FORMAT A40

-- ---------------------------------------------------------------------

-- A1. Running request details

-- ---------------------------------------------------------------------

SELECT r.request_id,

p.user_concurrent_program_name prog,

r.phase_code,

r.status_code,

r.actual_start_date,

ROUND((SYSDATE - r.actual_start_date) * 1440) elapsed_min,

r.argument_text,

r.oracle_process_id spid,

r.os_process_id,

r.logfile_node_name,

r.logfile_name

FROM apps.fnd_concurrent_requests r

JOIN apps.fnd_concurrent_programs_vl p

ON p.concurrent_program_id = r.concurrent_program_id

AND p.application_id = r.program_application_id

WHERE r.phase_code = 'R'

AND UPPER(p.user_concurrent_program_name) LIKE '%ARCHIVE%PURGE%';

-- ---------------------------------------------------------------------

-- B1. Database session for the request (via SPID from A1)

-- ---------------------------------------------------------------------

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

s.sql_child_number,

s.sql_exec_id,

s.sql_exec_start,

s.event,

s.state,

s.wait_time_micro,

s.blocking_instance,

s.blocking_session,

s.final_blocking_session,

s.row_wait_obj#,

s.module,

s.action

FROM gv$session s

JOIN gv$process p

ON p.addr = s.paddr

AND p.inst_id = s.inst_id

WHERE p.spid = '&spid';

-- ---------------------------------------------------------------------

-- B2. Object the session is currently waiting on

-- ---------------------------------------------------------------------

SELECT s.inst_id,

s.sid,

s.event,

s.p1 file#,

s.p2 block#,

o.owner,

o.object_name,

o.object_type

FROM gv$session s

LEFT JOIN dba_objects o

ON o.object_id = s.row_wait_obj#

WHERE s.inst_id = &inst

AND s.sid = &sid;

-- ---------------------------------------------------------------------

-- C1. Transaction progress (run twice, 5 minutes apart)

-- ---------------------------------------------------------------------

SELECT t.inst_id,

t.start_time,

t.status,

t.used_ublk,

t.used_urec,

t.log_io,

t.phy_io,

DECODE(BITAND(t.flag, 128), 128, 'ROLLING BACK', 'FORWARD') direction

FROM gv$transaction t

JOIN gv$session s

ON s.taddr = t.addr

AND s.inst_id = t.inst_id

WHERE s.inst_id = &inst

AND s.sid = &sid;

-- ---------------------------------------------------------------------

-- C2. Rollback progress (only if C1 shows ROLLING BACK or session gone)

-- ---------------------------------------------------------------------

SELECT inst_id,

usn,

state,

undoblockstotal,

undoblocksdone,

ROUND(undoblocksdone / NULLIF(undoblockstotal, 0) * 100, 2) pct_done

FROM gv$fast_start_transactions;

-- ---------------------------------------------------------------------

-- D1. Session statistics delta over 5 minutes

-- ---------------------------------------------------------------------

DECLARE

TYPE t IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

a t;

b t;

k VARCHAR2(64);

PROCEDURE snap (x IN OUT t) IS

BEGIN

FOR r IN (SELECT n.name, st.value

FROM gv$sesstat st

JOIN v$statname n ON n.statistic# = st.statistic#

WHERE st.inst_id = &inst

AND st.sid = &sid

AND n.name IN ('session logical reads',

'physical reads',

'db block changes',

'redo size',

'undo change vector size',

'CPU used by this session'))

LOOP

x(r.name) := r.value;

END LOOP;

END;

BEGIN

snap(a);

DBMS_SESSION.SLEEP(300);

snap(b);

k := a.FIRST;

WHILE k IS NOT NULL LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(k, 30) || LPAD(b(k) - a(k), 18));

k := a.NEXT(k);

END LOOP;

END;

-- ---------------------------------------------------------------------

-- D2. Cursor-level execution statistics (cumulative)

-- ---------------------------------------------------------------------

SELECT inst_id,

child_number,

plan_hash_value,

executions,

rows_processed,

buffer_gets,

disk_reads,

physical_read_bytes,

ROUND(elapsed_time / 1e6) ela_s,

ROUND(cpu_time / 1e6) cpu_s,

ROUND(user_io_wait_time / 1e6) io_s,

last_active_time

FROM gv$sql

WHERE sql_id = '6vhqjj8bwpga6';

-- ---------------------------------------------------------------------

-- D3. Captured bind values (all child cursors)

-- ---------------------------------------------------------------------

SELECT inst_id,

child_number,

name,

position,

datatype_string,

value_string,

last_captured

FROM gv$sql_bind_capture

WHERE sql_id = '6vhqjj8bwpga6'

ORDER BY inst_id, child_number, position;

-- ---------------------------------------------------------------------

-- E1. ASH: time by plan line and object (Diagnostics Pack required)

-- ---------------------------------------------------------------------

SELECT a.sql_exec_id,

a.sql_plan_line_id,

a.sql_plan_operation,

o.object_name,

o.object_type,

a.event,

COUNT(*) samples

FROM gv$active_session_history a

LEFT JOIN dba_objects o

ON o.object_id = a.current_obj#

WHERE a.session_id = &sid

AND a.session_serial# = &serial

AND a.sample_time > SYSTIMESTAMP - INTERVAL '60' MINUTE

GROUP BY a.sql_exec_id, a.sql_plan_line_id, a.sql_plan_operation,

o.object_name, o.object_type, a.event

ORDER BY samples DESC;

-- ---------------------------------------------------------------------

-- E2. ASH: executions of this SQL over the request lifetime

-- ---------------------------------------------------------------------

SELECT a.sql_exec_id,

MIN(a.sql_exec_start) exec_start,

MIN(a.sample_time) first_seen,

MAX(a.sample_time) last_seen,

COUNT(*) samples

FROM dba_hist_active_sess_history a

WHERE a.sql_id = '6vhqjj8bwpga6'

AND a.sample_time > TO_DATE('23-SEP-2026 06:00', 'DD-MON-YYYY HH24:MI')

GROUP BY a.sql_exec_id

ORDER BY exec_start;

-- ---------------------------------------------------------------------

-- E3. Segment statistics (license-free; run twice, 5 minutes apart)

-- ---------------------------------------------------------------------

SELECT owner,

object_name,

statistic_name,

value

FROM gv$segment_statistics

WHERE ( (owner = 'GL' AND object_name LIKE 'GL_BALANCES%')

OR object_name = 'XXGL_BALANCES_IND1')

AND statistic_name IN ('physical reads', 'logical reads', 'db block changes')

ORDER BY object_name, statistic_name;

-- ---------------------------------------------------------------------

-- F1. Current execution plan with peeked binds

-- ---------------------------------------------------------------------

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6vhqjj8bwpga6', NULL, 'TYPICAL +PEEKED_BINDS'));

-- ---------------------------------------------------------------------

-- F2. Historical plans and runtimes (AWR)

-- ---------------------------------------------------------------------

SELECT s.snap_id,

sn.begin_interval_time,

s.plan_hash_value,

s.executions_delta,

s.rows_processed_delta,

ROUND(s.elapsed_time_delta / 1e6) ela_s,

s.buffer_gets_delta,

s.disk_reads_delta

FROM dba_hist_sqlstat s

JOIN dba_hist_snapshot sn

ON sn.snap_id = s.snap_id

AND sn.instance_number = s.instance_number

AND sn.dbid = s.dbid

WHERE s.sql_id = '6vhqjj8bwpga6'

ORDER BY s.snap_id;

-- ---------------------------------------------------------------------

-- G1. SQL Monitor: estimated vs actual rows per line (Tuning Pack required)

-- ---------------------------------------------------------------------

SELECT sql_exec_id,

plan_line_id,

plan_operation,

plan_options,

plan_object_name,

plan_cardinality e_rows,

output_rows a_rows,

starts,

physical_read_requests,

physical_read_bytes

FROM gv$sql_plan_monitor

WHERE sql_id = '6vhqjj8bwpga6'

AND status = 'EXECUTING'

ORDER BY plan_line_id;

-- ---------------------------------------------------------------------

-- G2. SQL Monitor text report (Tuning Pack required)

-- ---------------------------------------------------------------------

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(

sql_id => '6vhqjj8bwpga6',

type => 'TEXT',

report_level => 'ALL')

FROM dual;

-- ---------------------------------------------------------------------

-- H1. Blocking sessions

-- ---------------------------------------------------------------------

SELECT inst_id,

sid,

serial#,

blocking_instance,

blocking_session,

event,

seconds_in_wait,

sql_id

FROM gv$session

WHERE blocking_session IS NOT NULL;

-- ---------------------------------------------------------------------

-- H2. Locks held or requested by the purge session

-- ---------------------------------------------------------------------

SELECT l.inst_id,

l.sid,

l.type,

l.id1,

l.id2,

l.lmode,

l.request,

l.block,

o.object_name

FROM gv$lock l

LEFT JOIN dba_objects o

ON o.object_id = l.id1

WHERE l.inst_id = &inst

AND l.sid = &sid;

-- ---------------------------------------------------------------------

-- H3. Storage latency, CPU, and redo rate (last 60 seconds)

-- ---------------------------------------------------------------------

SELECT inst_id,

metric_name,

ROUND(value, 2) val,

metric_unit

FROM gv$sysmetric

WHERE group_id = 2

AND metric_name IN ('Average Synchronous Single-Block Read Latency',

'Host CPU Utilization (%)',

'Physical Reads Per Sec',

'Redo Generated Per Sec');

-- ---------------------------------------------------------------------

-- H4. Per-datafile read latency (last interval)

-- ---------------------------------------------------------------------

SELECT f.file_name,

m.physical_reads,

ROUND(m.average_read_time * 10, 2) avg_read_ms

FROM v$filemetric m

JOIN dba_data_files f

ON f.file_id = m.file_id

ORDER BY m.average_read_time DESC

FETCH FIRST 20 ROWS ONLY;

-- ---------------------------------------------------------------------

-- H5. Undo pressure (last hour)

-- ---------------------------------------------------------------------

SELECT begin_time,

end_time,

undoblks,

txncount,

maxquerylen,

ssolderrcnt,

nospaceerrcnt

FROM v$undostat

WHERE begin_time > SYSDATE - 1/24

ORDER BY begin_time;

-- ---------------------------------------------------------------------

-- H6. Competing active workload

-- ---------------------------------------------------------------------

SELECT inst_id,

sid,

username,

module,

sql_id,

event,

wait_class

FROM gv$session

WHERE status = 'ACTIVE'

AND type = 'USER'

AND wait_class <> 'Idle'

ORDER BY inst_id, module;

-- ---------------------------------------------------------------------

-- H7. Other running concurrent requests

-- ---------------------------------------------------------------------

SELECT r.request_id,

p.user_concurrent_program_name prog,

r.actual_start_date,

ROUND((SYSDATE - r.actual_start_date) * 1440) elapsed_min

FROM apps.fnd_concurrent_requests r

JOIN apps.fnd_concurrent_programs_vl p

ON p.concurrent_program_id = r.concurrent_program_id

AND p.application_id = r.program_application_id

WHERE r.phase_code = 'R'

ORDER BY r.actual_start_date;

-- ---------------------------------------------------------------------

-- S1. Table statistics

-- ---------------------------------------------------------------------

SELECT owner,

table_name,

num_rows,

blocks,

sample_size,

stattype_locked,

stale_stats,

last_analyzed

FROM dba_tab_statistics

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES';

-- ---------------------------------------------------------------------

-- S2. Index statistics

-- ---------------------------------------------------------------------

SELECT index_name,

blevel,

leaf_blocks,

num_rows,

distinct_keys,

clustering_factor,

sample_size,

stattype_locked,

stale_stats,

last_analyzed

FROM dba_ind_statistics

WHERE table_owner = 'GL'

AND table_name = 'GL_BALANCES'

ORDER BY index_name;

-- ---------------------------------------------------------------------

-- S3. Column statistics and histograms

-- ---------------------------------------------------------------------

SELECT column_name,

num_distinct,

num_nulls,

density,

histogram,

num_buckets,

sample_size,

last_analyzed

FROM dba_tab_col_statistics

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES'

AND column_name IN ('LEDGER_ID', 'PERIOD_NAME', 'ACTUAL_FLAG', 'BUDGET_VERSION_ID');

-- ---------------------------------------------------------------------

-- S4. Histogram endpoints for ACTUAL_FLAG and LEDGER_ID (if present)

-- ---------------------------------------------------------------------

SELECT column_name,

endpoint_number,

endpoint_value,

endpoint_actual_value

FROM dba_tab_histograms

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES'

AND column_name IN ('ACTUAL_FLAG', 'LEDGER_ID')

ORDER BY column_name, endpoint_number;

-- ---------------------------------------------------------------------

-- S5. DML since last analyze

-- ---------------------------------------------------------------------

SELECT table_owner,

table_name,

inserts,

updates,

deletes,

truncated,

timestamp

FROM dba_tab_modifications

WHERE table_owner = 'GL'

AND table_name = 'GL_BALANCES';

-- ---------------------------------------------------------------------

-- S6. Pending statistics

-- ---------------------------------------------------------------------

SELECT *

FROM dba_tab_pending_stats

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES';

-- ---------------------------------------------------------------------

-- S7. Extended statistics (column groups)

-- ---------------------------------------------------------------------

SELECT extension_name,

extension,

creator,

droppable

FROM dba_stat_extensions

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES';

-- ---------------------------------------------------------------------

-- S8. Statistics history (restore points)

-- ---------------------------------------------------------------------

SELECT owner,

table_name,

stats_update_time

FROM dba_tab_stats_history

WHERE owner = 'GL'

AND table_name = 'GL_BALANCES'

ORDER BY stats_update_time DESC;

-- ---------------------------------------------------------------------

-- S9. EBS histogram column registration

-- ---------------------------------------------------------------------

SELECT *

FROM apps.fnd_histogram_cols

WHERE table_name = 'GL_BALANCES';

-- ---------------------------------------------------------------------

-- X1. Full column list of all GL_BALANCES indexes

-- ---------------------------------------------------------------------

SELECT index_owner,

index_name,

column_position,

column_name,

descend

FROM dba_ind_columns

WHERE table_owner = 'GL'

AND table_name = 'GL_BALANCES'

ORDER BY index_name, column_position;

-- ---------------------------------------------------------------------

-- X2. Index size

-- ---------------------------------------------------------------------

SELECT owner,

segment_name,

segment_type,

ROUND(bytes / 1024 / 1024 / 1024, 2) size_gb

FROM dba_segments

WHERE segment_name IN ('GL_BALANCES', 'GL_BALANCES_N1', 'GL_BALANCES_N2',

'GL_BALANCES_N3', 'GL_BALANCES_N4', 'XXGL_BALANCES_IND1')

ORDER BY bytes DESC;

-- ---------------------------------------------------------------------

-- X3. Index usage tracking (19c)

-- ---------------------------------------------------------------------

SELECT owner,

name,

total_access_count,

total_exec_count,

total_rows_returned,

last_used

FROM dba_index_usage

WHERE name IN ('GL_BALANCES_N1', 'GL_BALANCES_N2', 'GL_BALANCES_N3',

'GL_BALANCES_N4', 'XXGL_BALANCES_IND1');

-- =====================================================================

-- CLONE / OFF-HOURS ONLY - these read GL_BALANCES and add I/O load

-- =====================================================================

-- ---------------------------------------------------------------------

-- Z1. Period row count by ACTUAL_FLAG

-- ---------------------------------------------------------------------

SELECT actual_flag,

COUNT(*) cnt

FROM gl.gl_balances

WHERE period_name = '2000-12'

GROUP BY actual_flag;

-- ---------------------------------------------------------------------

-- Z2. Period row count by LEDGER_ID and ACTUAL_FLAG

-- ---------------------------------------------------------------------

SELECT ledger_id,

actual_flag,

COUNT(*) cnt

FROM gl.gl_balances

WHERE period_name = '2000-12'

GROUP BY ledger_id, actual_flag

ORDER BY cnt DESC;

-- ---------------------------------------------------------------------

-- Z3. Exact rows matching the captured purge predicates

-- ---------------------------------------------------------------------

SELECT COUNT(*) qualifying_rows

FROM gl.gl_balances

WHERE ledger_id = 50

AND period_name = '2000-12'

AND actual_flag = 'A';

-- ---------------------------------------------------------------------

-- Z4. Remaining volume per period for ledger 50

-- ---------------------------------------------------------------------

SELECT period_name,

actual_flag,

COUNT(*) cnt

FROM gl.gl_balances

WHERE ledger_id = 50

GROUP BY period_name, actual_flag

ORDER BY period_name, actual_flag;

-- ---------------------------------------------------------------------

-- Z5. Compare N2 vs XXGL_BALANCES_IND1 access path (clone only)

-- ---------------------------------------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(gb GL_BALANCES_N2) */

COUNT(*)

FROM gl.gl_balances gb

WHERE gb.ledger_id = 50

AND gb.period_name = '2000-12'

AND gb.actual_flag = 'A';

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(gb XXGL_BALANCES_IND1) */

COUNT(*)

FROM gl.gl_balances gb

WHERE gb.ledger_id = 50

AND gb.period_name = '2000-12'

AND gb.actual_flag = 'A';

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- =====================================================================

-- END

-- =====================================================================

3. Next: Check the actual SQL performance

Before considering any index change, we need to understand the work already performed by the running DELETE.

Run the following query in DEV03.

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    inst_id,
    sql_id,
    child_number,
    plan_hash_value,
    executions,
    rows_processed,
    buffer_gets,
    disk_reads,
    ROUND(elapsed_time / 1000000, 2)
        AS elapsed_seconds,
    ROUND(cpu_time / 1000000, 2)
        AS cpu_seconds,
    ROUND(user_io_wait_time / 1000000, 2)
        AS io_wait_seconds,
    last_active_time
FROM
    gv$sql
WHERE
    sql_id = '6vhqjj8bwpga6'
ORDER BY
    inst_id,
    child_number;

Run it twice, five minutes apart.

We need to compare the changes in physical reads, buffer gets, elapsed time, and I/O wait time.

The results will help establish whether the statement is accumulating substantial read activity. These are cumulative cursor statistics, so they must be interpreted alongside the current session and transaction information.

4. Check whether the running DELETE is actually modifying rows

SELECT
    SYSDATE AS sample_time,
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    s.event,
    t.used_ublk,
    t.used_urec,
    t.log_io,
    t.phy_io,
    t.cr_get,
    t.cr_change
FROM
    gv$session s
JOIN
    gv$transaction t
ON
    s.inst_id = t.inst_id
AND s.taddr = t.addr
WHERE
    s.sid = 268;

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

4. Run these five diagnostic queries next

All queries below are read-only. Run them in the DEV03 PDB.

Query 1 – Verify the columns of GL_BALANCES_N2

We need to understand why Oracle is using only PERIOD_NAME as its index access predicate.

SET LINESIZE 200
SET PAGESIZE 100

SELECT
    index_name,
    column_position,
    column_name,
    descend
FROM
    dba_ind_columns
WHERE
    table_owner = 'GL'
AND table_name = 'GL_BALANCES'
AND index_name = 'GL_BALANCES_N2'
ORDER BY
    column_position;

This will identify the actual index column order.

Even if the index contains additional columns, Oracle may still need to examine many index entries because of the access predicate shown in your plan.

Query 2 – Identify all indexes on GL_BALANCES

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    i.index_name,
    i.index_type,
    i.status,
    i.blevel,
    i.leaf_blocks,
    i.clustering_factor,
    i.num_rows,
    LISTAGG(
        c.column_name,
        ', '
    ) WITHIN GROUP (
        ORDER BY c.column_position
    ) AS index_columns
FROM
    dba_indexes i
JOIN
    dba_ind_columns c
ON
    i.owner = c.index_owner
AND i.index_name = c.index_name
WHERE
    i.table_owner = 'GL'
AND i.table_name = 'GL_BALANCES'
GROUP BY
    i.index_name,
    i.index_type,
    i.status,
    i.blevel,
    i.leaf_blocks,
    i.clustering_factor,
    i.num_rows
ORDER BY
    i.index_name;

We want to establish whether an existing index includes the relevant columns:

LEDGER_ID
PERIOD_NAME
ACTUAL_FLAG

Do not create or modify an index at this stage.

Query 3 – Check how many records match the purge criteria

This query counts rows currently matching the captured bind values.

SELECT
    COUNT(*) AS matching_rows
FROM
    gl.gl_balances
WHERE
    ledger_id = 50
AND period_name = '2000-12'
AND actual_flag = 'A';

Important consideration

This is a separate query against a table being modified by the running purge. It sees committed data according to Oracle's read-consistency rules, not the uncommitted deletions performed by the purge session.

The count may therefore include rows already deleted but not committed by the running request. It is not a reliable live progress counter.

If the table is very large, this COUNT may itself be expensive. Run it only after reviewing the table size and the available indexes, preferably during a suitable diagnostic window.

Query 4 – Check the SQL's actual I/O activity

This is the most important query for your current execution plan.

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    inst_id,
    sql_id,
    child_number,
    plan_hash_value,
    executions,
    rows_processed,
    buffer_gets,
    disk_reads,
    ROUND(
        elapsed_time / 1000000,
        2
    ) AS elapsed_seconds,
    ROUND(
        cpu_time / 1000000,
        2
    ) AS cpu_seconds,
    ROUND(
        user_io_wait_time / 1000000,
        2
    ) AS io_wait_seconds,
    last_active_time
FROM
    gv$sql
WHERE
    sql_id = '6vhqjj8bwpga6'
ORDER BY
    inst_id,
    child_number;

Run this query twice, five minutes apart.

Compare the increase in:

BUFFER_GETS
DISK_READS
USER_IO_WAIT_TIME
ROWS_PROCESSED

These statistics are cumulative for the cached cursor and may include multiple executions. They should not be treated as an exact measure of the currently running DELETE unless that execution has been isolated.

Query 5 – Check the current transaction's progress

Use the SID and serial number verified for the running request.

SET LINESIZE 250

SELECT
    SYSDATE AS sample_time,
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    s.event,
    s.state,
    t.used_ublk,
    t.used_urec,
    t.log_io,
    t.phy_io,
    t.cr_get,
    t.cr_change
FROM
    gv$session s
JOIN
    gv$transaction t
ON
    s.inst_id = t.inst_id
AND s.taddr = t.addr
WHERE
    s.sid = 268;

Monday, September 21, 2026

Oracle Database Performance Troubleshooting – Complete SQL Diagnostic Toolkit

Oracle Database performance issues can originate from many areas: long-running SQL statements, blocking sessions, database waits, inefficient execution plans, stale object statistics, SQL plan changes, excessive I/O, or application-level workload.

Instead of checking each area separately, an Oracle DBA can use a structured set of SQL queries to quickly move from a database-level overview to session-level and SQL-level diagnostics.

This post provides a practical troubleshooting approach that can be used for Oracle Database and Oracle E-Business Suite environments, including RAC environments where GV$ views are required.

1. Performance Troubleshooting Flow

A practical troubleshooting sequence is:

Capture database and instance information.
Review total database connections.
Identify active and inactive sessions.
Find currently executing SQL.
Check SQL Monitor information.
Identify blocking sessions.
Drill down into the affected SID and instance.
Review session wait events.
Check long-running operations.
Generate SQL Monitor reports.
Retrieve the complete SQL statement.
Review bind variables.
Compare historical SQL performance.
Determine where the SQL spends its time.
Check table and index statistics.
Review execution plans from memory and AWR.
Check SQL Profiles and SQL Plan Baselines.

2. Generate a Timestamped Diagnostic Output File

Before collecting diagnostic information, spool the results into a timestamped file. This makes it easier to preserve evidence for incident analysis and RCA.

set echo off
set trimspool on
set define on

column filename new_value filename
select to_char(sysdate,'yyyymmdd-hh-mi-ss') filename from dual;

column dbname new_value dbname noprint
select name dbname from v$pdbs;

spool &dbname-&filename..txt

3. Database and Instance Information

Always capture the database name, PDB, database role, instance name, host name and Oracle version before starting detailed analysis.

set lines 750 pages 9999

select name CDB_NAME,
       (select name from v$pdbs) PDB_NAME,
       database_role
from v$database;

select INSTANCE_NAME,
       HOST_NAME,
       logins,
       VERSION
from v$instance;

4. Check Total Database Connections

The following query summarizes active and inactive connections by database username.

set lines 750 pages 9999

break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report

col username for a50

select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
       count(*) TOT,
       COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
       COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

This is useful for identifying connection growth, application connection pools, unusually high inactive sessions and overall workload distribution.

5. Session Details

Once a workload issue is suspected, identify the sessions currently connected to the database.

set linesize 750 pages 9999

column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,
       b.sid,
       b.serial#,
       a.spid,
       substr(b.machine,1,30) box,
       to_char(b.logon_time,'dd-mon-yyyy hh24:mi:ss') logon_time,
       substr(b.username,1,30) username,
       substr(b.osuser,1,20) os_user,
       substr(b.program,1,30) program,
       status,
       b.last_call_et AS last_call_et_secs,
       b.sql_id
from gv$session b,
     gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;

Important columns include INST_ID, SID, SERIAL#, operating-system process ID, application program, session status and current SQL ID.

6. Find SQL Currently Executing

column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35

select a.inst_id,
       a.sid,
       a.username,
       b.PARSING_SCHEMA_NAME,
       a.module,
       a.sql_id,
       a.sql_child_number child,
       b.hash_value,
       to_char(a.sql_exec_start,'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,
       (sysdate-sql_exec_start)*24*60*60 SECS,
       b.rows_processed,
       a.status,
       substr(b.sql_text,1,50) sql_text
from gv$session a,
     gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

7. SQL Monitor – Currently Executing SQL

Real-Time SQL Monitoring is extremely useful when investigating resource-intensive SQL statements.

set lines 1000 pages 9999

SELECT *
FROM
(
 SELECT status,
        inst_id,
        sid,
        SESSION_SERIAL# as Serial,
        username,
        sql_id,
        SQL_PLAN_HASH_VALUE,
        program,
        TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
        ROUND(elapsed_time/1000000) AS "Elapsed (s)",
        ROUND(cpu_time/1000000) AS "CPU (s)",
        substr(sql_text,1,30) sql_text
 FROM gv$sql_monitor
 WHERE status='EXECUTING'
 and module not like '%emagent%'
 ORDER BY sql_exec_start desc
);

8. Identify Blocking Sessions

Blocking sessions should be checked early during performance incidents because one blocker can affect multiple application sessions.

set lines 750 pages 9999
col blocking_status for a100

select s1.inst_id,
       s2.inst_id,
       s1.username || '@' || s1.machine ||
       ' ( SID=' || s1.sid || ' ) is blocking ' ||
       s2.username || '@' || s2.machine ||
       ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1,
     gv$session s1,
     gv$lock l2,
     gv$session s2
where s1.sid=l1.sid
and s2.sid=l2.sid
and s1.inst_id=l1.inst_id
and s2.inst_id=l2.inst_id
and l1.BLOCK=1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;

9. Drill Down Using SID and Instance ID

accept sid default '' -
'Please provide the sid: '

accept inst_id default '' -
'Please provide the inst_id: '

In RAC environments, always capture both SID and INST_ID. A SID alone does not uniquely identify a session across all RAC instances.

10. What Is the Session Waiting For?

COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 999999
COLUMN event FORMAT A40

SELECT NVL(s.username,'(oracle)') AS username,
       s.sid,
       s.serial#,
       se.event,
       se.total_waits,
       se.total_timeouts,
       se.time_waited,
       se.average_wait,
       se.max_wait,
       se.time_waited_micro
FROM gv$session_event se,
     gv$session s
WHERE s.sid = se.sid
AND s.sid = &sid
AND s.inst_id = se.inst_id
AND s.inst_id = &inst_id
ORDER BY se.time_waited DESC;

Wait events help determine whether the session is spending time on I/O, locking, concurrency, network activity, commit activity or other database resources.

11. Current Session Wait

col WAIT_CLASS for a10

SELECT sw.inst_id,
       NVL(s.username,'(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM gv$session_wait sw,
     gv$session s
WHERE s.sid = sw.sid
and s.inst_id = sw.inst_id
and s.sid = &sid
and s.inst_id = &inst_id
ORDER BY sw.seconds_in_wait DESC;

12. Check Long-Running Operations

SET VERIFY OFF

SELECT a.sid,
       RPAD(a.opname,30),
       a.sofar,
       a.totalwork,
       a.ELAPSED_SECONDS,
       ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",
       time_remaining,
       RPAD(a.username,10) username,
       a.SQL_HASH_VALUE,
       B.STATUS
FROM GV$SESSION_LONGOPS a,
     gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<>a.totalwork;

This can help monitor operations such as full scans, RMAN operations, index builds and other operations exposed through V$SESSION_LONGOPS.

13. Real-Time SQL Resource Consumption

SELECT *
FROM
(
 SELECT status,
        sql_id,
        sql_exec_id,
        TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
        ROUND(elapsed_time/1000000) AS "Elapsed (s)",
        ROUND(cpu_time/1000000) AS "CPU (s)",
        buffer_gets,
        ROUND(physical_read_bytes/(1024*1024)) AS "Phys reads (MB)",
        ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
 FROM gv$sql_monitor
 WHERE sid=&sid
 and inst_id=&inst_id
 ORDER BY elapsed_time DESC
)
WHERE rownum<=20;

This quickly shows whether the SQL is CPU intensive, performing significant physical reads/writes or generating large numbers of buffer gets.

14. Generate SQL Monitor Report

set pagesize 0
set echo off
set timing off
set linesize 1000
set trimspool on
set trim on
set long 2000000
set longchunksize 2000000

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
       sql_id=>'&sql_id',
       report_level=>'ALL',
       type=>'TEXT')
from dual;

15. Retrieve Full SQL Statement

set lines 1000 pages 9999
set long 20000
col sql_text for a500

select sql_text
from dba_hist_sqltext
where sql_id = '&sql_id';

16. Check Bind Variables

col VALUE_STRING for a50

SELECT NAME,
       POSITION,
       DATATYPE_STRING,
       VALUE_STRING
FROM gv$sql_bind_capture
WHERE sql_id='&sql_id'
and inst_id=&inst_id;

Bind values are particularly useful when SQL performance differs depending on input values or data distribution.

17. SQL Historical Performance

One of the most important troubleshooting techniques is comparing current SQL performance against historical AWR information.

Useful measurements include:

Plan hash value
Executions
Rows per execution
Elapsed time per execution
CPU time per execution
I/O wait time
Cluster wait time
Application wait time
Concurrency wait time
PL/SQL execution time
Java execution time

A change in PLAN_HASH_VALUE combined with a significant increase in elapsed time is an important clue when investigating SQL performance regressions.

18. What Is the SQL ID Waiting On?

select sql_id,
       event,
       time_waited "time_waited(s)",
       case
         when time_waited = 0 then 0
         else round(time_waited*100 / sum(time_waited) Over(),2)
       end "percentage"
from
(
 select sql_id,
        event,
        sum(time_waited) time_waited
 from gv$active_session_history
 where sql_id='&sql_id'
 and inst_id=&inst_id
 group by sql_id,event
)
order by time_waited desc;

This provides a useful wait-event breakdown for the SQL ID using Active Session History.

19. Check Table Statistics

col table_name for a40
col owner for a30

select distinct owner,
       table_name,
       STALE_STATS,
       last_analyzed,
       stattype_locked
from dba_tab_statistics
where (owner,table_name) in
(
 select distinct owner,table_name
 from dba_tables
 where table_name in
 (
  select object_name
  from gv$sql_plan
  where upper(sql_id)=upper('&sql_id')
  and inst_id=&inst_id
  and object_name is not null
 )
);

Pay attention to STALE_STATS, LAST_ANALYZED and locked statistics before deciding whether statistics collection is required.

20. Check Index Statistics

After identifying the objects used by the SQL, review the indexes participating in its execution plan.

Important attributes include:

Index owner
Index name
Table name
Last analyzed date
Sample size
Number of rows
Partitioned status
Global statistics

21. Execution Plan from Cursor Cache

select *
from table(
 dbms_xplan.display_cursor(
   '&sql_id',
   NULL,
   'ALLSTATS LAST'
 )
);

ALLSTATS LAST is particularly useful because it can expose actual execution statistics for the most recent execution when those statistics are available.

22. Execution Plan from AWR

select *
from table(
 dbms_xplan.display_awr(
   '&sql_id',
   NULL,
   null,
   'ALLSTATS LAST'
 )
);

Comparing the current cursor plan with historical AWR plans can help identify execution-plan changes associated with a performance regression.

23. Check SQL Profiles

set lines 1000 pages 9999

col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150

select sql.sql_id,
       sql.child_number as child,
       prof.name,
       prof.category,
       prof.created,
       prof.task_exec_name,
       prof.FORCE_MATCHING,
       prof.status,
       prof.SIGNATURE
from dba_sql_profiles prof,
     gv$sql sql
where sql.sql_id in ('&sql_id')
order by created;

24. Check SQL Plan Baselines

col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30

select sql_handle,
       plan_name,
       origin,
       created,
       last_modified,
       last_verified,
       ENABLED,
       ACCEPTED,
       FIXED,
       REPRODUCED
from dba_sql_plan_baselines
where signature in
(
 select force_matching_signature
 from gv$sql
 where sql_id='&sql_id'
 and inst_id=&inst_id
);

When troubleshooting a plan regression, check whether a SQL Plan Baseline already exists and whether it is enabled, accepted, fixed and reproducible.

25. End the Diagnostic Collection

undef sid
undef sql_id
undef inst_id

spool off;

Recommended DBA Troubleshooting Sequence

Performance Issue
       |
       v
Check DB / Instance
       |
       v
Check Connections
       |
       v
Check Active Sessions
       |
       v
Identify SQL_ID
       |
       +-------------------+
       |                   |
       v                   v
Blocking?              Long Running?
       |                   |
       v                   v
Find Blocker          SQL Monitor
       |                   |
       +---------+---------+
                 |
                 v
           Check Wait Events
                 |
                 v
          Review SQL History
                 |
                 v
         Compare Plan Hashes
                 |
                 v
       Check Object Statistics
                 |
                 v
       Review Execution Plans
                 |
                 v
     SQL Profile / Baseline Check
                 |
                 v
          Identify Root Cause

Important Notes

Run diagnostic queries using an appropriately privileged database account.
Use GV$ views when troubleshooting Oracle RAC.
Always capture INST_ID together with the SID in RAC environments.
Do not kill a session simply because it appears long-running.
Confirm the blocker, wait event and business impact before terminating sessions.
Compare current and historical execution plans before concluding that a plan change caused a regression.
Check statistics before gathering them; do not gather statistics blindly in production.
AWR, ASH, SQL Monitor and some tuning functionality may require the appropriate Oracle licensing.

Conclusion

Oracle performance troubleshooting becomes much easier when the investigation follows a consistent sequence rather than jumping directly to individual SQL statements.

Start with database and session activity, identify the affected SID and SQL ID, analyze waits and SQL Monitor data, compare historical performance and execution plans, and finally check statistics, SQL Profiles and SQL Plan Baselines.

For Oracle E-Business Suite environments, this approach is particularly useful when investigating slow concurrent programs, online application performance issues, blocked transactions, expensive SQL statements and sudden SQL plan regressions.

AppsDBAStuff
Oracle E-Business Suite | Oracle Database | Performance Tuning | Apps DBA

Oracle E-Business Suite Apps DBA – Essential SQL Queries and Administration Scripts

This technical reference consolidates useful Oracle E-Business Suite administration and troubleshooting queries commonly required by Apps DBAs. The scripts cover database sessions, Concurrent Processing, Workflow Mailer, patching, ADOP, profile options, application users, tablespaces, performance troubleshooting and other day-to-day administration activities.

Important: Validate every command in a non-production environment before using it in Production. Queries that modify EBS application tables, terminate operating system processes, alter ADOP metadata or expose credentials should not be executed as routine troubleshooting procedures.

Contents

Blocking Sessions and Locks
Concurrent Processing
Long Running Requests
Scheduled and Pending Requests
Output Post Processor
Workflow Mailer
Profile Options
Installed Products and Patch Levels
EBS Patching and ADOP
Database Sessions
Tablespace and Segment Monitoring
Performance Diagnostics
Users and Responsibilities
Tracing and Diagnostics
Production Safety Notes

1. Blocking Sessions and Locks

Blocking-session analysis is one of the most common database-level troubleshooting activities for an Oracle E-Business Suite DBA.

Find Blocking Sessions

set lines 130
set pages 200

column module format a20
column program format a20
column username format a15

select s.sid,
       s.serial#,
       s.status,
       p.spid,
       s.module,
       s.action,
       s.program
from   v$session s,
       v$process p
where  s.sid in
       (select session_id
          from dba_locks
         where blocking_others = 'Blocking')
and    s.paddr = p.addr;

Do not terminate a database session solely because it appears as a blocker. First identify the application process, transaction, request, user and business impact.

Find Locked Objects

column object_name format a40

SELECT a.object_id,
       a.session_id,
       b.object_name
FROM   v$locked_object a,
       dba_objects b
WHERE  a.object_id = b.object_id
AND    b.owner = 'AP';

2. Concurrent Processing

Concurrent Processing is a core component of Oracle E-Business Suite. The following queries are useful when investigating running requests, pending requests, manager capacity and request execution.

Running Concurrent Requests

SELECT DISTINCT
       c.user_concurrent_program_name,
       ROUND(((SYSDATE-a.actual_start_date)*24*60),2)
          AS process_time_minutes,
       a.request_id,
       a.parent_request_id,
       a.request_date,
       a.actual_start_date,
       a.actual_completion_date,
       d.user_name,
       a.phase_code,
       a.status_code,
       a.argument_text,
       a.priority
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_programs b,
       apps.fnd_concurrent_programs_tl c,
       apps.fnd_user d
WHERE  a.concurrent_program_id = b.concurrent_program_id
AND    b.concurrent_program_id = c.concurrent_program_id
AND    a.requested_by = d.user_id
AND    a.status_code = 'R'
ORDER BY process_time_minutes DESC;

Pending Requests Waiting for Managers

set lines 130

col user_concurrent_queue_name format a39

SELECT b.user_concurrent_queue_name,
       COUNT(*)
FROM   apps.fnd_concurrent_worker_requests a,
       apps.fnd_concurrent_queues_vl b
WHERE  a.phase_code = 'P'
AND    a.hold_flag != 'Y'
AND    a.requested_start_date <= SYSDATE
AND    a.concurrent_queue_id != 1003
AND    a.concurrent_queue_id = b.concurrent_queue_id
GROUP BY b.user_concurrent_queue_name,
         a.status_code;

3. Long Running Concurrent Requests

The following example identifies requests that have been running for more than four hours.

set lines 130

column start_time format a15
column user_concurrent_program_name format a40

SELECT b.request_id,
       a.user_concurrent_program_name,
       b.phase_code,
       (SYSDATE-b.actual_start_date)*24 AS running_hours,
       TO_CHAR(b.request_date,
               'MM/DD/YYYY HH24:MI') AS request_date,
       TO_CHAR(b.actual_start_date,
               'MM/DD/YYYY HH24:MI') AS start_time
FROM   apps.fnd_concurrent_programs_vl a,
       apps.fnd_concurrent_requests b
WHERE  a.concurrent_program_id = b.concurrent_program_id
AND    a.application_id = b.program_application_id
AND    b.status_code = 'R'
AND    b.phase_code = 'R'
AND    ((SYSDATE-b.actual_start_date)*24) > 4;

4. Scheduled and Pending Requests

Count Scheduled Requests

SELECT 'Scheduled requests:' schedt,
       COUNT(*) schedcnt
FROM   fnd_concurrent_requests
WHERE  (requested_start_date > SYSDATE
        OR status_code = 'P')
AND    phase_code = 'P';

Requests on Hold

SELECT 'Requests on hold:' schedt,
       COUNT(*) schedcnt
FROM   fnd_concurrent_requests
WHERE  hold_flag = 'Y'
AND    phase_code = 'P';

5. Output Post Processor

Find OPP Log for a Concurrent Request

SELECT fcpa.concurrent_request_id req_id,
       fcp.node_name,
       fcp.logfile_name
FROM   fnd_conc_pp_actions fcpa,
       fnd_concurrent_processes fcp
WHERE  fcpa.processor_id = fcp.concurrent_process_id
AND    fcpa.action_type = 6
AND    fcpa.concurrent_request_id = :P_REQUEST_ID;

6. Workflow Mailer

Workflow Mailer Configuration

set lines 130
set pages 200

col value format a30

SELECT p.parameter_id,
       p.parameter_name,
       v.parameter_value value
FROM   apps.fnd_svc_comp_param_vals_v v,
       apps.fnd_svc_comp_params_b p,
       apps.fnd_svc_components c
WHERE  c.component_type = 'WF_MAILER'
AND    v.component_id = c.component_id
AND    v.parameter_id = p.parameter_id
AND    p.parameter_name IN
       ('OUTBOUND_SERVER',
        'INBOUND_SERVER',
        'ACCOUNT',
        'FROM',
        'NODENAME',
        'REPLYTO',
        'DISCARD',
        'PROCESS',
        'INBOX')
ORDER BY p.parameter_name;

7. Profile Option Auditing

Profile-option history can be useful when troubleshooting a problem that started after an application configuration change.

SELECT tl.user_profile_option_name "Profile Option",
       val.profile_option_value "Value",
       val.last_update_date "Set On",
       usr.user_name "Set By"
FROM   fnd_profile_options opt,
       fnd_profile_option_values val,
       fnd_profile_options_tl tl,
       fnd_user usr
WHERE  opt.profile_option_id = val.profile_option_id
AND    opt.profile_option_name = tl.profile_option_name
AND    usr.user_id = val.last_updated_by
ORDER BY val.last_update_date DESC;

8. Installed Products and Patch Levels

SELECT a.application_name,
       a.product_code,
       DECODE(b.status,
              'I','Installed',
              'S','Shared',
              'N/A') status,
       b.patch_level
FROM   apps.fnd_application_vl a,
       apps.fnd_product_installations b
WHERE  a.application_id = b.application_id
AND    b.status = 'I'
ORDER BY a.product_code;

AD and TXK Code Levels

SELECT abbreviation,
       codelevel
FROM   ad_trackable_entities
WHERE  abbreviation IN ('ad','txk');

9. EBS 12.2 Online Patching / ADOP

Oracle E-Business Suite Release 12.2 uses the ADOP online patching cycle. A normal patching cycle follows the prepare, apply, finalize, cutover and cleanup phases.

source <EBS_ROOT>/EBSapps.env run

adop phase=prepare

adop phase=apply patches=<PATCH_NUMBER>

adop phase=finalize

adop phase=cutover

source <EBS_ROOT>/EBSapps.env run

adop phase=cleanup

Review Recent ADOP Sessions

column id format 99
column nn format a10
column nt format a6

SELECT adop_session_id id,
       prepare_status,
       apply_status,
       finalize_status,
       cutover_status,
       cleanup_status,
       abort_status,
       status,
       node_name nn,
       node_type nt
FROM   ad_adop_sessions
ORDER BY adop_session_id DESC;

Review Patches Associated with ADOP Sessions

SELECT adop_session_id,
       bug_number,
       patchrun_id,
       status,
       node_name,
       CAST(end_date AS TIMESTAMP) end_date,
       driver_file_name,
       patch_top
FROM   ad_adop_session_patches
ORDER BY end_date DESC;

Do not manually update AD_ADOP_SESSIONS or other ADOP metadata tables as a routine recovery method. Investigate the failed ADOP session and use Oracle-supported recovery procedures appropriate to the failure.

10. Database Session Diagnostics

Find Oracle SID from Operating-System Process ID

SELECT a.sid,
       a.serial#,
       b.spid,
       a.username,
       a.osuser,
       a.status,
       a.module
FROM   v$session a,
       v$process b
WHERE  a.paddr = b.addr
AND    b.spid = '&SPID';

11. Tablespace Monitoring

SELECT t.tablespace,
       t.totalspace AS "Total Space (MB)",
       ROUND(t.totalspace-fs.freespace,2) AS "Used Space (MB)",
       fs.freespace AS "Free Space (MB)",
       ROUND(((t.totalspace-fs.freespace)/t.totalspace)*100,2)
          AS "% Used",
       ROUND((fs.freespace/t.totalspace)*100,2)
          AS "% Free"
FROM
(
  SELECT ROUND(SUM(bytes)/(1024*1024)) totalspace,
         tablespace_name tablespace
  FROM   dba_data_files
  GROUP BY tablespace_name
) t,
(
  SELECT ROUND(SUM(bytes)/(1024*1024)) freespace,
         tablespace_name tablespace
  FROM   dba_free_space
  GROUP BY tablespace_name
) fs
WHERE t.tablespace = fs.tablespace
ORDER BY t.tablespace;

Segment Size

SELECT owner,
       segment_name,
       segment_type,
       tablespace_name,
       bytes/1024/1024 MB
FROM   dba_segments
WHERE  owner = UPPER('&OWNER')
AND    segment_name = UPPER('&SEGMENT_NAME');

12. Performance Diagnostics

Monitor Long Operations

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100,2) "% Complete"
FROM   v$session_longops
WHERE  totalwork > 0
AND    sofar < totalwork;

Some performance scripts based on DBA_HIST_* views access AWR data. Use of AWR and related Diagnostics Pack functionality should be checked against the Oracle licensing applicable to the environment.

13. Users and Responsibilities

List Responsibilities

SELECT
       (SELECT application_short_name
          FROM fnd_application fa
         WHERE fa.application_id = frt.application_id) application,
       frt.responsibility_id,
       frt.responsibility_name
FROM   apps.fnd_responsibility_tl frt
ORDER BY frt.responsibility_name;

Users and Assigned Responsibilities

SELECT DISTINCT
       u.user_id,
       SUBSTR(u.user_name,1,30) user_name,
       SUBSTR(r.responsibility_name,1,60) responsibility,
       SUBSTR(a.application_name,1,50) application
FROM   fnd_user u,
       fnd_user_resp_groups g,
       fnd_application_tl a,
       fnd_responsibility_tl r
WHERE  g.user_id = u.user_id
AND    g.responsibility_application_id = a.application_id
AND    a.application_id = r.application_id
AND    g.responsibility_id = r.responsibility_id
ORDER BY user_name,
         application,
         responsibility;

14. Concurrent Request Trace and Diagnostics

Identify Request and Database Process

SELECT request_id,
       oracle_process_id,
       phase_code,
       status_code,
       actual_start_date,
       actual_completion_date
FROM   apps.fnd_concurrent_requests
WHERE  request_id = '&REQUEST_ID';

The Oracle process ID can then be correlated with V$PROCESS and V$SESSION to identify the database session servicing the request.

15. Production Safety Notes

Avoid indiscriminate process termination.
Commands that terminate every FNDLIBR process using kill -9 can affect multiple concurrent requests and Concurrent Manager processes. Identify the exact process and understand its application impact before taking any termination action.

Avoid direct updates to EBS application tables.
Directly changing phase/status values in FND_CONCURRENT_REQUESTS, ADOP metadata or other EBS-owned tables can leave application metadata inconsistent. Prefer supported application interfaces and documented recovery procedures.

Do not publish or extract database-link passwords.
Credential-recovery SQL and commands containing APPS or other database passwords should not be included in operational documentation or public blog posts.

Recommended practice: Use these scripts primarily for diagnostics and evidence collection. Before executing a command that changes data, terminates a process, modifies patching metadata or changes configuration, confirm the procedure against the documentation appropriate to your exact Oracle E-Business Suite and database release.

Conclusion

A well-organized Apps DBA SQL toolkit can significantly reduce the time required to troubleshoot Oracle E-Business Suite incidents. The most useful approach is to separate read-only diagnostic queries from commands that modify application state, terminate processes or alter configuration.

For day-to-day support, the core areas to monitor are database sessions, Concurrent Processing, Workflow components, application configuration, tablespace utilization, patching status and SQL performance.

Saturday, September 12, 2026

72 Golden Rules Every Oracle DBA and Enterprise Architect Should Live By

Battle-tested operational principles for Oracle E-Business Suite, Core DBA, RAC, ASM, Data Guard, RMAN, performance forensics, incident response, and engineering leadership.

Production systems rarely fail because somebody forgot a command. They fail because a technically valid command was executed at the wrong time, against the wrong target, without sufficient evidence or without a safe rollback path.

These golden rules are built around the most common operational anti-pattern: taking a premature, drastic, or misdirected action before establishing the actual cause. They are deliberately punchy, but each one reflects a serious lesson from running mission-critical Oracle environments.

The governing principle: Evidence first. Hypothesis second. Controlled action third. Validation always.

1. Apps DBA — Oracle E-Business Suite R12/12.2

This section covers Concurrent Managers, online patching, AutoConfig, WebLogic, application-tier services, and cloning.

We don’t bounce the entire EBS application tier until we identify which service, managed server, or node is actually unhealthy! 🎯
We don’t restart Concurrent Managers until we inspect FND_CONCURRENT_REQUESTS, FND_CONCURRENT_QUEUES, FND_CONCURRENT_PROCESSES, and the Internal Manager log! 🔍
We don’t blame the Internal Concurrent Manager for pending requests until incompatibilities, specialization rules, work shifts, target processes, and node assignments are validated! 🧭
We don’t terminate a long-running concurrent request until its database session, SQL ID, wait event, blocking chain, and business criticality are confirmed! 🛑
We don’t delete rows directly from FND_CONCURRENT_REQUESTS when Oracle provides supported purge programs and retention controls! 🧹
We don’t run adop phase=abort until the current session, failed phase, worker logs, AD_ADOP_SESSIONS, and recovery options are understood! 🚧
We don’t execute adop cleanup_mode=full as a magic broom until the failed patching cycle and filesystem synchronization state are documented! 🪄
We don’t apply an EBS patch because its README looks friendly until prerequisites, supersedence, ETCC results, code levels, and interoperability notes are checked! 📚
We don’t use adpatch casually in an online-patching-enabled EBS 12.2 environment when the patch belongs inside an adop cycle! ⚠️
We don’t run AutoConfig everywhere until the correct context file, RUN filesystem, node role, shared filesystem design, and pending configuration changes are verified! 🧩
We don’t edit generated files under $INST_TOP, OHS, WebLogic, or application configuration directories until we know whether AutoConfig will overwrite them! ✍️
We don’t declare an EBS clone successful when the login page opens until Concurrent Managers, Workflow Mailer, OPP, forms, integrations, printers, profiles, database links, and scheduled jobs are validated! 🧪

2. Core DBA and Architecture

These rules address RAC, ASM, storage, RMAN, Data Guard, disaster recovery, and platform architecture.

We don’t restart a RAC database until crsctl stat res -t, srvctl status database, instance health, services, and cluster interconnect symptoms identify the failing layer! 🧠
We don’t relocate a RAC service during an incident until connection pools, transaction affinity, FAN/TAF behavior, and surviving-instance capacity are confirmed! 🔀
We don’t evict a RAC node manually until CSS, voting-disk, interconnect, and OS evidence explains why the cluster is threatening to do it for us! 🗳️
We don’t add ASM disks until failure groups, allocation-unit size, rebalance power, usable capacity, and storage-path redundancy are validated! 💿
We don’t drop an ASM disk until V$ASM_OPERATION, rebalance status, redundancy, and partner-disk health prove the data is safely redistributed! 🧨
We don’t blame ASM for latency until iostat, sar, V$ASM_DISK_IOSTAT, database wait events, multipathing, and storage-array metrics tell the same story! 📊
We don’t increase DB_FILE_MULTIBLOCK_READ_COUNT to cure slow storage until execution plans and actual I/O latency prove that multiblock reads are the problem! 🐢
We don’t activate a Data Guard standby until the failover decision, redo gap, data-loss exposure, application fencing, and no-return point are formally accepted! 🚨
We don’t restart managed recovery until V$ARCHIVE_GAP, V$DATAGUARD_STATUS, transport errors, standby redo logs, and broker state are inspected! 📡
We don’t call a backup successful because RMAN returned exit code zero until logs, backup-piece availability, retention, control-file protection, and restore validation are checked! 🎭
We don’t trust an untested backup when RESTORE VALIDATE, VALIDATE DATABASE, block checks, and a recovery rehearsal have never met it! 🧯
We don’t design DR around an RPO/RTO PowerPoint until bandwidth, redo rate, restore throughput, dependency sequencing, DNS, certificates, and application recovery are measured! ⏱️

3. Performance Tuning and Forensics

Performance engineering begins with workload evidence—not parameter roulette.

We don’t tune CPU when the database is waiting on I/O, locks, commits, network responses, or application think time! 🩺
We don’t tune a SQL statement from elapsed time alone until DB time, CPU time, wait profile, executions, rows processed, and business context are known! 🔬
We don’t add an index until predicates, selectivity, clustering factor, DML overhead, plan alternatives, and existing index coverage are evaluated! 🗂️
We don’t drop an allegedly unused index until DBA_HIST_SQL_PLAN, index-monitoring limitations, reporting cycles, and emergency workloads have been considered! 🪦
We don’t flush the shared pool when one SQL statement misbehaves until child cursors, mutex waits, invalidations, bind behavior, and dependency churn are understood! 🚿
We don’t purge a SQL plan from the cursor cache until we have captured its SQL ID, plan hash value, outline, bind information, and reproducible evidence! 📸
We don’t gather schema-wide statistics during peak hours until stale objects, sampling strategy, histograms, incremental statistics, and plan-change risk are assessed! 🎲
We don’t delete histograms because bind peeking looks suspicious until V$SQL_SHARED_CURSOR, adaptive cursor sharing, column skew, and workload diversity are verified! 📐
We don’t increase SGA_TARGET, PGA_AGGREGATE_TARGET, or HugePages until swapping, paging, NUMA placement, PGA spills, and OS memory headroom are measured! 🧠
We don’t treat db file sequential read as automatically bad until latency, call volume, access path, storage tier, and rows returned per execution are correlated! 🧵
We don’t blame log file sync entirely on storage until commit frequency, log file parallel write, redo allocation, LGWR CPU scheduling, and application commit design are compared! 🧾
We don’t use one AWR snapshot pair to explain an intermittent incident until ASH, baselines, peak intervals, time models, and OS telemetry reconstruct the actual timeline! 🕵️

4. Troubleshooting and Root Cause Analysis

Troubleshooting is the disciplined elimination of possibilities—not a contest to see who can restart something first.

We don’t change a parameter until we can state the hypothesis, expected metric movement, validation window, and rollback command! 🧪
We don’t troubleshoot five layers simultaneously until the failure is isolated across client, load balancer, OHS, WebLogic, database, network, and OS boundaries! 🧅
We don’t blame the database because the application says “database error” until listener logs, JDBC errors, connection-pool state, SQL*Net evidence, and database alert logs agree! 🙃
We don’t blame the network because ping is slow until TCP throughput, packet loss, MTU, retransmissions, routing, firewall inspection, and application-port tests are measured! 🌐
We don’t accept “the server is slow” until CPU utilization, run queue, paging, filesystem latency, network errors, and top processes are timestamped! 🖥️
We don’t kill a blocker until V$SESSION, V$LOCK, DBA_BLOCKERS, DBA_WAITERS, transaction age, object ownership, and rollback cost are reviewed! 🔒
We don’t kill an OS process with kill -9 until graceful database or application termination has failed and process identity is proven beyond PID coincidence! ☠️
We don’t enable event 10046 or broad SQL tracing in production until scope, level, duration, trace-file growth, and performance overhead are controlled! 🔦
We don’t enable system-wide debug logging until targeted component logging has failed to capture the evidence and filesystem capacity can survive the experiment! 🪵
We don’t declare corruption from one ORA-01578 until DBV, RMAN validation, V$DATABASE_BLOCK_CORRUPTION, object mapping, and storage evidence confirm its scope! 🧱
We don’t call an issue intermittent when timestamps, time zones, request IDs, SQL IDs, hostnames, session identifiers, and correlation IDs were never captured! ⌚
We don’t close an incident because the symptom disappeared until the trigger, failure mechanism, corrective action, and recurrence-detection method are documented! 🧾

5. Crisis Management and War Rooms

During a critical incident, uncontrolled technical activity can become a second outage layered on top of the first.

We don’t start a SEV1 bridge without one incident commander, one technical lead, one communications owner, and one timestamped action log! 🎖️
We don’t let ten engineers execute ten ideas until hypotheses are ranked, owners are assigned, and mutually conflicting actions are stopped! 🚦
We don’t restart everything because executives joined the bridge until the failing component and expected recovery mechanism are identified! 🎪
We don’t make a production change during a crisis until the exact command, target, blast radius, success signal, and rollback path are read back! 📣
We don’t combine multiple fixes in one emergency change until each action’s effect can still be isolated and reversed! 🧬
We don’t fail over to DR until the primary is fenced and split-brain, data-loss, DNS, routing, integration, and reconciliation risks are accepted! ⚔️
We don’t promote a standby because replication is “almost caught up” until the business signs off on the exact recoverable SCN or timestamp! 🕰️
We don’t restore a database over the suspected source until forensic evidence, logs, control files, and recovery artifacts are safely preserved! 🧊
We don’t announce recovery when the homepage responds until transactions, batch processing, integrations, authentication, monitoring, and data consistency pass validation! 🟢
We don’t allow stakeholder pressure to redefine technical truth until metrics demonstrate stability across an agreed observation window! 🌡️
We don’t end the bridge until temporary workarounds, monitoring thresholds, ownership, next update, and customer-impact statements are recorded! 📋
We don’t call rollback a failure when rollback was the engineered control that prevented a larger outage! 🪂

6. Engineering Leadership and Operational Culture

Reliable platforms require more than technical skill. They require disciplined automation, meaningful ownership, useful documentation, and a culture that learns from failure.

We don’t automate a broken process until its inputs, ownership, exception paths, and desired outcome are understood! 🤖
We don’t automate destructive commands until dry-run mode, target validation, logging, idempotency, failure handling, and recovery controls exist! 🛡️
We don’t replace human toil with silent cron failures until alerting, exit-code handling, lock files, retention, and operational ownership are built in! ⏰
We don’t call a script production-ready until it handles spaces, nulls, timeouts, concurrent execution, partial failure, credentials, and reruns safely! 🧰
We don’t accept a dashboard full of green boxes until every metric has a source, threshold, timestamp, owner, and actionable response! 🚥
We don’t write an RCA around who clicked the button until we explain why one click could bypass review, safeguards, testing, or rollback! 🫵
We don’t conduct a blameless retrospective without evidence, because “blameless” does not mean “factless”! 🔎
We don’t close a problem record with “human error” until process design, access control, automation gaps, training, and workload conditions are examined! 🧑‍⚖️
We don’t carry a manual operational step forever when its frequency, error rate, recovery cost, and automation value justify eliminating the toil! 🏗️
We don’t postpone technical debt indefinitely until the accumulated outage risk, security exposure, delivery drag, and support cost are visible to decision-makers! 💳
We don’t approve architecture by diagram beauty until failure modes, capacity limits, observability, operability, upgrade paths, and ownership are demonstrated! 🏛️
We don’t call knowledge transfer complete until another engineer can diagnose, operate, recover, and safely change the platform without calling its original builder! 🧑‍🚀

Final Takeaway

Production does not reward confidence without evidence.
It rewards engineers who preserve evidence, control the blast radius, understand dependencies, prepare rollback options, and validate the complete business service—not merely the component they restarted.

The best Oracle DBA is not the person who remembers the most commands. It is the person who knows which command should be executed, why it should be executed, what could go wrong, how to reverse it, and how to prove that the system is genuinely healthy afterward.

Before touching production, ask four questions:

What evidence supports the proposed action?
What is the expected technical outcome?
What is the blast radius if the hypothesis is wrong?
How will we roll back and validate recovery?

If those questions cannot be answered clearly, the next command should probably be a diagnostic command.

Topics: Oracle DBA, Oracle E-Business Suite, Apps DBA, RAC, ASM, Data Guard, RMAN, Performance Tuning, Incident Management, AWR, ASH and Enterprise Architecture

Wednesday, September 9, 2026

in


SELECT
    sql_id,
    executions,
    elapsed_time/1000000 elapsed_sec,
    elapsed_time/1000/NULLIF(executions,0) ms_per_exec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    rows_processed
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj';

And find whether it is executing right now:


SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.sql_id,
    s.module,
    s.action,
    s.program,
    s.machine
FROM v$session s
WHERE s.sql_id = '0bujgc94rg3fj';



What is this SQL waiting on when it becomes slow?
That is where ASH/AWR becomes important.
Run this first:
SELECT
    sql_id,
    plan_hash_value,
    executions,
    elapsed_time/1000000 elapsed_sec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    rows_processed
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj';

Then check whether there are multiple child cursors:
SELECT
    child_number,
    plan_hash_value,
    executions,
    elapsed_time/1000000 elapsed_sec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    loads,
    invalidations,
    parse_calls
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj'
ORDER BY child_number;

More importantly, use ASH:
SELECT
    event,
    wait_class,
    session_state,
    COUNT(*) samples
FROM v$active_session_history
WHERE sql_id = '0bujgc94rg3fj'
GROUP BY
    event,
    wait_class,
    session_state
ORDER BY samples DESC;

If Diagnostic Pack/AWR is available, check historical ASH:
SELECT
    event,
    wait_class,
    session_state,
    COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sql_id = '0bujgc94rg3fj'
GROUP BY
    event,
    wait_class,
    session_state
ORDER BY samples DESC;

Also check SQL performance over time:
SELECT
    sn.begin_interval_time,
    ss.plan_hash_value,
    ss.executions_delta,
    ROUND(ss.elapsed_time_delta/1000000,2) elapsed_sec,
    ROUND(
        ss.elapsed_time_delta /
        NULLIF(ss.executions_delta,0) / 1000,
        2
    ) ms_per_exec,
    ss.buffer_gets_delta,
    ss.disk_reads_delta
FROM dba_hist_sqlstat ss,
     dba_hist_snapshot sn
WHERE ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.sql_id = '0bujgc94rg3fj'
ORDER BY sn.begin_interval_time DESC;



++++++++++++++


SELECT
       NVL(sql_id,'NO_SQL_ID') sql_id,
       NVL(event,'ON CPU') event,
       wait_class,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
GROUP BY
       sql_id,
       event,
       wait_class,
       session_state
ORDER BY samples DESC;

This is probably the most valuable query at this point.
It may reveal something like:
TOP LEVEL
WF_EVENT.LISTEN

        |
        +--> SQL A against WF_EVENT_SUBSCRIPTIONS
        |
        +--> SQL B against WF_EVENTS
        |
        +--> SQL C against AQ queue
        |
        +--> SQL D ...

Then we can identify which one is burning the CPU and logical reads.
Also run this version
This will give us the internal SQL IDs ranked by activity:
SELECT
       sql_id,
       COUNT(*) samples,
       SUM(CASE
             WHEN session_state = 'ON CPU'
             THEN 1
             ELSE 0
           END) cpu_samples,
       SUM(CASE
             WHEN session_state = 'WAITING'
             THEN 1
             ELSE 0
           END) wait_samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
GROUP BY sql_id
ORDER BY samples DESC;

Send me that result.

One more thing I noticed in your AWR screenshot
You have identical BEGIN_INTERVAL_TIME values appearing more than once, for example around 04-Aug.
That could be because the database has multiple instances. Your query currently doesn't display INSTANCE_NUMBER.
If this is RAC, we absolutely need to know which instance is experiencing the problem.
Modify the AWR query to:
SELECT
       sn.begin_interval_time,
       ss.instance_number,
       ss.plan_hash_value,
       ss.executions_delta,

       ROUND(
         ss.elapsed_time_delta / 1000000,
         2
       ) elapsed_sec,

       ROUND(
         ss.elapsed_time_delta /
         NULLIF(ss.executions_delta,0) /
         1000,
         2
       ) ms_per_exec,

       ss.buffer_gets_delta,

       ROUND(
         ss.buffer_gets_delta /
         NULLIF(ss.executions_delta,0)
       ) buffer_gets_per_exec,

       ss.disk_reads_delta

FROM dba_hist_sqlstat ss,
     dba_hist_snapshot sn

WHERE ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.sql_id = '0bujgc94rg3fj'

ORDER BY sn.begin_interval_time DESC,
         ss.instance_number;

++++++++++++++++++


Find the exact execution-plan operation where ASH is accumulating

SELECT
       sql_plan_line_id,
       sql_plan_operation,
       sql_plan_options,
       event,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
AND sql_id = 'b2jckdvl94knx'
GROUP BY
       sql_plan_line_id,
       sql_plan_operation,
       sql_plan_options,
       event,
       session_state
ORDER BY samples DESC;



 Find which Oracle object is generating the reads
Run this one as well:
SELECT
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event,
       COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
       ON obj.object_id = ash.current_obj#
WHERE ash.top_level_sql_id = '0bujgc94rg3fj'
AND ash.sql_id = 'b2jckdvl94knx'
AND ash.current_obj# > 0
GROUP BY
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event
ORDER BY samples DESC;



       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_sec,
       ROUND(cpu_time/1000000,2) cpu_sec,
       buffer_gets,
       disk_reads,
       rows_processed,
       sql_fulltext
FROM v$sql
WHERE sql_id = 'b2jckdvl94knx';

If it is no longer in shared pool:
SELECT
       sql_id,
       DBMS_LOB.SUBSTR(sql_text,4000,1) sql_text
FROM dba_hist_sqltext
WHERE sql_id = 'b2jckdvl94knx';






The next query should therefore combine everything into one result.
SELECT
       sql_id,
       SUM(executions) executions,
       ROUND(SUM(elapsed_time)/1000000,2) elapsed_sec,
       ROUND(SUM(cpu_time)/1000000,2) cpu_sec,
       SUM(buffer_gets) buffer_gets,
       SUM(disk_reads) disk_reads,

       ROUND(
          SUM(elapsed_time) /
          NULLIF(SUM(executions),0) /
          1000,
          2
       ) ms_per_exec,

       ROUND(
          SUM(buffer_gets) /
          NULLIF(SUM(executions),0),
          2
       ) buffer_gets_per_exec,

       ROUND(
          SUM(disk_reads) /
          NULLIF(SUM(executions),0),
          2
       ) disk_reads_per_exec

FROM gv$sql

WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
)

GROUP BY sql_id
ORDER BY elapsed_sec DESC;

That result is much more meaningful.
Also determine why there are multiple rows
Run:
SELECT
       inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_sec,
       ROUND(cpu_time/1000000,2) cpu_sec,
       buffer_gets,
       disk_reads
FROM gv$sql
WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
)
ORDER BY sql_id,
         inst_id,
         child_number;




Now get the SQL text
This is the most important next piece:
SELECT DISTINCT
       sql_id,
       DBMS_LOB.SUBSTR(sql_fulltext,4000,1) sql_text
FROM gv$sql
WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
);




SELECT
       event,
       wait_class,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sql_id = '8nzx90zdhgfgc'
GROUP BY
       event,
       wait_class,
       session_state
ORDER BY samples DESC;

Then:
SELECT
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event,
       COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
       ON obj.object_id = ash.current_obj#
WHERE ash.sql_id = '8nzx90zdhgfgc'
AND ash.current_obj# > 0
GROUP BY
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event
ORDER BY samples DESC;

Thursday, September 24, 2026

Step By Step

analsis

3. Next: Check the actual SQL performance

4. Check whether the running DELETE is actually modifying rows

4. Run these five diagnostic queries next

Query 1 – Verify the columns of GL_BALANCES_N2

Query 2 – Identify all indexes on GL_BALANCES

Query 3 – Check how many records match the purge criteria

Query 4 – Check the SQL's actual I/O activity

Query 5 – Check the current transaction's progress

Monday, September 21, 2026

Oracle Database Performance Troubleshooting – Complete SQL Diagnostic Toolkit

Oracle Database Performance Troubleshooting – Complete SQL Diagnostic Toolkit

1. Performance Troubleshooting Flow

2. Generate a Timestamped Diagnostic Output File

3. Database and Instance Information

4. Check Total Database Connections

5. Session Details

6. Find SQL Currently Executing

7. SQL Monitor – Currently Executing SQL

8. Identify Blocking Sessions

9. Drill Down Using SID and Instance ID

10. What Is the Session Waiting For?

11. Current Session Wait

12. Check Long-Running Operations

13. Real-Time SQL Resource Consumption

14. Generate SQL Monitor Report

15. Retrieve Full SQL Statement

16. Check Bind Variables

17. SQL Historical Performance

18. What Is the SQL ID Waiting On?

19. Check Table Statistics

20. Check Index Statistics

21. Execution Plan from Cursor Cache

22. Execution Plan from AWR

23. Check SQL Profiles

24. Check SQL Plan Baselines

25. End the Diagnostic Collection

Recommended DBA Troubleshooting Sequence

Important Notes

Conclusion

Oracle E-Business Suite Apps DBA – Essential SQL Queries and Administration Scripts

Oracle E-Business Suite Apps DBA – Essential SQL Queries and Administration Scripts

1. Blocking Sessions and Locks

Find Blocking Sessions

Find Locked Objects

2. Concurrent Processing

Running Concurrent Requests

Pending Requests Waiting for Managers

3. Long Running Concurrent Requests

4. Scheduled and Pending Requests

Count Scheduled Requests

Requests on Hold

5. Output Post Processor

Find OPP Log for a Concurrent Request

6. Workflow Mailer

Workflow Mailer Configuration

7. Profile Option Auditing

8. Installed Products and Patch Levels

AD and TXK Code Levels

9. EBS 12.2 Online Patching / ADOP

Review Recent ADOP Sessions

Review Patches Associated with ADOP Sessions

10. Database Session Diagnostics

Find Oracle SID from Operating-System Process ID

11. Tablespace Monitoring

Segment Size

12. Performance Diagnostics

Monitor Long Operations

13. Users and Responsibilities

List Responsibilities

Users and Assigned Responsibilities

14. Concurrent Request Trace and Diagnostics

Identify Request and Database Process

15. Production Safety Notes

Conclusion

Saturday, September 12, 2026

72 Golden Rules Every Oracle DBA and Enterprise Architect Should Live By

72 Golden Rules Every Oracle DBA and Enterprise Architect Should Live By