1. Basic: Find SID / Serial# from SQL_ID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.program,
       s.machine,
       s.osuser,
       s.event,
       s.wait_class,
       s.seconds_in_wait
FROM   gv$session s
WHERE  s.sql_id = '&SQL_ID'
ORDER BY s.inst_id, s.sid;

Interpretation

Output	Meaning
`SID`, `SERIAL#`	Database session details
`INST_ID`	RAC instance number
`SQL_ID`	Currently running SQL
`PREV_SQL_ID`	Previously executed SQL
`EVENT`	Current wait event
`WAIT_CLASS`	CPU/I/O/Application/Cluster etc.
`SECONDS_IN_WAIT`	How long session is waiting

2. Find SID Using SQL_ID or Previous SQL_ID

Sometimes SQL has completed, and session moved to another SQL. Then check PREV_SQL_ID.


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.program,
       s.event,
       s.wait_class,
       s.seconds_in_wait
FROM   gv$session s
WHERE  s.sql_id      = '&SQL_ID'
   OR  s.prev_sql_id = '&SQL_ID'
ORDER BY s.inst_id, s.sid;

Interpretation

Condition	Meaning
Found in `SQL_ID`	SQL is active now
Found in `PREV_SQL_ID`	SQL recently executed
Not found	SQL completed, aged out, or not active

3. Find SQL_ID from SID / Serial#

If business gives only SID/Serial#:


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.program,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.blocking_instance,
       s.blocking_session
FROM   gv$session s
WHERE  s.sid = '&SID'
AND    s.serial# = '&SERIAL';

For RAC, include instance:


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class
FROM   gv$session s
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

4. Find OS Process ID from SQL_ID / SID

This is useful for mapping database session to OS process and EBS concurrent request.


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.sql_id,
       s.event,
       s.wait_class,
       p.spid os_pid,
       p.program os_program
FROM   gv$session s
JOIN   gv$process p
       ON s.inst_id = p.inst_id
      AND s.paddr   = p.addr
WHERE  s.sql_id = '&SQL_ID'
ORDER BY s.inst_id, s.sid;

5. Map SQL_ID / SID to EBS Concurrent Request

This is the most important Apps DBA query.


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       p.spid os_pid,
       r.request_id,
       r.phase_code,
       r.status_code,
       r.actual_start_date,
       ROUND((SYSDATE - r.actual_start_date) * 24 * 60, 2) runtime_mins,
       cp.concurrent_program_name short_name,
       cpt.user_concurrent_program_name,
       u.user_name requested_by,
       r.argument_text
FROM   gv$session s
JOIN   gv$process p
       ON s.inst_id = p.inst_id
      AND s.paddr = p.addr
JOIN   apps.fnd_concurrent_requests r
       ON r.oracle_process_id = p.spid
JOIN   apps.fnd_concurrent_programs cp
       ON r.concurrent_program_id = cp.concurrent_program_id
      AND r.program_application_id = cp.application_id
JOIN   apps.fnd_concurrent_programs_tl cpt
       ON cp.concurrent_program_id = cpt.concurrent_program_id
      AND cp.application_id = cpt.application_id
      AND cpt.language = 'US'
JOIN   apps.fnd_user u
       ON r.requested_by = u.user_id
WHERE  s.sql_id = '&SQL_ID'
   OR  s.prev_sql_id = '&SQL_ID'
ORDER BY runtime_mins DESC;

RCA Mapping


SQL_ID
→ SID / SERIAL#
→ OS PID
→ Request ID
→ Concurrent Program
→ User
→ Parameters

6. Find Active Long-Running Sessions with SQL_ID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.program,
       s.event,
       s.wait_class,
       s.last_call_et seconds_running,
       ROUND(s.last_call_et/60,2) mins_running,
       s.blocking_instance,
       s.blocking_session
FROM   gv$session s
WHERE  s.status = 'ACTIVE'
AND    s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

Interpretation

Column	Meaning
`LAST_CALL_ET`	Seconds since current call started
`SQL_ID`	Current SQL
`PREV_SQL_ID`	Previous SQL
`EVENT`	Current wait
`BLOCKING_SESSION`	Blocking session if any

7. Find Sessions Running Same SQL_ID


SELECT s.inst_id,
       s.sql_id,
       COUNT(*) session_count,
       MIN(s.sid) sample_sid,
       MAX(s.last_call_et) max_seconds_running,
       LISTAGG(s.sid || ',' || s.serial#, ' | ')
         WITHIN GROUP (ORDER BY s.sid) sid_serial_list
FROM   gv$session s
WHERE  s.sql_id = '&SQL_ID'
GROUP BY s.inst_id, s.sql_id
ORDER BY session_count DESC;

Interpretation

Finding	Meaning
One session	Single request/session issue
Many sessions	Parallel query, duplicate requests, or shared SQL
Sessions on many RAC instances	Cluster-wide workload
Sessions on one instance	Node-specific issue

8. Check Current Wait Event for SID + SQL_ID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.wait_class,
       s.state,
       s.wait_time,
       s.seconds_in_wait,
       s.blocking_instance,
       s.blocking_session
FROM   gv$session s
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

Wait Class Meaning

Wait Class	Meaning
`User I/O`	Physical read/index/full scan
`Application`	Blocking/locking
`Cluster`	RAC global cache waits
`Concurrency`	Buffer/latch/mutex contention
`Commit`	`log file sync`
`Configuration`	Resource Manager / PX limits
`ON CPU`	CPU-bound SQL

9. Find SQL Text from SID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       q.child_number,
       q.plan_hash_value,
       DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) sql_text
FROM   gv$session s
JOIN   gv$sql q
       ON s.inst_id = q.inst_id
      AND s.sql_id  = q.sql_id
WHERE  s.sid = '&SID'
AND    s.serial# = '&SERIAL';

10. Find SQL Text from SQL_ID


SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       parsing_schema_name,
       module,
       action,
       DBMS_LOB.SUBSTR(sql_fulltext, 4000, 1) sql_text
FROM   gv$sql
WHERE  sql_id = '&SQL_ID'
ORDER BY inst_id, child_number;

If SQL is not in memory:


SELECT sql_id,
       DBMS_LOB.SUBSTR(sql_text, 4000, 1) sql_text
FROM   dba_hist_sqltext
WHERE  sql_id = '&SQL_ID';

11. Get SQL Performance Stats from SID / SQL_ID


SELECT q.inst_id,
       q.sql_id,
       q.child_number,
       q.plan_hash_value,
       q.executions,
       ROUND(q.elapsed_time/1000000,2) elapsed_sec,
       ROUND(q.cpu_time/1000000,2) cpu_sec,
       q.buffer_gets,
       q.disk_reads,
       q.rows_processed,
       q.fetches,
       q.parse_calls,
       q.loads,
       q.invalidations,
       q.sql_plan_baseline,
       q.sql_profile,
       q.sql_patch
FROM   gv$sql q
WHERE  q.sql_id = '&SQL_ID'
ORDER BY q.elapsed_time DESC;

Interpretation

Metric	Meaning
High `ELAPSED_SEC`	SQL took long
High `CPU_SEC`	CPU-bound
High `BUFFER_GETS`	Logical I/O issue
High `DISK_READS`	Physical I/O
High `ROWS_PROCESSED`	Large volume
`SQL_PLAN_BASELINE`	SPM baseline used
`SQL_PROFILE`	SQL profile used
`SQL_PATCH`	SQL patch used

12. Display Execution Plan from SQL_ID


SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
         sql_id          => '&SQL_ID',
         cursor_child_no => NULL,
         format          => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS +NOTE'
       ));

Check

Plan Finding	Meaning
Full table scan	Data volume/stats/index issue
Nested loop with high rows	Bad join method
Hash join spilling	TEMP/PGA issue
Actual rows much higher than estimated	Stats/cardinality issue
No PX operation	Running serial
SQL baseline note	SPM used

13. Find Plan Line Currently Active for SID

Useful for advanced troubleshooting.


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.sql_child_number,
       s.sql_exec_id,
       s.sql_exec_start,
       s.sql_plan_hash_value,
       s.sql_plan_line_id,
       p.operation,
       p.options,
       p.object_owner,
       p.object_name,
       s.event,
       s.wait_class
FROM   gv$session s
LEFT JOIN gv$sql_plan p
       ON s.inst_id          = p.inst_id
      AND s.sql_id           = p.sql_id
      AND s.sql_child_number = p.child_number
      AND s.sql_plan_line_id = p.id
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

Why useful?

It tells which execution plan step the session is currently waiting on.

Example:

Current Plan Line	Meaning
TABLE ACCESS FULL	Full scan currently happening
HASH JOIN	Join processing
SORT ORDER BY	Sort operation
PX SEND / PX RECEIVE	Parallel query communication
INDEX RANGE SCAN	Index access

14. Check TEMP Usage by SID / SQL_ID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.wait_class,
       u.tablespace,
       ROUND(u.blocks * t.block_size / 1024 / 1024 / 1024, 2) temp_gb
FROM   gv$tempseg_usage u
JOIN   gv$session s
       ON u.inst_id = s.inst_id
      AND u.session_addr = s.saddr
JOIN   dba_tablespaces t
       ON u.tablespace = t.tablespace_name
WHERE  s.sid = '&SID'
AND    s.serial# = '&SERIAL'
ORDER BY temp_gb DESC;

By SQL_ID:


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       u.tablespace,
       ROUND(u.blocks * t.block_size / 1024 / 1024 / 1024, 2) temp_gb,
       s.event,
       s.wait_class
FROM   gv$tempseg_usage u
JOIN   gv$session s
       ON u.inst_id = s.inst_id
      AND u.session_addr = s.saddr
JOIN   dba_tablespaces t
       ON u.tablespace = t.tablespace_name
WHERE  s.sql_id = '&SQL_ID'
ORDER BY temp_gb DESC;

15. Check Blocking for SID / SQL_ID

Check if SID is blocked


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.blocking_instance,
       s.blocking_session
FROM   gv$session s
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

Find blocker details


SELECT b.inst_id,
       b.sid,
       b.serial#,
       b.username,
       b.status,
       b.sql_id,
       b.prev_sql_id,
       b.module,
       b.action,
       b.program,
       b.machine,
       b.osuser,
       b.event,
       b.wait_class
FROM   gv$session b
WHERE  b.inst_id = '&BLOCKING_INSTANCE'
AND    b.sid     = '&BLOCKING_SESSION';

16. Blocking Chain Advanced Query


SELECT LPAD(' ', LEVEL*2) || s.inst_id || ':' || s.sid || ',' || s.serial# AS session_tree,
       s.username,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.blocking_instance,
       s.blocking_session,
       s.module,
       s.action
FROM   gv$session s
START WITH s.blocking_session IS NULL
CONNECT BY PRIOR s.sid = s.blocking_session
       AND PRIOR s.inst_id = s.blocking_instance
ORDER SIBLINGS BY s.seconds_in_wait DESC;

Use carefully. In RAC environments, blocking chains can be complex.

17. Check Parallel Query Details for SID / SQL_ID


SELECT px.inst_id,
       px.qcsid,
       px.qcserial#,
       px.sid,
       px.serial#,
       s.sql_id,
       px.server_group,
       px.server_set,
       px.degree,
       px.req_degree,
       s.event,
       s.wait_class
FROM   gv$px_session px
JOIN   gv$session s
       ON px.inst_id = s.inst_id
      AND px.sid     = s.sid
WHERE  s.sql_id = '&SQL_ID'
ORDER BY px.inst_id, px.qcsid, px.sid;

Interpretation

Finding	Meaning
No rows	SQL is serial
`REQ_DEGREE > DEGREE`	DOP downgraded
Many PX sessions	Parallel query active
PX waits high	Parallel skew/bottleneck

18. Check Parallel Server Availability


SELECT inst_id,
       statistic,
       value
FROM   gv$px_process_sysstat
WHERE  statistic LIKE 'Servers%'
ORDER BY inst_id, statistic;

Key points:

Statistic	Meaning
Servers In Use	Current PX load
Servers Highwater	Peak usage
Servers Started	PX processes started
Servers Shutdown	PX processes shut down

19. Check SQL Monitor by SQL_ID


SELECT inst_id,
       sql_id,
       sql_exec_id,
       sql_exec_start,
       status,
       username,
       module,
       action,
       elapsed_time/1000000 elapsed_sec,
       cpu_time/1000000 cpu_sec,
       buffer_gets,
       disk_reads,
       px_servers_requested,
       px_servers_allocated
FROM   gv$sql_monitor
WHERE  sql_id = '&SQL_ID'
ORDER BY sql_exec_start DESC;

Generate report:


SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id       => '&SQL_ID',
         type         => 'TEXT',
         report_level => 'ALL'
       )
FROM dual;

20. Check Long Operations for SID / SQL_ID


SELECT inst_id,
       sid,
       serial#,
       sql_id,
       opname,
       target,
       sofar,
       totalwork,
       units,
       ROUND(sofar / NULLIF(totalwork,0) * 100,2) pct_done,
       elapsed_seconds,
       time_remaining
FROM   gv$session_longops
WHERE  sid = '&SID'
AND    serial# = '&SERIAL'
AND    totalwork > 0
ORDER BY elapsed_seconds DESC;

By SQL_ID:


SELECT inst_id,
       sid,
       serial#,
       sql_id,
       opname,
       target,
       sofar,
       totalwork,
       units,
       ROUND(sofar / NULLIF(totalwork,0) * 100,2) pct_done,
       elapsed_seconds,
       time_remaining
FROM   gv$session_longops
WHERE  sql_id = '&SQL_ID'
AND    totalwork > 0
ORDER BY elapsed_seconds DESC;

Interpretation

Finding	Meaning
`PCT_DONE` increasing	SQL is progressing
`TIME_REMAINING` high	Long but not stuck
`PCT_DONE` not changing	Possible stuck/wait issue

21. ASH Real-Time by SID / SQL_ID

Last 30 minutes from memory ASH:


SELECT inst_id,
       sample_time,
       session_id,
       session_serial#,
       sql_id,
       prev_sql_id,
       session_state,
       wait_class,
       event,
       blocking_inst_id,
       blocking_session,
       module,
       action
FROM   gv$active_session_history
WHERE  sample_time >= SYSDATE - (30/1440)
AND   (sql_id = '&SQL_ID' OR prev_sql_id = '&SQL_ID')
ORDER BY sample_time DESC;

By SID:


SELECT inst_id,
       sample_time,
       session_id,
       session_serial#,
       sql_id,
       prev_sql_id,
       session_state,
       wait_class,
       event,
       blocking_inst_id,
       blocking_session,
       module,
       action
FROM   gv$active_session_history
WHERE  sample_time >= SYSDATE - (30/1440)
AND    session_id = '&SID'
AND    session_serial# = '&SERIAL'
ORDER BY sample_time DESC;

22. Historical ASH by SID / SQL_ID


SELECT ash.instance_number,
       ash.session_id,
       ash.session_serial#,
       ash.sql_id,
       ash.prev_sql_id,
       ash.sql_plan_hash_value,
       ash.session_state,
       ash.wait_class,
       ash.event,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history ash
WHERE  ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                           AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND   (ash.sql_id = '&SQL_ID' OR ash.prev_sql_id = '&SQL_ID')
GROUP BY ash.instance_number,
         ash.session_id,
         ash.session_serial#,
         ash.sql_id,
         ash.prev_sql_id,
         ash.sql_plan_hash_value,
         ash.session_state,
         ash.wait_class,
         ash.event
ORDER BY ash_samples DESC;

By SID:


SELECT ash.instance_number,
       ash.session_id,
       ash.session_serial#,
       ash.sql_id,
       ash.prev_sql_id,
       ash.sql_plan_hash_value,
       ash.session_state,
       ash.wait_class,
       ash.event,
       ash.module,
       ash.action,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history ash
WHERE  ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                           AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND    ash.session_id = '&SID'
AND    ash.session_serial# = '&SERIAL'
GROUP BY ash.instance_number,
         ash.session_id,
         ash.session_serial#,
         ash.sql_id,
         ash.prev_sql_id,
         ash.sql_plan_hash_value,
         ash.session_state,
         ash.wait_class,
         ash.event,
         ash.module,
         ash.action
ORDER BY ash_samples DESC;

23. Find SQL_ID History for One SID

Useful when you know SID but not SQL_ID.


SELECT ash.instance_number,
       ash.session_id,
       ash.session_serial#,
       ash.sql_id,
       ash.prev_sql_id,
       ash.sql_plan_hash_value,
       COUNT(*) ash_samples,
       MIN(ash.sample_time) first_seen,
       MAX(ash.sample_time) last_seen
FROM   dba_hist_active_sess_history ash
WHERE  ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                           AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND    ash.session_id = '&SID'
AND    ash.session_serial# = '&SERIAL'
GROUP BY ash.instance_number,
         ash.session_id,
         ash.session_serial#,
         ash.sql_id,
         ash.prev_sql_id,
         ash.sql_plan_hash_value
ORDER BY ash_samples DESC;

24. Advanced: Session Wait Timeline


SELECT TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI') sample_minute,
       sql_id,
       prev_sql_id,
       session_state,
       wait_class,
       event,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                       AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND    session_id = '&SID'
AND    session_serial# = '&SERIAL'
GROUP BY TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI'),
         sql_id,
         prev_sql_id,
         session_state,
         wait_class,
         event
ORDER BY sample_minute, ash_samples DESC;

This gives a timeline like:


10:01 - ON CPU
10:05 - db file scattered read
10:15 - direct path read temp
10:25 - enq: TX row lock contention

25. Advanced: Object Hotspot for SID / SQL_ID


SELECT ash.instance_number,
       ash.session_id,
       ash.session_serial#,
       ash.sql_id,
       ash.sql_plan_hash_value,
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.wait_class,
       ash.event,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
       ON ash.current_obj# = obj.object_id
WHERE  ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                           AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND   (ash.sql_id = '&SQL_ID' OR ash.prev_sql_id = '&SQL_ID')
GROUP BY ash.instance_number,
         ash.session_id,
         ash.session_serial#,
         ash.sql_id,
         ash.sql_plan_hash_value,
         ash.current_obj#,
         obj.owner,
         obj.object_name,
         obj.object_type,
         ash.wait_class,
         ash.event
ORDER BY ash_samples DESC;

26. Advanced: Plan Step Wait Analysis for SID / SQL_ID


SELECT ash.sql_id,
       ash.sql_plan_hash_value,
       ash.sql_plan_line_id,
       ash.sql_plan_operation,
       ash.sql_plan_options,
       ash.wait_class,
       ash.event,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history ash
WHERE  ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                           AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND   (ash.sql_id = '&SQL_ID' OR ash.prev_sql_id = '&SQL_ID')
GROUP BY ash.sql_id,
         ash.sql_plan_hash_value,
         ash.sql_plan_line_id,
         ash.sql_plan_operation,
         ash.sql_plan_options,
         ash.wait_class,
         ash.event
ORDER BY ash_samples DESC;

This is very useful for RCA:

Plan Operation	Meaning
`TABLE ACCESS FULL` + User I/O	Full scan pressure
`HASH JOIN` + temp waits	Hash spill
`SORT GROUP BY` + temp waits	Sort spill
`PX SEND` + PX waits	Parallel distribution issue
`INDEX RANGE SCAN` + sequential read	Index access pressure

27. Advanced: Compare Current SID Plan with Historical Plan

Current:


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.sql_child_number,
       s.sql_plan_hash_value,
       q.plan_hash_value,
       q.sql_plan_baseline,
       q.sql_profile,
       q.sql_patch
FROM   gv$session s
LEFT JOIN gv$sql q
       ON s.inst_id = q.inst_id
      AND s.sql_id = q.sql_id
      AND s.sql_child_number = q.child_number
WHERE  s.sid = '&SID'
AND    s.serial# = '&SERIAL';

Historical:


SELECT sql_id,
       sql_plan_hash_value,
       COUNT(*) ash_samples,
       MIN(sample_time) first_seen,
       MAX(sample_time) last_seen
FROM   dba_hist_active_sess_history
WHERE  sql_id = '&SQL_ID'
AND    sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                       AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY sql_id, sql_plan_hash_value
ORDER BY ash_samples DESC;

28. Advanced: Detect Whether SID Is Stuck or Progressing

Run this query twice with 2–5 minutes gap.


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.wait_class,
       s.last_call_et,
       q.executions,
       q.buffer_gets,
       q.disk_reads,
       q.rows_processed,
       q.elapsed_time/1000000 elapsed_sec
FROM   gv$session s
LEFT JOIN gv$sql q
       ON s.inst_id = q.inst_id
      AND s.sql_id = q.sql_id
      AND s.sql_child_number = q.child_number
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

Interpretation

Observation after repeated runs	Meaning
`ROWS_PROCESSED` increasing	Progressing
`BUFFER_GETS` increasing	Working, but maybe inefficient
`DISK_READS` increasing	I/O active
Same event + same counters	Possibly stuck
`SECONDS_IN_WAIT` increasing with blocker	Blocked

29. Advanced: Find Duplicate EBS Requests Running Same SQL_ID


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       r.request_id,
       cpt.user_concurrent_program_name,
       u.user_name,
       r.actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24*60,2) runtime_mins,
       r.argument_text
FROM   gv$session s
JOIN   gv$process p
       ON s.inst_id = p.inst_id
      AND s.paddr = p.addr
JOIN   apps.fnd_concurrent_requests r
       ON r.oracle_process_id = p.spid
JOIN   apps.fnd_concurrent_programs_tl cpt
       ON r.concurrent_program_id = cpt.concurrent_program_id
      AND r.program_application_id = cpt.application_id
      AND cpt.language = 'US'
JOIN   apps.fnd_user u
       ON r.requested_by = u.user_id
WHERE  s.sql_id = '&SQL_ID'
ORDER BY cpt.user_concurrent_program_name, runtime_mins DESC;

Use case

If same program is submitted multiple times, SQL may become slow due to self-contention or heavy workload.

30. Final Advanced RCA Summary Query


SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.sql_id,
       s.prev_sql_id,
       s.sql_child_number,
       s.sql_plan_hash_value,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.last_call_et,
       s.blocking_instance,
       s.blocking_session,
       p.spid os_pid,
       q.plan_hash_value,
       q.executions,
       ROUND(q.elapsed_time/1000000,2) elapsed_sec,
       ROUND(q.cpu_time/1000000,2) cpu_sec,
       q.buffer_gets,
       q.disk_reads,
       q.rows_processed,
       q.sql_plan_baseline,
       q.sql_profile,
       q.sql_patch,
       s.module,
       s.action,
       s.program,
       s.machine,
       s.osuser
FROM   gv$session s
LEFT JOIN gv$process p
       ON s.inst_id = p.inst_id
      AND s.paddr = p.addr
LEFT JOIN gv$sql q
       ON s.inst_id = q.inst_id
      AND s.sql_id = q.sql_id
      AND s.sql_child_number = q.child_number
WHERE  s.inst_id = '&INST_ID'
AND    s.sid     = '&SID'
AND    s.serial# = '&SERIAL';

Final Production Analysis Flow

Level 1: Basic


Find SID from SQL_ID
Find SQL_ID from SID
Check wait event
Check runtime

Level 2: Apps DBA Mapping


Map SQL_ID/SID to OS PID
Map OS PID to EBS request
Identify program, user, parameters

Level 3: Performance Diagnosis


Check current wait
Check blocking
Check TEMP
Check DOP/PX
Check SQL Monitor
Check longops

Level 4: Plan Diagnosis


Check GV$SQL stats
Display current plan
Check plan hash value
Check baseline/profile/patch
Compare historical ASH/AWR plan

Level 5: RCA


Classify root cause:
CPU
User I/O
TEMP spill
Blocking
RAC cluster wait
Parallel downgrade
Plan regression
Duplicate requests
Data volume

Business-Safe Wording


Team,

We are analyzing SQL_ID <SQL_ID> associated with SID/SERIAL# <SID_SERIAL> on instance <INST_ID>.

The SQL has been mapped to request <REQUEST_ID> / program <PROGRAM_NAME>. Current wait event is <WAIT_EVENT>, wait class is <WAIT_CLASS>, and current runtime is <ACTUAL_RUNTIME> against normal runtime <NORMAL_RUNTIME>.

DBA team is validating whether the delay is due to SQL execution, blocking, TEMP usage, parallelism, RAC waits, or execution plan behavior.

No direct database session kill is recommended at this stage. If terminate/rerun is required, it should be taken only aft

Tuesday, May 12, 2026

Analysis Queries

1. Basic: Find SID / Serial# from SQL_ID

Interpretation

2. Find SID Using SQL_ID or Previous SQL_ID

Interpretation

3. Find SQL_ID from SID / Serial#

4. Find OS Process ID from SQL_ID / SID

5. Map SQL_ID / SID to EBS Concurrent Request

RCA Mapping

6. Find Active Long-Running Sessions with SQL_ID

Interpretation

7. Find Sessions Running Same SQL_ID

Interpretation

8. Check Current Wait Event for SID + SQL_ID

Wait Class Meaning

9. Find SQL Text from SID

10. Find SQL Text from SQL_ID

11. Get SQL Performance Stats from SID / SQL_ID

Interpretation

12. Display Execution Plan from SQL_ID

Check

13. Find Plan Line Currently Active for SID

Why useful?

14. Check TEMP Usage by SID / SQL_ID

15. Check Blocking for SID / SQL_ID

Check if SID is blocked

Find blocker details

16. Blocking Chain Advanced Query

17. Check Parallel Query Details for SID / SQL_ID

Interpretation

18. Check Parallel Server Availability

19. Check SQL Monitor by SQL_ID

20. Check Long Operations for SID / SQL_ID

Interpretation

21. ASH Real-Time by SID / SQL_ID

22. Historical ASH by SID / SQL_ID

23. Find SQL_ID History for One SID

24. Advanced: Session Wait Timeline

25. Advanced: Object Hotspot for SID / SQL_ID

26. Advanced: Plan Step Wait Analysis for SID / SQL_ID

27. Advanced: Compare Current SID Plan with Historical Plan

28. Advanced: Detect Whether SID Is Stuck or Progressing

Interpretation

29. Advanced: Find Duplicate EBS Requests Running Same SQL_ID

Use case

30. Final Advanced RCA Summary Query

Final Production Analysis Flow

Level 1: Basic

Level 2: Apps DBA Mapping

Level 3: Performance Diagnosis

Level 4: Plan Diagnosis

Level 5: RCA

Business-Safe Wording

No comments:

Post a Comment