awr_performance_analyzer.sql
-- ------------------------------------------------------------------------------------- File Name : awr_performance_analyzer.sql-- Author : Pierre Montbleau-- Description : Oracle AWR (Automatic Workload Repository) performance analysis and reporting-- Purpose : Analyze historical performance data, identify trends, and generate AWR reports-- Call Syntax : @F:\DBA\Scripts\awr_performance_analyzer.sql (begin_snap_id) (end_snap_id)-- Parameters : begin_snap_id - Starting snapshot ID (use 0 for latest, -1 for auto-detect)-- end_snap_id - Ending snapshot ID (use 0 for latest)-- Execution Ex : @F:\DBA\Scripts\AI\awr_performance_analyzer.sql -1 0-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate AWR availability-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_AWR_Report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || &1 || '_' || &2 || '_DETAILED.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting AWR Performance Analyzer...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Begin Snap ID: &1PROMPT End Snap ID: &2PROMPT Report Type: DETAILEDPROMPT Timestamp: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT
-- First, determine the actual snapshot IDs to useCOLUMN actual_begin_snap NEW_VALUE actual_begin_snapCOLUMN actual_end_snap NEW_VALUE actual_end_snap
SELECT CASE WHEN &1 = -1 THEN max_snap - 2 WHEN &1 = 0 THEN max_snap ELSE &1 END as actual_begin_snap, CASE WHEN &2 = 0 THEN max_snap ELSE &2 END as actual_end_snapFROM ( SELECT MIN(snap_id) as min_snap, MAX(snap_id) as max_snap FROM dba_hist_snapshot WHERE startup_time = (SELECT MAX(startup_time) FROM dba_hist_snapshot));
-- Check AWR availability and validate parametersDECLARE v_begin_snap NUMBER := TO_NUMBER('&1'); v_end_snap NUMBER := TO_NUMBER('&2'); v_awr_available NUMBER; v_min_snap NUMBER; v_max_snap NUMBER; v_actual_begin_snap NUMBER := &actual_begin_snap; v_actual_end_snap NUMBER := &actual_end_snap;BEGIN -- Check if AWR is available SELECT COUNT(*) INTO v_awr_available FROM v$parameter WHERE name = 'statistics_level' AND value = 'TYPICAL'; IF v_awr_available = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'AWR not available - STATISTICS_LEVEL must be TYPICAL or ALL'); END IF; -- Get snapshot range for validation SELECT MIN(snap_id), MAX(snap_id) INTO v_min_snap, v_max_snap FROM dba_hist_snapshot WHERE startup_time = (SELECT MAX(startup_time) FROM dba_hist_snapshot); -- Validate the actual snapshot range IF v_actual_begin_snap < v_min_snap OR v_actual_begin_snap > v_max_snap THEN RAISE_APPLICATION_ERROR(-20002, 'Begin snapshot ' || v_actual_begin_snap || ' not in range ' || v_min_snap || '-' || v_max_snap); END IF; IF v_actual_end_snap < v_min_snap OR v_actual_end_snap > v_max_snap THEN RAISE_APPLICATION_ERROR(-20003, 'End snapshot ' || v_actual_end_snap || ' not in range ' || v_min_snap || '-' || v_max_snap); END IF; IF v_actual_begin_snap >= v_actual_end_snap THEN RAISE_APPLICATION_ERROR(-20004, 'Begin snapshot must be less than end snapshot'); END IF; DBMS_OUTPUT.PUT_LINE('AWR validation successful'); DBMS_OUTPUT.PUT_LINE('Input range: ' || v_begin_snap || ' to ' || v_end_snap); DBMS_OUTPUT.PUT_LINE('Actual range: ' || v_actual_begin_snap || ' to ' || v_actual_end_snap); DBMS_OUTPUT.PUT_LINE('Available range: ' || v_min_snap || ' to ' || v_max_snap);EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for snapshot IDs'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
PROMPT Using snapshots: &actual_begin_snap to &actual_end_snapPROMPT
-- ------------------------------------------------------------------------------------- SNAPSHOT INFORMATION: Display snapshot time range and details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. AWR SNAPSHOT INFORMATIONPROMPT ============================
COLUMN snap_id FORMAT 999999COLUMN begin_time FORMAT A20COLUMN end_time FORMAT A20COLUMN duration_min FORMAT 999,999COLUMN instance FORMAT 999COLUMN snap_level FORMAT 999
SELECT s.snap_id, TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') as begin_time, TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, ROUND(EXTRACT(DAY FROM (s.end_interval_time - s.begin_interval_time)) * 24 * 60 + EXTRACT(HOUR FROM (s.end_interval_time - s.begin_interval_time)) * 60 + EXTRACT(MINUTE FROM (s.end_interval_time - s.begin_interval_time)) * 60 + EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)) / 60, 2) as duration_min, s.instance_number as instance, s.snap_levelFROM dba_hist_snapshot sWHERE s.snap_id BETWEEN &actual_begin_snap AND &actual_end_snapORDER BY s.snap_id;
-- ------------------------------------------------------------------------------------- SYSTEM STATISTICS SUMMARY: Key system metrics during snapshot period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SYSTEM STATISTICS SUMMARYPROMPT =============================
COLUMN stat_name FORMAT A40COLUMN begin_value FORMAT 999,999,999,999,999COLUMN end_value FORMAT 999,999,999,999,999COLUMN delta_value FORMAT 999,999,999,999COLUMN per_second FORMAT 999,999.99
SELECT stat_name, begin_value, end_value, (end_value - begin_value) as delta_value, ROUND((end_value - begin_value) / NULLIF(duration_seconds, 0), 2) as per_secondFROM ( SELECT s.stat_name, MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as begin_value, MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) as end_value, EXTRACT(DAY FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 60 + EXTRACT(SECOND FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) as duration_seconds FROM dba_hist_sysstat s JOIN dba_hist_snapshot s2 ON s.snap_id = s2.snap_id AND s.instance_number = s2.instance_number WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name IN ( 'CPU used by this session', 'DB time', 'DB CPU', 'logons cumulative', 'user commits', 'user rollbacks', 'physical reads', 'physical writes', 'redo size' ) GROUP BY s.stat_name)WHERE begin_value IS NOT NULL AND end_value IS NOT NULLORDER BY delta_value DESC;
-- ------------------------------------------------------------------------------------- TOP WAIT EVENTS: Most significant wait events during period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TOP WAIT EVENTS ANALYSISPROMPT ============================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20COLUMN pct_total_time FORMAT 999.99
SELECT event_name, total_waits, ROUND(total_time_waited_micro / 1000000, 2) as total_time_sec, ROUND((total_time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, wait_class, ROUND((total_time_waited_micro / SUM(total_time_waited_micro) OVER ()) * 100, 2) as pct_total_timeFROM ( SELECT e.event_name, e.wait_class, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.total_waits END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.total_waits END) as total_waits, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.time_waited_micro END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.time_waited_micro END) as total_time_waited_micro FROM dba_hist_system_event e WHERE e.snap_id IN (&actual_begin_snap, &actual_end_snap) AND e.wait_class != 'Idle' GROUP BY e.event_name, e.wait_class)WHERE total_waits > 0 AND total_time_waited_micro > 0ORDER BY total_time_waited_micro DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- TOP SQL BY ELAPSED TIME: Most time-consuming SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. TOP SQL BY ELAPSED TIMEPROMPT ===========================
COLUMN sql_id FORMAT A15COLUMN elapsed_time_sec FORMAT 999,999,999.99COLUMN cpu_time_sec FORMAT 999,999,999.99COLUMN executions FORMAT 999,999,999COLUMN avg_elapsed_ms FORMAT 999,999.99COLUMN sql_text FORMAT A50
SELECT sql_id, ROUND(elapsed_time_delta / 1000000, 2) as elapsed_time_sec, ROUND(cpu_time_delta / 1000000, 2) as cpu_time_sec, executions_delta as executions, ROUND((elapsed_time_delta / 1000) / NULLIF(executions_delta, 0), 2) as avg_elapsed_ms, (SELECT SUBSTR(sql_text, 1, 50) FROM dba_hist_sqltext WHERE sql_id = s.sql_id AND ROWNUM = 1) as sql_textFROM ( SELECT sql_id, SUM(CASE WHEN snap_id = &actual_end_snap THEN elapsed_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN elapsed_time_total END) as elapsed_time_delta, SUM(CASE WHEN snap_id = &actual_end_snap THEN cpu_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN cpu_time_total END) as cpu_time_delta, SUM(CASE WHEN snap_id = &actual_end_snap THEN executions_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN executions_total END) as executions_delta FROM dba_hist_sqlstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY sql_id) sWHERE elapsed_time_delta > 0 AND executions_delta > 0ORDER BY elapsed_time_delta DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INSTANCE EFFICIENCY METRICS: Database efficiency percentages-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. INSTANCE EFFICIENCY METRICSPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999.99COLUMN description FORMAT A50
WITH sysstats AS ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name)SELECT 'Buffer Cache Hit Ratio' as metric_name, ROUND((1 - (phy.value_delta - lob_phy.value_delta) / (ses.value_delta - lob_ses.value_delta)) * 100, 2) as value, 'Percentage of logical reads served from buffer cache' as descriptionFROM (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads') phy, (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads direct') direct_phy, (SELECT value_delta FROM sysstats WHERE stat_name = 'session logical reads') ses, (SELECT value_delta FROM sysstats WHERE stat_name = 'session logical reads - LOB') lob_ses, (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads - LOB') lob_phyWHERE ses.value_delta > lob_ses.value_deltaUNION ALLSELECT 'Library Cache Hit Ratio', ROUND((1 - (reloads.value_delta / pins.value_delta)) * 100, 2), 'Percentage of library cache requests served from memory'FROM (SELECT value_delta FROM sysstats WHERE stat_name = 'pin requests') pins, (SELECT value_delta FROM sysstats WHERE stat_name = 'reloads') reloadsWHERE pins.value_delta > 0UNION ALLSELECT 'Soft Parse Ratio', ROUND(((total_parses.value_delta - hard_parses.value_delta) / total_parses.value_delta) * 100, 2), 'Percentage of parses that were soft parses'FROM (SELECT value_delta FROM sysstats WHERE stat_name = 'parse count (total)') total_parses, (SELECT value_delta FROM sysstats WHERE stat_name = 'parse count (hard)') hard_parsesWHERE total_parses.value_delta > 0;
-- ------------------------------------------------------------------------------------- TIME MODEL STATISTICS: Where database time is spent-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. TIME MODEL STATISTICSPROMPT =========================
COLUMN stat_name FORMAT A40COLUMN time_sec FORMAT 999,999,999COLUMN pct_dbtime FORMAT 999.99COLUMN avg_per_sec FORMAT 999,999.99
SELECT stat_name, ROUND(time_micro / 1000000, 2) as time_sec, ROUND((time_micro / db_time.value) * 100, 2) as pct_dbtime, ROUND((time_micro / 1000000) / duration_sec, 2) as avg_per_secFROM ( SELECT s.stat_name, MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) - MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as time_micro FROM dba_hist_sys_time_model s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name IN ( 'DB time', 'DB CPU', 'sql execute elapsed time', 'parse time elapsed', 'hard parse elapsed time', 'PL/SQL execution elapsed time' ) GROUP BY s.stat_name) t,( SELECT MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) - MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as value FROM dba_hist_sys_time_model s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name = 'DB time') db_time,( SELECT EXTRACT(DAY FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 60 + EXTRACT(SECOND FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) as duration_sec FROM dba_hist_snapshot s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap)) dWHERE time_micro > 0ORDER BY time_micro DESC;
-- ------------------------------------------------------------------------------------- AWR REPORT GENERATION: Generate detailed AWR report-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. DETAILED AWR REPORT GENERATIONPROMPT ==================================
SET LONG 1000000SET LONGCHUNKSIZE 1000000SET PAGESIZE 0SET LINESIZE 200SET TRIMSPOOL ONSET TERMOUT OFF
-- Generate detailed HTML AWR reportDECLARE v_begin_snap NUMBER := &actual_begin_snap; v_end_snap NUMBER := &actual_end_snap; v_dbid NUMBER; v_inst_num NUMBER; v_report CLOB;
v_pos PLS_INTEGER := 1; v_chunk_size PLS_INTEGER := 32000; -- DBMS_OUTPUT limit-safe chunk v_len PLS_INTEGER;BEGIN -- Get DBID and instance number SELECT d.dbid, i.instance_number INTO v_dbid, v_inst_num FROM v$database d CROSS JOIN v$instance i;
DBMS_OUTPUT.PUT_LINE('Generating DETAILED HTML AWR report...'); v_report := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( l_dbid => v_dbid, l_inst_num => v_inst_num, l_bid => v_begin_snap, l_eid => v_end_snap );
IF v_report IS NOT NULL THEN v_len := LENGTH(v_report);
DBMS_OUTPUT.PUT_LINE('DETAILED AWR Report Generated Successfully'); DBMS_OUTPUT.PUT_LINE('Report length: ' || v_len || ' characters'); DBMS_OUTPUT.PUT_LINE('=======================');
-- Output full report in chunks so it lands in the spool file WHILE v_pos <= v_len LOOP DBMS_OUTPUT.PUT_LINE( DBMS_LOB.SUBSTR(v_report, v_chunk_size, v_pos) ); v_pos := v_pos + v_chunk_size; END LOOP;
DBMS_OUTPUT.PUT_LINE('======================='); DBMS_OUTPUT.PUT_LINE('End of DETAILED AWR report'); ELSE DBMS_OUTPUT.PUT_LINE('Error: AWR report generation returned NULL'); END IF;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating DETAILED AWR report: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Ensure you have proper privileges and valid snapshot IDs');END;/
-- Reset output settingsSET TERMOUT ONSET PAGESIZE 1000SET TRIMSPOOL OFF
-- ------------------------------------------------------------------------------------- SNAPSHOT RETENTION AND SETTINGS: AWR configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AWR CONFIGURATION SETTINGSPROMPT ==============================
COLUMN parameter FORMAT A30COLUMN value FORMAT A30COLUMN description FORMAT A50
SELECT 'Snapshot Retention (days)' as parameter, TO_CHAR(retention) as value, 'How long snapshots are maintained' as descriptionFROM dba_hist_wr_controlUNION ALLSELECT 'Snapshot Interval (minutes)', TO_CHAR(extract(minute from snap_interval) + extract(hour from snap_interval) * 60 + extract(day from snap_interval) * 24 * 60), 'Frequency of automatic snapshots'FROM dba_hist_wr_controlUNION ALLSELECT 'Current Snapshot Count', TO_CHAR(COUNT(*)), 'Number of snapshots in repository'FROM dba_hist_snapshotUNION ALLSELECT 'Oldest Snapshot', TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI:SS'), 'Date of oldest retained snapshot'FROM dba_hist_snapshotUNION ALLSELECT 'Latest Snapshot', TO_CHAR(MAX(end_interval_time), 'YYYY-MM-DD HH24:MI:SS'), 'Date of most recent snapshot'FROM dba_hist_snapshot;
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Actionable insights from AWR analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PERFORMANCE RECOMMENDATIONSPROMPT ===============================
COLUMN recommendation_type FORMAT A25COLUMN finding FORMAT A60COLUMN recommendation FORMAT A80COLUMN priority FORMAT A8
SELECT recommendation_type, finding, recommendation, priorityFROM ( SELECT 'Wait Event' as recommendation_type, 'High ' || event_name || ' waits detected' as finding, 'Investigate and tune: ' || event_name as recommendation, 'HIGH' as priority, 1 as sort_order FROM ( SELECT event_name, ROUND(total_time_waited_micro / 1000000, 2) as wait_seconds FROM ( SELECT e.event_name, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.time_waited_micro END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.time_waited_micro END) as total_time_waited_micro FROM dba_hist_system_event e WHERE e.snap_id IN (&actual_begin_snap, &actual_end_snap) AND e.wait_class != 'Idle' GROUP BY e.event_name ) WHERE total_time_waited_micro / 1000000 > 3600 -- More than 1 hour ORDER BY total_time_waited_micro DESC FETCH FIRST 1 ROWS ONLY ) UNION ALL SELECT 'SQL Tuning', 'High impact SQL identified: ' || sql_id, 'Analyze and optimize SQL execution plan', 'HIGH', 2 FROM ( SELECT sql_id, ROUND(elapsed_time_delta / 1000000, 2) as elapsed_seconds FROM ( SELECT sql_id, SUM(CASE WHEN snap_id = &actual_end_snap THEN elapsed_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN elapsed_time_total END) as elapsed_time_delta FROM dba_hist_sqlstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY sql_id ) WHERE elapsed_time_delta / 1000000 > 1800 -- More than 30 minutes ORDER BY elapsed_time_delta DESC FETCH FIRST 1 ROWS ONLY ) UNION ALL SELECT 'Memory', 'Buffer cache hit ratio below 90%', 'Consider increasing buffer cache size', 'MEDIUM', 3 FROM dual WHERE ( SELECT ROUND((1 - (phy.value_delta / ses.value_delta)) * 100, 2) FROM ( SELECT value_delta FROM ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name ) WHERE stat_name = 'physical reads' ) phy, ( SELECT value_delta FROM ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name ) WHERE stat_name = 'session logical reads' ) ses WHERE ses.value_delta > 0 ) < 90)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, sort_order;
-- ------------------------------------------------------------------------------------- COMPLETION: AWR analysis summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT AWR PERFORMANCE ANALYSIS COMPLETEDPROMPT =====================================PROMPT Snapshot Range: &actual_begin_snap to &actual_end_snapPROMPT Report Type: DETAILEDPROMPT Completion Time: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations immediatelyPROMPT 2. Analyze top wait events and SQL statementsPROMPT 3. Review detailed HTML AWR report for comprehensive analysisPROMPT 4. Schedule regular AWR reviews for performance trendingPROMPT 5. Review output file: &output_filenamePROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate AWR availability-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_AWR_Report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || &1 || '_' || &2 || '_DETAILED.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting AWR Performance Analyzer...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Begin Snap ID: &1PROMPT End Snap ID: &2PROMPT Report Type: DETAILEDPROMPT Timestamp: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT
-- First, determine the actual snapshot IDs to useCOLUMN actual_begin_snap NEW_VALUE actual_begin_snapCOLUMN actual_end_snap NEW_VALUE actual_end_snap
SELECT CASE WHEN &1 = -1 THEN max_snap - 2 WHEN &1 = 0 THEN max_snap ELSE &1 END as actual_begin_snap, CASE WHEN &2 = 0 THEN max_snap ELSE &2 END as actual_end_snapFROM ( SELECT MIN(snap_id) as min_snap, MAX(snap_id) as max_snap FROM dba_hist_snapshot WHERE startup_time = (SELECT MAX(startup_time) FROM dba_hist_snapshot));
-- Check AWR availability and validate parametersDECLARE v_begin_snap NUMBER := TO_NUMBER('&1'); v_end_snap NUMBER := TO_NUMBER('&2'); v_awr_available NUMBER; v_min_snap NUMBER; v_max_snap NUMBER; v_actual_begin_snap NUMBER := &actual_begin_snap; v_actual_end_snap NUMBER := &actual_end_snap;BEGIN -- Check if AWR is available SELECT COUNT(*) INTO v_awr_available FROM v$parameter WHERE name = 'statistics_level' AND value = 'TYPICAL'; IF v_awr_available = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'AWR not available - STATISTICS_LEVEL must be TYPICAL or ALL'); END IF; -- Get snapshot range for validation SELECT MIN(snap_id), MAX(snap_id) INTO v_min_snap, v_max_snap FROM dba_hist_snapshot WHERE startup_time = (SELECT MAX(startup_time) FROM dba_hist_snapshot); -- Validate the actual snapshot range IF v_actual_begin_snap < v_min_snap OR v_actual_begin_snap > v_max_snap THEN RAISE_APPLICATION_ERROR(-20002, 'Begin snapshot ' || v_actual_begin_snap || ' not in range ' || v_min_snap || '-' || v_max_snap); END IF; IF v_actual_end_snap < v_min_snap OR v_actual_end_snap > v_max_snap THEN RAISE_APPLICATION_ERROR(-20003, 'End snapshot ' || v_actual_end_snap || ' not in range ' || v_min_snap || '-' || v_max_snap); END IF; IF v_actual_begin_snap >= v_actual_end_snap THEN RAISE_APPLICATION_ERROR(-20004, 'Begin snapshot must be less than end snapshot'); END IF; DBMS_OUTPUT.PUT_LINE('AWR validation successful'); DBMS_OUTPUT.PUT_LINE('Input range: ' || v_begin_snap || ' to ' || v_end_snap); DBMS_OUTPUT.PUT_LINE('Actual range: ' || v_actual_begin_snap || ' to ' || v_actual_end_snap); DBMS_OUTPUT.PUT_LINE('Available range: ' || v_min_snap || ' to ' || v_max_snap);EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for snapshot IDs'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
PROMPT Using snapshots: &actual_begin_snap to &actual_end_snapPROMPT
-- ------------------------------------------------------------------------------------- SNAPSHOT INFORMATION: Display snapshot time range and details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. AWR SNAPSHOT INFORMATIONPROMPT ============================
COLUMN snap_id FORMAT 999999COLUMN begin_time FORMAT A20COLUMN end_time FORMAT A20COLUMN duration_min FORMAT 999,999COLUMN instance FORMAT 999COLUMN snap_level FORMAT 999
SELECT s.snap_id, TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') as begin_time, TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, ROUND(EXTRACT(DAY FROM (s.end_interval_time - s.begin_interval_time)) * 24 * 60 + EXTRACT(HOUR FROM (s.end_interval_time - s.begin_interval_time)) * 60 + EXTRACT(MINUTE FROM (s.end_interval_time - s.begin_interval_time)) * 60 + EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)) / 60, 2) as duration_min, s.instance_number as instance, s.snap_levelFROM dba_hist_snapshot sWHERE s.snap_id BETWEEN &actual_begin_snap AND &actual_end_snapORDER BY s.snap_id;
-- ------------------------------------------------------------------------------------- SYSTEM STATISTICS SUMMARY: Key system metrics during snapshot period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SYSTEM STATISTICS SUMMARYPROMPT =============================
COLUMN stat_name FORMAT A40COLUMN begin_value FORMAT 999,999,999,999,999COLUMN end_value FORMAT 999,999,999,999,999COLUMN delta_value FORMAT 999,999,999,999COLUMN per_second FORMAT 999,999.99
SELECT stat_name, begin_value, end_value, (end_value - begin_value) as delta_value, ROUND((end_value - begin_value) / NULLIF(duration_seconds, 0), 2) as per_secondFROM ( SELECT s.stat_name, MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as begin_value, MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) as end_value, EXTRACT(DAY FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) * 60 + EXTRACT(SECOND FROM (MAX(s2.end_interval_time) - MIN(s2.begin_interval_time))) as duration_seconds FROM dba_hist_sysstat s JOIN dba_hist_snapshot s2 ON s.snap_id = s2.snap_id AND s.instance_number = s2.instance_number WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name IN ( 'CPU used by this session', 'DB time', 'DB CPU', 'logons cumulative', 'user commits', 'user rollbacks', 'physical reads', 'physical writes', 'redo size' ) GROUP BY s.stat_name)WHERE begin_value IS NOT NULL AND end_value IS NOT NULLORDER BY delta_value DESC;
-- ------------------------------------------------------------------------------------- TOP WAIT EVENTS: Most significant wait events during period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TOP WAIT EVENTS ANALYSISPROMPT ============================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20COLUMN pct_total_time FORMAT 999.99
SELECT event_name, total_waits, ROUND(total_time_waited_micro / 1000000, 2) as total_time_sec, ROUND((total_time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, wait_class, ROUND((total_time_waited_micro / SUM(total_time_waited_micro) OVER ()) * 100, 2) as pct_total_timeFROM ( SELECT e.event_name, e.wait_class, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.total_waits END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.total_waits END) as total_waits, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.time_waited_micro END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.time_waited_micro END) as total_time_waited_micro FROM dba_hist_system_event e WHERE e.snap_id IN (&actual_begin_snap, &actual_end_snap) AND e.wait_class != 'Idle' GROUP BY e.event_name, e.wait_class)WHERE total_waits > 0 AND total_time_waited_micro > 0ORDER BY total_time_waited_micro DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- TOP SQL BY ELAPSED TIME: Most time-consuming SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. TOP SQL BY ELAPSED TIMEPROMPT ===========================
COLUMN sql_id FORMAT A15COLUMN elapsed_time_sec FORMAT 999,999,999.99COLUMN cpu_time_sec FORMAT 999,999,999.99COLUMN executions FORMAT 999,999,999COLUMN avg_elapsed_ms FORMAT 999,999.99COLUMN sql_text FORMAT A50
SELECT sql_id, ROUND(elapsed_time_delta / 1000000, 2) as elapsed_time_sec, ROUND(cpu_time_delta / 1000000, 2) as cpu_time_sec, executions_delta as executions, ROUND((elapsed_time_delta / 1000) / NULLIF(executions_delta, 0), 2) as avg_elapsed_ms, (SELECT SUBSTR(sql_text, 1, 50) FROM dba_hist_sqltext WHERE sql_id = s.sql_id AND ROWNUM = 1) as sql_textFROM ( SELECT sql_id, SUM(CASE WHEN snap_id = &actual_end_snap THEN elapsed_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN elapsed_time_total END) as elapsed_time_delta, SUM(CASE WHEN snap_id = &actual_end_snap THEN cpu_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN cpu_time_total END) as cpu_time_delta, SUM(CASE WHEN snap_id = &actual_end_snap THEN executions_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN executions_total END) as executions_delta FROM dba_hist_sqlstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY sql_id) sWHERE elapsed_time_delta > 0 AND executions_delta > 0ORDER BY elapsed_time_delta DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INSTANCE EFFICIENCY METRICS: Database efficiency percentages-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. INSTANCE EFFICIENCY METRICSPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999.99COLUMN description FORMAT A50
WITH sysstats AS ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name)SELECT 'Buffer Cache Hit Ratio' as metric_name, ROUND((1 - (phy.value_delta - lob_phy.value_delta) / (ses.value_delta - lob_ses.value_delta)) * 100, 2) as value, 'Percentage of logical reads served from buffer cache' as descriptionFROM (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads') phy, (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads direct') direct_phy, (SELECT value_delta FROM sysstats WHERE stat_name = 'session logical reads') ses, (SELECT value_delta FROM sysstats WHERE stat_name = 'session logical reads - LOB') lob_ses, (SELECT value_delta FROM sysstats WHERE stat_name = 'physical reads - LOB') lob_phyWHERE ses.value_delta > lob_ses.value_deltaUNION ALLSELECT 'Library Cache Hit Ratio', ROUND((1 - (reloads.value_delta / pins.value_delta)) * 100, 2), 'Percentage of library cache requests served from memory'FROM (SELECT value_delta FROM sysstats WHERE stat_name = 'pin requests') pins, (SELECT value_delta FROM sysstats WHERE stat_name = 'reloads') reloadsWHERE pins.value_delta > 0UNION ALLSELECT 'Soft Parse Ratio', ROUND(((total_parses.value_delta - hard_parses.value_delta) / total_parses.value_delta) * 100, 2), 'Percentage of parses that were soft parses'FROM (SELECT value_delta FROM sysstats WHERE stat_name = 'parse count (total)') total_parses, (SELECT value_delta FROM sysstats WHERE stat_name = 'parse count (hard)') hard_parsesWHERE total_parses.value_delta > 0;
-- ------------------------------------------------------------------------------------- TIME MODEL STATISTICS: Where database time is spent-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. TIME MODEL STATISTICSPROMPT =========================
COLUMN stat_name FORMAT A40COLUMN time_sec FORMAT 999,999,999COLUMN pct_dbtime FORMAT 999.99COLUMN avg_per_sec FORMAT 999,999.99
SELECT stat_name, ROUND(time_micro / 1000000, 2) as time_sec, ROUND((time_micro / db_time.value) * 100, 2) as pct_dbtime, ROUND((time_micro / 1000000) / duration_sec, 2) as avg_per_secFROM ( SELECT s.stat_name, MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) - MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as time_micro FROM dba_hist_sys_time_model s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name IN ( 'DB time', 'DB CPU', 'sql execute elapsed time', 'parse time elapsed', 'hard parse elapsed time', 'PL/SQL execution elapsed time' ) GROUP BY s.stat_name) t,( SELECT MAX(CASE WHEN s.snap_id = &actual_end_snap THEN s.value END) - MIN(CASE WHEN s.snap_id = &actual_begin_snap THEN s.value END) as value FROM dba_hist_sys_time_model s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap) AND s.stat_name = 'DB time') db_time,( SELECT EXTRACT(DAY FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) * 60 + EXTRACT(SECOND FROM (MAX(s.end_interval_time) - MIN(s.begin_interval_time))) as duration_sec FROM dba_hist_snapshot s WHERE s.snap_id IN (&actual_begin_snap, &actual_end_snap)) dWHERE time_micro > 0ORDER BY time_micro DESC;
-- ------------------------------------------------------------------------------------- AWR REPORT GENERATION: Generate detailed AWR report-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. DETAILED AWR REPORT GENERATIONPROMPT ==================================
SET LONG 1000000SET LONGCHUNKSIZE 1000000SET PAGESIZE 0SET LINESIZE 200SET TRIMSPOOL ONSET TERMOUT OFF
-- Generate detailed HTML AWR reportDECLARE v_begin_snap NUMBER := &actual_begin_snap; v_end_snap NUMBER := &actual_end_snap; v_dbid NUMBER; v_inst_num NUMBER; v_report CLOB;
v_pos PLS_INTEGER := 1; v_chunk_size PLS_INTEGER := 32000; -- DBMS_OUTPUT limit-safe chunk v_len PLS_INTEGER;BEGIN -- Get DBID and instance number SELECT d.dbid, i.instance_number INTO v_dbid, v_inst_num FROM v$database d CROSS JOIN v$instance i;
DBMS_OUTPUT.PUT_LINE('Generating DETAILED HTML AWR report...'); v_report := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( l_dbid => v_dbid, l_inst_num => v_inst_num, l_bid => v_begin_snap, l_eid => v_end_snap );
IF v_report IS NOT NULL THEN v_len := LENGTH(v_report);
DBMS_OUTPUT.PUT_LINE('DETAILED AWR Report Generated Successfully'); DBMS_OUTPUT.PUT_LINE('Report length: ' || v_len || ' characters'); DBMS_OUTPUT.PUT_LINE('=======================');
-- Output full report in chunks so it lands in the spool file WHILE v_pos <= v_len LOOP DBMS_OUTPUT.PUT_LINE( DBMS_LOB.SUBSTR(v_report, v_chunk_size, v_pos) ); v_pos := v_pos + v_chunk_size; END LOOP;
DBMS_OUTPUT.PUT_LINE('======================='); DBMS_OUTPUT.PUT_LINE('End of DETAILED AWR report'); ELSE DBMS_OUTPUT.PUT_LINE('Error: AWR report generation returned NULL'); END IF;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating DETAILED AWR report: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Ensure you have proper privileges and valid snapshot IDs');END;/
-- Reset output settingsSET TERMOUT ONSET PAGESIZE 1000SET TRIMSPOOL OFF
-- ------------------------------------------------------------------------------------- SNAPSHOT RETENTION AND SETTINGS: AWR configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AWR CONFIGURATION SETTINGSPROMPT ==============================
COLUMN parameter FORMAT A30COLUMN value FORMAT A30COLUMN description FORMAT A50
SELECT 'Snapshot Retention (days)' as parameter, TO_CHAR(retention) as value, 'How long snapshots are maintained' as descriptionFROM dba_hist_wr_controlUNION ALLSELECT 'Snapshot Interval (minutes)', TO_CHAR(extract(minute from snap_interval) + extract(hour from snap_interval) * 60 + extract(day from snap_interval) * 24 * 60), 'Frequency of automatic snapshots'FROM dba_hist_wr_controlUNION ALLSELECT 'Current Snapshot Count', TO_CHAR(COUNT(*)), 'Number of snapshots in repository'FROM dba_hist_snapshotUNION ALLSELECT 'Oldest Snapshot', TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI:SS'), 'Date of oldest retained snapshot'FROM dba_hist_snapshotUNION ALLSELECT 'Latest Snapshot', TO_CHAR(MAX(end_interval_time), 'YYYY-MM-DD HH24:MI:SS'), 'Date of most recent snapshot'FROM dba_hist_snapshot;
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Actionable insights from AWR analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PERFORMANCE RECOMMENDATIONSPROMPT ===============================
COLUMN recommendation_type FORMAT A25COLUMN finding FORMAT A60COLUMN recommendation FORMAT A80COLUMN priority FORMAT A8
SELECT recommendation_type, finding, recommendation, priorityFROM ( SELECT 'Wait Event' as recommendation_type, 'High ' || event_name || ' waits detected' as finding, 'Investigate and tune: ' || event_name as recommendation, 'HIGH' as priority, 1 as sort_order FROM ( SELECT event_name, ROUND(total_time_waited_micro / 1000000, 2) as wait_seconds FROM ( SELECT e.event_name, MAX(CASE WHEN e.snap_id = &actual_end_snap THEN e.time_waited_micro END) - MIN(CASE WHEN e.snap_id = &actual_begin_snap THEN e.time_waited_micro END) as total_time_waited_micro FROM dba_hist_system_event e WHERE e.snap_id IN (&actual_begin_snap, &actual_end_snap) AND e.wait_class != 'Idle' GROUP BY e.event_name ) WHERE total_time_waited_micro / 1000000 > 3600 -- More than 1 hour ORDER BY total_time_waited_micro DESC FETCH FIRST 1 ROWS ONLY ) UNION ALL SELECT 'SQL Tuning', 'High impact SQL identified: ' || sql_id, 'Analyze and optimize SQL execution plan', 'HIGH', 2 FROM ( SELECT sql_id, ROUND(elapsed_time_delta / 1000000, 2) as elapsed_seconds FROM ( SELECT sql_id, SUM(CASE WHEN snap_id = &actual_end_snap THEN elapsed_time_total END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN elapsed_time_total END) as elapsed_time_delta FROM dba_hist_sqlstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY sql_id ) WHERE elapsed_time_delta / 1000000 > 1800 -- More than 30 minutes ORDER BY elapsed_time_delta DESC FETCH FIRST 1 ROWS ONLY ) UNION ALL SELECT 'Memory', 'Buffer cache hit ratio below 90%', 'Consider increasing buffer cache size', 'MEDIUM', 3 FROM dual WHERE ( SELECT ROUND((1 - (phy.value_delta / ses.value_delta)) * 100, 2) FROM ( SELECT value_delta FROM ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name ) WHERE stat_name = 'physical reads' ) phy, ( SELECT value_delta FROM ( SELECT stat_name, SUM(CASE WHEN snap_id = &actual_end_snap THEN value END) - SUM(CASE WHEN snap_id = &actual_begin_snap THEN value END) as value_delta FROM dba_hist_sysstat WHERE snap_id IN (&actual_begin_snap, &actual_end_snap) GROUP BY stat_name ) WHERE stat_name = 'session logical reads' ) ses WHERE ses.value_delta > 0 ) < 90)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, sort_order;
-- ------------------------------------------------------------------------------------- COMPLETION: AWR analysis summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT AWR PERFORMANCE ANALYSIS COMPLETEDPROMPT =====================================PROMPT Snapshot Range: &actual_begin_snap to &actual_end_snapPROMPT Report Type: DETAILEDPROMPT Completion Time: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations immediatelyPROMPT 2. Analyze top wait events and SQL statementsPROMPT 3. Review detailed HTML AWR report for comprehensive analysisPROMPT 4. Schedule regular AWR reviews for performance trendingPROMPT 5. Review output file: &output_filenamePROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------