Concurrent Request / EBS Layer Analysis
1. Request basic details
SELECT r.request_id,
r.phase_code,
r.status_code,
r.request_date,
r.requested_start_date,
r.actual_start_date,
r.actual_completion_date,
ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60,2) runtime_mins,
cpt.user_concurrent_program_name,
cp.concurrent_program_name short_name,
u.user_name requested_by,
r.argument_text,
r.completion_text
FROM apps.fnd_concurrent_requests r
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 r.request_id = &REQUEST_ID;
2. Request wait time before actual start
SELECT request_id,
request_date,
requested_start_date,
actual_start_date,
ROUND((actual_start_date-request_date)*24*60,2) queue_wait_mins,
ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*24*60,2) run_mins
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID;
Use this to separate:
Delay before start = Concurrent Manager queue issue
Delay after start = DB / SQL / lock / I/O / TEMP issue
3. Program definition for Planning Data Pull
SELECT cp.application_id,
cp.concurrent_program_id,
cp.concurrent_program_name short_name,
cpt.user_concurrent_program_name,
cp.execution_method_code,
cp.queue_method_code,
cp.enabled_flag,
cp.concurrent_class_id,
cp.output_file_type,
cp.enable_trace
FROM apps.fnd_concurrent_programs cp
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'
WHERE UPPER(cpt.user_concurrent_program_name) LIKE UPPER('%&PROGRAM_NAME%');
4. Recent runtime history for same program
SELECT r.request_id,
r.actual_start_date,
r.actual_completion_date,
ROUND((r.actual_completion_date-r.actual_start_date)*24*60,2) runtime_mins,
r.phase_code,
r.status_code,
r.argument_text
FROM apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = (
SELECT concurrent_program_id
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID)
AND r.actual_completion_date IS NOT NULL
AND r.actual_start_date >= SYSDATE - 30
ORDER BY r.actual_start_date DESC;
5. Average / minimum / maximum runtime trend
SELECT TRUNC(r.actual_start_date) run_date,
COUNT(*) total_runs,
ROUND(MIN((r.actual_completion_date-r.actual_start_date)*24*60),2) min_mins,
ROUND(AVG((r.actual_completion_date-r.actual_start_date)*24*60),2) avg_mins,
ROUND(MAX((r.actual_completion_date-r.actual_start_date)*24*60),2) max_mins
FROM apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = (
SELECT concurrent_program_id
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID)
AND r.actual_completion_date IS NOT NULL
AND r.actual_start_date >= SYSDATE - 60
GROUP BY TRUNC(r.actual_start_date)
ORDER BY run_date DESC;
6. Requests submitted at same time
SELECT r.request_id,
cpt.user_concurrent_program_name,
r.phase_code,
r.status_code,
r.actual_start_date,
ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60,2) runtime_mins,
u.user_name requested_by
FROM apps.fnd_concurrent_requests r
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 r.actual_start_date BETWEEN TO_DATE('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_DATE('&END_TIME','DD-MON-YYYY HH24:MI')
ORDER BY r.actual_start_date;
7. Duplicate Planning Data Pull requests
SELECT r.request_id,
r.phase_code,
r.status_code,
r.actual_start_date,
ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60,2) runtime_mins,
u.user_name,
r.argument_text
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_user u
ON r.requested_by = u.user_id
WHERE r.concurrent_program_id = (
SELECT concurrent_program_id
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID)
AND r.phase_code IN ('P','R')
ORDER BY r.actual_start_date;
8. Parent / child request relationship
SELECT request_id,
parent_request_id,
phase_code,
status_code,
actual_start_date,
actual_completion_date,
ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*24*60,2) runtime_mins
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID
OR parent_request_id = &REQUEST_ID
ORDER BY actual_start_date;
9. Request log and output file names
SELECT request_id,
logfile_name,
outfile_name,
outfile_node_name,
logfile_node_name
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID;
10. Concurrent manager handling the request
SELECT r.request_id,
r.controlling_manager,
q.concurrent_queue_name,
q.user_concurrent_queue_name,
p.os_process_id,
p.oracle_process_id,
p.process_status_code
FROM apps.fnd_concurrent_requests r
LEFT JOIN apps.fnd_concurrent_processes p
ON r.controlling_manager = p.concurrent_process_id
LEFT JOIN apps.fnd_concurrent_queues_vl q
ON p.concurrent_queue_id = q.concurrent_queue_id
WHERE r.request_id = &REQUEST_ID;
B. Concurrent Manager / Queue Analysis
11. Concurrent manager status
SELECT concurrent_queue_name,
user_concurrent_queue_name,
running_processes,
max_processes,
target_processes,
control_code,
enabled_flag
FROM apps.fnd_concurrent_queues_vl
ORDER BY user_concurrent_queue_name;
12. Running requests by manager
SELECT q.user_concurrent_queue_name,
COUNT(*) running_requests
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_processes p
ON r.controlling_manager = p.concurrent_process_id
JOIN apps.fnd_concurrent_queues_vl q
ON p.concurrent_queue_id = q.concurrent_queue_id
WHERE r.phase_code = 'R'
GROUP BY q.user_concurrent_queue_name
ORDER BY running_requests DESC;
13. Pending requests by manager/program
SELECT cpt.user_concurrent_program_name,
COUNT(*) pending_count,
MIN(r.requested_start_date) oldest_pending
FROM apps.fnd_concurrent_requests r
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'
WHERE r.phase_code = 'P'
GROUP BY cpt.user_concurrent_program_name
ORDER BY pending_count DESC;
14. Long-running requests during issue window
SELECT r.request_id,
cpt.user_concurrent_program_name,
r.actual_start_date,
ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60,2) runtime_mins,
r.phase_code,
r.status_code
FROM apps.fnd_concurrent_requests r
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'
WHERE r.actual_start_date BETWEEN TO_DATE('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_DATE('&END_TIME','DD-MON-YYYY HH24:MI')
AND ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60,2) > 30
ORDER BY runtime_mins DESC;
15. Check manager process availability
SELECT q.user_concurrent_queue_name,
p.concurrent_process_id,
p.os_process_id,
p.oracle_process_id,
p.process_status_code,
p.process_start_date
FROM apps.fnd_concurrent_processes p
JOIN apps.fnd_concurrent_queues_vl q
ON p.concurrent_queue_id = q.concurrent_queue_id
ORDER BY q.user_concurrent_queue_name, p.process_start_date DESC;
C. Map Request to Database Session / SQL_ID
16. Map request to DB session
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.sql_id,
s.prev_sql_id,
s.event,
s.wait_class,
s.seconds_in_wait,
s.module,
s.action,
s.program,
p.spid os_pid,
r.request_id
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
WHERE r.request_id = &REQUEST_ID;
17. Map SQL_ID to EBS request
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,
cpt.user_concurrent_program_name,
u.user_name requested_by,
ROUND((SYSDATE-r.actual_start_date)*24*60,2) runtime_mins
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'
OR s.prev_sql_id = '&SQL_ID'
ORDER BY runtime_mins DESC;
18. Active session details for request
SELECT s.inst_id,
s.sid,
s.serial#,
s.status,
s.sql_id,
s.prev_sql_id,
s.sql_child_number,
s.sql_plan_hash_value,
s.event,
s.wait_class,
s.state,
s.seconds_in_wait,
s.last_call_et,
s.blocking_instance,
s.blocking_session,
s.module,
s.action,
s.machine,
s.osuser
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
WHERE r.request_id = &REQUEST_ID;
19. SQL text for active request
SELECT s.inst_id,
s.sid,
s.serial#,
q.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
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
WHERE r.request_id = &REQUEST_ID;
20. Current SQL stats for request
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.sql_plan_baseline,
q.sql_profile,
q.sql_patch
FROM gv$session s
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
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
WHERE r.request_id = &REQUEST_ID;
D. Wait Event / Blocking / Locking Analysis
21. Current wait event for request
SELECT s.inst_id,
s.sid,
s.serial#,
s.sql_id,
s.event,
s.wait_class,
s.state,
s.seconds_in_wait,
s.last_call_et
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
WHERE r.request_id = &REQUEST_ID;
22. Blocking details for request
SELECT s.inst_id blocked_inst,
s.sid blocked_sid,
s.serial# blocked_serial,
s.sql_id blocked_sql_id,
s.event blocked_event,
s.seconds_in_wait,
s.blocking_instance blocker_inst,
s.blocking_session blocker_sid
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
WHERE r.request_id = &REQUEST_ID
AND s.blocking_session IS NOT NULL;
23. Blocker session details
SELECT b.inst_id,
b.sid,
b.serial#,
b.username,
b.status,
b.sql_id,
b.prev_sql_id,
b.event,
b.wait_class,
b.module,
b.action,
b.program,
b.machine,
b.osuser
FROM gv$session b
WHERE b.inst_id = &BLOCKING_INSTANCE
AND b.sid = &BLOCKING_SESSION;
24. Lock details for request session
SELECT s.inst_id,
s.sid,
s.serial#,
l.type,
l.id1,
l.id2,
l.lmode,
l.request,
l.block,
o.owner,
o.object_name,
o.object_type
FROM gv$session s
JOIN gv$lock l
ON s.inst_id = l.inst_id
AND s.sid = l.sid
LEFT JOIN dba_objects o
ON l.id1 = o.object_id
WHERE s.sid = &SID
AND s.serial# = &SERIAL;
25. ASH blocking evidence for request SQL_ID
SELECT ash.instance_number,
ash.session_id,
ash.session_serial#,
ash.sql_id,
ash.blocking_inst_id,
ash.blocking_session,
ash.blocking_session_serial#,
ash.event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
WHERE ash.sql_id = '&SQL_ID'
AND 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.blocking_session IS NOT NULL
GROUP BY ash.instance_number,
ash.session_id,
ash.session_serial#,
ash.sql_id,
ash.blocking_inst_id,
ash.blocking_session,
ash.blocking_session_serial#,
ash.event
ORDER BY ash_samples DESC;
E. AWR / ASH Analysis
26. ASH wait class by SQL_ID
SELECT ash.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.sql_id = '&SQL_ID'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY ash.sql_id,
ash.sql_plan_hash_value,
ash.session_state,
ash.wait_class,
ash.event
ORDER BY ash_samples DESC;
27. ASH wait class percentage
SELECT wait_class,
event,
COUNT(*) ash_samples,
ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER (),2) pct_of_total
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 wait_class, event
ORDER BY ash_samples DESC;
28. ASH by RAC instance
SELECT instance_number,
wait_class,
event,
COUNT(*) ash_samples
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 instance_number, wait_class, event
ORDER BY instance_number, ash_samples DESC;
29. ASH timeline by minute
SELECT TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI') sample_minute,
wait_class,
event,
COUNT(*) ash_samples
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 TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI'),
wait_class,
event
ORDER BY sample_minute, ash_samples DESC;
30. ASH by plan line
SELECT sql_plan_hash_value,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
wait_class,
event,
COUNT(*) ash_samples
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_plan_hash_value,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
wait_class,
event
ORDER BY ash_samples DESC;
31. ASH object hotspot
SELECT ash.current_obj#,
o.owner,
o.object_name,
o.object_type,
ash.wait_class,
ash.event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects o
ON ash.current_obj# = o.object_id
WHERE ash.sql_id = '&SQL_ID'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY ash.current_obj#,
o.owner,
o.object_name,
o.object_type,
ash.wait_class,
ash.event
ORDER BY ash_samples DESC;
32. AWR SQL stats by SQL_ID
SELECT sn.begin_interval_time,
ss.instance_number,
ss.sql_id,
ss.plan_hash_value,
ss.executions_delta,
ROUND(ss.elapsed_time_delta/1000000/60,2) elapsed_mins,
ROUND(ss.cpu_time_delta/1000000/60,2) cpu_mins,
ss.buffer_gets_delta,
ss.disk_reads_delta,
ss.rows_processed_delta
FROM dba_hist_sqlstat ss
JOIN dba_hist_snapshot sn
ON ss.snap_id = sn.snap_id
AND ss.instance_number = sn.instance_number
WHERE ss.sql_id = '&SQL_ID'
AND sn.begin_interval_time BETWEEN TO_DATE('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_DATE('&END_TIME','DD-MON-YYYY HH24:MI')
ORDER BY sn.begin_interval_time;
33. Plan hash comparison
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) executions,
ROUND(SUM(elapsed_time_delta)/1000000/60,2) elapsed_mins,
ROUND(SUM(cpu_time_delta)/1000000/60,2) cpu_mins,
SUM(buffer_gets_delta) buffer_gets,
SUM(disk_reads_delta) disk_reads,
ROUND(SUM(elapsed_time_delta)/1000000 /
NULLIF(SUM(executions_delta),0),2) avg_elapsed_sec
FROM dba_hist_sqlstat
WHERE sql_id = '&SQL_ID'
GROUP BY sql_id, plan_hash_value
ORDER BY avg_elapsed_sec DESC;
34. System wait events during issue window
SELECT sn.begin_interval_time,
se.instance_number,
se.event_name,
se.wait_class,
ROUND(se.time_waited_micro_delta/1000000/60,2) wait_mins,
se.total_waits_delta
FROM dba_hist_system_event se
JOIN dba_hist_snapshot sn
ON se.snap_id = sn.snap_id
AND se.instance_number = sn.instance_number
WHERE sn.begin_interval_time BETWEEN TO_DATE('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_DATE('&END_TIME','DD-MON-YYYY HH24:MI')
AND se.wait_class <> 'Idle'
ORDER BY wait_mins DESC;
35. CPU / I/O metric during issue window
SELECT begin_time,
end_time,
instance_number,
metric_name,
ROUND(value,2) value
FROM dba_hist_sysmetric_history
WHERE begin_time BETWEEN TO_DATE('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_DATE('&END_TIME','DD-MON-YYYY HH24:MI')
AND metric_name IN (
'Host CPU Utilization (%)',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Average Active Sessions',
'Physical Read Total Bytes Per Sec',
'Physical Write Total Bytes Per Sec'
)
ORDER BY begin_time, instance_number, metric_name;
F. TEMP / PGA / Parallelism
36. Current TEMP usage by request
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
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
WHERE r.request_id = &REQUEST_ID
ORDER BY temp_gb DESC;
37. TEMP usage 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;
38. Historical TEMP wait evidence
SELECT event,
sql_plan_operation,
sql_plan_options,
COUNT(*) ash_samples
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')
AND (event LIKE '%temp%' OR event IN ('direct path read temp','direct path write temp'))
GROUP BY event,
sql_plan_operation,
sql_plan_options
ORDER BY ash_samples DESC;
39. PGA usage by request session
SELECT s.inst_id,
s.sid,
s.serial#,
s.sql_id,
ROUND(pga.value/1024/1024,2) pga_mb
FROM gv$session s
JOIN gv$sesstat pga
ON s.inst_id = pga.inst_id
AND s.sid = pga.sid
JOIN gv$statname n
ON pga.inst_id = n.inst_id
AND pga.statistic# = n.statistic#
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
WHERE r.request_id = &REQUEST_ID
AND n.name = 'session pga memory'
ORDER BY pga_mb DESC;
40. Parallel query details by SQL_ID
SELECT px.inst_id,
px.qcsid,
px.qcserial#,
px.sid,
px.serial#,
s.sql_id,
px.degree,
px.req_degree,
px.server_group,
px.server_set,
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;
41. Parallel server availability
SELECT inst_id,
statistic,
value
FROM gv$px_process_sysstat
WHERE statistic LIKE 'Servers%'
ORDER BY inst_id, statistic;
42. Parallel parameters
SELECT inst_id,
name,
value
FROM gv$parameter
WHERE name IN ('parallel_max_servers',
'parallel_servers_target',
'parallel_degree_policy',
'parallel_min_time_threshold')
ORDER BY inst_id, name;
G. Execution Plan / Object / Statistics
43. Current execution plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&SQL_ID',
cursor_child_no => NULL,
format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS +NOTE'
));
44. Historical AWR execution plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(
sql_id => '&SQL_ID',
plan_hash_value => NULL,
format => 'ALL +OUTLINE +NOTE'
));
45. Objects used by SQL_ID
SELECT DISTINCT object_owner,
object_name,
object_type
FROM dba_hist_sql_plan
WHERE sql_id = '&SQL_ID'
AND object_owner IS NOT NULL
ORDER BY object_owner, object_name;
46. Table statistics for involved objects
SELECT owner,
table_name,
num_rows,
blocks,
sample_size,
last_analyzed,
stale_stats,
stattype_locked
FROM dba_tab_statistics
WHERE owner = '&OWNER'
AND table_name = '&TABLE_NAME';
47. Index statistics for involved table
SELECT owner,
index_name,
table_name,
blevel,
leaf_blocks,
clustering_factor,
num_rows,
last_analyzed,
stale_stats
FROM dba_ind_statistics
WHERE owner = '&OWNER'
AND table_name = '&TABLE_NAME'
ORDER BY index_name;
48. Segment size for involved objects
SELECT owner,
segment_name,
segment_type,
ROUND(bytes/1024/1024/1024,2) size_gb,
blocks
FROM dba_segments
WHERE owner = '&OWNER'
AND segment_name = '&OBJECT_NAME'
ORDER BY size_gb DESC;
H. SQL Monitor / Longops / Final Evidence
49. SQL Monitor for SQL_ID
SELECT inst_id,
sql_id,
sql_exec_id,
sql_exec_start,
status,
username,
module,
action,
ROUND(elapsed_time/1000000,2) elapsed_sec,
ROUND(cpu_time/1000000,2) 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;
50. Long operations progress
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;
RCA Decision Matrix for Planning Data Pull Delay
| Evidence | Likely Reason for Delay | RCA Wording |
|---|---|---|
| High queue wait before actual start | Concurrent Manager queue delay | Request waited before execution due to manager capacity/workload |
| High DB runtime after start | Database/SQL execution delay | Request started on time but SQL execution consumed more time |
User I/O waits | Physical read / full scan / index access | Delay caused by high I/O activity |
direct path read/write temp | Sort/hash spill to TEMP | Delay caused by TEMP spill during large processing |
Application wait / blocking | Locking | Request was waiting on another transaction/session |
| High CPU samples | CPU-bound SQL | SQL consumed high CPU due to large data volume or inefficient plan |
| Cluster waits | RAC global cache | RAC block contention during concurrent access |
| Multiple plan hash values | Plan regression | SQL used a different/slower execution plan |
| DOP downgraded | Parallel shortage | SQL did not get expected parallel resources |
| Many duplicate requests | Self-contention/workload | Multiple same requests increased contention/load |
Business RCA Template
During month-end production processing, the Planning Data Pull concurrent worker request <REQUEST_ID> ran longer than expected.
Normal runtime:
<NORMAL_RUNTIME>
Actual runtime:
<ACTUAL_RUNTIME>
Request details:
Program: <PROGRAM_NAME>
Request ID: <REQUEST_ID>
SQL_ID: <SQL_ID>
Plan hash value: <PLAN_HASH_VALUE>
Technical findings:
DBA analysis confirmed that the delay was mainly due to <ROOT_CAUSE>.
Evidence:
1. Concurrent request runtime showed <QUEUE_WAIT/RUN_TIME_DETAILS>.
2. Active session mapping showed SQL_ID <SQL_ID> linked to request <REQUEST_ID>.
3. ASH/AWR evidence showed dominant wait event <WAIT_EVENT>.
4. TEMP/blocking/parallel/plan analysis showed <OBSERVATION>.
5. Historical comparison showed normal runtime was <NORMAL_RUNTIME>, while current runtime was <ACTUAL_RUNTIME>.
Conclusion:
The request delay was caused by <CPU / I/O / TEMP spill / blocking / RAC cluster wait / parallel downgrade / plan regression / concurrent manager queue / high month-end data volume>.
Action taken:
DBA team monitored the request, validated session and SQL evidence, and did not perform any direct database kill. Any terminate/rerun decision should be business-approved.
Preventive action:
For the next month-end cycle, DBA team will proactively monitor Planning Data Pull request runtime, SQL_ID, wait eve
No comments:
Post a Comment