sql_performance_analyzer.sql
-- ------------------------------------------------------------------------------------- File Name : sql_performance_analyzer.sql-- Author : Pierre MontbleAU-- Description : Comprehensive SQL performance analysis and tuning utility-- Purpose : Identify, analyze, and provide tuning recommendations for SQL statements-- Call Syntax : @F:\DBA\Scripts\sql_performance_analyzer.sql (sql_id) (analysis_type) (top_n)-- Parameters : sql_id - Specific SQL ID to analyze or 'TOP' for top SQL-- analysis_type - Type of analysis (EXECUTION, PLAN, STATS, WAITS, ALL)-- top_n - Number of top SQL to display when sql_id='TOP' (default: 10)-- 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_sql_performance_analyzer_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
TIMING START sql_analysis
PROMPT Starting SQL Performance Analyzer...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT SQL ID: &1PROMPT Analysis Type: &2PROMPT Top N: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_sql_id VARCHAR2(20) := UPPER('&1'); v_analysis_type VARCHAR2(20) := UPPER(NVL('&2', 'ALL')); v_top_n NUMBER := NVL(TO_NUMBER('&3'), 10); v_sql_exists NUMBER;BEGIN IF v_analysis_type NOT IN ('EXECUTION', 'PLAN', 'STATS', 'WAITS', 'ALL') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be EXECUTION, PLAN, STATS, WAITS, or ALL'); END IF; IF v_top_n < 1 OR v_top_n > 100 THEN RAISE_APPLICATION_ERROR(-20002, 'Top N must be between 1 and 100'); END IF; IF v_sql_id != 'TOP' THEN SELECT COUNT(*) INTO v_sql_exists FROM v$sql WHERE sql_id = v_sql_id; IF v_sql_exists = 0 THEN RAISE_APPLICATION_ERROR(-20003, 'SQL ID ' || v_sql_id || ' not found in shared pool'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for Top N'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TOP SQL BY RESOURCE CONSUMPTION: Identify high-impact SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. TOP &3 SQL BY RESOURCE CONSUMPTIONPROMPT ======================================
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 cpu_per_exec FORMAT 999,999.99COLUMN buffer_gets FORMAT 999,999,999,999COLUMN disk_reads FORMAT 999,999,999,999COLUMN sql_text FORMAT A50
BEGIN IF UPPER('&1') = 'TOP' THEN DBMS_OUTPUT.PUT_LINE('Displaying top ' || '&3' || ' SQL statements by resource consumption...'); END IF;END;/
SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_time_sec, ROUND(elapsed_time/1000000, 2) as elapsed_time_sec, executions, ROUND((cpu_time/1000000)/NULLIF(executions, 0), 2) as cpu_per_exec, buffer_gets, disk_reads, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE UPPER('&1') = 'TOP' AND cpu_time > 0ORDER BY cpu_time DESCFETCH FIRST &3 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SQL EXECUTION STATISTICS: Detailed execution metrics for specific SQL-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SQL EXECUTION STATISTICSPROMPT ============================
COLUMN metric FORMAT A30COLUMN value FORMAT A30COLUMN per_execution FORMAT A20
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('EXECUTION', 'STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution statistics for SQL ID: ' || '&1'); END IF;END;/
SELECT 'SQL Text' as metric, SUBSTR(sql_text, 1, 100) as value, '' as per_executionFROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Executions', TO_CHAR(executions), 'N/A'FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'CPU Time (seconds)', TO_CHAR(ROUND(cpu_time/1000000, 2)), TO_CHAR(ROUND((cpu_time/1000000)/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Elapsed Time (seconds)', TO_CHAR(ROUND(elapsed_time/1000000, 2)), TO_CHAR(ROUND((elapsed_time/1000000)/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Buffer Gets', TO_CHAR(buffer_gets), TO_CHAR(ROUND(buffer_gets/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Disk Reads', TO_CHAR(disk_reads), TO_CHAR(ROUND(disk_reads/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Rows Processed', TO_CHAR(rows_processed), TO_CHAR(ROUND(rows_processed/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Parse Calls', TO_CHAR(parse_calls), TO_CHAR(ROUND(parse_calls/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1ORDER BY 1;
-- ------------------------------------------------------------------------------------- EXECUTION PLAN ANALYSIS: Display and analyze execution plan-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. EXECUTION PLAN ANALYSISPROMPT ===========================
COLUMN plan_line FORMAT A200
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('PLAN', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution plan for SQL ID: ' || '&1'); -- Display execution plan using DBMS_XPLAN FOR plan_rec IN ( SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&1', NULL, 'ADVANCED ALLSTATS LAST')) ) LOOP DBMS_OUTPUT.PUT_LINE(plan_rec.plan_table_output); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Execution plan analysis skipped (sql_id=TOP or analysis_type not PLAN/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- SQL WAIT EVENTS: Wait event analysis for SQL execution-- -----------------------------------------------------------------------------------
PROMPT ============================================PROMPT 4. SQL WAIT EVENT ANALYSIS - SYSTEM WIDEPROMPT ============================================
PROMPTPROMPT Option 4.1: Top SQL by Total Wait TimePROMPT ---------------------------------------.
COL sql_id FOR A15COL sql_text_short FOR A60COL username FOR A15COL elapsed_sec FOR 999,999,999.99COL wait_sec FOR 999,999,999.99COL wait_pct FOR 999.99
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 50) as sql_text_short, u.username, s.executions, ROUND(s.elapsed_time/1000000, 2) as elapsed_sec, ROUND((s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time)/1000000, 2) as wait_sec, ROUND(100 * (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) / NULLIF(s.elapsed_time, 0), 2) as wait_pct, ROUND(s.cpu_time/1000000, 2) as cpu_sec, s.buffer_gets, s.disk_reads, s.rows_processed FROM v$sql s LEFT JOIN dba_users u ON s.parsing_user_id = u.user_id WHERE s.elapsed_time > 0 AND (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) > 0 ORDER BY wait_sec DESC)WHERE ROWNUM <= 10;
PROMPTPROMPT Option 4.2: Top Wait Events by Category (Aggregated)PROMPT ----------------------------------------------------.
COL wait_category FOR A20COL total_wait_sec FOR 999,999,999.99COL avg_wait_per_sql_sec FOR 999,999.99COL sql_count FOR 999,999
SELECT wait_category, COUNT(DISTINCT sql_id) as sql_count, ROUND(SUM(total_wait_time)/1000000, 2) as total_wait_sec, ROUND(AVG(total_wait_time)/1000000, 2) as avg_wait_per_sql_sec, ROUND(100 * SUM(total_wait_time) / SUM(SUM(total_wait_time)) OVER (), 2) as pct_of_totalFROM ( SELECT sql_id, 'Application' as wait_category, application_wait_time as total_wait_time FROM v$sql WHERE application_wait_time > 0 UNION ALL SELECT sql_id, 'Concurrency' as wait_category, concurrency_wait_time as total_wait_time FROM v$sql WHERE concurrency_wait_time > 0 UNION ALL SELECT sql_id, 'Cluster' as wait_category, cluster_wait_time as total_wait_time FROM v$sql WHERE cluster_wait_time > 0 UNION ALL SELECT sql_id, 'User I/O' as wait_category, user_io_wait_time as total_wait_time FROM v$sql WHERE user_io_wait_time > 0)GROUP BY wait_categoryORDER BY total_wait_sec DESC;
PROMPTPROMPT Option 4.3: SQL with Highest Wait PercentagePROMPT --------------------------------------------.
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 40) as sql_text_short, s.executions, ROUND(s.elapsed_time/1000000, 2) as elapsed_sec, ROUND(s.cpu_time/1000000, 2) as cpu_sec, ROUND((s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time)/1000000, 2) as wait_sec, ROUND(100 * (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) / NULLIF(s.elapsed_time, 0), 2) as wait_pct, s.buffer_gets, s.disk_reads, ROUND(s.disk_reads/NULLIF(s.executions, 0), 2) as reads_per_exec FROM v$sql s WHERE s.elapsed_time > 1000000 -- At least 1 second elapsed AND s.executions > 0 AND (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) > 0 ORDER BY wait_pct DESC)WHERE ROWNUM <= 15;
PROMPTPROMPT Option 4.4: Wait Time Distribution by Execution CountPROMPT -----------------------------------------------------.
COL exec_range FOR A30COL sql_count FOR 999,999COL avg_wait_per_sql_sec FOR 999,999.99
SELECT CASE WHEN executions <= 10 THEN '1-10 executions' WHEN executions <= 100 THEN '11-100 executions' WHEN executions <= 1000 THEN '101-1,000 executions' WHEN executions <= 10000 THEN '1,001-10,000 executions' ELSE '10,000+ executions' END as exec_range, COUNT(*) as sql_count, ROUND(SUM((application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000), 2) as total_wait_sec, ROUND(AVG((application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000), 2) as avg_wait_per_sql_sec, ROUND(AVG(100 * (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) / NULLIF(elapsed_time, 0)), 2) as avg_wait_pctFROM v$sqlWHERE elapsed_time > 0 AND executions > 0GROUP BY CASE WHEN executions <= 10 THEN '1-10 executions' WHEN executions <= 100 THEN '11-100 executions' WHEN executions <= 1000 THEN '101-1,000 executions' WHEN executions <= 10000 THEN '1,001-10,000 executions' ELSE '10,000+ executions' ENDORDER BY CASE WHEN exec_range = '1-10 executions' THEN 1 WHEN exec_range = '11-100 executions' THEN 2 WHEN exec_range = '101-1,000 executions' THEN 3 WHEN exec_range = '1,001-10,000 executions' THEN 4 ELSE 5 END;
PROMPTPROMPT Option 4.5: Active Session History - Current Top Wait EventsPROMPT ------------------------------------------------------------.PROMPT Note: Requires Oracle Diagnostic Pack licensePROMPT
COL event FOR A40COL wait_class FOR A20COL total_wait_time_sec FOR 999,999.99COL active_sessions FOR 999
SELECT event, wait_class, COUNT(DISTINCT sql_id) as sql_count, COUNT(*) as total_waits, ROUND(SUM(time_waited)/1000000, 2) as total_wait_time_sec, COUNT(DISTINCT session_id) as active_sessions, ROUND(AVG(time_waited)/1000, 2) as avg_wait_msFROM v$active_session_historyWHERE session_state = 'WAITING' AND sample_time > SYSDATE - (5/1440) -- Last 5 minutes AND time_waited > 0GROUP BY event, wait_classHAVING COUNT(*) > 10ORDER BY total_wait_time_sec DESCFETCH FIRST 15 ROWS ONLY;
PROMPTPROMPT Option 4.6: I/O Intensive SQL (High Disk Reads with Waits)PROMPT ---------------------------------------------------------.
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 40) as sql_text_short, s.executions, s.disk_reads, ROUND(s.disk_reads/NULLIF(s.executions, 0), 2) as reads_per_exec, ROUND(s.user_io_wait_time/1000000, 2) as io_wait_sec, ROUND(s.user_io_wait_time/NULLIF(s.disk_reads, 0)/1000, 2) as avg_io_wait_ms_per_read, ROUND(100 * s.user_io_wait_time / NULLIF(s.user_io_wait_time + s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time, 0), 2) as io_wait_pct FROM v$sql s WHERE s.disk_reads > 1000 AND s.user_io_wait_time > 0 AND s.elapsed_time > 0 ORDER BY s.disk_reads DESC)WHERE ROWNUM <= 15;
PROMPTPROMPT Option 4.7: Wait Time Trend Analysis (Last Hour from ASH)PROMPT ---------------------------------------------------------.
COL sample_time FOR A20COL event FOR A30COL wait_class FOR A20
SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') as sample_time_minute, wait_class, event, COUNT(DISTINCT sql_id) as sql_count, COUNT(*) as wait_count, ROUND(SUM(time_waited)/1000000, 2) as total_wait_secFROM v$active_session_historyWHERE session_state = 'WAITING' AND sample_time > SYSDATE - (60/1440) -- Last 60 minutes AND time_waited > 0GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI'), wait_class, eventHAVING COUNT(*) > 5ORDER BY sample_time_minute DESC, total_wait_sec DESCFETCH FIRST 20 ROWS ONLY;
PROMPTPROMPT Option 4.8: System-Wide Wait SummaryPROMPT -------------------------------------.
SELECT 'Total SQL Statements' as metric, COUNT(*) as value, 'count' as unitFROM v$sqlWHERE elapsed_time > 0
UNION ALL
SELECT 'SQL with Wait Time > 0', COUNT(*), 'count'FROM v$sqlWHERE (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) > 0
UNION ALL
SELECT 'Total Wait Time', ROUND(SUM(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql
UNION ALL
SELECT 'Avg Wait Time per SQL', ROUND(AVG(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sqlWHERE (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) > 0
UNION ALL
SELECT 'Max Wait Time in a SQL', ROUND(MAX(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql
UNION ALL
SELECT 'Total I/O Wait Time', ROUND(SUM(user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql;
-- Clear column formattingCLEAR COL
-- ------------------------------------------------------------------------------------- BIND VARIABLE ANALYSIS: Bind variable usage and impact-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BIND VARIABLE ANALYSISPROMPT ==========================
COLUMN name FORMAT A20COLUMN datatype_string FORMAT A15COLUMN value_string FORMAT A30COLUMN max_length FORMAT 999999
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying bind variables for SQL ID: ' || '&1'); END IF;END;/
SELECT name, datatype_string, value_string, max_lengthFROM v$sql_bind_captureWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL')ORDER BY 1;
-- ------------------------------------------------------------------------------------- OBJECT ACCESS PATTERNS: Tables and indexes accessed by SQL-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. OBJECT ACCESS PATTERNSPROMPT ===========================
COLUMN object_owner FORMAT A15COLUMN object_name FORMAT A30COLUMN object_type FORMAT A15COLUMN operation FORMAT A20COLUMN options FORMAT A20COLUMN cost FORMAT 999,999COLUMN cardinality FORMAT 999,999,999
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('PLAN', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying object access patterns for SQL ID: ' || '&1'); FOR obj_rec IN ( SELECT object_owner, object_name, object_type, operation, options, cost, cardinality FROM v$sql_plan WHERE sql_id = '&1' AND object_name IS NOT NULL AND object_owner NOT LIKE 'SYS%' ORDER BY id ) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(obj_rec.object_owner || '.' || obj_rec.object_name, 50) || RPAD(obj_rec.operation || ' ' || NVL(obj_rec.options, ''), 30) || RPAD('Cost: ' || NVL(TO_CHAR(obj_rec.cost), 'N/A'), 15) || 'Rows: ' || NVL(TO_CHAR(obj_rec.cardinality), 'N/A') ); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Object access analysis skipped (sql_id=TOP or analysis_type not PLAN/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE HISTOGRAM: Execution time distribution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. EXECUTION TIME DISTRIBUTIONPROMPT ===============================
COLUMN bucket FORMAT A20COLUMN execution_count FORMAT 999,999COLUMN pct_total FORMAT 999.99
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution time distribution for SQL ID: ' || '&1'); FOR hist_rec IN ( SELECT CASE WHEN elapsed_time < 1000000 THEN '0-1 sec' WHEN elapsed_time < 5000000 THEN '1-5 sec' WHEN elapsed_time < 30000000 THEN '5-30 sec' WHEN elapsed_time < 60000000 THEN '30-60 sec' ELSE '60+ sec' END as bucket, COUNT(*) as execution_count, ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) as pct_total FROM v$sql WHERE sql_id = '&1' GROUP BY CASE WHEN elapsed_time < 1000000 THEN '0-1 sec' WHEN elapsed_time < 5000000 THEN '1-5 sec' WHEN elapsed_time < 30000000 THEN '5-30 sec' WHEN elapsed_time < 60000000 THEN '30-60 sec' ELSE '60+ sec' END ORDER BY CASE WHEN bucket = '0-1 sec' THEN 1 WHEN bucket = '1-5 sec' THEN 2 WHEN bucket = '5-30 sec' THEN 3 WHEN bucket = '30-60 sec' THEN 4 ELSE 5 END ) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(hist_rec.bucket, 15) || RPAD(TO_CHAR(hist_rec.execution_count), 10) || hist_rec.pct_total || '%' ); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Execution histogram skipped (sql_id=TOP or analysis_type not STATS/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- TUNING RECOMMENDATIONS: Actionable performance improvement suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE TUNING RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A25COLUMN description FORMAT A60COLUMN potential_impact FORMAT A15COLUMN priority FORMAT A8
SELECT 'High CPU Consumption' as recommendation_type, 'SQL consumes significant CPU resources: ' || ROUND(cpu_time/1000000, 2) || ' seconds' as description, 'HIGH' as potential_impact, 'HIGH' as priorityFROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND ROUND(cpu_time/1000000, 2) > 10UNION ALLSELECT 'Frequent Executions', 'High execution count: ' || executions || ' executions', 'MEDIUM', 'MEDIUM'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND executions > 10000UNION ALLSELECT 'High Buffer Gets', 'Excessive buffer gets: ' || buffer_gets || ' gets', 'HIGH', 'HIGH'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND buffer_gets > 1000000UNION ALLSELECT 'Physical I/O Intensive', 'High disk reads: ' || disk_reads || ' reads', 'HIGH', 'HIGH'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND disk_reads > 10000UNION ALLSELECT 'Parse Intensive', 'High parse calls: ' || parse_calls || ' parses', 'MEDIUM', 'MEDIUM'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND parse_calls > 1000 AND ROUND(parse_calls/NULLIF(executions, 0), 2) > 0.1ORDER BY 4 DESC, 1;
-- ------------------------------------------------------------------------------------- SQL MONITORING REPORT: Real-time SQL monitoring (if available)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. REAL-TIME SQL MONITORINGPROMPT =============================
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('ALL') THEN DBMS_OUTPUT.PUT_LINE('Checking for real-time SQL monitoring data...'); DECLARE v_report CLOB; BEGIN SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id => '&1', type => 'TEXT', report_level => 'ALL' ) INTO v_report FROM DUAL; IF LENGTH(v_report) > 0 THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring report available for SQL ID: ' || '&1'); DBMS_OUTPUT.PUT_LINE('Use DBMS_SQLTUNE.REPORT_SQL_MONITOR for detailed report'); ELSE DBMS_OUTPUT.PUT_LINE('No real-time monitoring data available'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring not available: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring check skipped'); END IF;END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Analysis completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT SQL PERFORMANCE ANALYSIS COMPLETEDPROMPT =====================================PROMPT SQL ID: &1PROMPT Analysis Type: &2PROMPT Top N: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendationsPROMPT 2. Analyze execution plan for optimization opportunitiesPROMPT 3. Consider SQL profile or plan baseline for unstable plansPROMPT 4. Monitor performance after implementing changesPROMPT
TIMING SHOW
-- ------------------------------------------------------------------------------------- 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_sql_performance_analyzer_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
TIMING START sql_analysis
PROMPT Starting SQL Performance Analyzer...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT SQL ID: &1PROMPT Analysis Type: &2PROMPT Top N: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_sql_id VARCHAR2(20) := UPPER('&1'); v_analysis_type VARCHAR2(20) := UPPER(NVL('&2', 'ALL')); v_top_n NUMBER := NVL(TO_NUMBER('&3'), 10); v_sql_exists NUMBER;BEGIN IF v_analysis_type NOT IN ('EXECUTION', 'PLAN', 'STATS', 'WAITS', 'ALL') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be EXECUTION, PLAN, STATS, WAITS, or ALL'); END IF; IF v_top_n < 1 OR v_top_n > 100 THEN RAISE_APPLICATION_ERROR(-20002, 'Top N must be between 1 and 100'); END IF; IF v_sql_id != 'TOP' THEN SELECT COUNT(*) INTO v_sql_exists FROM v$sql WHERE sql_id = v_sql_id; IF v_sql_exists = 0 THEN RAISE_APPLICATION_ERROR(-20003, 'SQL ID ' || v_sql_id || ' not found in shared pool'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for Top N'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TOP SQL BY RESOURCE CONSUMPTION: Identify high-impact SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. TOP &3 SQL BY RESOURCE CONSUMPTIONPROMPT ======================================
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 cpu_per_exec FORMAT 999,999.99COLUMN buffer_gets FORMAT 999,999,999,999COLUMN disk_reads FORMAT 999,999,999,999COLUMN sql_text FORMAT A50
BEGIN IF UPPER('&1') = 'TOP' THEN DBMS_OUTPUT.PUT_LINE('Displaying top ' || '&3' || ' SQL statements by resource consumption...'); END IF;END;/
SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_time_sec, ROUND(elapsed_time/1000000, 2) as elapsed_time_sec, executions, ROUND((cpu_time/1000000)/NULLIF(executions, 0), 2) as cpu_per_exec, buffer_gets, disk_reads, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE UPPER('&1') = 'TOP' AND cpu_time > 0ORDER BY cpu_time DESCFETCH FIRST &3 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SQL EXECUTION STATISTICS: Detailed execution metrics for specific SQL-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SQL EXECUTION STATISTICSPROMPT ============================
COLUMN metric FORMAT A30COLUMN value FORMAT A30COLUMN per_execution FORMAT A20
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('EXECUTION', 'STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution statistics for SQL ID: ' || '&1'); END IF;END;/
SELECT 'SQL Text' as metric, SUBSTR(sql_text, 1, 100) as value, '' as per_executionFROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Executions', TO_CHAR(executions), 'N/A'FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'CPU Time (seconds)', TO_CHAR(ROUND(cpu_time/1000000, 2)), TO_CHAR(ROUND((cpu_time/1000000)/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Elapsed Time (seconds)', TO_CHAR(ROUND(elapsed_time/1000000, 2)), TO_CHAR(ROUND((elapsed_time/1000000)/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Buffer Gets', TO_CHAR(buffer_gets), TO_CHAR(ROUND(buffer_gets/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Disk Reads', TO_CHAR(disk_reads), TO_CHAR(ROUND(disk_reads/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Rows Processed', TO_CHAR(rows_processed), TO_CHAR(ROUND(rows_processed/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1UNION ALLSELECT 'Parse Calls', TO_CHAR(parse_calls), TO_CHAR(ROUND(parse_calls/NULLIF(executions, 0), 2))FROM v$sqlWHERE sql_id = '&1' AND ROWNUM = 1ORDER BY 1;
-- ------------------------------------------------------------------------------------- EXECUTION PLAN ANALYSIS: Display and analyze execution plan-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. EXECUTION PLAN ANALYSISPROMPT ===========================
COLUMN plan_line FORMAT A200
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('PLAN', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution plan for SQL ID: ' || '&1'); -- Display execution plan using DBMS_XPLAN FOR plan_rec IN ( SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&1', NULL, 'ADVANCED ALLSTATS LAST')) ) LOOP DBMS_OUTPUT.PUT_LINE(plan_rec.plan_table_output); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Execution plan analysis skipped (sql_id=TOP or analysis_type not PLAN/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- SQL WAIT EVENTS: Wait event analysis for SQL execution-- -----------------------------------------------------------------------------------
PROMPT ============================================PROMPT 4. SQL WAIT EVENT ANALYSIS - SYSTEM WIDEPROMPT ============================================
PROMPTPROMPT Option 4.1: Top SQL by Total Wait TimePROMPT ---------------------------------------.
COL sql_id FOR A15COL sql_text_short FOR A60COL username FOR A15COL elapsed_sec FOR 999,999,999.99COL wait_sec FOR 999,999,999.99COL wait_pct FOR 999.99
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 50) as sql_text_short, u.username, s.executions, ROUND(s.elapsed_time/1000000, 2) as elapsed_sec, ROUND((s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time)/1000000, 2) as wait_sec, ROUND(100 * (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) / NULLIF(s.elapsed_time, 0), 2) as wait_pct, ROUND(s.cpu_time/1000000, 2) as cpu_sec, s.buffer_gets, s.disk_reads, s.rows_processed FROM v$sql s LEFT JOIN dba_users u ON s.parsing_user_id = u.user_id WHERE s.elapsed_time > 0 AND (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) > 0 ORDER BY wait_sec DESC)WHERE ROWNUM <= 10;
PROMPTPROMPT Option 4.2: Top Wait Events by Category (Aggregated)PROMPT ----------------------------------------------------.
COL wait_category FOR A20COL total_wait_sec FOR 999,999,999.99COL avg_wait_per_sql_sec FOR 999,999.99COL sql_count FOR 999,999
SELECT wait_category, COUNT(DISTINCT sql_id) as sql_count, ROUND(SUM(total_wait_time)/1000000, 2) as total_wait_sec, ROUND(AVG(total_wait_time)/1000000, 2) as avg_wait_per_sql_sec, ROUND(100 * SUM(total_wait_time) / SUM(SUM(total_wait_time)) OVER (), 2) as pct_of_totalFROM ( SELECT sql_id, 'Application' as wait_category, application_wait_time as total_wait_time FROM v$sql WHERE application_wait_time > 0 UNION ALL SELECT sql_id, 'Concurrency' as wait_category, concurrency_wait_time as total_wait_time FROM v$sql WHERE concurrency_wait_time > 0 UNION ALL SELECT sql_id, 'Cluster' as wait_category, cluster_wait_time as total_wait_time FROM v$sql WHERE cluster_wait_time > 0 UNION ALL SELECT sql_id, 'User I/O' as wait_category, user_io_wait_time as total_wait_time FROM v$sql WHERE user_io_wait_time > 0)GROUP BY wait_categoryORDER BY total_wait_sec DESC;
PROMPTPROMPT Option 4.3: SQL with Highest Wait PercentagePROMPT --------------------------------------------.
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 40) as sql_text_short, s.executions, ROUND(s.elapsed_time/1000000, 2) as elapsed_sec, ROUND(s.cpu_time/1000000, 2) as cpu_sec, ROUND((s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time)/1000000, 2) as wait_sec, ROUND(100 * (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) / NULLIF(s.elapsed_time, 0), 2) as wait_pct, s.buffer_gets, s.disk_reads, ROUND(s.disk_reads/NULLIF(s.executions, 0), 2) as reads_per_exec FROM v$sql s WHERE s.elapsed_time > 1000000 -- At least 1 second elapsed AND s.executions > 0 AND (s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time + s.user_io_wait_time) > 0 ORDER BY wait_pct DESC)WHERE ROWNUM <= 15;
PROMPTPROMPT Option 4.4: Wait Time Distribution by Execution CountPROMPT -----------------------------------------------------.
COL exec_range FOR A30COL sql_count FOR 999,999COL avg_wait_per_sql_sec FOR 999,999.99
SELECT CASE WHEN executions <= 10 THEN '1-10 executions' WHEN executions <= 100 THEN '11-100 executions' WHEN executions <= 1000 THEN '101-1,000 executions' WHEN executions <= 10000 THEN '1,001-10,000 executions' ELSE '10,000+ executions' END as exec_range, COUNT(*) as sql_count, ROUND(SUM((application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000), 2) as total_wait_sec, ROUND(AVG((application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000), 2) as avg_wait_per_sql_sec, ROUND(AVG(100 * (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) / NULLIF(elapsed_time, 0)), 2) as avg_wait_pctFROM v$sqlWHERE elapsed_time > 0 AND executions > 0GROUP BY CASE WHEN executions <= 10 THEN '1-10 executions' WHEN executions <= 100 THEN '11-100 executions' WHEN executions <= 1000 THEN '101-1,000 executions' WHEN executions <= 10000 THEN '1,001-10,000 executions' ELSE '10,000+ executions' ENDORDER BY CASE WHEN exec_range = '1-10 executions' THEN 1 WHEN exec_range = '11-100 executions' THEN 2 WHEN exec_range = '101-1,000 executions' THEN 3 WHEN exec_range = '1,001-10,000 executions' THEN 4 ELSE 5 END;
PROMPTPROMPT Option 4.5: Active Session History - Current Top Wait EventsPROMPT ------------------------------------------------------------.PROMPT Note: Requires Oracle Diagnostic Pack licensePROMPT
COL event FOR A40COL wait_class FOR A20COL total_wait_time_sec FOR 999,999.99COL active_sessions FOR 999
SELECT event, wait_class, COUNT(DISTINCT sql_id) as sql_count, COUNT(*) as total_waits, ROUND(SUM(time_waited)/1000000, 2) as total_wait_time_sec, COUNT(DISTINCT session_id) as active_sessions, ROUND(AVG(time_waited)/1000, 2) as avg_wait_msFROM v$active_session_historyWHERE session_state = 'WAITING' AND sample_time > SYSDATE - (5/1440) -- Last 5 minutes AND time_waited > 0GROUP BY event, wait_classHAVING COUNT(*) > 10ORDER BY total_wait_time_sec DESCFETCH FIRST 15 ROWS ONLY;
PROMPTPROMPT Option 4.6: I/O Intensive SQL (High Disk Reads with Waits)PROMPT ---------------------------------------------------------.
SELECT *FROM ( SELECT s.sql_id, SUBSTR(s.sql_text, 1, 40) as sql_text_short, s.executions, s.disk_reads, ROUND(s.disk_reads/NULLIF(s.executions, 0), 2) as reads_per_exec, ROUND(s.user_io_wait_time/1000000, 2) as io_wait_sec, ROUND(s.user_io_wait_time/NULLIF(s.disk_reads, 0)/1000, 2) as avg_io_wait_ms_per_read, ROUND(100 * s.user_io_wait_time / NULLIF(s.user_io_wait_time + s.application_wait_time + s.concurrency_wait_time + s.cluster_wait_time, 0), 2) as io_wait_pct FROM v$sql s WHERE s.disk_reads > 1000 AND s.user_io_wait_time > 0 AND s.elapsed_time > 0 ORDER BY s.disk_reads DESC)WHERE ROWNUM <= 15;
PROMPTPROMPT Option 4.7: Wait Time Trend Analysis (Last Hour from ASH)PROMPT ---------------------------------------------------------.
COL sample_time FOR A20COL event FOR A30COL wait_class FOR A20
SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') as sample_time_minute, wait_class, event, COUNT(DISTINCT sql_id) as sql_count, COUNT(*) as wait_count, ROUND(SUM(time_waited)/1000000, 2) as total_wait_secFROM v$active_session_historyWHERE session_state = 'WAITING' AND sample_time > SYSDATE - (60/1440) -- Last 60 minutes AND time_waited > 0GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI'), wait_class, eventHAVING COUNT(*) > 5ORDER BY sample_time_minute DESC, total_wait_sec DESCFETCH FIRST 20 ROWS ONLY;
PROMPTPROMPT Option 4.8: System-Wide Wait SummaryPROMPT -------------------------------------.
SELECT 'Total SQL Statements' as metric, COUNT(*) as value, 'count' as unitFROM v$sqlWHERE elapsed_time > 0
UNION ALL
SELECT 'SQL with Wait Time > 0', COUNT(*), 'count'FROM v$sqlWHERE (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) > 0
UNION ALL
SELECT 'Total Wait Time', ROUND(SUM(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql
UNION ALL
SELECT 'Avg Wait Time per SQL', ROUND(AVG(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sqlWHERE (application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time) > 0
UNION ALL
SELECT 'Max Wait Time in a SQL', ROUND(MAX(application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql
UNION ALL
SELECT 'Total I/O Wait Time', ROUND(SUM(user_io_wait_time)/1000000, 2), 'seconds'FROM v$sql;
-- Clear column formattingCLEAR COL
-- ------------------------------------------------------------------------------------- BIND VARIABLE ANALYSIS: Bind variable usage and impact-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BIND VARIABLE ANALYSISPROMPT ==========================
COLUMN name FORMAT A20COLUMN datatype_string FORMAT A15COLUMN value_string FORMAT A30COLUMN max_length FORMAT 999999
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying bind variables for SQL ID: ' || '&1'); END IF;END;/
SELECT name, datatype_string, value_string, max_lengthFROM v$sql_bind_captureWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL')ORDER BY 1;
-- ------------------------------------------------------------------------------------- OBJECT ACCESS PATTERNS: Tables and indexes accessed by SQL-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. OBJECT ACCESS PATTERNSPROMPT ===========================
COLUMN object_owner FORMAT A15COLUMN object_name FORMAT A30COLUMN object_type FORMAT A15COLUMN operation FORMAT A20COLUMN options FORMAT A20COLUMN cost FORMAT 999,999COLUMN cardinality FORMAT 999,999,999
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('PLAN', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying object access patterns for SQL ID: ' || '&1'); FOR obj_rec IN ( SELECT object_owner, object_name, object_type, operation, options, cost, cardinality FROM v$sql_plan WHERE sql_id = '&1' AND object_name IS NOT NULL AND object_owner NOT LIKE 'SYS%' ORDER BY id ) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(obj_rec.object_owner || '.' || obj_rec.object_name, 50) || RPAD(obj_rec.operation || ' ' || NVL(obj_rec.options, ''), 30) || RPAD('Cost: ' || NVL(TO_CHAR(obj_rec.cost), 'N/A'), 15) || 'Rows: ' || NVL(TO_CHAR(obj_rec.cardinality), 'N/A') ); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Object access analysis skipped (sql_id=TOP or analysis_type not PLAN/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- PERFORMANCE HISTOGRAM: Execution time distribution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. EXECUTION TIME DISTRIBUTIONPROMPT ===============================
COLUMN bucket FORMAT A20COLUMN execution_count FORMAT 999,999COLUMN pct_total FORMAT 999.99
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('STATS', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying execution time distribution for SQL ID: ' || '&1'); FOR hist_rec IN ( SELECT CASE WHEN elapsed_time < 1000000 THEN '0-1 sec' WHEN elapsed_time < 5000000 THEN '1-5 sec' WHEN elapsed_time < 30000000 THEN '5-30 sec' WHEN elapsed_time < 60000000 THEN '30-60 sec' ELSE '60+ sec' END as bucket, COUNT(*) as execution_count, ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) as pct_total FROM v$sql WHERE sql_id = '&1' GROUP BY CASE WHEN elapsed_time < 1000000 THEN '0-1 sec' WHEN elapsed_time < 5000000 THEN '1-5 sec' WHEN elapsed_time < 30000000 THEN '5-30 sec' WHEN elapsed_time < 60000000 THEN '30-60 sec' ELSE '60+ sec' END ORDER BY CASE WHEN bucket = '0-1 sec' THEN 1 WHEN bucket = '1-5 sec' THEN 2 WHEN bucket = '5-30 sec' THEN 3 WHEN bucket = '30-60 sec' THEN 4 ELSE 5 END ) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(hist_rec.bucket, 15) || RPAD(TO_CHAR(hist_rec.execution_count), 10) || hist_rec.pct_total || '%' ); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Execution histogram skipped (sql_id=TOP or analysis_type not STATS/ALL)'); END IF;END;/
-- ------------------------------------------------------------------------------------- TUNING RECOMMENDATIONS: Actionable performance improvement suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE TUNING RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A25COLUMN description FORMAT A60COLUMN potential_impact FORMAT A15COLUMN priority FORMAT A8
SELECT 'High CPU Consumption' as recommendation_type, 'SQL consumes significant CPU resources: ' || ROUND(cpu_time/1000000, 2) || ' seconds' as description, 'HIGH' as potential_impact, 'HIGH' as priorityFROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND ROUND(cpu_time/1000000, 2) > 10UNION ALLSELECT 'Frequent Executions', 'High execution count: ' || executions || ' executions', 'MEDIUM', 'MEDIUM'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND executions > 10000UNION ALLSELECT 'High Buffer Gets', 'Excessive buffer gets: ' || buffer_gets || ' gets', 'HIGH', 'HIGH'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND buffer_gets > 1000000UNION ALLSELECT 'Physical I/O Intensive', 'High disk reads: ' || disk_reads || ' reads', 'HIGH', 'HIGH'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND disk_reads > 10000UNION ALLSELECT 'Parse Intensive', 'High parse calls: ' || parse_calls || ' parses', 'MEDIUM', 'MEDIUM'FROM v$sqlstatsWHERE sql_id = '&1' AND UPPER('&1') != 'TOP' AND parse_calls > 1000 AND ROUND(parse_calls/NULLIF(executions, 0), 2) > 0.1ORDER BY 4 DESC, 1;
-- ------------------------------------------------------------------------------------- SQL MONITORING REPORT: Real-time SQL monitoring (if available)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. REAL-TIME SQL MONITORINGPROMPT =============================
BEGIN IF UPPER('&1') != 'TOP' AND UPPER('&2') IN ('ALL') THEN DBMS_OUTPUT.PUT_LINE('Checking for real-time SQL monitoring data...'); DECLARE v_report CLOB; BEGIN SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id => '&1', type => 'TEXT', report_level => 'ALL' ) INTO v_report FROM DUAL; IF LENGTH(v_report) > 0 THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring report available for SQL ID: ' || '&1'); DBMS_OUTPUT.PUT_LINE('Use DBMS_SQLTUNE.REPORT_SQL_MONITOR for detailed report'); ELSE DBMS_OUTPUT.PUT_LINE('No real-time monitoring data available'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring not available: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring check skipped'); END IF;END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Analysis completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT SQL PERFORMANCE ANALYSIS COMPLETEDPROMPT =====================================PROMPT SQL ID: &1PROMPT Analysis Type: &2PROMPT Top N: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendationsPROMPT 2. Analyze execution plan for optimization opportunitiesPROMPT 3. Consider SQL profile or plan baseline for unstable plansPROMPT 4. Monitor performance after implementing changesPROMPT
TIMING SHOW
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------