database_performance_baseline.sql
-- ------------------------------------------------------------------------------------- File Name : database_performance_baseline.sql-- Author : Pierre Montbleau-- Description : Oracle database performance baseline creation and comparison utility-- Purpose : Establish performance baselines, track changes, and identify performance deviations-- Call Syntax : @F:\DBA\Scripts\database_performance_baseline.sql (action) (baseline_name) (comparison_period)-- Parameters : action - Baseline action (CREATE, COMPARE, LIST, DELETE, REPORT)-- baseline_name - Name for the baseline (or existing baseline for comparison)-- comparison_period - Period for comparison in days (default: 7)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR 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_performance_baseline_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.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 Database Performance Baseline Manager...PROMPT ================================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Baseline Name: &2PROMPT Comparison Period: &3 daysPROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_baseline_name VARCHAR2(50) := '&2'; v_comparison_period NUMBER := NVL(TO_NUMBER('&3'), 7);BEGIN IF v_action NOT IN ('CREATE', 'COMPARE', 'LIST', 'DELETE', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be CREATE, COMPARE, LIST, DELETE, or REPORT'); END IF; IF v_action IN ('CREATE', 'COMPARE', 'DELETE') AND v_baseline_name IS NULL THEN RAISE_APPLICATION_ERROR(-20002, 'Baseline name required for CREATE, COMPARE, and DELETE actions'); END IF; IF v_comparison_period < 1 OR v_comparison_period > 365 THEN RAISE_APPLICATION_ERROR(-20003, 'Comparison period must be between 1 and 365 days'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for comparison period'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- EXISTING BASELINES: List current performance baselines-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. EXISTING PERFORMANCE BASELINESPROMPT ==================================
COLUMN baseline_name FORMAT A30COLUMN baseline_type FORMAT A15COLUMN start_time FORMAT A20COLUMN end_time FORMAT A20COLUMN creation_time FORMAT A20
SELECT baseline_name, baseline_type, TO_CHAR(start_snap_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(end_snap_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, TO_CHAR(creation_time, 'YYYY-MM-DD HH24:MI:SS') as creation_timeFROM dba_hist_baselineORDER BY creation_time DESC;
-- ------------------------------------------------------------------------------------- SYSTEM METRICS BASELINE: Capture current system performance metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CURRENT SYSTEM PERFORMANCE METRICSPROMPT ======================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20COLUMN baseline_value FORMAT 999,999,999.99COLUMN deviation_pct FORMAT 999.99
SELECT metric_name, value as current_value, metric_unit, NULL as baseline_value, NULL as deviation_pctFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Physical Writes Per Sec', 'Average Active Sessions', 'Current Logons Count' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- WAIT EVENT BASELINE: Current wait event statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. CURRENT WAIT EVENT STATISTICSPROMPT =================================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20
SELECT event as event_name, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, ROUND((time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, wait_classFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0ORDER BY time_waited_micro DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE BASELINE: Current memory configuration and usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY USAGE BASELINEPROMPT =========================
COLUMN memory_component FORMAT A25COLUMN allocated_gb FORMAT 999,999.99COLUMN used_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN recommendation FORMAT A60
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pct, 'Monitor SGA usage trends' as recommendationFROM v$sgaUNION ALLSELECT 'Buffer Cache', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'Consider increasing buffer cache' ELSE 'Buffer cache utilization normal' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')UNION ALLSELECT 'Shared Pool', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 85 THEN 'Monitor shared pool fragmentation' ELSE 'Shared pool utilization normal' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'shared pool')ORDER BY 1; -- Use column position instead of alias
-- ------------------------------------------------------------------------------------- BASELINE CREATION: Create new performance baseline-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. PERFORMANCE BASELINE CREATIONPROMPT =================================
BEGIN IF UPPER('&1') = 'CREATE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_start_snap_id NUMBER; v_end_snap_id NUMBER; v_baseline_count NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Creating performance baseline: ' || v_baseline_name); -- Get latest snapshot IDs SELECT MAX(snap_id) - 1, MAX(snap_id) INTO v_start_snap_id, v_end_snap_id FROM dba_hist_snapshot; -- Check if baseline already exists SELECT COUNT(*) INTO v_baseline_count FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_count > 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' already exists'); DBMS_OUTPUT.PUT_LINE('Use different name or DELETE existing baseline first'); ELSE -- Create baseline using AWR DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id => v_start_snap_id, end_snap_id => v_end_snap_id, baseline_name => v_baseline_name, dbid => NULL, expiration => 365 ); DBMS_OUTPUT.PUT_LINE('Performance baseline created successfully'); DBMS_OUTPUT.PUT_LINE('Start Snapshot: ' || v_start_snap_id); DBMS_OUTPUT.PUT_LINE('End Snapshot: ' || v_end_snap_id); DBMS_OUTPUT.PUT_LINE('Baseline will expire in 365 days'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating performance baseline: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline creation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SQL PERFORMANCE BASELINE: Top SQL statements by resource usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. TOP SQL PERFORMANCE BASELINEPROMPT =================================
COLUMN sql_id FORMAT A15COLUMN cpu_time_sec FORMAT 999,999,999.99COLUMN elapsed_time_sec FORMAT 999,999,999.99COLUMN executions FORMAT 999,999,999COLUMN buffer_gets FORMAT 999,999,999,999COLUMN sql_text FORMAT A50
SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_time_sec, ROUND(elapsed_time/1000000, 2) as elapsed_time_sec, executions, buffer_gets, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND executions > 0ORDER BY cpu_time DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS TREND: Historical performance trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. PERFORMANCE METRICS TREND ANALYSISPROMPT ======================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN avg_7day FORMAT 999,999,999.99COLUMN avg_30day FORMAT 999,999,999.99COLUMN trend FORMAT A15
SELECT metric_name, value as current_value, NULL as avg_7day, NULL as avg_30day, 'N/A' as trendFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Physical Reads Per Sec', 'Average Active Sessions' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- BASELINE COMPARISON: Compare current performance with baseline-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. BASELINE COMPARISON ANALYSISPROMPT =================================
BEGIN IF UPPER('&1') = 'COMPARE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_comparison_period NUMBER := &3; v_baseline_exists NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Comparing current performance with baseline: ' || v_baseline_name); DBMS_OUTPUT.PUT_LINE('Comparison period: ' || v_comparison_period || ' days'); -- Check if baseline exists SELECT COUNT(*) INTO v_baseline_exists FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_exists = 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' not found'); DBMS_OUTPUT.PUT_LINE('Available baselines:'); FOR bl_rec IN (SELECT baseline_name FROM dba_hist_baseline) LOOP DBMS_OUTPUT.PUT_LINE(' - ' || bl_rec.baseline_name); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Baseline comparison would be performed here'); DBMS_OUTPUT.PUT_LINE('This would compare:'); DBMS_OUTPUT.PUT_LINE(' - System metrics (CPU, memory, I/O)'); DBMS_OUTPUT.PUT_LINE(' - Wait event statistics'); DBMS_OUTPUT.PUT_LINE(' - SQL performance characteristics'); DBMS_OUTPUT.PUT_LINE(' - Memory usage patterns'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: Full comparison requires AWR data and DBMS_WORKLOAD_REPOSITORY'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during baseline comparison: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline comparison skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE DEVIATIONS: Identify significant performance changes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PERFORMANCE DEVIATION ANALYSISPROMPT ===================================
COLUMN performance_metric FORMAT A30COLUMN current_value FORMAT 999,999,999.99COLUMN expected_range FORMAT A20COLUMN deviation_pct FORMAT 999.99COLUMN severity FORMAT A10
SELECT 'CPU Usage (%)' as performance_metric, (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) as current_value, '70-85%' as expected_range, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 25.0 WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 85 THEN 15.0 ELSE 0.0 END as deviation_pct, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'HIGH' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 85 THEN 'MEDIUM' ELSE 'LOW' END as severityFROM dualUNION ALLSELECT 'Physical Reads/sec', (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2), '< 1000', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 2000 THEN 100.0 WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 50.0 ELSE 0.0 END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 2000 THEN 'HIGH' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 'MEDIUM' ELSE 'LOW' ENDFROM dualORDER BY severity DESC;
-- ------------------------------------------------------------------------------------- BASELINE MAINTENANCE: Baseline management operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. BASELINE MAINTENANCE OPERATIONSPROMPT ====================================
BEGIN IF UPPER('&1') = 'DELETE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_baseline_exists NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Deleting performance baseline: ' || v_baseline_name); -- Check if baseline exists SELECT COUNT(*) INTO v_baseline_exists FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_exists = 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' not found'); ELSE -- Drop the baseline DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE( baseline_name => v_baseline_name, cascade => FALSE ); DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' deleted successfully'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting baseline: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline maintenance skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Actionable insights from baseline analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. PERFORMANCE OPTIMIZATION RECOMMENDATIONSPROMPT =============================================
COLUMN recommendation FORMAT A60COLUMN category FORMAT A15COLUMN priority FORMAT A10COLUMN estimated_impact FORMAT A20COLUMN effort FORMAT A15
SELECT 'Optimize high CPU SQL statements' as recommendation, 'SQL TUNING' as category, 'HIGH' as priority, 'HIGH' as estimated_impact, 'MEDIUM' as effortFROM dualWHERE EXISTS ( SELECT 1 FROM v$sqlstats WHERE ROUND(cpu_time/1000000, 2) > 1000)UNION ALLSELECT 'Increase buffer cache size', 'MEMORY', 'MEDIUM', 'MEDIUM', 'LOW'FROM dualWHERE (SELECT ROUND((bytes - free_memory) / NULLIF(bytes, 0) * 100, 2) FROM (SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')) > 90UNION ALLSELECT 'Investigate I/O wait events', 'I/O TUNING', 'HIGH', 'HIGH', 'HIGH'FROM dualWHERE EXISTS ( SELECT 1 FROM v$system_event WHERE wait_class = 'User I/O' AND ROUND(time_waited_micro / 1000000, 2) > 3600)UNION ALLSELECT 'Review and update statistics', 'MAINTENANCE', 'MEDIUM', 'MEDIUM', 'LOW'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_tables WHERE last_analyzed IS NULL OR last_analyzed < SYSDATE - 30)ORDER BY 3, 5; -- Column position for effort
-- ------------------------------------------------------------------------------------- COMPLETION: Performance baseline summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================PROMPT PERFORMANCE BASELINE MANAGEMENT COMPLETEDPROMPT ================================================PROMPT Action: &1PROMPT Baseline Name: &2PROMPT Comparison Period: &3 daysPROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Monitor performance deviations from baselinePROMPT 2. Schedule regular baseline updatesPROMPT 3. Investigate HIGH priority recommendationsPROMPT 4. Document performance trends and changesPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR 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_performance_baseline_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.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 Database Performance Baseline Manager...PROMPT ================================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Baseline Name: &2PROMPT Comparison Period: &3 daysPROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_baseline_name VARCHAR2(50) := '&2'; v_comparison_period NUMBER := NVL(TO_NUMBER('&3'), 7);BEGIN IF v_action NOT IN ('CREATE', 'COMPARE', 'LIST', 'DELETE', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be CREATE, COMPARE, LIST, DELETE, or REPORT'); END IF; IF v_action IN ('CREATE', 'COMPARE', 'DELETE') AND v_baseline_name IS NULL THEN RAISE_APPLICATION_ERROR(-20002, 'Baseline name required for CREATE, COMPARE, and DELETE actions'); END IF; IF v_comparison_period < 1 OR v_comparison_period > 365 THEN RAISE_APPLICATION_ERROR(-20003, 'Comparison period must be between 1 and 365 days'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for comparison period'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- EXISTING BASELINES: List current performance baselines-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. EXISTING PERFORMANCE BASELINESPROMPT ==================================
COLUMN baseline_name FORMAT A30COLUMN baseline_type FORMAT A15COLUMN start_time FORMAT A20COLUMN end_time FORMAT A20COLUMN creation_time FORMAT A20
SELECT baseline_name, baseline_type, TO_CHAR(start_snap_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(end_snap_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, TO_CHAR(creation_time, 'YYYY-MM-DD HH24:MI:SS') as creation_timeFROM dba_hist_baselineORDER BY creation_time DESC;
-- ------------------------------------------------------------------------------------- SYSTEM METRICS BASELINE: Capture current system performance metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CURRENT SYSTEM PERFORMANCE METRICSPROMPT ======================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20COLUMN baseline_value FORMAT 999,999,999.99COLUMN deviation_pct FORMAT 999.99
SELECT metric_name, value as current_value, metric_unit, NULL as baseline_value, NULL as deviation_pctFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Physical Writes Per Sec', 'Average Active Sessions', 'Current Logons Count' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- WAIT EVENT BASELINE: Current wait event statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. CURRENT WAIT EVENT STATISTICSPROMPT =================================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20
SELECT event as event_name, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, ROUND((time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, wait_classFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0ORDER BY time_waited_micro DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE BASELINE: Current memory configuration and usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY USAGE BASELINEPROMPT =========================
COLUMN memory_component FORMAT A25COLUMN allocated_gb FORMAT 999,999.99COLUMN used_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN recommendation FORMAT A60
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pct, 'Monitor SGA usage trends' as recommendationFROM v$sgaUNION ALLSELECT 'Buffer Cache', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'Consider increasing buffer cache' ELSE 'Buffer cache utilization normal' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')UNION ALLSELECT 'Shared Pool', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 85 THEN 'Monitor shared pool fragmentation' ELSE 'Shared pool utilization normal' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'shared pool')ORDER BY 1; -- Use column position instead of alias
-- ------------------------------------------------------------------------------------- BASELINE CREATION: Create new performance baseline-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. PERFORMANCE BASELINE CREATIONPROMPT =================================
BEGIN IF UPPER('&1') = 'CREATE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_start_snap_id NUMBER; v_end_snap_id NUMBER; v_baseline_count NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Creating performance baseline: ' || v_baseline_name); -- Get latest snapshot IDs SELECT MAX(snap_id) - 1, MAX(snap_id) INTO v_start_snap_id, v_end_snap_id FROM dba_hist_snapshot; -- Check if baseline already exists SELECT COUNT(*) INTO v_baseline_count FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_count > 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' already exists'); DBMS_OUTPUT.PUT_LINE('Use different name or DELETE existing baseline first'); ELSE -- Create baseline using AWR DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id => v_start_snap_id, end_snap_id => v_end_snap_id, baseline_name => v_baseline_name, dbid => NULL, expiration => 365 ); DBMS_OUTPUT.PUT_LINE('Performance baseline created successfully'); DBMS_OUTPUT.PUT_LINE('Start Snapshot: ' || v_start_snap_id); DBMS_OUTPUT.PUT_LINE('End Snapshot: ' || v_end_snap_id); DBMS_OUTPUT.PUT_LINE('Baseline will expire in 365 days'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating performance baseline: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline creation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SQL PERFORMANCE BASELINE: Top SQL statements by resource usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. TOP SQL PERFORMANCE BASELINEPROMPT =================================
COLUMN sql_id FORMAT A15COLUMN cpu_time_sec FORMAT 999,999,999.99COLUMN elapsed_time_sec FORMAT 999,999,999.99COLUMN executions FORMAT 999,999,999COLUMN buffer_gets FORMAT 999,999,999,999COLUMN sql_text FORMAT A50
SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_time_sec, ROUND(elapsed_time/1000000, 2) as elapsed_time_sec, executions, buffer_gets, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND executions > 0ORDER BY cpu_time DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS TREND: Historical performance trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. PERFORMANCE METRICS TREND ANALYSISPROMPT ======================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN avg_7day FORMAT 999,999,999.99COLUMN avg_30day FORMAT 999,999,999.99COLUMN trend FORMAT A15
SELECT metric_name, value as current_value, NULL as avg_7day, NULL as avg_30day, 'N/A' as trendFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Physical Reads Per Sec', 'Average Active Sessions' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- BASELINE COMPARISON: Compare current performance with baseline-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. BASELINE COMPARISON ANALYSISPROMPT =================================
BEGIN IF UPPER('&1') = 'COMPARE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_comparison_period NUMBER := &3; v_baseline_exists NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Comparing current performance with baseline: ' || v_baseline_name); DBMS_OUTPUT.PUT_LINE('Comparison period: ' || v_comparison_period || ' days'); -- Check if baseline exists SELECT COUNT(*) INTO v_baseline_exists FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_exists = 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' not found'); DBMS_OUTPUT.PUT_LINE('Available baselines:'); FOR bl_rec IN (SELECT baseline_name FROM dba_hist_baseline) LOOP DBMS_OUTPUT.PUT_LINE(' - ' || bl_rec.baseline_name); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Baseline comparison would be performed here'); DBMS_OUTPUT.PUT_LINE('This would compare:'); DBMS_OUTPUT.PUT_LINE(' - System metrics (CPU, memory, I/O)'); DBMS_OUTPUT.PUT_LINE(' - Wait event statistics'); DBMS_OUTPUT.PUT_LINE(' - SQL performance characteristics'); DBMS_OUTPUT.PUT_LINE(' - Memory usage patterns'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: Full comparison requires AWR data and DBMS_WORKLOAD_REPOSITORY'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during baseline comparison: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline comparison skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE DEVIATIONS: Identify significant performance changes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PERFORMANCE DEVIATION ANALYSISPROMPT ===================================
COLUMN performance_metric FORMAT A30COLUMN current_value FORMAT 999,999,999.99COLUMN expected_range FORMAT A20COLUMN deviation_pct FORMAT 999.99COLUMN severity FORMAT A10
SELECT 'CPU Usage (%)' as performance_metric, (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) as current_value, '70-85%' as expected_range, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 25.0 WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 85 THEN 15.0 ELSE 0.0 END as deviation_pct, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'HIGH' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 85 THEN 'MEDIUM' ELSE 'LOW' END as severityFROM dualUNION ALLSELECT 'Physical Reads/sec', (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2), '< 1000', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 2000 THEN 100.0 WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 50.0 ELSE 0.0 END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 2000 THEN 'HIGH' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 'MEDIUM' ELSE 'LOW' ENDFROM dualORDER BY severity DESC;
-- ------------------------------------------------------------------------------------- BASELINE MAINTENANCE: Baseline management operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. BASELINE MAINTENANCE OPERATIONSPROMPT ====================================
BEGIN IF UPPER('&1') = 'DELETE' THEN DECLARE v_baseline_name VARCHAR2(50) := '&2'; v_baseline_exists NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('Deleting performance baseline: ' || v_baseline_name); -- Check if baseline exists SELECT COUNT(*) INTO v_baseline_exists FROM dba_hist_baseline WHERE baseline_name = v_baseline_name; IF v_baseline_exists = 0 THEN DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' not found'); ELSE -- Drop the baseline DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE( baseline_name => v_baseline_name, cascade => FALSE ); DBMS_OUTPUT.PUT_LINE('Baseline ' || v_baseline_name || ' deleted successfully'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting baseline: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Baseline maintenance skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Actionable insights from baseline analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. PERFORMANCE OPTIMIZATION RECOMMENDATIONSPROMPT =============================================
COLUMN recommendation FORMAT A60COLUMN category FORMAT A15COLUMN priority FORMAT A10COLUMN estimated_impact FORMAT A20COLUMN effort FORMAT A15
SELECT 'Optimize high CPU SQL statements' as recommendation, 'SQL TUNING' as category, 'HIGH' as priority, 'HIGH' as estimated_impact, 'MEDIUM' as effortFROM dualWHERE EXISTS ( SELECT 1 FROM v$sqlstats WHERE ROUND(cpu_time/1000000, 2) > 1000)UNION ALLSELECT 'Increase buffer cache size', 'MEMORY', 'MEDIUM', 'MEDIUM', 'LOW'FROM dualWHERE (SELECT ROUND((bytes - free_memory) / NULLIF(bytes, 0) * 100, 2) FROM (SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')) > 90UNION ALLSELECT 'Investigate I/O wait events', 'I/O TUNING', 'HIGH', 'HIGH', 'HIGH'FROM dualWHERE EXISTS ( SELECT 1 FROM v$system_event WHERE wait_class = 'User I/O' AND ROUND(time_waited_micro / 1000000, 2) > 3600)UNION ALLSELECT 'Review and update statistics', 'MAINTENANCE', 'MEDIUM', 'MEDIUM', 'LOW'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_tables WHERE last_analyzed IS NULL OR last_analyzed < SYSDATE - 30)ORDER BY 3, 5; -- Column position for effort
-- ------------------------------------------------------------------------------------- COMPLETION: Performance baseline summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================PROMPT PERFORMANCE BASELINE MANAGEMENT COMPLETEDPROMPT ================================================PROMPT Action: &1PROMPT Baseline Name: &2PROMPT Comparison Period: &3 daysPROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Monitor performance deviations from baselinePROMPT 2. Schedule regular baseline updatesPROMPT 3. Investigate HIGH priority recommendationsPROMPT 4. Document performance trends and changesPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------