performance_diagnostics.sql
-- ------------------------------------------------------------------------------------- File Name : performance_diagnostics.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database performance diagnostics and tuning analysis-- Purpose : Identify performance bottlenecks, analyze SQL performance, and provide tuning recommendations-- Call Syntax : @F:\DBA\Scripts\performance_diagnostics.sql (analysis_type) (top_n)-- Parameters : analysis_type - Type of analysis to perform (SYSTEM, SQL, WAIT, MEMORY, ALL)-- top_n - Number of top items to display in each section (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 = &TIME1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_performance_diagnostics_' || 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 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
TIMING START performance_diagnostics
PROMPT Starting Performance Diagnostics Script...PROMPT ===========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Analysis Type: &1PROMPT Top N Results: &2PROMPT Timestamp: &&_DATE &TIME1PROMPT
-- Validate parametersDECLARE v_analysis_type VARCHAR2(20) := UPPER(NVL('&1', 'ALL')); v_top_n NUMBER := NVL(TO_NUMBER('&2'), 10);BEGIN IF v_analysis_type NOT IN ('SYSTEM', 'SQL', 'WAIT', 'MEMORY', 'ALL') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be SYSTEM, SQL, WAIT, MEMORY, 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; 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;/
-- ------------------------------------------------------------------------------------- SYSTEM PERFORMANCE OVERVIEW: Key system metrics and statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SYSTEM PERFORMANCE OVERVIEWPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying system performance metrics...'); END IF;END;/
SELECT metric_name, value, metric_unitFROM 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', 'Redo Generated Per Sec', 'Current Logons Count' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- TOP SQL BY CPU CONSUMPTION: Identify high CPU-consuming SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TOP &2 SQL STATEMENTS BY CPU 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 sql_text FORMAT A60
BEGIN IF UPPER('&1') IN ('SQL', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying top CPU-consuming SQL statements...'); 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, SUBSTR(sql_text, 1, 60) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND UPPER('&1') IN ('SQL', 'ALL')ORDER BY cpu_time DESCFETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- WAIT EVENT ANALYSIS: Top wait events and time breakdown-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TOP &2 WAIT EVENTS ANALYSISPROMPT ===============================
COLUMN event FORMAT A40COLUMN total_waits FORMAT 999,999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20
BEGIN IF UPPER('&1') IN ('WAIT', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying top wait events...'); END IF;END;/
SELECT event, total_waits, ROUND(time_waited_micro/1000000, 2) as total_time_sec, ROUND((time_waited_micro/total_waits)/1000, 2) as avg_wait_ms, wait_classFROM v$system_eventWHERE wait_class != 'Idle' AND total_waits > 0 AND UPPER('&1') IN ('WAIT', 'ALL')ORDER BY time_waited_micro DESC;-- FETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE ANALYSIS: SGA and PGA memory utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY USAGE ANALYSISPROMPT =========================
COLUMN pool FORMAT A20COLUMN name FORMAT A30COLUMN bytes_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99
BEGIN IF UPPER('&1') IN ('MEMORY', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying memory usage analysis...'); END IF;END;/
SELECT pool, name, ROUND(bytes/1024/1024, 2) as bytes_mb, ROUND(ratio_to_report(bytes) over (partition by pool) * 100, 2) as pct_usedFROM v$sgastatWHERE pool IS NOT NULL AND bytes > 1024*1024 AND UPPER('&1') IN ('MEMORY', 'ALL')ORDER BY pool, bytes DESC;
-- ------------------------------------------------------------------------------------- BUFFER CACHE HIT RATIO: Database buffer cache efficiency-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BUFFER CACHE EFFICIENCYPROMPT ===========================
COLUMN statistic FORMAT A40COLUMN value FORMAT 999,999,999,999COLUMN hit_ratio FORMAT 999.99
SELECT 'Buffer Cache Hit Ratio' as statistic, ROUND((1 - (phy.value - lob_phy.value - direct_phy.value) / (ses.value - lob_ses.value - direct_ses.value)) * 100, 2) as hit_ratio, 'Percentage' as unitFROM v$sysstat ses, v$sysstat phy, v$sysstat lob_ses, v$sysstat lob_phy, v$sysstat direct_ses, v$sysstat direct_phyWHERE ses.name = 'session logical reads' AND phy.name = 'physical reads' AND lob_ses.name = 'session logical reads - LOB' AND lob_phy.name = 'physical reads - LOB' AND direct_ses.name = 'session logical reads - direct' AND direct_phy.name = 'physical reads direct' AND UPPER('&1') IN ('SYSTEM', 'MEMORY', 'ALL');
-- ------------------------------------------------------------------------------------- SESSION ACTIVITY: Current active sessions and resource consumption-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. CURRENT ACTIVE SESSIONSPROMPT ===========================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN event FORMAT A30COLUMN state FORMAT A15COLUMN seconds_in_wait FORMAT 999999
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying current active sessions...'); END IF;END;/
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, s.event, s.state, s.seconds_in_waitFROM v$session sWHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.username IS NOT NULL AND UPPER('&1') IN ('SYSTEM', 'ALL')ORDER BY s.seconds_in_wait DESC;-- FETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- TABLESPACE I/O STATISTICS: I/O performance by tablespace-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. TABLESPACE I/O STATISTICSPROMPT =============================
COLUMN tablespace_name FORMAT A30COLUMN phy_reads_mb FORMAT 999,999,999COLUMN phy_writes_mb FORMAT 999,999,999COLUMN read_time_sec FORMAT 999,999.99COLUMN write_time_sec FORMAT 999,999.99COLUMN avg_read_ms FORMAT 999.99
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying tablespace I/O statistics...'); END IF;END;/
SELECT ddf.tablespace_name, ROUND(SUM(fs.phyrds * dt.block_size) / 1024 / 1024, 2) as phy_reads_mb, ROUND(SUM(fs.phywrts * dt.block_size) / 1024 / 1024, 2) as phy_writes_mb, ROUND(SUM(fs.phyrds), 0) as total_physical_reads, ROUND(SUM(fs.phywrts), 0) as total_physical_writes, ROUND(SUM(fs.readtim) / 100, 2) as read_time_sec, ROUND(SUM(fs.writetim) / 100, 2) as write_time_sec, CASE WHEN SUM(fs.phyrds) > 0 THEN ROUND(SUM(fs.readtim) / SUM(fs.phyrds) * 10, 2) ELSE 0 END as avg_read_msFROM v$filestat fsJOIN dba_data_files ddf ON fs.file# = ddf.file_idJOIN dba_tablespaces dt ON ddf.tablespace_name = dt.tablespace_nameWHERE fs.phyrds > 0GROUP BY ddf.tablespace_nameORDER BY phy_reads_mb DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Tuning suggestions based on analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE TUNING RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A20COLUMN description FORMAT A60COLUMN priority FORMAT A8
SELECT 'SQL Tuning' as recommendation_type, 'Investigate high CPU SQL: ' || sql_id as description, 'HIGH' as priorityFROM ( SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_sec FROM v$sqlstats ORDER BY cpu_time DESC FETCH FIRST 1 ROWS ONLY)WHERE cpu_sec > 10 AND UPPER('&1') IN ('SQL', 'ALL')UNION ALLSELECT 'Wait Event' as recommendation_type, 'Address wait event: ' || event as description, 'MEDIUM' as priorityFROM ( SELECT event, ROUND(time_waited_micro/1000000, 2) as wait_sec FROM v$system_event WHERE wait_class != 'Idle' ORDER BY time_waited_micro DESC FETCH FIRST 1 ROWS ONLY)WHERE wait_sec > 60 AND UPPER('&1') IN ('WAIT', 'ALL')UNION ALLSELECT 'Memory' as recommendation_type, 'Monitor memory component: ' || pool as description, 'LOW' as priorityFROM ( SELECT pool, ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool ORDER BY SUM(bytes) DESC FETCH FIRST 1 ROWS ONLY)WHERE size_gb > 1 AND UPPER('&1') IN ('MEMORY', 'ALL')ORDER BY 3 DESC, 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===========================================PROMPT PERFORMANCE DIAGNOSTICS COMPLETEDPROMPT ===========================================PROMPT Analysis Type: &1PROMPT Top N Results: &2PROMPT Completion Time: &&_DATE &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Investigate top SQL statements for optimizationPROMPT 3. Address significant wait events identifiedPROMPT 4. Monitor memory usage trends over timePROMPT
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 = &TIME1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_performance_diagnostics_' || 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 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
TIMING START performance_diagnostics
PROMPT Starting Performance Diagnostics Script...PROMPT ===========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Analysis Type: &1PROMPT Top N Results: &2PROMPT Timestamp: &&_DATE &TIME1PROMPT
-- Validate parametersDECLARE v_analysis_type VARCHAR2(20) := UPPER(NVL('&1', 'ALL')); v_top_n NUMBER := NVL(TO_NUMBER('&2'), 10);BEGIN IF v_analysis_type NOT IN ('SYSTEM', 'SQL', 'WAIT', 'MEMORY', 'ALL') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be SYSTEM, SQL, WAIT, MEMORY, 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; 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;/
-- ------------------------------------------------------------------------------------- SYSTEM PERFORMANCE OVERVIEW: Key system metrics and statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SYSTEM PERFORMANCE OVERVIEWPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying system performance metrics...'); END IF;END;/
SELECT metric_name, value, metric_unitFROM 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', 'Redo Generated Per Sec', 'Current Logons Count' )ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- TOP SQL BY CPU CONSUMPTION: Identify high CPU-consuming SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TOP &2 SQL STATEMENTS BY CPU 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 sql_text FORMAT A60
BEGIN IF UPPER('&1') IN ('SQL', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying top CPU-consuming SQL statements...'); 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, SUBSTR(sql_text, 1, 60) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND UPPER('&1') IN ('SQL', 'ALL')ORDER BY cpu_time DESCFETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- WAIT EVENT ANALYSIS: Top wait events and time breakdown-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TOP &2 WAIT EVENTS ANALYSISPROMPT ===============================
COLUMN event FORMAT A40COLUMN total_waits FORMAT 999,999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN wait_class FORMAT A20
BEGIN IF UPPER('&1') IN ('WAIT', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying top wait events...'); END IF;END;/
SELECT event, total_waits, ROUND(time_waited_micro/1000000, 2) as total_time_sec, ROUND((time_waited_micro/total_waits)/1000, 2) as avg_wait_ms, wait_classFROM v$system_eventWHERE wait_class != 'Idle' AND total_waits > 0 AND UPPER('&1') IN ('WAIT', 'ALL')ORDER BY time_waited_micro DESC;-- FETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE ANALYSIS: SGA and PGA memory utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY USAGE ANALYSISPROMPT =========================
COLUMN pool FORMAT A20COLUMN name FORMAT A30COLUMN bytes_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99
BEGIN IF UPPER('&1') IN ('MEMORY', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying memory usage analysis...'); END IF;END;/
SELECT pool, name, ROUND(bytes/1024/1024, 2) as bytes_mb, ROUND(ratio_to_report(bytes) over (partition by pool) * 100, 2) as pct_usedFROM v$sgastatWHERE pool IS NOT NULL AND bytes > 1024*1024 AND UPPER('&1') IN ('MEMORY', 'ALL')ORDER BY pool, bytes DESC;
-- ------------------------------------------------------------------------------------- BUFFER CACHE HIT RATIO: Database buffer cache efficiency-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BUFFER CACHE EFFICIENCYPROMPT ===========================
COLUMN statistic FORMAT A40COLUMN value FORMAT 999,999,999,999COLUMN hit_ratio FORMAT 999.99
SELECT 'Buffer Cache Hit Ratio' as statistic, ROUND((1 - (phy.value - lob_phy.value - direct_phy.value) / (ses.value - lob_ses.value - direct_ses.value)) * 100, 2) as hit_ratio, 'Percentage' as unitFROM v$sysstat ses, v$sysstat phy, v$sysstat lob_ses, v$sysstat lob_phy, v$sysstat direct_ses, v$sysstat direct_phyWHERE ses.name = 'session logical reads' AND phy.name = 'physical reads' AND lob_ses.name = 'session logical reads - LOB' AND lob_phy.name = 'physical reads - LOB' AND direct_ses.name = 'session logical reads - direct' AND direct_phy.name = 'physical reads direct' AND UPPER('&1') IN ('SYSTEM', 'MEMORY', 'ALL');
-- ------------------------------------------------------------------------------------- SESSION ACTIVITY: Current active sessions and resource consumption-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. CURRENT ACTIVE SESSIONSPROMPT ===========================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN event FORMAT A30COLUMN state FORMAT A15COLUMN seconds_in_wait FORMAT 999999
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying current active sessions...'); END IF;END;/
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, s.event, s.state, s.seconds_in_waitFROM v$session sWHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.username IS NOT NULL AND UPPER('&1') IN ('SYSTEM', 'ALL')ORDER BY s.seconds_in_wait DESC;-- FETCH FIRST &2 ROWS ONLY;
-- ------------------------------------------------------------------------------------- TABLESPACE I/O STATISTICS: I/O performance by tablespace-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. TABLESPACE I/O STATISTICSPROMPT =============================
COLUMN tablespace_name FORMAT A30COLUMN phy_reads_mb FORMAT 999,999,999COLUMN phy_writes_mb FORMAT 999,999,999COLUMN read_time_sec FORMAT 999,999.99COLUMN write_time_sec FORMAT 999,999.99COLUMN avg_read_ms FORMAT 999.99
BEGIN IF UPPER('&1') IN ('SYSTEM', 'ALL') THEN DBMS_OUTPUT.PUT_LINE('Displaying tablespace I/O statistics...'); END IF;END;/
SELECT ddf.tablespace_name, ROUND(SUM(fs.phyrds * dt.block_size) / 1024 / 1024, 2) as phy_reads_mb, ROUND(SUM(fs.phywrts * dt.block_size) / 1024 / 1024, 2) as phy_writes_mb, ROUND(SUM(fs.phyrds), 0) as total_physical_reads, ROUND(SUM(fs.phywrts), 0) as total_physical_writes, ROUND(SUM(fs.readtim) / 100, 2) as read_time_sec, ROUND(SUM(fs.writetim) / 100, 2) as write_time_sec, CASE WHEN SUM(fs.phyrds) > 0 THEN ROUND(SUM(fs.readtim) / SUM(fs.phyrds) * 10, 2) ELSE 0 END as avg_read_msFROM v$filestat fsJOIN dba_data_files ddf ON fs.file# = ddf.file_idJOIN dba_tablespaces dt ON ddf.tablespace_name = dt.tablespace_nameWHERE fs.phyrds > 0GROUP BY ddf.tablespace_nameORDER BY phy_reads_mb DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE RECOMMENDATIONS: Tuning suggestions based on analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE TUNING RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A20COLUMN description FORMAT A60COLUMN priority FORMAT A8
SELECT 'SQL Tuning' as recommendation_type, 'Investigate high CPU SQL: ' || sql_id as description, 'HIGH' as priorityFROM ( SELECT sql_id, ROUND(cpu_time/1000000, 2) as cpu_sec FROM v$sqlstats ORDER BY cpu_time DESC FETCH FIRST 1 ROWS ONLY)WHERE cpu_sec > 10 AND UPPER('&1') IN ('SQL', 'ALL')UNION ALLSELECT 'Wait Event' as recommendation_type, 'Address wait event: ' || event as description, 'MEDIUM' as priorityFROM ( SELECT event, ROUND(time_waited_micro/1000000, 2) as wait_sec FROM v$system_event WHERE wait_class != 'Idle' ORDER BY time_waited_micro DESC FETCH FIRST 1 ROWS ONLY)WHERE wait_sec > 60 AND UPPER('&1') IN ('WAIT', 'ALL')UNION ALLSELECT 'Memory' as recommendation_type, 'Monitor memory component: ' || pool as description, 'LOW' as priorityFROM ( SELECT pool, ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool ORDER BY SUM(bytes) DESC FETCH FIRST 1 ROWS ONLY)WHERE size_gb > 1 AND UPPER('&1') IN ('MEMORY', 'ALL')ORDER BY 3 DESC, 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===========================================PROMPT PERFORMANCE DIAGNOSTICS COMPLETEDPROMPT ===========================================PROMPT Analysis Type: &1PROMPT Top N Results: &2PROMPT Completion Time: &&_DATE &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Investigate top SQL statements for optimizationPROMPT 3. Address significant wait events identifiedPROMPT 4. Monitor memory usage trends over timePROMPT
TIMING SHOW
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------