[Admin] Oralce 파라미터 설정 자동 가이드(audit_trail, processes, archive log directory, _diag_daemon, memory_target)

오라클을 구축 후 운영단계에서 퍼포먼스 튜닝을 진행해야 합니다. 

Oracle 11g 이상 또는 Single 설치에서만 진행 하시기 바라며, 파라미터는 반드시 백업 받고 진행 해주시기 바랍니다.
파라미터 적용 시 DB 종료가 필요하니 DB 종료 가능 여부도 같이 확인 하시기 바랍니다.

1. 주요 변경 사항

audit_trail
  - 주로 감사(Audition)에서 활용되며, 데이터베이스 작업을 모니터링, 기록을 수집하는 기능입니다.
  - 모든 추척 내용을 감사 로그로 기록하기 때문에 시스템의 속도를 저하 시킵니다.

processes
  - 프로세스를 변경하기 위해서 일반적으로 "processes" 파라미터 값을 변경 합니다.
  - 프로세스는 기본적으로 150으로 되어있지만, 운영 하다보면 점점 늘어나기 때문에 표준을 1000 기준으로 합니다.
 
log_archive_dest_1
   - archive log mode를 확인해서 Default Directory일 경우 변경 합니다.


_diag_daemon
  - 분석 데몬의 자동 시작 유무 결정하는 것이며, Diagnosability Daemon은 process와 instance failure에 관련한 진단 정보를 포착해서 systemstate dump를 생성하는 역할을 합니다.
  
memory_target
  - 총 메모리 량을 지정하는 파라미터 입니다.
  - memory_target PGA 및 SGA 메모리를 자동으로 조정 해주는 역할 합니다.(11g 부터 가능)
  - memory_target, memory_max_target 동일하게 사이즈를 설정하는 게 좋습니다.
   

2. Oracle Parameter check PL/SQL
  - 전체 운영체제에 존재하는 메모리 사이즈를 확인 후 최소 40~60% 오라클 메모리 사이즈를 지정해주시기 바라며, 해당 사이즈에 맞게 변경된 출력 결과를 얻게 됩니다.

SPOOL /tmp/Parameter_Result.txt  /* 경로는 운영체제 환경에 맞게 조정 해주시기 바랍니다. */

SET LINE 100
SET SERVEROUTPUT ON

DECLARE

  IN_DB_MEMORY   NUMBER;
  V_DB_MEMORY    NUMBER;
  V_VERSION      VARCHAR2(100);
  V_PAM_FILE     VARCHAR2(100);
  V_AUDIT_RS     VARCHAR2(100);
  V_PROCESS_RS   NUMBER;
  V_AR_RS        VARCHAR2(100);
  V_AR_DIR_RS    VARCHAR2(100);
  V_MR_RS        NUMBER;
  V_MR_REC       NUMBER;
  V_DIAG_DAEMON  VARCHAR2(100);
  V_MR_RT_10     NUMBER := 0.7;
  V_MR_RT_11     NUMBER := 0.8; /*SGA_MAX_TARGET 비율*/

  
BEGIN
  DBMS_OUTPUT.PUT_LINE('/*********DB가 사용할 수 있는 메모리를 입력하시기 바랍니다.[단위 G](ex. 8G -> 8)*********/'); 
  
  IN_DB_MEMORY := &in_memory; /*[입력]G 단위로 입력*/
  
  V_DB_MEMORY := IN_DB_MEMORY * 1024;
  
  DBMS_OUTPUT.PUT_LINE(' - DB Memory : '||V_DB_MEMORY||'MB');
  DBMS_OUTPUT.PUT_LINE(' '); 

  DBMS_OUTPUT.PUT_LINE('/****************************************************************************************/'); 
  DBMS_OUTPUT.PUT_LINE('                    파라미터 변경 시 DB 종료가 필요합니다.');
  DBMS_OUTPUT.PUT_LINE('                   DB 종료 가능 여부를 파악한 후에 진행하시고');  
  DBMS_OUTPUT.PUT_LINE('        권고 작업 진행 전, Parameter file(spfile[SID].ora)을 백업하시기 바랍니다.');  
  DBMS_OUTPUT.PUT_LINE('/****************************************************************************************/'); 
  DBMS_OUTPUT.PUT_LINE(' '); 
  DBMS_OUTPUT.PUT_LINE(' '); 
  /*Oracle Version 확인*/
  DBMS_OUTPUT.PUT_LINE('/*************************************Oracle Version*************************************/');   
  DECLARE CURSOR ORACLE_VERSION IS
  SELECT    A.BANNER
  FROM      V$VERSION A; 
  BEGIN
    FOR ORACLE_VERSION_LIST IN ORACLE_VERSION LOOP
      DBMS_OUTPUT.PUT_LINE(ORACLE_VERSION_LIST.BANNER);  
    END LOOP;
  END;
  DBMS_OUTPUT.PUT_LINE('/****************************************************************************************/'); 
  DBMS_OUTPUT.PUT_LINE(' '); 

  /*Oracle Version 확인*/
  SELECT    TO_NUMBER(REGEXP_SUBSTR(A.VERSION,'[^.]+',1,1)) AS VERSION
  INTO      V_VERSION
  FROM      V$INSTANCE A; 
 
  IF V_VERSION < 10 THEN
    DBMS_OUTPUT.PUT_LINE('Oracle Version 10g 이상부터 사용할 수 있습니다.'); 
    RETURN; 
  END IF;
  
  /*Parameter File 확인*/  
  DBMS_OUTPUT.PUT_LINE('Check Parameter File');   
  SELECT    A.VALUE
  INTO      V_PAM_FILE
  FROM      V$PARAMETER A
  WHERE     A.NAME = 'spfile';
  IF V_PAM_FILE IS NULL THEN
    DBMS_OUTPUT.PUT_LINE(' - Parameter File을 spfile로 변경하신 후 재작업하시기 바랍니다.');  
    DBMS_OUTPUT.PUT_LINE(' '); 
    DBMS_OUTPUT.PUT_LINE(' [권고 작업] ');
    DBMS_OUTPUT.PUT_LINE('  CREATE SPFILE FROM PFILE;');
    DBMS_OUTPUT.PUT_LINE('  DB 종료');
    DBMS_OUTPUT.PUT_LINE('  PFILE(INIT[SID].ORA) 파일 제거');
    DBMS_OUTPUT.PUT_LINE('  DB 시작');
    RETURN; 
  ELSE
    DBMS_OUTPUT.PUT_LINE(' - Pass');   
  END IF;
  DBMS_OUTPUT.PUT_LINE(' ');  
  /*audit_trail 확인*/
  DBMS_OUTPUT.PUT_LINE('Check audit_trail');  
  SELECT    A.VALUE
  INTO      V_AUDIT_RS
  FROM      V$PARAMETER A
  WHERE     A.NAME = 'audit_trail';
  IF V_AUDIT_RS != 'NONE' THEN
    DBMS_OUTPUT.PUT_LINE(' - audit_trail을 NONE으로 변경하시기 바랍니다.');
    DBMS_OUTPUT.PUT_LINE(' [현재 audit_trail 설정값]'); 
    DBMS_OUTPUT.PUT_LINE('  - '||V_AUDIT_RS);
    DBMS_OUTPUT.PUT_LINE(' '); 
    DBMS_OUTPUT.PUT_LINE(' [권고 audit_trail 설정값]'); 
    DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;'); 
    DBMS_OUTPUT.PUT_LINE('  DB 종료');      
    DBMS_OUTPUT.PUT_LINE('  DB 시작'); 
  ELSE
    DBMS_OUTPUT.PUT_LINE(' - Pass');
  END IF;
  DBMS_OUTPUT.PUT_LINE(' ');  
  /*processes 확인*/  
  DBMS_OUTPUT.PUT_LINE('Check processes');  
  SELECT    A.VALUE
  INTO      V_PROCESS_RS
  FROM      V$PARAMETER A
  WHERE     A.NAME = 'processes';
  IF V_PROCESS_RS < 1000 THEN
    DBMS_OUTPUT.PUT_LINE(' - processes 개수를 1000개로 변경하시기 바랍니다.');
    DBMS_OUTPUT.PUT_LINE(' [현재 processes 설정값]');  
    DBMS_OUTPUT.PUT_LINE('  '||V_PROCESS_RS);  
    DBMS_OUTPUT.PUT_LINE(' ');     
    DBMS_OUTPUT.PUT_LINE(' [권고 작업] ');
    DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;');
    DBMS_OUTPUT.PUT_LINE('  DB 종료');      
    DBMS_OUTPUT.PUT_LINE('  DB 시작');  
  ELSE
    DBMS_OUTPUT.PUT_LINE(' - Pass');
  END IF;
  DBMS_OUTPUT.PUT_LINE(' '); 
  /*archive log mode 확인 - Default Directory일 경우, 변경문구출력*/
  SELECT    A.LOG_MODE
  INTO      V_AR_RS
  FROM      V$DATABASE A;
  IF V_AR_RS = 'ARCHIVELOG' THEN
    DBMS_OUTPUT.PUT_LINE('Check archive log directory');  
    SELECT    A.VALUE
    INTO      V_AR_DIR_RS
    FROM      V$PARAMETER A
    WHERE     A.NAME = 'log_archive_dest_1';
    IF V_AR_DIR_RS IS NULL THEN
      DBMS_OUTPUT.PUT_LINE(' - archive log 위치를 변경해주시기 바랍니다.'); 
      DBMS_OUTPUT.PUT_LINE(' [현재 Archive log 디렉토리 설정값]');  
      DBMS_OUTPUT.PUT_LINE('  db_recovery_file_dest'); 
      DBMS_OUTPUT.PUT_LINE(' ');     
      DBMS_OUTPUT.PUT_LINE(' [권고 작업] ');
      DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=''location=[directory 경로]'' ; /*directory 경로 변경*/');
    ELSE
      DBMS_OUTPUT.PUT_LINE(' - Pass');    
    END IF;
    DBMS_OUTPUT.PUT_LINE(' '); 
  END IF;
  /*diag_daemon 확인*/
  DBMS_OUTPUT.PUT_LINE('Check _diag_daemon'); 
  SELECT    Y.KSPPSTVL
  INTO      V_DIAG_DAEMON
  FROM      X$KSPPI X
           ,X$KSPPCV Y
  WHERE     X.INDX = Y.INDX
  AND       X.KSPPINM = '_diag_daemon';
  IF V_DIAG_DAEMON = 'TRUE' THEN
    DBMS_OUTPUT.PUT_LINE(' - 파라미터를 FALSE로 변경하시기 바랍니다.'); 
    DBMS_OUTPUT.PUT_LINE(' [현재 _diag_daemon 설정값]');  
    DBMS_OUTPUT.PUT_LINE('  '||V_DIAG_DAEMON);  
    DBMS_OUTPUT.PUT_LINE(' ');     
    DBMS_OUTPUT.PUT_LINE(' [권고 작업] ');
    DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET "_diag_daemon"=FALSE SCOPE=SPFILE;');
    DBMS_OUTPUT.PUT_LINE('  DB 종료');      
    DBMS_OUTPUT.PUT_LINE('  DB 시작');  
  ELSE
    DBMS_OUTPUT.PUT_LINE(' - Pass');     
  END IF;
  DBMS_OUTPUT.PUT_LINE(' ');   
  /*Oracle Memory 확인*/
  DBMS_OUTPUT.PUT_LINE('Check Oracle Memory');  
  SELECT    A.BYTE_VALUE / 1024 / 1024, V_DB_MEMORY AS VALUE
  INTO      V_MR_RS, V_MR_REC
  FROM      (SELECT    CASE
                         WHEN V_VERSION = 10
                         THEN
                           SUM(CASE
                                 WHEN A.NAME IN ('sga_target', 'pga_aggregate_target') THEN TO_NUMBER(A.VALUE)
                                 ELSE 0
                               END)
                         ELSE
                           MAX(CASE
                                 WHEN A.NAME = 'memory_target' THEN TO_NUMBER(A.VALUE)
                                 ELSE 0
                               END)
                       END
                         AS BYTE_VALUE
             FROM      V$PARAMETER A
             WHERE     A.NAME IN ('memory_target', 'sga_target', 'pga_aggregate_target')) A;

  IF V_MR_RS = 0 OR V_MR_RS < V_MR_REC THEN
    DBMS_OUTPUT.PUT_LINE(' - 파라미터를 재설정하시기 바랍니다.');     
    DBMS_OUTPUT.PUT_LINE(' [현재 메모리 설정값]'); 
    DECLARE CURSOR ORACLE_MEMORY IS
    SELECT    A.NAME AS PARAMETER_NAME, A.VALUE AS BYTE_VALUE, ROUND(A.VALUE / 1024 / 1024, 2) || 'MB' AS MB_VALUE
    FROM      V$PARAMETER A
    WHERE     A.NAME IN
                ('sga_max_size'
                ,'sga_target'
                ,'java_pool_size'
                ,'streams_pool_size'
                ,'large_pool_size'
                ,'shared_pool_size'
                ,'shared_pool_reserved_size'
                ,'db_cache_size'
                ,'pga_aggregate_target'
                ,'memory_target'
                ,'memory_max_target')
    ORDER BY  A.NAME;
    
    BEGIN
      FOR ORACLE_MEMORY_LIST IN ORACLE_MEMORY LOOP
        DBMS_OUTPUT.PUT_LINE('  - '||RPAD(ORACLE_MEMORY_LIST.PARAMETER_NAME,25,' ')||' : '||ORACLE_MEMORY_LIST.MB_VALUE);  
      END LOOP;
    END;
    DBMS_OUTPUT.PUT_LINE(' ');  
    DBMS_OUTPUT.PUT_LINE(' [권고 작업] ');  
    
    IF V_VERSION = 10 THEN
      DECLARE CURSOR ORACLE_MEMORY_RT IS
      SELECT    'ALTER SYSTEM SET '|| A.PARAMETER_NAME ||' = '||A.VALUE||'M SCOPE=SPFILE;' AS STMT
      FROM      (SELECT    'sga_max_size' AS PARAMETER_NAME, TRUNC(V_DB_MEMORY * V_MR_RT_10) AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'sga_target' AS PARAMETER_NAME, TRUNC(V_DB_MEMORY * V_MR_RT_10) AS VALUE
                 FROM      DUAL               
                 UNION ALL
                 SELECT    'pga_aggregate_target' AS PARAMETER_NAME, V_DB_MEMORY - TRUNC(V_DB_MEMORY * V_MR_RT_10) AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'java_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'streams_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'large_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'shared_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'shared_pool_reserved_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 ) A    ;
      BEGIN
        FOR ORACLE_MEMORY_STMT IN ORACLE_MEMORY_RT LOOP
          DBMS_OUTPUT.PUT_LINE('  '||ORACLE_MEMORY_STMT.STMT);  
        END LOOP;
      END;
             
      DBMS_OUTPUT.PUT_LINE('  DB 종료');      
      DBMS_OUTPUT.PUT_LINE('  DB 시작');        
      DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET db_cache_size = 0M ;'); 
    
    ELSIF V_VERSION >= 11 THEN
      DECLARE CURSOR ORACLE_MEMORY_RT IS
      SELECT    'ALTER SYSTEM SET '|| A.PARAMETER_NAME ||' = '||A.VALUE||'M SCOPE=SPFILE;' AS STMT
      FROM      (SELECT    'memory_max_target' AS PARAMETER_NAME, V_DB_MEMORY AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'memory_target' AS PARAMETER_NAME, V_DB_MEMORY AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'sga_max_size' AS PARAMETER_NAME, TRUNC(V_DB_MEMORY * V_MR_RT_11) AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'sga_target' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL               
                 UNION ALL
                 SELECT    'pga_aggregate_target' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'java_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'streams_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'large_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'shared_pool_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 UNION ALL
                 SELECT    'shared_pool_reserved_size' AS PARAMETER_NAME, 0 AS VALUE
                 FROM      DUAL
                 ) A    ;
      BEGIN
        FOR ORACLE_MEMORY_STMT IN ORACLE_MEMORY_RT LOOP
          DBMS_OUTPUT.PUT_LINE('  '||ORACLE_MEMORY_STMT.STMT);  
        END LOOP;
      END;
      
      DBMS_OUTPUT.PUT_LINE('  DB 종료');      
      DBMS_OUTPUT.PUT_LINE('  DB 시작');        
      DBMS_OUTPUT.PUT_LINE('  ALTER SYSTEM SET db_cache_size = 0M ;'); 
      
    END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE(' - Pass');   
  END IF;
END;
/

SPOOL OFF
SET SERVEROUTPUT OFF

3. Oracle Parameter check 결과

Check audit_trail
 - audit_trail을 NONE으로 변경하시기 바랍니다.
 [현재 audit_trail 설정값]
  - DB
 
 [권고 audit_trail 설정값]
  ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
  DB 종료
  DB 시작
 
Check processes
 - processes 개수를 1000개로 변경하시기 바랍니다.
 [현재 processes 설정값]
  150
 
 [권고 작업] 
  ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

Check archive log directory
 - archive log 위치를 변경해주시기 바랍니다.
 [현재 Archive log 디렉토리 설정값]
  db_recovery_file_dest
 
 [권고 작업] 
  ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=[directory 경로]';

Check _diag_daemon
 - 파라미터를 FALSE로 변경하시기 바랍니다.
 [현재 _diag_daemon 설정값]
  TRUE
 
 [권고 작업] 
  ALTER SYSTEM SET "_diag_daemon"=FALSE SCOPE=SPFILE;
  DB 종료
  DB 시작
 
Check Oracle Memory
 - 파라미터를 재설정하시기 바랍니다.
 [현재 메모리 설정값]
  - db_cache_size             : 0MB
  - java_pool_size            : 0MB
  - large_pool_size           : 0MB
  - memory_max_target         : 616MB
  - memory_target             : 616MB
  - pga_aggregate_target      : 0MB
  - sga_max_size              : 412MB
  - sga_target                : 0MB
  - shared_pool_reserved_size : 13.6MB
  - shared_pool_size          : 0MB
  - streams_pool_size         : 0MB
 
 [권고 작업] 
  ALTER SYSTEM SET memory_max_target = 26M SCOPE=SPFILE;
  ALTER SYSTEM SET memory_target = 26M SCOPE=SPFILE;
  ALTER SYSTEM SET sga_max_size = 20M SCOPE=SPFILE;
  ALTER SYSTEM SET sga_target = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET pga_aggregate_target = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET java_pool_size = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET streams_pool_size = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET large_pool_size = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET shared_pool_size = 0M SCOPE=SPFILE;
  ALTER SYSTEM SET shared_pool_reserved_size = 0M SCOPE=SPFILE;
  DB 종료
  DB 시작
  ALTER SYSTEM SET db_cache_size = 0M ;
  

해당 내용은 자체적으로 테스트하고 운영하면서 변경된 최적화 내용을 토대로 전달해드립니다. 
운영 환경과 오라클 버전에 따라서 조금씩 차이가 날 수 있습니다. 반드시 내부 테스트를 거쳐서 반영하시기 바랍니다.

메모리에 대한 변경은 11G 이상부터 적용가능 합니다.