1. Identify Active Sessions Causing Issues
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.event, s.wait_class, s.wait_time, s.seconds_in_wait, s.blocking_session FROM v$session s WHERE s.status = 'ACTIVE' -- AND s.username = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user -- AND s.sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' to filter by sql_id (use IN ('id1', 'id2') for multiple) ORDER BY s.seconds_in_wait DESC;
2. History of a Specific SQL_ID (from AWR)
SELECT dhss.sql_id, sn.begin_interval_time, sn.end_interval_time, dhss.executions_delta, ROUND(dhss.elapsed_time_delta / 1000000 / NULLIF(dhss.executions_delta, 0), 2) AS avg_elapsed_sec, ROUND(dhss.cpu_time_delta / 1000000 / NULLIF(dhss.executions_delta, 0), 2) AS avg_cpu_sec, sq.sql_text FROM dba_hist_sqlstat dhss JOIN dba_hist_snapshot sn ON dhss.snap_id = sn.snap_id JOIN dba_hist_sqltext sq ON dhss.sql_id = sq.sql_id WHERE 1=1 -- AND dhss.parsing_schema_name = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user -- AND dhss.sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' to filter by sql_id (use IN ('id1', 'id2') for multiple) ORDER BY sn.end_interval_time DESC;
WITH snap_range AS (
SELECT
MIN(snap_id) AS begin_snap,
MAX(snap_id) AS end_snap
FROM dba_hist_snapshot
WHERE begin_interval_time <= TO_DATE('2025-08-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -- Replace with start timestamp
AND end_interval_time >= TO_DATE('2025-08-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS') -- Replace with end timestamp
),
total_cpu AS (
SELECT SUM(cpu_time_delta) AS total
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range)
-- AND parsing_schema_name = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user
-- AND sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' to filter by sql_id (use IN ('id1', 'id2') for multiple)
)
SELECT
dhss.sql_id,
dhss.parsing_schema_name,
ROUND(dhss.cpu_time_delta / 1000000, 2) AS cpu_sec,
ROUND((dhss.cpu_time_delta / total_cpu.total) * 100, 2) AS cpu_percent,
sq.sql_text
FROM dba_hist_sqlstat dhss
CROSS JOIN total_cpu
JOIN dba_hist_sqltext sq ON dhss.sql_id = sq.sql_id
WHERE dhss.snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range)
-- AND dhss.parsing_schema_name = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user
-- AND dhss.sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' to filter by sql_id (use IN ('id1', 'id2') for multiple)
ORDER BY cpu_sec DESC
FETCH FIRST 10 ROWS ONLY;
```
queries worked best
overall queries ran at a particular point of time:
AWR Report:
SELECT
MIN(snap_id) AS begin_snap,
MAX(snap_id) AS end_snap
FROM dba_hist_snapshot
WHERE begin_interval_time <= TO_DATE('2025-08-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -- Replace with start timestamp
AND end_interval_time >= TO_DATE('2025-08-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS') -- Replace with end timestamp
),
total_cpu AS (
SELECT SUM(cpu_time_delta) AS total
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range)
)
SELECT
dhss.sql_id,
dhss.parsing_schema_name,
ROUND(dhss.cpu_time_delta / 1000000, 2) AS cpu_sec,
ROUND((dhss.cpu_time_delta / total_cpu.total) * 100, 2) AS cpu_percent,
sq.sql_text
FROM dba_hist_sqlstat dhss
CROSS JOIN total_cpu
JOIN dba_hist_sqltext sq ON dhss.sql_id = sq.sql_id
WHERE dhss.snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range)
ORDER BY cpu_sec DESC
FETCH FIRST 10 ROWS ONLY;
3. Queries Ran at a Particular Time (from ASH)
FROM v$active_session_history ash
JOIN v$sql sql ON ash.sql_id = sql.sql_id
WHERE ash.sample_time BETWEEN TO_DATE('2025-08-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2025-08-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS') -- Replace with time range
-- AND ash.user_id = (SELECT user_id FROM dba_users WHERE username = 'USERNAME') -- Uncomment and replace 'USERNAME'
-- AND ash.sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' (use IN ('id1', 'id2') for multiple)
ORDER BY ash.sample_time DESC;
4. Queries Ran at a Particular Time (from AWR Historical ASH)
5. High CPU Queries and Percentage Load (from AWR)
WITH snap_range AS ( SELECT MIN(snap_id) AS begin_snap, MAX(snap_id) AS end_snap FROM dba_hist_snapshot WHERE begin_interval_time <= TO_DATE('2025-08-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -- Replace with start timestamp AND end_interval_time >= TO_DATE('2025-08-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS') -- Replace with end timestamp ), total_cpu AS ( SELECT SUM(cpu_time_delta) AS total FROM dba_hist_sqlstat WHERE snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range) -- AND parsing_schema_name = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user -- AND sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' (use IN ('id1', 'id2') for multiple) ) SELECT dhss.sql_id, dhss.parsing_schema_name, ROUND(dhss.cpu_time_delta / 1000000, 2) AS cpu_sec, ROUND((dhss.cpu_time_delta / total_cpu.total) * 100, 2) AS cpu_percent, sq.sql_text FROM dba_hist_sqlstat dhss CROSS JOIN total_cpu JOIN dba_hist_sqltext sq ON dhss.sql_id = sq.sql_id WHERE dhss.snap_id BETWEEN (SELECT begin_snap FROM snap_range) AND (SELECT end_snap FROM snap_range) -- AND dhss.parsing_schema_name = 'USERNAME' -- Uncomment and replace 'USERNAME' to filter by user -- AND dhss.sql_id = 'SQL_ID' -- Uncomment and replace 'SQL_ID' (use IN ('id1', 'id2') for multiple) ORDER BY cpu_sec DESC FETCH FIRST 10 ROWS ONLY;
6. Wait Events for High Load Queries (from ASH)
7. System State at a Particular Time (from ASH - CPU, I/O, Waits)
8. System State at a Particular Time (from AWR - Overall Load)
9. Query for Recent Active Session History (from V$ACTIVE_SESSION_HISTORY)
This gives a quick view of recently active SQL for the user, with estimated duration based on ASH samples (sampled every second). Useful for very recent activity (last hour or so, depending on retention).
System-Wide CPU Boundedness During Package Execution
SELECT
ash.sql_id,
MIN(ash.sample_time) AS first_seen,
MAX(ash.sample_time) AS last_seen,
COUNT(*) AS ash_samples,
ROUND(COUNT(*) * 1) AS approx_elapsed_seconds, -- Each sample ~1 second of activity
SUM(CASE WHEN ash.wait_time > 0 THEN 1 ELSE 0 END) AS wait_samples,
sql.sql_text,
ash.session_id,
ash.session_state, -- ON CPU or WAITING
ash.wait_class, -- e.g., User I/O, CPU
ash.event -- Specific wait event for troubleshooting
FROM
v$active_session_history ash
JOIN
v$sql sql ON ash.sql_id = sql.sql_id
JOIN
dba_users u ON ash.user_id = u.user_id
WHERE
u.username = 'USERNAME'
GROUP BY
ash.sql_id, sql.sql_text, ash.session_id, ash.session_state, ash.wait_class, ash.event
ORDER BY
MAX(ash.sample_time) DESC;2. Query for Historical SQL Stats (from DBA_HIST_SQLSTAT via AWR Snapshots)
This provides execution history over time (e.g., last day, week), including elapsed time per snapshot interval. Filter the date range as needed (e.g., last 7 days shown). Deltas show stats for that specific snapshot period, ideal for troubleshooting individual runs.
SELECT
dhss.parsing_schema_name,
dhss.sql_id,
sn.begin_interval_time,
sn.end_interval_time,
dhss.executions_delta AS executions_in_interval,
ROUND(dhss.elapsed_time_delta / 1000000, 2) AS elapsed_seconds_in_interval,
ROUND(dhss.cpu_time_delta / 1000000, 2) AS cpu_seconds_in_interval,
ROUND(dhss.iowait_delta / 1000000, 2) AS io_wait_seconds_in_interval,
dhss.buffer_gets_delta AS buffer_gets,
dhss.disk_reads_delta AS disk_reads,
dhss.rows_processed_delta AS rows_processed,
sq.sql_text
FROM
dba_hist_sqlstat dhss
JOIN
dba_hist_snapshot sn ON dhss.snap_id = sn.snap_id
JOIN
dba_hist_sqltext sq ON dhss.sql_id = sq.sql_id
WHERE
dhss.parsing_schema_name = 'USERNAME'
AND sn.begin_interval_time >= SYSDATE - 7 -- Adjust for your time range, e.g., SYSDATE - 1 for last day
ORDER BY
sn.end_interval_time DESC;3. Query for In-Memory SQL History (from V$SQL)
If you need non-historical (current shared pool) data, this shows SQL loaded/executed recently by the user, with cumulative stats. Sort by last_active_time for the "last run".
SELECT
v.sql_id,
v.parsing_schema_name,
TO_DATE(v.last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
TO_DATE(v.first_load_time, 'YYYY-MM-DD HH24:MI:SS') AS first_load_time,
v.executions,
ROUND(v.elapsed_time / 1000000 / NULLIF(v.executions, 0), 2) AS avg_elapsed_seconds_per_exec,
ROUND(v.cpu_time / 1000000 / NULLIF(v.executions, 0), 2) AS avg_cpu_seconds_per_exec,
v.disk_reads,
v.buffer_gets,
v.rows_processed,
v.sql_text
FROM
v$sql v
WHERE
v.parsing_schema_name = 'USERNAME'
AND v.last_active_time > SYSDATE - 7 -- Adjust for time range
ORDER BY
v.last_active_time DESC;These should cover most troubleshooting needs (e.g., identify slow SQL, waits, resource usage). If the user has no recent activity in these views, check AWR retention settings or enable auditing for fuller history. Provide the specific username (and any "name" details) if you need me to run/adapt these further.
No comments:
Post a Comment