[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% 오라클 메모리 사이즈를 지정해주시기 바라며, 해당 사이즈에 맞게 변경된 출력 결과를 얻게 됩니다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300

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 결과

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

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 이상부터 적용가능 합니다.