Oracle 19c 권장 파라미터, 세팅

Oracle 19c에서 권장되는 파라미터는 시스템 환경에 따라 다를 수 있지만, 전반적인 성능과 안정성을 높이기 위한 몇 가지 중요한 파라미터를 소개하겠습니다. 아래 파라미터들은 주로 성능, 보안, 리소스 관리에 중점을 둔 설정들입니다.

1. MEMORY_TARGET / MEMORY_MAX_TARGET

• 설명: 메모리 자동 관리 기능을 활성화하기 위한 파라미터입니다. Oracle 19c에서는 SGA와 PGA를 자동으로 관리할 수 있도록 해주는 MEMORY_TARGET과 MEMORY_MAX_TARGET 설정이 있습니다.
• 권장값: 시스템 메모리의 50% 정도로 설정하되, DB 사용량에 맞게 조정.

2. SGA_TARGET / SGA_MAX_SIZE

• 설명: SGA 영역의 크기를 정의하는 파라미터입니다. Oracle 19c에서 메모리 자원을 효율적으로 사용할 수 있도록 SGA_TARGET을 적절히 설정하는 것이 중요합니다.
• 권장값: Oracle Automatic Memory Management(AMM)를 사용할 경우 MEMORY_TARGET으로 대체 가능. 그렇지 않다면, SGA_TARGET과 PGA_AGGREGATE_TARGET을 수동으로 설정해야 합니다.

3. PGA_AGGREGATE_TARGET

• 설명: PGA(Program Global Area)의 총 메모리 사용량을 정의하는 파라미터입니다.
• 권장값: 시스템 메모리의 20~30% 권장. 필요 시 시스템의 워크로드에 맞게 조정.

4. OPTIMIZER_ADAPTIVE_PLANS

• 설명: 옵티마이저가 쿼리 계획을 동적으로 조정할 수 있도록 하는 파라미터입니다.
• 권장값: TRUE로 설정하여 쿼리 성능을 자동으로 최적화.

5. OPTIMIZER_MODE

• 설명: 쿼리 실행 계획을 선택하는 방법을 결정하는 파라미터입니다.
• 권장값: 대부분의 경우 ALL_ROWS로 설정하여 전체적인 쿼리 성능을 향상시키는 것이 좋습니다.

6. UNDO_RETENTION

• 설명: UNDO 데이터의 보존 시간을 설정하는 파라미터입니다. 롤백과 관련된 데이터를 적절히 관리하기 위해 필요합니다.
• 권장값: 시스템에서 발생하는 트랜잭션의 크기에 따라 다르지만 일반적으로 900~3600초 정도로 설정합니다.

7. DB_BLOCK_SIZE

• 설명: 데이터베이스 블록의 크기를 결정하는 파라미터입니다.
• 권장값: 8KB가 기본값이며 대부분의 경우 적합하지만, 대규모 데이터 웨어하우스 시스템에서는 16KB 또는 32KB로 설정하는 것이 좋습니다.

8. DB_CACHE_SIZE

• 설명: 데이터베이스 버퍼 캐시의 크기를 설정하는 파라미터입니다.
• 권장값: 시스템의 메모리 크기에 따라 적절히 설정. 보통 256MB에서 시작해 시스템 요구에 따라 조정.

9. LOG_BUFFER

• 설명: 리두 로그 버퍼의 크기를 설정하는 파라미터입니다.
• 권장값: 일반적으로 8MB 이상으로 설정하여 디스크 I/O 성능을 향상시킵니다.

10. OPEN_CURSORS

• 설명: 한 세션에서 열 수 있는 최대 커서 수를 정의하는 파라미터입니다.
• 권장값: 기본값은 300이지만, 응용 프로그램의 요구에 따라 더 높은 값으로 설정할 수 있습니다.
  이 외에도 각 애플리케이션 환경과 하드웨어 구성에 따라 다양한 추가 파라미터를 조정할 수 있습니다. Oracle 19c의 경우 Automatic Memory Management(AMM) 기능을 적극 활용하여 메모리 관련 파라미터를 자동으로 관리하는 것이 일반적인 권장 사항입니다. 또한, 보안과 관련된 파라미터들도 최신 패치와 보안 정책을 반영해 적절히 조정하는 것이 중요합니다.

Oracle 19c에서 권장 파라미터를 설정하는 방법

Oracle에서 파라미터를 설정하는 방법은 두 가지로 나뉩니다: SPFILE을 사용하는 방법과 PFILE을 사용하는 방법입니다. 여기서는 SPFILE을 사용하는 방법을 중점적으로 설명하겠습니다. SPFILE은 Oracle의 동적 파라미터 파일로, 데이터베이스가 시작될 때 사용됩니다.

1. 현재 파라미터 값 확인
   먼저, 현재 설정된 파라미터 값을 확인하여 적절히 변경할 파라미터를 식별합니다.

SHOW PARAMETER <파라미터명>;

예를 들어, SGA_TARGET 파라미터를 확인하려면 다음 명령을 실행합니다:

SHOW PARAMETER SGA_TARGET;

2. 파라미터 변경하기 (SPFILE을 사용하는 경우)
   Oracle 19c에서는 대부분의 파라미터가 동적으로 변경 가능하며, 데이터베이스를 재시작하지 않고도 적용할 수 있습니다.

파라미터를 변경하는 기본 명령은 다음과 같습니다:

ALTER SYSTEM SET <파라미터명> = <값> SCOPE = <MEMORY|SPFILE|BOTH>;

SCOPE = MEMORY: 현재 인스턴스에만 적용되며, 데이터베이스를 다시 시작하면 사라집니다.
SCOPE = SPFILE: 다음에 데이터베이스를 시작할 때 적용됩니다.
SCOPE = BOTH: 현재 인스턴스와 다음에 데이터베이스가 시작될 때 모두 적용됩니다.
예를 들어, SGA_TARGET을 1GB로 설정하려면 다음 명령을 사용합니다:

ALTER SYSTEM SET SGA_TARGET = 1G SCOPE = BOTH;

3. 주요 파라미터 설정 예시
   3.1 MEMORY_TARGET / MEMORY_MAX_TARGET
   메모리 자동 관리를 위해 MEMORY_TARGET과 MEMORY_MAX_TARGET을 설정합니다:

ALTER SYSTEM SET MEMORY_TARGET = 4G SCOPE = BOTH;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 4G SCOPE = BOTH;

3.2 PGA_AGGREGATE_TARGET
PGA 메모리를 2GB로 설정하는 예시입니다:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G SCOPE = BOTH;

3.3 OPTIMIZER_ADAPTIVE_PLANS
옵티마이저의 적응 계획 기능을 활성화하는 방법입니다:

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE SCOPE = BOTH;

3.4 UNDO_RETENTION
UNDO 데이터를 1800초(30분) 동안 보존하도록 설정하는 방법입니다:

ALTER SYSTEM SET UNDO_RETENTION = 1800 SCOPE = BOTH;

3.5 DB_CACHE_SIZE
데이터베이스 캐시의 크기를 500MB로 설정하는 예시입니다:

ALTER SYSTEM SET DB_CACHE_SIZE = 500M SCOPE = BOTH;

3.6 LOG_BUFFER
리두 로그 버퍼의 크기를 16MB로 설정하는 방법입니다:

ALTER SYSTEM SET LOG_BUFFER = 16M SCOPE = SPFILE;

4. PFILE을 사용하는 경우
   만약 SPFILE 대신 PFILE을 사용하는 경우, PFILE을 직접 수정한 후 데이터베이스를 재시작해야 합니다.

PFILE을 열어서 수정합니다:

PFILE 파일 위치는 보통 $ORACLE_HOME/dbs/init.ora입니다.

파일을 열어 직접 파라미터 값을 변경합니다.
데이터베이스 재시작:

SHUTDOWN IMMEDIATE;
STARTUP PFILE='/path/to/init<SID>.ora';

5. 변경 사항 확인
   변경한 파라미터가 제대로 반영되었는지 확인하려면 다시 SHOW PARAMETER 명령을 사용하여 확인할 수 있습니다:

SHOW PARAMETER <파라미터명>;

6. SPFILE에서 PFILE로 변환
   만약 SPFILE을 사용하고 있지만, PFILE로 변경하고 싶다면 다음 명령을 사용할 수 있습니다:

CREATE PFILE FROM SPFILE;

이를 통해 SPFILE에서 PFILE을 생성하고 수동으로 PFILE을 편집할 수 있습니다.

Oracle 19c에서는 대부분의 파라미터가 동적으로 설정되며, 관리자는 특정 워크로드나 시스템 요구에 맞게 메모리, I/O, 옵티마이저 등의 설정을 최적화해야 합니다. 이러한 설정이 시스템 성능에 미치는 영향을 주기 때문에 충분한 테스트 후 적용하는 것이 좋습니다.

실행 예시

  # Parameter 변경
alter system set "_buddy_instance"=0 scope=spfile; 
alter system set "_buffer_busy_wait_timeout"=2 scope=spfile; 
alter system set "_cursor_obsolete_threshold"=200 scope=spfile; 
alter system set "_cursor_reload_failure_threshold"=5 scope=spfile; 
alter system set "_disable_ilm_internal"=TRUE scope=spfile;
alter system set "_dlm_stats_collect"=0 scope=spfile;
alter system set "_drop_stat_segment"=1 scope=spfile;
alter system set "_enable_ptime_update_for_sys"=TRUE scope=spfile;
alter system set "_enable_space_preallocation"=0 scope=spfile;
alter system set "_gc_dump_remote_lock"=FALSE scope=spfile;
alter system set "_gc_integrity_checks"=0 scope=spfile;
alter system set "_gc_persistent_read_mostly"=FALSE scope=spfile;
alter system set "_highest_priority_processes"='VKTMILG*|LMS*' scope=spfile;
alter system set "_imr_non_blocking_device_driver"=FALSE scope=spfile;
alter system set "_in_memory_cdt"=OFF scope=spfile;
alter system set "_in_memory_undo" =FALSE scope=spfile;
alter system set "_kks_obsolete_dump_threshold"=0 scope=spfile;
alter system set "_kks_parse_error_warning"=0 scope=spfile;
alter system set "_ksipc_service_mask"=0 scope=spfile;
alter system set "_nonfatalprocess_redo_dump_time_limit"=0 scope=spfile;
alter system set "_optimizer_aggr_groupby_elim"=FALSE scope=spfile;
alter system set "_optimizer_ansi_rearchitecture"=FALSE scope=spfile;
alter system set "_optimizer_band_join_aware"=FALSE scope=spfile;
alter system set "_optimizer_batch_table_access_by_rowid"=FALSE scope=spfile;
alter system set "_optimizer_cbqt_or_expansion"=OFF scope=spfile;
alter system set "_optimizer_gather_feedback"=FALSE scope=spfile;
alter system set "_optimizer_gather_stats_on_load"=FALSE scope=spfile;
alter system set "_optimizer_gather_stats_on_load_index"=FALSE scope=spfile;
alter system set "_optimizer_inmemory_capture_stored_stats"=FALSE scope=spfile;
alter system set "_optimizer_join_factorization"=FALSE scope=spfile;
alter system set "_optimizer_reduce_groupby_key"=FALSE scope=spfile;
alter system set "_optimizer_unnest_scalar_sq"=FALSE scope=spfile;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_sys_logon_delay"=0 scope=spfile;
alter system set "_use_single_log_writer"=TRUE scope=spfile;
alter system set db_block_checking=TRUE scope=spfile;
alter system set optimizer_adaptive_plans=FALSE scope=spfile;
alter system set optimizer_adaptive_reporting_only=TRUE scope=spfile;
alter system set optimizer_inmemory_aware=TRUE scope=spfile;
alter system set pga_aggregate_limit=0 scope=spfile;
alter system set sql92_security=FALSE scope=spfile;
alter system set use_large_pages=ONLY scope=spfile;
alter system set allow_group_access_to_sga=TRUE scope=spfile;