AWR/ASH - Initial Draft

 -- Oracle Performance Troubleshooting Queries for AWS RDS

-- Run these as a privileged user (e.g., master user) in SQL*Plus, SQL Developer, or similar.

-- Adjust dates, snapshot IDs, and other parameters as needed for your environment.

-- Dates are set for July 9-10, 2025 outage example.

-- Section 1: Identify Snapshot IDs for AWR

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time >= TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND end_interval_time <= TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

ORDER BY snap_id;

-- Section 1.1: Generate AWR Report (Replace &begin_snap and &end_snap)

SELECT output FROM TABLE(rdsadmin.rdsadmin_diagnostic_util.awr_report(

    dbid => (SELECT dbid FROM v$database),

    inst_num => (SELECT instance_number FROM v$instance),

    begin_snap => &begin_snap,  -- e.g., 1234

    end_snap => &end_snap,      -- e.g., 1235

    report_type => 'html'       -- Or 'text'

));

-- Manually Create Snapshot if Needed

EXEC rdsadmin.rdsadmin_util.create_snapshot;

-- Section 1.2: Generate ASH Report

SELECT output FROM TABLE(rdsadmin.rdsadmin_diagnostic_util.ash_report(

    begin_time => TO_TIMESTAMP_TZ('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS'),  -- Adjust start time

    end_time => TO_TIMESTAMP_TZ('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS'),    -- Adjust end time

    report_type => 'html'  -- Or 'text'

));

-- Section 2.1: Top 10 Sessions at a Particular Period (Historical from ASH)

SELECT 

    session_id, 

    session_serial#, 

    user_id, 

    program, 

    COUNT(*) AS samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct_load

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY session_id, session_serial#, user_id, program

ORDER BY samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Real-time Top 10 Active Sessions

SELECT sid, serial#, username, program, status

FROM v$session

WHERE status = 'ACTIVE'

ORDER BY last_call_et DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.2: Top 10 CPU-Heavy Queries (Historical from ASH)

SELECT 

    sql_id, 

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct_cpu

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS')

AND session_state = 'ON CPU'

GROUP BY sql_id

ORDER BY cpu_samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Get SQL Text for a Specific sql_id (Replace &sql_id)

SELECT sql_fulltext FROM v$sql WHERE sql_id = '&sql_id';

-- Real-time Top 10 CPU-Heavy Queries

SELECT sql_id, cpu_time, executions, cpu_time/executions AS avg_cpu

FROM v$sql

ORDER BY cpu_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.3: Top Wait Events (Historical from AWR, Replace &begin_snap and &end_snap)

SELECT 

    event, 

    total_waits, 

    time_waited_micro / 1000000 AS time_waited_sec,

    ROUND(time_waited_micro * 100 / SUM(time_waited_micro) OVER(), 2) AS pct_time

FROM dba_hist_system_event

WHERE snap_id BETWEEN &begin_snap AND &end_snap

AND wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

FETCH FIRST 10 ROWS ONLY;

-- Real-time Top Wait Events

SELECT event, total_waits, time_waited

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.4: Top Users by Session Count (Real-time)

SELECT username, COUNT(*) AS session_count

FROM v$session

WHERE username IS NOT NULL

GROUP BY username

ORDER BY session_count DESC

FETCH FIRST 10 ROWS ONLY;

-- Historical Top Users by Unique Sessions

SELECT username, COUNT(DISTINCT session_id) AS unique_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY username

ORDER BY unique_sessions DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.5: Top Programs/Modules (Real-time)

SELECT program, module, COUNT(*) AS count

FROM v$session

GROUP BY program, module

ORDER BY count DESC

FETCH FIRST 10 ROWS ONLY;

-- Historical Top Programs/Modules

SELECT program, module, COUNT(*) AS samples

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY program, module

ORDER BY samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.6: DB Load (AAS Historical)

SELECT 

    begin_time, 

    ROUND(SUM(active_sessions) / COUNT(*), 2) AS aas

FROM (

    SELECT 

        begin_interval_time AS begin_time,

        COUNT(*) AS active_sessions

    FROM dba_hist_active_sess_history h

    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id

    WHERE s.begin_interval_time BETWEEN TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

        AND TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

    GROUP BY sample_id, begin_interval_time

)

GROUP BY begin_time

ORDER BY begin_time;

-- Real-time AAS (Last 5 Minutes)

SELECT ROUND(COUNT(*) / 300, 2) AS aas  -- 300 samples in 5 min (1/sec)

FROM v$active_session_history

WHERE sample_time > SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- Additional: Top SQL by Elapsed Time/IO

SELECT sql_id, elapsed_time, disk_reads

FROM v$sql

ORDER BY elapsed_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Buffer Cache Hit Ratio

SELECT ROUND((1 - (physical_reads / (consistent_gets + db_block_gets))) * 100, 2) AS hit_ratio

FROM v$buffer_pool_statistics;

-- Lock Contention

SELECT sid, type, id1, id2, lmode, request, block

FROM v$lock

WHERE request > 0;