Version 1: SQL-Centric
This version starts from V$SQL and optionally attaches information from V$SESSION.
Use this query when:
- Investigating a specific SQL ID
- Comparing child cursors
- Looking for multiple execution plans
- Checking SQL Profiles or SQL Baselines
- Determining whether a live session is currently using a particular child cursor
Advantages
- Shows all child cursors currently in the shared pool
- Shows all plans for the SQL.
- Shows SQL Profile/Baseline information
- Continues to return rows even when no session is executing the SQL
- Preserves the behavior of the original query while adding live session visibility.
SELECT
TO_CHAR(q.last_active_time,'dd.mm.yyyy hh24:mi') AS last_active,
q.sql_id,
q.plan_hash_value,
q.child_number,
q.sql_plan_baseline,
q.sql_profile,
q.executions,
ROUND(q.elapsed_time/1000000,1) AS total_elapsed_sec,
ROUND(
q.elapsed_time /
NULLIF(q.executions,0) /
1000000,
1
) AS avg_elapsed_sec,
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.module,
s.status,
s.state,
s.event,
s.sql_exec_start,
s.seconds_in_wait,
s.last_call_et,
CASE
WHEN s.sql_exec_start IS NOT NULL
THEN ROUND((SYSDATE - s.sql_exec_start) * 86400)
END AS current_runtime_sec
FROM v$sql q
LEFT JOIN v$session s
ON s.sql_id = q.sql_id
AND s.sql_child_number = q.child_number
WHERE q.sql_id = '9sdp1k6x6ut5v'
ORDER BY q.last_active_time DESC;
Put differently, the query will obey the command
"Show me everything Oracle knows about this SQL ID, and if somebody happens to be running it right now, show me who it is."
Interpretation of timing columns
| Column | Meaning |
|---|---|
| TOTAL_ELAPSED_SEC | Total elapsed time accumulated by this child cursor since it was loaded |
| AVG_ELAPSED_SEC | Average elapsed time per execution |
| CURRENT_RUNTIME_SEC | Runtime of the current execution (if active) |
| LAST_CALL_ET | Seconds since current call started (ACTIVE) or last call ended (INACTIVE) |
| SECONDS_IN_WAIT | Time spent in the current wait event |
Version 2: Session-Centric
This version starts from V$SESSION and joins to V$SQL.
Advantages
Use this query when:
- A SQL statement is currently causing performance issues
- You need the SID and SERIAL# immediately
- You want to see wait events and session state
- You need to identify the exact plan used by a running session
Advantages
- Focuses only on active sessions
- Quickly identifies the responsible user and machine
- Immediately displays wait information
- Shows exactly which child cursor and plan the session is using
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.module,
s.status,
s.state,
s.event,
s.sql_exec_start,
s.last_call_et,
s.seconds_in_wait,
CASE
WHEN s.sql_exec_start IS NOT NULL
THEN ROUND((SYSDATE - s.sql_exec_start) * 86400)
END AS current_runtime_sec,
q.sql_id,
q.child_number,
q.plan_hash_value,
q.sql_plan_baseline,
q.sql_profile,
q.executions,
ROUND(q.elapsed_time/1000000,1) AS total_elapsed_sec,
ROUND(
q.elapsed_time /
NULLIF(q.executions,0) /
1000000,
1
) AS avg_elapsed_sec
FROM v$session s
JOIN v$sql q
ON q.sql_id = s.sql_id
AND q.child_number = s.sql_child_number
WHERE s.sql_id = '9sdp1k6x6ut5v';
Interpretation of timing columns
Column Meaning CURRENT_RUNTIME_SEC How long the current execution has been running LAST_CALL_ET Session-centric timing maintained by Oracle SECONDS_IN_WAIT Current wait duration TOTAL_ELAPSED_SEC Historical cumulative elapsed time for the child cursor AVG_ELAPSED_SEC Historical average elapsed time per execution
The difference between the two
Aspect SQL-Centric Session-Centric Driving table V$SQLV$SESSIONPrimary purpose Investigate a SQL ID Find running sessions Returns rows if no session exists Yes No Shows all child cursors Yes No Shows all cached plans Yes No Shows current waits If session exists Yes Best for plan analysis Yes Limited Best for firefighting active problems Good Excellent
