database_workload_analyzer.sql
-- ------------------------------------------------------------------------------------- File Name : database_workload_analyzer.sql-- Author : Pierre Montbleau-- Description : Oracle database workload analysis and characterization utility-- Purpose : Analyze database workload patterns, identify resource consumers, and optimize performance-- Call Syntax : @F:\DBA\Scripts\database_workload_analyzer.sql (analysis_type) (time_period) (detail_level)-- Parameters : analysis_type - Type of analysis (CURRENT, HISTORICAL, PEAK, PATTERN, SUMMARY)-- time_period - Analysis period in hours or 'PEAK' for peak hours (default: 24)-- detail_level - Detail level (BASIC, STANDARD, COMPREHENSIVE)-- 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_workload_analyzer_' || 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 Workload Analyzer...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Analysis Type: &1PROMPT Time Period: &2PROMPT Detail Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_analysis_type VARCHAR2(20) := UPPER('&1'); v_time_period VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3'); v_time_value NUMBER;BEGIN IF v_analysis_type NOT IN ('CURRENT', 'HISTORICAL', 'PEAK', 'PATTERN', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be CURRENT, HISTORICAL, PEAK, PATTERN, or SUMMARY'); END IF; IF v_analysis_type = 'HISTORICAL' AND v_time_period != 'PEAK' THEN BEGIN v_time_value := TO_NUMBER(v_time_period); IF v_time_value < 1 OR v_time_value > 744 THEN -- Max 31 days RAISE_APPLICATION_ERROR(-20002, 'Time period must be between 1 and 744 hours or PEAK'); END IF; EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20003, 'Time period must be numeric or PEAK for historical analysis'); END; END IF; IF v_detail_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20004, 'Detail level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- WORKLOAD SUMMARY: Overall workload characteristics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. WORKLOAD CHARACTERISTICS SUMMARYPROMPT ====================================
COLUMN workload_metric FORMAT A40COLUMN current_value FORMAT A30COLUMN trend FORMAT A15COLUMN workload_intensity FORMAT A20COLUMN recommendation FORMAT A60
SELECT 'Active Sessions' as workload_metric, TO_CHAR(COUNT(*)) as current_value, 'STABLE' as trend, CASE WHEN COUNT(*) < 10 THEN 'LOW' WHEN COUNT(*) < 50 THEN 'MODERATE' WHEN COUNT(*) < 100 THEN 'HIGH' ELSE 'VERY HIGH' END as workload_intensity, CASE WHEN COUNT(*) > 100 THEN 'Consider load balancing or scaling' WHEN COUNT(*) > 50 THEN 'Monitor session growth' ELSE 'Workload within normal range' END as recommendationFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'UNION ALLSELECT 'CPU Utilization (%)', TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2)), 'STABLE', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) < 50 THEN 'LOW' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) < 80 THEN 'MODERATE' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'Investigate high CPU queries' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 80 THEN 'Monitor CPU usage trends' ELSE 'CPU utilization normal' ENDFROM dualUNION ALLSELECT 'I/O Rate (Reads/sec)', TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2), 2)), 'STABLE', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) < 100 THEN 'LOW' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) < 500 THEN 'MODERATE' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 'Optimize queries and indexes' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 500 THEN 'Consider increasing buffer cache' ELSE 'I/O rate normal' ENDFROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- TOP RESOURCE CONSUMERS: Identify highest resource-consuming sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TOP RESOURCE CONSUMING SESSIONSPROMPT ===================================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN cpu_seconds FORMAT 999,999COLUMN logical_reads FORMAT 999,999,999COLUMN physical_reads FORMAT 999,999,999COLUMN status FORMAT A10
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, ROUND(ss.value/100) as cpu_seconds, st.buffer_gets as logical_reads, st.disk_reads as physical_reads, s.statusFROM v$sesstat ss, v$statname sn, v$session s, v$sqlarea stWHERE ss.statistic# = sn.statistic# AND sn.name = 'CPU used by this session' AND ss.sid = s.sid AND s.sql_address = st.address(+) AND s.sql_hash_value = st.hash_value(+) AND ss.value > 0 AND s.type = 'USER' AND s.username IS NOT NULLORDER BY ss.value DESC;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SQL WORKLOAD ANALYSIS: High-impact SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. HIGH-IMPACT SQL WORKLOAD ANALYSISPROMPT =====================================
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 rows_processed 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, rows_processed, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND executions > 0 AND UPPER('&3') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- WAIT EVENT ANALYSIS: Workload wait distribution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. WORKLOAD WAIT EVENT DISTRIBUTIONPROMPT ====================================
COLUMN wait_class FORMAT A20COLUMN total_waits FORMAT 999,999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN pct_total_time FORMAT 999.99
SELECT wait_class, SUM(total_waits) as total_waits, ROUND(SUM(time_waited_micro) / 1000000, 2) as total_time_sec, ROUND((SUM(time_waited_micro) / SUM(total_waits)) / 1000, 2) as avg_wait_ms, ROUND((SUM(time_waited_micro) / SUM(SUM(time_waited_micro)) OVER ()) * 100, 2) as pct_total_timeFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0GROUP BY wait_classORDER BY SUM(time_waited_micro) DESC;
-- ------------------------------------------------------------------------------------- APPLICATION WORKLOAD PATTERNS: User and program analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. APPLICATION WORKLOAD PATTERNSPROMPT =================================
COLUMN username FORMAT A20COLUMN program FORMAT A30COLUMN session_count FORMAT 999,999COLUMN avg_active_time FORMAT 999,999COLUMN workload_type FORMAT A20
SELECT NVL(username, 'BATCH') as username, SUBSTR(program, 1, 30) as program, COUNT(*) as session_count, ROUND(AVG(last_call_et)) as avg_active_time, CASE WHEN program LIKE '%JDBC%' OR program LIKE '%OCI%' THEN 'APPLICATION' WHEN program LIKE '%sqlplus%' THEN 'AD-HOC' WHEN username IS NULL THEN 'BATCH' WHEN program LIKE '%rman%' THEN 'BACKUP' ELSE 'OTHER' END as workload_typeFROM v$sessionWHERE type = 'USER' AND status = 'ACTIVE'GROUP BY username, SUBSTR(program, 1, 30)HAVING COUNT(*) > 1 OR AVG(last_call_et) > 300ORDER BY 3 DESC, 4 DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- I/O WORKLOAD ANALYSIS: Storage I/O patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. I/O WORKLOAD CHARACTERISTICSPROMPT =================================
COLUMN tablespace_name FORMAT A20COLUMN read_mb_per_sec FORMAT 999,999.99COLUMN write_mb_per_sec FORMAT 999,999.99COLUMN total_iops FORMAT 999,999COLUMN avg_read_time_ms FORMAT 999.99COLUMN io_intensity FORMAT A15
SELECT tablespace_name, ROUND(phyblkrd_bytes / 1024 / 1024 / NULLIF(time_ms, 0) * 1000, 2) as read_mb_per_sec, ROUND(phyblkwrt_bytes / 1024 / 1024 / NULLIF(time_ms, 0) * 1000, 2) as write_mb_per_sec, ROUND((phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000) as total_iops, ROUND((readtim / NULLIF(phyblkrd, 0)) * 10, 2) as avg_read_time_ms, CASE WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 1000 THEN 'VERY HIGH' WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 500 THEN 'HIGH' WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 100 THEN 'MODERATE' ELSE 'LOW' END as io_intensityFROM v$tablespace_io_statsWHERE time_ms > 0 AND (phyblkrd > 0 OR phyblkwrt > 0)ORDER BY total_iops DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY WORKLOAD ANALYSIS: Memory usage patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MEMORY WORKLOAD PATTERNSPROMPT =============================
COLUMN memory_component FORMAT A25COLUMN current_usage_gb FORMAT 999,999.99COLUMN allocation_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN workload_pressure FORMAT A20
SELECT 'Buffer Cache' as memory_component, ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2) as current_usage_gb, ROUND(bytes / 1024 / 1024 / 1024, 2) as allocation_gb, ROUND((bytes - free_memory) / bytes * 100, 2) as utilization_pct, CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 95 THEN 'HIGH PRESSURE' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' END as workload_pressureFROM ( 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 - free_memory) / 1024 / 1024 / 1024, 2), ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'HIGH PRESSURE' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 85 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' 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')UNION ALLSELECT 'PGA Aggregate', ROUND(value / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2), CASE WHEN ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2) > 90 THEN 'HIGH PRESSURE' WHEN ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2) > 80 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' ENDFROM v$pgastatWHERE name = 'aggregate PGA target parameter'ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- WORKLOAD TREND ANALYSIS: Historical workload patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. WORKLOAD TREND ANALYSISPROMPT ===========================
COLUMN time_period FORMAT A15COLUMN avg_active_sessions FORMAT 999.99COLUMN max_active_sessions FORMAT 999COLUMN avg_cpu_pct FORMAT 999.99COLUMN workload_trend FORMAT A15
SELECT 'Current Hour' as time_period, (SELECT ROUND(AVG(value), 2) FROM v$sysmetric WHERE metric_name = 'Average Active Sessions' AND group_id = 2) as avg_active_sessions, (SELECT MAX(session_count) FROM (SELECT COUNT(*) as session_count FROM v$session WHERE status = 'ACTIVE' AND type = 'USER' GROUP BY TO_CHAR(logon_time, 'YYYY-MM-DD HH24'))) as max_active_sessions, (SELECT ROUND(AVG(value), 2) FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) as avg_cpu_pct, 'STABLE' as workload_trendFROM dualUNION ALLSELECT 'Last 24 Hours', NULL as avg_active_sessions, NULL as max_active_sessions, NULL as avg_cpu_pct, 'ANALYSIS REQUIRED' as workload_trendFROM dualWHERE UPPER('&1') = 'HISTORICAL'UNION ALLSELECT 'Peak Hours', NULL, NULL, NULL, 'IDENTIFY PEAKS' as workload_trendFROM dualWHERE UPPER('&2') = 'PEAK'ORDER BY CASE time_period WHEN 'Current Hour' THEN 1 WHEN 'Last 24 Hours' THEN 2 WHEN 'Peak Hours' THEN 3 END;
-- ------------------------------------------------------------------------------------- WORKLOAD RECOMMENDATIONS: Optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. WORKLOAD OPTIMIZATION RECOMMENDATIONSPROMPT =========================================
COLUMN recommendation FORMAT A60COLUMN category FORMAT A15COLUMN priority FORMAT A10COLUMN expected_impact FORMAT A15COLUMN implementation_effort FORMAT A15
SELECT 'Optimize high CPU SQL statements' as recommendation, 'SQL TUNING' as category, 'HIGH' as priority, 'HIGH' as expected_impact, 'MEDIUM' as implementation_effortFROM dualWHERE EXISTS ( SELECT 1 FROM v$sqlstats WHERE ROUND(cpu_time/1000000, 2) > 1000 AND executions > 100)UNION ALLSELECT 'Increase buffer cache size for high physical reads', 'MEMORY', 'MEDIUM', 'HIGH', 'LOW'FROM dualWHERE (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000UNION ALLSELECT 'Implement connection pooling for high session count', 'CONFIGURATION', 'MEDIUM', 'MEDIUM', 'MEDIUM'FROM dualWHERE (SELECT COUNT(*) FROM v$session WHERE type = 'USER') > 200UNION ALLSELECT 'Schedule batch jobs during off-peak hours', 'SCHEDULING', 'LOW', 'MEDIUM', 'LOW'FROM dualWHERE EXISTS ( SELECT 1 FROM v$session WHERE program LIKE '%batch%' OR program LIKE '%job%' AND status = 'ACTIVE')UNION ALLSELECT 'Review and optimize I/O intensive tablespaces', 'STORAGE', 'MEDIUM', 'HIGH', 'HIGH'FROM dualWHERE EXISTS ( SELECT 1 FROM v$tablespace_io_stats WHERE (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 1000)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 END, implementation_effort;
-- ------------------------------------------------------------------------------------- WORKLOAD CAPACITY PLANNING: Resource utilization and capacity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. WORKLOAD CAPACITY ANALYSISPROMPT ===============================
COLUMN resource_type FORMAT A20COLUMN current_utilization FORMAT A20COLUMN capacity_limit FORMAT A20COLUMN utilization_pct FORMAT 999.99COLUMN capacity_status FORMAT A15
SELECT 'CPU Utilization' as resource_type, TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2)) || '%' as current_utilization, '100%' as capacity_limit, ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2) as utilization_pct, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'CRITICAL' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 80 THEN 'WARNING' ELSE 'HEALTHY' END as capacity_statusFROM dualUNION ALLSELECT 'Memory Utilization', ROUND((SELECT SUM(value) FROM v$sga) / 1024 / 1024 / 1024, 2) || ' GB', ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) || ' GB', ROUND((SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') * 100, 2), CASE WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.95 THEN 'CRITICAL' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Session Capacity', TO_CHAR(COUNT(*)), (SELECT value FROM v$parameter WHERE name = 'sessions') as capacity_limit, ROUND(COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) * 100, 2), CASE WHEN COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) > 0.90 THEN 'CRITICAL' WHEN COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) > 0.80 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM v$sessionWHERE type = 'USER'ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Workload analysis summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT DATABASE WORKLOAD ANALYSIS COMPLETEDPROMPT ======================================PROMPT Analysis Type: &1PROMPT Time Period: &2PROMPT Detail Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Findings:PROMPT - Review HIGH priority optimization recommendationsPROMPT - Monitor resource utilization trendsPROMPT - Plan capacity based on workload patternsPROMPT - Implement workload-specific optimizationsPROMPT
-- ------------------------------------------------------------------------------------- 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_workload_analyzer_' || 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 Workload Analyzer...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Analysis Type: &1PROMPT Time Period: &2PROMPT Detail Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_analysis_type VARCHAR2(20) := UPPER('&1'); v_time_period VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3'); v_time_value NUMBER;BEGIN IF v_analysis_type NOT IN ('CURRENT', 'HISTORICAL', 'PEAK', 'PATTERN', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20001, 'Analysis type must be CURRENT, HISTORICAL, PEAK, PATTERN, or SUMMARY'); END IF; IF v_analysis_type = 'HISTORICAL' AND v_time_period != 'PEAK' THEN BEGIN v_time_value := TO_NUMBER(v_time_period); IF v_time_value < 1 OR v_time_value > 744 THEN -- Max 31 days RAISE_APPLICATION_ERROR(-20002, 'Time period must be between 1 and 744 hours or PEAK'); END IF; EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20003, 'Time period must be numeric or PEAK for historical analysis'); END; END IF; IF v_detail_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20004, 'Detail level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- WORKLOAD SUMMARY: Overall workload characteristics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. WORKLOAD CHARACTERISTICS SUMMARYPROMPT ====================================
COLUMN workload_metric FORMAT A40COLUMN current_value FORMAT A30COLUMN trend FORMAT A15COLUMN workload_intensity FORMAT A20COLUMN recommendation FORMAT A60
SELECT 'Active Sessions' as workload_metric, TO_CHAR(COUNT(*)) as current_value, 'STABLE' as trend, CASE WHEN COUNT(*) < 10 THEN 'LOW' WHEN COUNT(*) < 50 THEN 'MODERATE' WHEN COUNT(*) < 100 THEN 'HIGH' ELSE 'VERY HIGH' END as workload_intensity, CASE WHEN COUNT(*) > 100 THEN 'Consider load balancing or scaling' WHEN COUNT(*) > 50 THEN 'Monitor session growth' ELSE 'Workload within normal range' END as recommendationFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'UNION ALLSELECT 'CPU Utilization (%)', TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2)), 'STABLE', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) < 50 THEN 'LOW' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) < 80 THEN 'MODERATE' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'Investigate high CPU queries' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 80 THEN 'Monitor CPU usage trends' ELSE 'CPU utilization normal' ENDFROM dualUNION ALLSELECT 'I/O Rate (Reads/sec)', TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2), 2)), 'STABLE', CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) < 100 THEN 'LOW' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) < 500 THEN 'MODERATE' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000 THEN 'Optimize queries and indexes' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 500 THEN 'Consider increasing buffer cache' ELSE 'I/O rate normal' ENDFROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- TOP RESOURCE CONSUMERS: Identify highest resource-consuming sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TOP RESOURCE CONSUMING SESSIONSPROMPT ===================================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN cpu_seconds FORMAT 999,999COLUMN logical_reads FORMAT 999,999,999COLUMN physical_reads FORMAT 999,999,999COLUMN status FORMAT A10
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, ROUND(ss.value/100) as cpu_seconds, st.buffer_gets as logical_reads, st.disk_reads as physical_reads, s.statusFROM v$sesstat ss, v$statname sn, v$session s, v$sqlarea stWHERE ss.statistic# = sn.statistic# AND sn.name = 'CPU used by this session' AND ss.sid = s.sid AND s.sql_address = st.address(+) AND s.sql_hash_value = st.hash_value(+) AND ss.value > 0 AND s.type = 'USER' AND s.username IS NOT NULLORDER BY ss.value DESC;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SQL WORKLOAD ANALYSIS: High-impact SQL statements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. HIGH-IMPACT SQL WORKLOAD ANALYSISPROMPT =====================================
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 rows_processed 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, rows_processed, SUBSTR(sql_text, 1, 50) as sql_textFROM v$sqlstatsWHERE cpu_time > 0 AND executions > 0 AND UPPER('&3') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- WAIT EVENT ANALYSIS: Workload wait distribution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. WORKLOAD WAIT EVENT DISTRIBUTIONPROMPT ====================================
COLUMN wait_class FORMAT A20COLUMN total_waits FORMAT 999,999,999,999COLUMN total_time_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN pct_total_time FORMAT 999.99
SELECT wait_class, SUM(total_waits) as total_waits, ROUND(SUM(time_waited_micro) / 1000000, 2) as total_time_sec, ROUND((SUM(time_waited_micro) / SUM(total_waits)) / 1000, 2) as avg_wait_ms, ROUND((SUM(time_waited_micro) / SUM(SUM(time_waited_micro)) OVER ()) * 100, 2) as pct_total_timeFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0GROUP BY wait_classORDER BY SUM(time_waited_micro) DESC;
-- ------------------------------------------------------------------------------------- APPLICATION WORKLOAD PATTERNS: User and program analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. APPLICATION WORKLOAD PATTERNSPROMPT =================================
COLUMN username FORMAT A20COLUMN program FORMAT A30COLUMN session_count FORMAT 999,999COLUMN avg_active_time FORMAT 999,999COLUMN workload_type FORMAT A20
SELECT NVL(username, 'BATCH') as username, SUBSTR(program, 1, 30) as program, COUNT(*) as session_count, ROUND(AVG(last_call_et)) as avg_active_time, CASE WHEN program LIKE '%JDBC%' OR program LIKE '%OCI%' THEN 'APPLICATION' WHEN program LIKE '%sqlplus%' THEN 'AD-HOC' WHEN username IS NULL THEN 'BATCH' WHEN program LIKE '%rman%' THEN 'BACKUP' ELSE 'OTHER' END as workload_typeFROM v$sessionWHERE type = 'USER' AND status = 'ACTIVE'GROUP BY username, SUBSTR(program, 1, 30)HAVING COUNT(*) > 1 OR AVG(last_call_et) > 300ORDER BY 3 DESC, 4 DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- I/O WORKLOAD ANALYSIS: Storage I/O patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. I/O WORKLOAD CHARACTERISTICSPROMPT =================================
COLUMN tablespace_name FORMAT A20COLUMN read_mb_per_sec FORMAT 999,999.99COLUMN write_mb_per_sec FORMAT 999,999.99COLUMN total_iops FORMAT 999,999COLUMN avg_read_time_ms FORMAT 999.99COLUMN io_intensity FORMAT A15
SELECT tablespace_name, ROUND(phyblkrd_bytes / 1024 / 1024 / NULLIF(time_ms, 0) * 1000, 2) as read_mb_per_sec, ROUND(phyblkwrt_bytes / 1024 / 1024 / NULLIF(time_ms, 0) * 1000, 2) as write_mb_per_sec, ROUND((phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000) as total_iops, ROUND((readtim / NULLIF(phyblkrd, 0)) * 10, 2) as avg_read_time_ms, CASE WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 1000 THEN 'VERY HIGH' WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 500 THEN 'HIGH' WHEN (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 100 THEN 'MODERATE' ELSE 'LOW' END as io_intensityFROM v$tablespace_io_statsWHERE time_ms > 0 AND (phyblkrd > 0 OR phyblkwrt > 0)ORDER BY total_iops DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY WORKLOAD ANALYSIS: Memory usage patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MEMORY WORKLOAD PATTERNSPROMPT =============================
COLUMN memory_component FORMAT A25COLUMN current_usage_gb FORMAT 999,999.99COLUMN allocation_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN workload_pressure FORMAT A20
SELECT 'Buffer Cache' as memory_component, ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2) as current_usage_gb, ROUND(bytes / 1024 / 1024 / 1024, 2) as allocation_gb, ROUND((bytes - free_memory) / bytes * 100, 2) as utilization_pct, CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 95 THEN 'HIGH PRESSURE' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' END as workload_pressureFROM ( 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 - free_memory) / 1024 / 1024 / 1024, 2), ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'HIGH PRESSURE' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 85 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' 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')UNION ALLSELECT 'PGA Aggregate', ROUND(value / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2), CASE WHEN ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2) > 90 THEN 'HIGH PRESSURE' WHEN ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2) > 80 THEN 'MODERATE PRESSURE' ELSE 'LOW PRESSURE' ENDFROM v$pgastatWHERE name = 'aggregate PGA target parameter'ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- WORKLOAD TREND ANALYSIS: Historical workload patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. WORKLOAD TREND ANALYSISPROMPT ===========================
COLUMN time_period FORMAT A15COLUMN avg_active_sessions FORMAT 999.99COLUMN max_active_sessions FORMAT 999COLUMN avg_cpu_pct FORMAT 999.99COLUMN workload_trend FORMAT A15
SELECT 'Current Hour' as time_period, (SELECT ROUND(AVG(value), 2) FROM v$sysmetric WHERE metric_name = 'Average Active Sessions' AND group_id = 2) as avg_active_sessions, (SELECT MAX(session_count) FROM (SELECT COUNT(*) as session_count FROM v$session WHERE status = 'ACTIVE' AND type = 'USER' GROUP BY TO_CHAR(logon_time, 'YYYY-MM-DD HH24'))) as max_active_sessions, (SELECT ROUND(AVG(value), 2) FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) as avg_cpu_pct, 'STABLE' as workload_trendFROM dualUNION ALLSELECT 'Last 24 Hours', NULL as avg_active_sessions, NULL as max_active_sessions, NULL as avg_cpu_pct, 'ANALYSIS REQUIRED' as workload_trendFROM dualWHERE UPPER('&1') = 'HISTORICAL'UNION ALLSELECT 'Peak Hours', NULL, NULL, NULL, 'IDENTIFY PEAKS' as workload_trendFROM dualWHERE UPPER('&2') = 'PEAK'ORDER BY CASE time_period WHEN 'Current Hour' THEN 1 WHEN 'Last 24 Hours' THEN 2 WHEN 'Peak Hours' THEN 3 END;
-- ------------------------------------------------------------------------------------- WORKLOAD RECOMMENDATIONS: Optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. WORKLOAD OPTIMIZATION RECOMMENDATIONSPROMPT =========================================
COLUMN recommendation FORMAT A60COLUMN category FORMAT A15COLUMN priority FORMAT A10COLUMN expected_impact FORMAT A15COLUMN implementation_effort FORMAT A15
SELECT 'Optimize high CPU SQL statements' as recommendation, 'SQL TUNING' as category, 'HIGH' as priority, 'HIGH' as expected_impact, 'MEDIUM' as implementation_effortFROM dualWHERE EXISTS ( SELECT 1 FROM v$sqlstats WHERE ROUND(cpu_time/1000000, 2) > 1000 AND executions > 100)UNION ALLSELECT 'Increase buffer cache size for high physical reads', 'MEMORY', 'MEDIUM', 'HIGH', 'LOW'FROM dualWHERE (SELECT value FROM v$sysmetric WHERE metric_name = 'Physical Reads Per Sec' AND group_id = 2) > 1000UNION ALLSELECT 'Implement connection pooling for high session count', 'CONFIGURATION', 'MEDIUM', 'MEDIUM', 'MEDIUM'FROM dualWHERE (SELECT COUNT(*) FROM v$session WHERE type = 'USER') > 200UNION ALLSELECT 'Schedule batch jobs during off-peak hours', 'SCHEDULING', 'LOW', 'MEDIUM', 'LOW'FROM dualWHERE EXISTS ( SELECT 1 FROM v$session WHERE program LIKE '%batch%' OR program LIKE '%job%' AND status = 'ACTIVE')UNION ALLSELECT 'Review and optimize I/O intensive tablespaces', 'STORAGE', 'MEDIUM', 'HIGH', 'HIGH'FROM dualWHERE EXISTS ( SELECT 1 FROM v$tablespace_io_stats WHERE (phyblkrd + phyblkwrt) / NULLIF(time_ms, 0) * 1000 > 1000)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 END, implementation_effort;
-- ------------------------------------------------------------------------------------- WORKLOAD CAPACITY PLANNING: Resource utilization and capacity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. WORKLOAD CAPACITY ANALYSISPROMPT ===============================
COLUMN resource_type FORMAT A20COLUMN current_utilization FORMAT A20COLUMN capacity_limit FORMAT A20COLUMN utilization_pct FORMAT 999.99COLUMN capacity_status FORMAT A15
SELECT 'CPU Utilization' as resource_type, TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2)) || '%' as current_utilization, '100%' as capacity_limit, ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2), 2) as utilization_pct, CASE WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 90 THEN 'CRITICAL' WHEN (SELECT value FROM v$sysmetric WHERE metric_name = 'Database CPU Time Ratio' AND group_id = 2) > 80 THEN 'WARNING' ELSE 'HEALTHY' END as capacity_statusFROM dualUNION ALLSELECT 'Memory Utilization', ROUND((SELECT SUM(value) FROM v$sga) / 1024 / 1024 / 1024, 2) || ' GB', ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) || ' GB', ROUND((SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') * 100, 2), CASE WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.95 THEN 'CRITICAL' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Session Capacity', TO_CHAR(COUNT(*)), (SELECT value FROM v$parameter WHERE name = 'sessions') as capacity_limit, ROUND(COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) * 100, 2), CASE WHEN COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) > 0.90 THEN 'CRITICAL' WHEN COUNT(*) / TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'sessions')) > 0.80 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM v$sessionWHERE type = 'USER'ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Workload analysis summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT DATABASE WORKLOAD ANALYSIS COMPLETEDPROMPT ======================================PROMPT Analysis Type: &1PROMPT Time Period: &2PROMPT Detail Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Findings:PROMPT - Review HIGH priority optimization recommendationsPROMPT - Monitor resource utilization trendsPROMPT - Plan capacity based on workload patternsPROMPT - Implement workload-specific optimizationsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------