database_parameter_optimizer.sql
-- ------------------------------------------------------------------------------------- File Name : database_parameter_optimizer.sql-- Author : Pierre Montbleau-- Description : Oracle database parameter analysis and optimization utility-- Purpose : Analyze initialization parameters, recommend optimizations, and validate settings-- Call Syntax : @F:\DBA\Scripts\database_parameter_optimizer.sql (action) (parameter_scope) (optimization_level)-- Parameters : action - Optimization action (ANALYZE, COMPARE, RECOMMEND, VALIDATE, REPORT)-- parameter_scope - Scope of parameters (ALL, PERFORMANCE, MEMORY, SECURITY, STORAGE)-- optimization_level - Optimization level (BASIC, AGGRESSIVE, CONSERVATIVE, CUSTOM)-- 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_parameter_optimizer_' || 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 Parameter Optimizer...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Parameter Scope: &2PROMPT Optimization Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_parameter_scope VARCHAR2(20) := UPPER('&2'); v_optimization_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('ANALYZE', 'COMPARE', 'RECOMMEND', 'VALIDATE', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ANALYZE, COMPARE, RECOMMEND, VALIDATE, or REPORT'); END IF; IF v_parameter_scope NOT IN ('ALL', 'PERFORMANCE', 'MEMORY', 'SECURITY', 'STORAGE') THEN RAISE_APPLICATION_ERROR(-20002, 'Parameter scope must be ALL, PERFORMANCE, MEMORY, SECURITY, or STORAGE'); END IF; IF v_optimization_level NOT IN ('BASIC', 'AGGRESSIVE', 'CONSERVATIVE', 'CUSTOM') THEN RAISE_APPLICATION_ERROR(-20003, 'Optimization level must be BASIC, AGGRESSIVE, CONSERVATIVE, or CUSTOM'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CURRENT PARAMETER ANALYSIS: Current database parameter settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. CURRENT PARAMETER SETTINGS ANALYSISPROMPT =======================================
col PARAMETER_NAME for a40col CURRENT_VALUE for a45col IS_DEFAU for a10col DESCRIPTION for a55
DEFINE v_parameter_scope = '&1' -- or 'ALL', 'PERFORMANCE', 'MEMORY', 'SECURITY', 'STORAGE'
SELECT name as parameter_name, value as current_value, CASE WHEN isdefault = 'TRUE' THEN 'DEFAULT' ELSE 'MODIFIED' END as is_default, SUBSTR(description, 1, 50) as descriptionFROM v$parameterWHERE ('&&v_parameter_scope' = 'ALL' OR ('&&v_parameter_scope' = 'PERFORMANCE' AND name IN ( 'sga_target', 'pga_aggregate_target', 'db_cache_size', 'shared_pool_size', 'processes', 'sessions', 'open_cursors' )) OR ('&&v_parameter_scope' = 'MEMORY' AND (name LIKE '%memory%' OR name LIKE '%size%')) OR ('&&v_parameter_scope' = 'SECURITY' AND (name LIKE '%audit%' OR name LIKE '%password%')) OR ('&&v_parameter_scope' = 'STORAGE' AND (name LIKE '%db_block%' OR name LIKE '%file%'))) --AND ROWNUM <= 20ORDER BY 3, 1;
-- Clean up substitution variableUNDEFINE v_parameter_scope
-- ------------------------------------------------------------------------------------- PARAMETER COMPARISON: Compare current vs recommended values-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PARAMETER VALUE COMPARISONPROMPT ==============================
COLUMN parameter_name FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN difference_pct FORMAT 999.99COLUMN optimization_status FORMAT A15
WITH sga_info AS ( SELECT SUM(value) as total_sga FROM v$sga),pga_info AS ( SELECT value/1024/1024 as max_pga_mb FROM v$pgastat WHERE name = 'maximum PGA allocated'),param_data AS ( SELECT name, value as current_value, CASE '&3' WHEN 'AGGRESSIVE' THEN 1.2 WHEN 'CONSERVATIVE' THEN 1.1 ELSE 1.0 END as sga_multiplier, CASE '&3' WHEN 'AGGRESSIVE' THEN 1.5 WHEN 'CONSERVATIVE' THEN 1.2 ELSE 1.0 END as pga_multiplier FROM v$parameter WHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors'))SELECT p.name as parameter_name, p.current_value, CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) WHEN 'processes' THEN CASE WHEN TO_NUMBER(p.current_value) < 500 THEN '500' ELSE p.current_value END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(p.current_value) < 600 THEN '600' ELSE p.current_value END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(p.current_value) < 300 THEN '300' ELSE p.current_value END ELSE p.current_value END as recommended_value, CASE WHEN p.name IN ('sga_target', 'pga_aggregate_target') THEN ROUND( ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) * 100, 2 ) ELSE 0 END as difference_pct, CASE WHEN p.current_value = CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) WHEN 'processes' THEN CASE WHEN TO_NUMBER(p.current_value) < 500 THEN '500' ELSE p.current_value END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(p.current_value) < 600 THEN '600' ELSE p.current_value END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(p.current_value) < 300 THEN '300' ELSE p.current_value END ELSE p.current_value END THEN 'OPTIMAL' WHEN p.name IN ('sga_target', 'pga_aggregate_target') AND ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) > 0.2 THEN 'HIGH IMPACT' WHEN p.name IN ('sga_target', 'pga_aggregate_target') AND ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) > 0.1 THEN 'MEDIUM IMPACT' ELSE 'LOW IMPACT' END as optimization_statusFROM param_data pWHERE '&2' IN ('ALL', 'PERFORMANCE', 'MEMORY')ORDER BY CASE optimization_status WHEN 'HIGH IMPACT' THEN 1 WHEN 'MEDIUM IMPACT' THEN 2 WHEN 'LOW IMPACT' THEN 3 ELSE 4 END, difference_pct DESC;
-- ------------------------------------------------------------------------------------- MEMORY PARAMETER ANALYSIS: Memory-related parameter optimization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. MEMORY PARAMETER OPTIMIZATION ANALYSISPROMPT ==========================================
COLUMN memory_parameter FORMAT A25COLUMN current_setting FORMAT A20COLUMN recommended_setting FORMAT A20COLUMN utilization_pct FORMAT 999.99COLUMN optimization_recommendation FORMAT A60
SELECT 'SGA_TARGET' as memory_parameter, TO_CHAR(value) as current_setting, TO_CHAR(ROUND((SELECT SUM(value) FROM v$sga) * CASE '&3' WHEN 'AGGRESSIVE' THEN 1.3 WHEN 'CONSERVATIVE' THEN 1.15 ELSE 1.2 END)) as recommended_setting, ROUND((SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) * 100, 2) as utilization_pct, CASE WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.95 THEN 'CRITICAL: Increase SGA_TARGET immediately' WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.90 THEN 'HIGH: Consider increasing SGA_TARGET' WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) < 0.70 THEN 'LOW: SGA may be over-allocated' ELSE 'OPTIMAL: SGA allocation appropriate' END as optimization_recommendationFROM v$parameterWHERE name = 'sga_target'UNION ALLSELECT 'PGA_AGGREGATE_TARGET', TO_CHAR(value), TO_CHAR(ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * CASE '&3' WHEN 'AGGRESSIVE' THEN 1.8 WHEN 'CONSERVATIVE' THEN 1.3 ELSE 1.5 END)), ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / NULLIF(TO_NUMBER(value), 0) * 100, 2), CASE WHEN (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.95 THEN 'CRITICAL: Increase PGA target' WHEN (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.80 THEN 'HIGH: Monitor PGA usage' ELSE 'OPTIMAL: PGA allocation sufficient' ENDFROM v$parameterWHERE name = 'pga_aggregate_target'ORDER BY 1;
-- ------------------------------------------------------------------------------------- PERFORMANCE PARAMETER ANALYSIS: Critical performance parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. PERFORMANCE PARAMETER ANALYSISPROMPT ==================================
COLUMN performance_parameter FORMAT A25COLUMN current_value FORMAT A15COLUMN optimal_range FORMAT A20COLUMN current_status FORMAT A15COLUMN impact_level FORMAT A15
WITH param_data AS ( SELECT name as performance_parameter, value as current_value, CASE name WHEN 'processes' THEN '300-2000' WHEN 'sessions' THEN '1.1 * processes' WHEN 'open_cursors' THEN '300-1000' WHEN 'db_block_size' THEN '8192' WHEN 'db_file_multiblock_read_count' THEN '16-128' ELSE 'Varies' END as optimal_range, CASE name WHEN 'processes' THEN CASE WHEN TO_NUMBER(value) < 300 THEN 'TOO LOW' WHEN TO_NUMBER(value) > 2000 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(value) < TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'processes')) * 1.1 THEN 'TOO LOW' WHEN TO_NUMBER(value) > TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'processes')) * 1.5 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(value) < 300 THEN 'TOO LOW' WHEN TO_NUMBER(value) > 2000 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'db_block_size' THEN CASE WHEN value = '8192' THEN 'OPTIMAL' ELSE 'SUBOPTIMAL' END ELSE 'CHECK REQUIRED' END as current_status FROM v$parameter WHERE name IN ('processes', 'sessions', 'open_cursors', 'db_block_size', 'db_file_multiblock_read_count') AND '&2' IN ('ALL', 'PERFORMANCE'))SELECT performance_parameter, current_value, optimal_range, current_status, CASE WHEN performance_parameter IN ('processes', 'sessions') AND current_status != 'OPTIMAL' THEN 'HIGH' WHEN performance_parameter IN ('open_cursors', 'db_block_size') AND current_status != 'OPTIMAL' THEN 'MEDIUM' ELSE 'LOW' END as impact_levelFROM param_dataORDER BY CASE impact_level WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, performance_parameter;
-- ------------------------------------------------------------------------------------- SECURITY PARAMETER ANALYSIS: Security-related parameter validation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. SECURITY PARAMETER VALIDATIONPROMPT =================================
COLUMN security_parameter FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN compliance_status FORMAT A15COLUMN security_risk FORMAT A15
WITH security_params AS ( SELECT name as security_parameter, value as current_value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'os_authent_prefix' THEN 'OPS$' WHEN '07_dictionary_accessibility' THEN 'FALSE' WHEN 'audit_trail' THEN 'DB,EXTENDED' WHEN 'sec_case_sensitive_logon' THEN 'TRUE' WHEN 'remote_login_passwordfile' THEN 'EXCLUSIVE' ELSE value END as recommended_value FROM v$parameter WHERE name IN ('remote_os_authent', 'os_authent_prefix', '07_dictionary_accessibility', 'audit_trail', 'sec_case_sensitive_logon', 'remote_login_passwordfile') AND '&2' IN ('ALL', 'SECURITY'))SELECT security_parameter, current_value, recommended_value, CASE WHEN current_value = recommended_value THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as compliance_status, CASE WHEN security_parameter IN ('remote_os_authent', '07_dictionary_accessibility') AND current_value != recommended_value THEN 'HIGH' WHEN security_parameter IN ('audit_trail', 'sec_case_sensitive_logon') AND current_value != recommended_value THEN 'MEDIUM' ELSE 'LOW' END as security_riskFROM security_paramsORDER BY CASE security_risk WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, security_parameter;
-- ------------------------------------------------------------------------------------- PARAMETER OPTIMIZATION RECOMMENDATIONS: Actionable optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. PARAMETER OPTIMIZATION RECOMMENDATIONSPROMPT ==========================================
COLUMN recommendation FORMAT A60COLUMN parameter_name FORMAT A25COLUMN current_value FORMAT A15COLUMN recommended_value FORMAT A20COLUMN priority FORMAT A10
SELECT 'Increase ' || name || ' for better performance' as recommendation, name as parameter_name, value as current_value, CASE name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT SUM(value) FROM v$sga) * 1.2)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * 1.5)) WHEN 'processes' THEN '500' WHEN 'sessions' THEN '600' WHEN 'open_cursors' THEN '300' ELSE value END as recommended_value, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'HIGH' WHEN name IN ('processes', 'sessions') AND TO_NUMBER(value) < 300 THEN 'HIGH' WHEN name = 'open_cursors' AND TO_NUMBER(value) < 200 THEN 'MEDIUM' ELSE 'LOW' END as priorityFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors') AND (name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 OR name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.8 OR name = 'processes' AND TO_NUMBER(value) < 300 OR name = 'sessions' AND TO_NUMBER(value) < 400 OR name = 'open_cursors' AND TO_NUMBER(value) < 200)UNION ALLSELECT 'Modify security parameter: ' || name, name, value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'O7_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' -- Fixed: O7 not 07 WHEN 'audit_trail' THEN 'DB,EXTENDED' ELSE value END, 'HIGH'FROM v$parameterWHERE name IN ('remote_os_authent', 'O7_DICTIONARY_ACCESSIBILITY', 'audit_trail') -- Fixed here too AND value != CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'O7_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' -- Fixed here too WHEN 'audit_trail' THEN 'DB,EXTENDED' ELSE value ENDORDER BY 4,2;
-- ------------------------------------------------------------------------------------- PARAMETER CHANGE IMPACT ANALYSIS: Impact assessment of proposed changes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. PARAMETER CHANGE IMPACT ANALYSISPROMPT ====================================
COLUMN parameter_name FORMAT A25COLUMN change_description FORMAT A50COLUMN restart_required FORMAT A5COLUMN risk_level FORMAT A10COLUMN testing_recommendation FORMAT A60
SELECT name as parameter_name, CASE name WHEN 'sga_target' THEN 'Increase SGA size by ' || ROUND( ((SELECT SUM(value) FROM v$sga) * 1.2 - TO_NUMBER(value)) / NULLIF(1024 * 1024 * 1024, 0), 2 ) || ' GB' WHEN 'pga_aggregate_target' THEN 'Increase PGA target by ' || ROUND( ((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * 1.5 - TO_NUMBER(value)) / 1024, 2 ) || ' GB' WHEN 'processes' THEN 'Increase processes from ' || value || ' to 500' WHEN 'sessions' THEN 'Increase sessions from ' || value || ' to 600' ELSE 'Modify ' || name || ' setting' END as change_description, CASE WHEN issys_modifiable = 'FALSE' THEN 'YES' ELSE 'NO' END as restart_required, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') THEN 'MEDIUM' WHEN name IN ('processes', 'sessions') THEN 'LOW' ELSE 'LOW' END as risk_level, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') THEN 'Test in non-production environment first' WHEN name IN ('processes', 'sessions') THEN 'Monitor session usage after change' ELSE 'Standard parameter change procedure' END as testing_recommendationFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors') AND ((name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9) OR (name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.8) OR (name = 'processes' AND TO_NUMBER(value) < 300) OR (name = 'sessions' AND TO_NUMBER(value) < 400))ORDER BY CASE risk_level WHEN 'MEDIUM' THEN 1 WHEN 'LOW' THEN 2 ELSE 3 END DESC, CASE restart_required WHEN 'YES' THEN 1 WHEN 'NO' THEN 2 ELSE 3 END DESC;
-- ------------------------------------------------------------------------------------- PARAMETER VALIDATION: Validate current parameter settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PARAMETER SETTINGS VALIDATIONPROMPT =================================
COLUMN validation_check FORMAT A40COLUMN parameter_name FORMAT A25COLUMN current_value FORMAT A15COLUMN validation_status FORMAT A15COLUMN validation_notes FORMAT A60
SELECT 'Memory Parameter Validation' as validation_check, name as parameter_name, value as current_value, CASE WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) BETWEEN 0.7 AND 0.9 THEN 'PASS' WHEN name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) < 0.9 THEN 'PASS' ELSE 'REVIEW' END as validation_status, CASE WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'SGA utilization high - consider increase' WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) < 0.7 THEN 'SGA may be over-allocated' WHEN name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'PGA usage high - monitor closely' ELSE 'Parameter within acceptable range' END as validation_notesFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target')UNION ALLSELECT 'Connection Parameter Validation', name, value, CASE WHEN name = 'processes' AND TO_NUMBER(value) >= 300 THEN 'PASS' WHEN name = 'sessions' AND TO_NUMBER(value) >= 400 THEN 'PASS' WHEN name = 'open_cursors' AND TO_NUMBER(value) >= 200 THEN 'PASS' ELSE 'REVIEW' END, CASE WHEN name = 'processes' AND TO_NUMBER(value) < 300 THEN 'Consider increasing processes parameter' WHEN name = 'sessions' AND TO_NUMBER(value) < 400 THEN 'Consider increasing sessions parameter' WHEN name = 'open_cursors' AND TO_NUMBER(value) < 200 THEN 'Consider increasing open_cursors' ELSE 'Connection parameters adequate' ENDFROM v$parameterWHERE name IN ('processes', 'sessions', 'open_cursors')ORDER BY 1,3,2;
-- ------------------------------------------------------------------------------------- OPTIMIZATION IMPLEMENTATION: Generate parameter change commands-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PARAMETER OPTIMIZATION IMPLEMENTATIONPROMPT =========================================
BEGIN IF UPPER('&1') IN ('RECOMMEND', 'REPORT') THEN DECLARE v_optimization_level VARCHAR2(20) := UPPER('&3'); BEGIN DBMS_OUTPUT.PUT_LINE('Parameter Optimization Commands'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Optimization Level: ' || v_optimization_level); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Recommended ALTER SYSTEM commands:'); DBMS_OUTPUT.PUT_LINE(''); -- Generate ALTER SYSTEM commands for memory parameters FOR mem_rec IN ( SELECT name, value as current_value, CASE name WHEN 'sga_target' THEN ROUND((SELECT SUM(value) FROM v$sga) * CASE v_optimization_level WHEN 'AGGRESSIVE' THEN 1.3 WHEN 'CONSERVATIVE' THEN 1.15 ELSE 1.2 END) WHEN 'pga_aggregate_target' THEN ROUND((SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') * CASE v_optimization_level WHEN 'AGGRESSIVE' THEN 1.8 WHEN 'CONSERVATIVE' THEN 1.3 ELSE 1.5 END) END as recommended_value, CASE WHEN issys_modifiable = 'FALSE' THEN 'SCOPE=SPFILE' ELSE 'SCOPE=BOTH' END as scope_setting FROM v$parameter WHERE name IN ('sga_target', 'pga_aggregate_target') AND ((name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / TO_NUMBER(value) > 0.9) OR (name = 'pga_aggregate_target' AND (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') / TO_NUMBER(value) > 0.8)) ) LOOP IF mem_rec.recommended_value IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SET ' || mem_rec.name || ' = ' || mem_rec.recommended_value || ' ' || mem_rec.scope_setting || ';'); DBMS_OUTPUT.PUT_LINE('-- Current: ' || mem_rec.current_value || ', Recommended: ' || mem_rec.recommended_value); DBMS_OUTPUT.PUT_LINE(''); END IF; END LOOP; -- Generate commands for connection parameters FOR conn_rec IN ( SELECT name, value as current_value, CASE name WHEN 'processes' THEN '500' WHEN 'sessions' THEN '600' WHEN 'open_cursors' THEN '300' END as recommended_value, 'SCOPE=SPFILE' as scope_setting FROM v$parameter WHERE name IN ('processes', 'sessions', 'open_cursors') AND ((name = 'processes' AND TO_NUMBER(value) < 300) OR (name = 'sessions' AND TO_NUMBER(value) < 400) OR (name = 'open_cursors' AND TO_NUMBER(value) < 200)) ) LOOP DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SET ' || conn_rec.name || ' = ' || conn_rec.recommended_value || ' ' || conn_rec.scope_setting || ';'); DBMS_OUTPUT.PUT_LINE('-- Current: ' || conn_rec.current_value || ', Recommended: ' || conn_rec.recommended_value); DBMS_OUTPUT.PUT_LINE(''); END LOOP; DBMS_OUTPUT.PUT_LINE('Important Notes:'); DBMS_OUTPUT.PUT_LINE('- Parameters with SCOPE=SPFILE require database restart'); DBMS_OUTPUT.PUT_LINE('- Test changes in non-production environment first'); DBMS_OUTPUT.PUT_LINE('- Monitor performance after implementing changes'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating optimization commands: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Optimization implementation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Parameter optimization summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE PARAMETER OPTIMIZATION COMPLETEDPROMPT ========================================PROMPT Action: &1PROMPT Parameter Scope: &2PROMPT Optimization Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Optimization Summary:PROMPT - Review HIGH priority recommendations firstPROMPT - Validate parameter changes in test environmentPROMPT - Consider database restart requirementsPROMPT - Monitor performance after implementationPROMPT
-- ------------------------------------------------------------------------------------- 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_parameter_optimizer_' || 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 Parameter Optimizer...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Parameter Scope: &2PROMPT Optimization Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_parameter_scope VARCHAR2(20) := UPPER('&2'); v_optimization_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('ANALYZE', 'COMPARE', 'RECOMMEND', 'VALIDATE', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ANALYZE, COMPARE, RECOMMEND, VALIDATE, or REPORT'); END IF; IF v_parameter_scope NOT IN ('ALL', 'PERFORMANCE', 'MEMORY', 'SECURITY', 'STORAGE') THEN RAISE_APPLICATION_ERROR(-20002, 'Parameter scope must be ALL, PERFORMANCE, MEMORY, SECURITY, or STORAGE'); END IF; IF v_optimization_level NOT IN ('BASIC', 'AGGRESSIVE', 'CONSERVATIVE', 'CUSTOM') THEN RAISE_APPLICATION_ERROR(-20003, 'Optimization level must be BASIC, AGGRESSIVE, CONSERVATIVE, or CUSTOM'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CURRENT PARAMETER ANALYSIS: Current database parameter settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. CURRENT PARAMETER SETTINGS ANALYSISPROMPT =======================================
col PARAMETER_NAME for a40col CURRENT_VALUE for a45col IS_DEFAU for a10col DESCRIPTION for a55
DEFINE v_parameter_scope = '&1' -- or 'ALL', 'PERFORMANCE', 'MEMORY', 'SECURITY', 'STORAGE'
SELECT name as parameter_name, value as current_value, CASE WHEN isdefault = 'TRUE' THEN 'DEFAULT' ELSE 'MODIFIED' END as is_default, SUBSTR(description, 1, 50) as descriptionFROM v$parameterWHERE ('&&v_parameter_scope' = 'ALL' OR ('&&v_parameter_scope' = 'PERFORMANCE' AND name IN ( 'sga_target', 'pga_aggregate_target', 'db_cache_size', 'shared_pool_size', 'processes', 'sessions', 'open_cursors' )) OR ('&&v_parameter_scope' = 'MEMORY' AND (name LIKE '%memory%' OR name LIKE '%size%')) OR ('&&v_parameter_scope' = 'SECURITY' AND (name LIKE '%audit%' OR name LIKE '%password%')) OR ('&&v_parameter_scope' = 'STORAGE' AND (name LIKE '%db_block%' OR name LIKE '%file%'))) --AND ROWNUM <= 20ORDER BY 3, 1;
-- Clean up substitution variableUNDEFINE v_parameter_scope
-- ------------------------------------------------------------------------------------- PARAMETER COMPARISON: Compare current vs recommended values-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PARAMETER VALUE COMPARISONPROMPT ==============================
COLUMN parameter_name FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN difference_pct FORMAT 999.99COLUMN optimization_status FORMAT A15
WITH sga_info AS ( SELECT SUM(value) as total_sga FROM v$sga),pga_info AS ( SELECT value/1024/1024 as max_pga_mb FROM v$pgastat WHERE name = 'maximum PGA allocated'),param_data AS ( SELECT name, value as current_value, CASE '&3' WHEN 'AGGRESSIVE' THEN 1.2 WHEN 'CONSERVATIVE' THEN 1.1 ELSE 1.0 END as sga_multiplier, CASE '&3' WHEN 'AGGRESSIVE' THEN 1.5 WHEN 'CONSERVATIVE' THEN 1.2 ELSE 1.0 END as pga_multiplier FROM v$parameter WHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors'))SELECT p.name as parameter_name, p.current_value, CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) WHEN 'processes' THEN CASE WHEN TO_NUMBER(p.current_value) < 500 THEN '500' ELSE p.current_value END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(p.current_value) < 600 THEN '600' ELSE p.current_value END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(p.current_value) < 300 THEN '300' ELSE p.current_value END ELSE p.current_value END as recommended_value, CASE WHEN p.name IN ('sga_target', 'pga_aggregate_target') THEN ROUND( ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) * 100, 2 ) ELSE 0 END as difference_pct, CASE WHEN p.current_value = CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) WHEN 'processes' THEN CASE WHEN TO_NUMBER(p.current_value) < 500 THEN '500' ELSE p.current_value END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(p.current_value) < 600 THEN '600' ELSE p.current_value END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(p.current_value) < 300 THEN '300' ELSE p.current_value END ELSE p.current_value END THEN 'OPTIMAL' WHEN p.name IN ('sga_target', 'pga_aggregate_target') AND ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) > 0.2 THEN 'HIGH IMPACT' WHEN p.name IN ('sga_target', 'pga_aggregate_target') AND ABS( TO_NUMBER( CASE p.name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT total_sga FROM sga_info) * p.sga_multiplier)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT max_pga_mb FROM pga_info) * p.pga_multiplier)) ELSE p.current_value END ) - TO_NUMBER(p.current_value) ) / NULLIF(TO_NUMBER(p.current_value), 0) > 0.1 THEN 'MEDIUM IMPACT' ELSE 'LOW IMPACT' END as optimization_statusFROM param_data pWHERE '&2' IN ('ALL', 'PERFORMANCE', 'MEMORY')ORDER BY CASE optimization_status WHEN 'HIGH IMPACT' THEN 1 WHEN 'MEDIUM IMPACT' THEN 2 WHEN 'LOW IMPACT' THEN 3 ELSE 4 END, difference_pct DESC;
-- ------------------------------------------------------------------------------------- MEMORY PARAMETER ANALYSIS: Memory-related parameter optimization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. MEMORY PARAMETER OPTIMIZATION ANALYSISPROMPT ==========================================
COLUMN memory_parameter FORMAT A25COLUMN current_setting FORMAT A20COLUMN recommended_setting FORMAT A20COLUMN utilization_pct FORMAT 999.99COLUMN optimization_recommendation FORMAT A60
SELECT 'SGA_TARGET' as memory_parameter, TO_CHAR(value) as current_setting, TO_CHAR(ROUND((SELECT SUM(value) FROM v$sga) * CASE '&3' WHEN 'AGGRESSIVE' THEN 1.3 WHEN 'CONSERVATIVE' THEN 1.15 ELSE 1.2 END)) as recommended_setting, ROUND((SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) * 100, 2) as utilization_pct, CASE WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.95 THEN 'CRITICAL: Increase SGA_TARGET immediately' WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.90 THEN 'HIGH: Consider increasing SGA_TARGET' WHEN (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) < 0.70 THEN 'LOW: SGA may be over-allocated' ELSE 'OPTIMAL: SGA allocation appropriate' END as optimization_recommendationFROM v$parameterWHERE name = 'sga_target'UNION ALLSELECT 'PGA_AGGREGATE_TARGET', TO_CHAR(value), TO_CHAR(ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * CASE '&3' WHEN 'AGGRESSIVE' THEN 1.8 WHEN 'CONSERVATIVE' THEN 1.3 ELSE 1.5 END)), ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / NULLIF(TO_NUMBER(value), 0) * 100, 2), CASE WHEN (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.95 THEN 'CRITICAL: Increase PGA target' WHEN (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.80 THEN 'HIGH: Monitor PGA usage' ELSE 'OPTIMAL: PGA allocation sufficient' ENDFROM v$parameterWHERE name = 'pga_aggregate_target'ORDER BY 1;
-- ------------------------------------------------------------------------------------- PERFORMANCE PARAMETER ANALYSIS: Critical performance parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. PERFORMANCE PARAMETER ANALYSISPROMPT ==================================
COLUMN performance_parameter FORMAT A25COLUMN current_value FORMAT A15COLUMN optimal_range FORMAT A20COLUMN current_status FORMAT A15COLUMN impact_level FORMAT A15
WITH param_data AS ( SELECT name as performance_parameter, value as current_value, CASE name WHEN 'processes' THEN '300-2000' WHEN 'sessions' THEN '1.1 * processes' WHEN 'open_cursors' THEN '300-1000' WHEN 'db_block_size' THEN '8192' WHEN 'db_file_multiblock_read_count' THEN '16-128' ELSE 'Varies' END as optimal_range, CASE name WHEN 'processes' THEN CASE WHEN TO_NUMBER(value) < 300 THEN 'TOO LOW' WHEN TO_NUMBER(value) > 2000 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'sessions' THEN CASE WHEN TO_NUMBER(value) < TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'processes')) * 1.1 THEN 'TOO LOW' WHEN TO_NUMBER(value) > TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'processes')) * 1.5 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'open_cursors' THEN CASE WHEN TO_NUMBER(value) < 300 THEN 'TOO LOW' WHEN TO_NUMBER(value) > 2000 THEN 'TOO HIGH' ELSE 'OPTIMAL' END WHEN 'db_block_size' THEN CASE WHEN value = '8192' THEN 'OPTIMAL' ELSE 'SUBOPTIMAL' END ELSE 'CHECK REQUIRED' END as current_status FROM v$parameter WHERE name IN ('processes', 'sessions', 'open_cursors', 'db_block_size', 'db_file_multiblock_read_count') AND '&2' IN ('ALL', 'PERFORMANCE'))SELECT performance_parameter, current_value, optimal_range, current_status, CASE WHEN performance_parameter IN ('processes', 'sessions') AND current_status != 'OPTIMAL' THEN 'HIGH' WHEN performance_parameter IN ('open_cursors', 'db_block_size') AND current_status != 'OPTIMAL' THEN 'MEDIUM' ELSE 'LOW' END as impact_levelFROM param_dataORDER BY CASE impact_level WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, performance_parameter;
-- ------------------------------------------------------------------------------------- SECURITY PARAMETER ANALYSIS: Security-related parameter validation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. SECURITY PARAMETER VALIDATIONPROMPT =================================
COLUMN security_parameter FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN compliance_status FORMAT A15COLUMN security_risk FORMAT A15
WITH security_params AS ( SELECT name as security_parameter, value as current_value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'os_authent_prefix' THEN 'OPS$' WHEN '07_dictionary_accessibility' THEN 'FALSE' WHEN 'audit_trail' THEN 'DB,EXTENDED' WHEN 'sec_case_sensitive_logon' THEN 'TRUE' WHEN 'remote_login_passwordfile' THEN 'EXCLUSIVE' ELSE value END as recommended_value FROM v$parameter WHERE name IN ('remote_os_authent', 'os_authent_prefix', '07_dictionary_accessibility', 'audit_trail', 'sec_case_sensitive_logon', 'remote_login_passwordfile') AND '&2' IN ('ALL', 'SECURITY'))SELECT security_parameter, current_value, recommended_value, CASE WHEN current_value = recommended_value THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as compliance_status, CASE WHEN security_parameter IN ('remote_os_authent', '07_dictionary_accessibility') AND current_value != recommended_value THEN 'HIGH' WHEN security_parameter IN ('audit_trail', 'sec_case_sensitive_logon') AND current_value != recommended_value THEN 'MEDIUM' ELSE 'LOW' END as security_riskFROM security_paramsORDER BY CASE security_risk WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, security_parameter;
-- ------------------------------------------------------------------------------------- PARAMETER OPTIMIZATION RECOMMENDATIONS: Actionable optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. PARAMETER OPTIMIZATION RECOMMENDATIONSPROMPT ==========================================
COLUMN recommendation FORMAT A60COLUMN parameter_name FORMAT A25COLUMN current_value FORMAT A15COLUMN recommended_value FORMAT A20COLUMN priority FORMAT A10
SELECT 'Increase ' || name || ' for better performance' as recommendation, name as parameter_name, value as current_value, CASE name WHEN 'sga_target' THEN TO_CHAR(ROUND((SELECT SUM(value) FROM v$sga) * 1.2)) WHEN 'pga_aggregate_target' THEN TO_CHAR(ROUND((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * 1.5)) WHEN 'processes' THEN '500' WHEN 'sessions' THEN '600' WHEN 'open_cursors' THEN '300' ELSE value END as recommended_value, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'HIGH' WHEN name IN ('processes', 'sessions') AND TO_NUMBER(value) < 300 THEN 'HIGH' WHEN name = 'open_cursors' AND TO_NUMBER(value) < 200 THEN 'MEDIUM' ELSE 'LOW' END as priorityFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors') AND (name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 OR name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.8 OR name = 'processes' AND TO_NUMBER(value) < 300 OR name = 'sessions' AND TO_NUMBER(value) < 400 OR name = 'open_cursors' AND TO_NUMBER(value) < 200)UNION ALLSELECT 'Modify security parameter: ' || name, name, value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'O7_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' -- Fixed: O7 not 07 WHEN 'audit_trail' THEN 'DB,EXTENDED' ELSE value END, 'HIGH'FROM v$parameterWHERE name IN ('remote_os_authent', 'O7_DICTIONARY_ACCESSIBILITY', 'audit_trail') -- Fixed here too AND value != CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'O7_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' -- Fixed here too WHEN 'audit_trail' THEN 'DB,EXTENDED' ELSE value ENDORDER BY 4,2;
-- ------------------------------------------------------------------------------------- PARAMETER CHANGE IMPACT ANALYSIS: Impact assessment of proposed changes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. PARAMETER CHANGE IMPACT ANALYSISPROMPT ====================================
COLUMN parameter_name FORMAT A25COLUMN change_description FORMAT A50COLUMN restart_required FORMAT A5COLUMN risk_level FORMAT A10COLUMN testing_recommendation FORMAT A60
SELECT name as parameter_name, CASE name WHEN 'sga_target' THEN 'Increase SGA size by ' || ROUND( ((SELECT SUM(value) FROM v$sga) * 1.2 - TO_NUMBER(value)) / NULLIF(1024 * 1024 * 1024, 0), 2 ) || ' GB' WHEN 'pga_aggregate_target' THEN 'Increase PGA target by ' || ROUND( ((SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') * 1.5 - TO_NUMBER(value)) / 1024, 2 ) || ' GB' WHEN 'processes' THEN 'Increase processes from ' || value || ' to 500' WHEN 'sessions' THEN 'Increase sessions from ' || value || ' to 600' ELSE 'Modify ' || name || ' setting' END as change_description, CASE WHEN issys_modifiable = 'FALSE' THEN 'YES' ELSE 'NO' END as restart_required, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') THEN 'MEDIUM' WHEN name IN ('processes', 'sessions') THEN 'LOW' ELSE 'LOW' END as risk_level, CASE WHEN name IN ('sga_target', 'pga_aggregate_target') THEN 'Test in non-production environment first' WHEN name IN ('processes', 'sessions') THEN 'Monitor session usage after change' ELSE 'Standard parameter change procedure' END as testing_recommendationFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors') AND ((name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9) OR (name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.8) OR (name = 'processes' AND TO_NUMBER(value) < 300) OR (name = 'sessions' AND TO_NUMBER(value) < 400))ORDER BY CASE risk_level WHEN 'MEDIUM' THEN 1 WHEN 'LOW' THEN 2 ELSE 3 END DESC, CASE restart_required WHEN 'YES' THEN 1 WHEN 'NO' THEN 2 ELSE 3 END DESC;
-- ------------------------------------------------------------------------------------- PARAMETER VALIDATION: Validate current parameter settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PARAMETER SETTINGS VALIDATIONPROMPT =================================
COLUMN validation_check FORMAT A40COLUMN parameter_name FORMAT A25COLUMN current_value FORMAT A15COLUMN validation_status FORMAT A15COLUMN validation_notes FORMAT A60
SELECT 'Memory Parameter Validation' as validation_check, name as parameter_name, value as current_value, CASE WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) BETWEEN 0.7 AND 0.9 THEN 'PASS' WHEN name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) < 0.9 THEN 'PASS' ELSE 'REVIEW' END as validation_status, CASE WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'SGA utilization high - consider increase' WHEN name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / NULLIF(TO_NUMBER(value), 0) < 0.7 THEN 'SGA may be over-allocated' WHEN name = 'pga_aggregate_target' AND (SELECT value/1024/1024 FROM v$pgastat WHERE name = 'maximum PGA allocated') / NULLIF(TO_NUMBER(value), 0) > 0.9 THEN 'PGA usage high - monitor closely' ELSE 'Parameter within acceptable range' END as validation_notesFROM v$parameterWHERE name IN ('sga_target', 'pga_aggregate_target')UNION ALLSELECT 'Connection Parameter Validation', name, value, CASE WHEN name = 'processes' AND TO_NUMBER(value) >= 300 THEN 'PASS' WHEN name = 'sessions' AND TO_NUMBER(value) >= 400 THEN 'PASS' WHEN name = 'open_cursors' AND TO_NUMBER(value) >= 200 THEN 'PASS' ELSE 'REVIEW' END, CASE WHEN name = 'processes' AND TO_NUMBER(value) < 300 THEN 'Consider increasing processes parameter' WHEN name = 'sessions' AND TO_NUMBER(value) < 400 THEN 'Consider increasing sessions parameter' WHEN name = 'open_cursors' AND TO_NUMBER(value) < 200 THEN 'Consider increasing open_cursors' ELSE 'Connection parameters adequate' ENDFROM v$parameterWHERE name IN ('processes', 'sessions', 'open_cursors')ORDER BY 1,3,2;
-- ------------------------------------------------------------------------------------- OPTIMIZATION IMPLEMENTATION: Generate parameter change commands-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. PARAMETER OPTIMIZATION IMPLEMENTATIONPROMPT =========================================
BEGIN IF UPPER('&1') IN ('RECOMMEND', 'REPORT') THEN DECLARE v_optimization_level VARCHAR2(20) := UPPER('&3'); BEGIN DBMS_OUTPUT.PUT_LINE('Parameter Optimization Commands'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Optimization Level: ' || v_optimization_level); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Recommended ALTER SYSTEM commands:'); DBMS_OUTPUT.PUT_LINE(''); -- Generate ALTER SYSTEM commands for memory parameters FOR mem_rec IN ( SELECT name, value as current_value, CASE name WHEN 'sga_target' THEN ROUND((SELECT SUM(value) FROM v$sga) * CASE v_optimization_level WHEN 'AGGRESSIVE' THEN 1.3 WHEN 'CONSERVATIVE' THEN 1.15 ELSE 1.2 END) WHEN 'pga_aggregate_target' THEN ROUND((SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') * CASE v_optimization_level WHEN 'AGGRESSIVE' THEN 1.8 WHEN 'CONSERVATIVE' THEN 1.3 ELSE 1.5 END) END as recommended_value, CASE WHEN issys_modifiable = 'FALSE' THEN 'SCOPE=SPFILE' ELSE 'SCOPE=BOTH' END as scope_setting FROM v$parameter WHERE name IN ('sga_target', 'pga_aggregate_target') AND ((name = 'sga_target' AND (SELECT SUM(value) FROM v$sga) / TO_NUMBER(value) > 0.9) OR (name = 'pga_aggregate_target' AND (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') / TO_NUMBER(value) > 0.8)) ) LOOP IF mem_rec.recommended_value IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SET ' || mem_rec.name || ' = ' || mem_rec.recommended_value || ' ' || mem_rec.scope_setting || ';'); DBMS_OUTPUT.PUT_LINE('-- Current: ' || mem_rec.current_value || ', Recommended: ' || mem_rec.recommended_value); DBMS_OUTPUT.PUT_LINE(''); END IF; END LOOP; -- Generate commands for connection parameters FOR conn_rec IN ( SELECT name, value as current_value, CASE name WHEN 'processes' THEN '500' WHEN 'sessions' THEN '600' WHEN 'open_cursors' THEN '300' END as recommended_value, 'SCOPE=SPFILE' as scope_setting FROM v$parameter WHERE name IN ('processes', 'sessions', 'open_cursors') AND ((name = 'processes' AND TO_NUMBER(value) < 300) OR (name = 'sessions' AND TO_NUMBER(value) < 400) OR (name = 'open_cursors' AND TO_NUMBER(value) < 200)) ) LOOP DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SET ' || conn_rec.name || ' = ' || conn_rec.recommended_value || ' ' || conn_rec.scope_setting || ';'); DBMS_OUTPUT.PUT_LINE('-- Current: ' || conn_rec.current_value || ', Recommended: ' || conn_rec.recommended_value); DBMS_OUTPUT.PUT_LINE(''); END LOOP; DBMS_OUTPUT.PUT_LINE('Important Notes:'); DBMS_OUTPUT.PUT_LINE('- Parameters with SCOPE=SPFILE require database restart'); DBMS_OUTPUT.PUT_LINE('- Test changes in non-production environment first'); DBMS_OUTPUT.PUT_LINE('- Monitor performance after implementing changes'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating optimization commands: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Optimization implementation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Parameter optimization summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE PARAMETER OPTIMIZATION COMPLETEDPROMPT ========================================PROMPT Action: &1PROMPT Parameter Scope: &2PROMPT Optimization Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Optimization Summary:PROMPT - Review HIGH priority recommendations firstPROMPT - Validate parameter changes in test environmentPROMPT - Consider database restart requirementsPROMPT - Monitor performance after implementationPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------