오라클 권고 파라미터 12C / 11G
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
오라클 DB 권고 파라메타 (Oracle DB Initialization Parameter)
오라클
"Always use proper testing mechanisms."
충분히 테스트해보고 적용하기 바랍니다
버전마다 시스템구성마다 어떤 파라메타는 값을 다르게 해야하고, 어떤거는 버그가 있어서 쓰면 안되는 등 고려해야 할 요소가 너무 많아서 이거다 싶은 딱 맞는 답을 줄 수 없습니다.
가장 정확한 답은 오라클 ACS엔지니어(유료서비스)의 도움을 받는 겁니다. 이들은 해당 사이트의 구성에 맞는 최적의 파라메타를 제공하는(최신 버그내역까지 반영한..) 서비스를 유료로 판매하고 있습니다.
[ Oracle Database Enterprise Edition 12c 기준 ]
- 파라메타에 (괄호) 안의 값은 원래 안건드리면 Default로 가지고 있는 값입니다.
_add_col_optim_enabled (true) => false
- column add 시 dictionary만 update 하고 이후 insert시 해당 컬럼이 생성되게 하는 기능
- Wrong result 버그로 인해 false 권고 (MOS 문서 #1492674.1, #19183343.8) _gc_policy_time (10) => 0 _gc_undo_affinity (true) => false
- RAC DRM Remastering 기능을 끄는게 좋음. Remastering 에 따른 오버헤드가 너무 크다고 알려져 있음.
- 1000번이상 다른쪽 노드에서 Writing이 발생하면 Mastering 정보를 옮기는 Remastering이 발생하는데 이때 모든 gc request 가 중단되기 때문에 느려지는 현상이 발생됨. _optimizer_use_feedback (true) => false
- 11gR2 NF 파라메타
- Optimizer가 정확한 cardinality를 계산하지 않을 경우 새로운 실행계획을 수립하는 기능 - 갑작스런 plan 변경 및 잘못된 실행계획수립으로 인해 false 권고 (MOS 문서 참조 #8521689.8, #13648166.8, #13454409.8) deferred_segment_creation (true) => false
- Create table 수행시 즉시로 table을 생성할 지를 결정
- true(default)로 설정시 library cache: mutex X, exp/imp에 포함되지 않는 문제, ora-600으로 인해 false 권고 (#1352678.1 , #1590806.1, #1352678.1) - 주의 : false 설정 이전에 만든 table은 parameter변경에도 영향을 받지 않으므로 SQL> alter table "테이블" move 또는 SQL> alter table "테이블" allocate extent; 를 수행해야 함. (#1178343.1) optimizer_adaptive_features (true) => false (12cR1 only)
- 12cR1 NF 파라메타
- 12cR2 의 경우, optimizer_adaptive_plans 파라메타로 제어해야 함. (12cR2에서 optimizer_adaptive_features 는 없어졌음) - Plan 이 매번 바뀔 수 있기 때문에 false 로 권고.. 하지만, 이 기능이 원래 매우 괜찮은 기능이라.. 이 좋은 New Feature를 꺼버리는게 맞을지는 좀 논란의 여지가 있음. optimizer_adaptive_plans (true) => false (12cR2 ~)
- 12cR2 NF 파라메타
- 12cR1 의 경우, optimizer_adaptive_features 파라메타를 사용함.
( optimizer_adaptive_features = optimizer_adaptive_plans + optimizer_adaptive_statistics )
- Plan 이 매번 바뀔 수 있기 때문에 false 로 권고.. 하지만, 이 기능이 원래 매우 괜찮은 기능이라.. 이 좋은 New Feature를 꺼버리는게 맞을지는 좀 논란의 여지가 있음. parallel_adaptive_multi_user (true) => false
- 요청된 parallel degree 에 대해서 query startup time 시의 system load 에 근거해서 degree 를 축소하는 기능.
- 문제는 이 파라메타로 인해 parallel 프로세스가 의도한대로 충분히 안뜨기 때문에 false 로 권고. - DOP 를 의도한대로 유지하면서 수행되어야 할 Batch Job 등을 운영하는 시스템의 경우 System Resource가 여유가 있다면 False로 운영할 것을 권장함. parallel_force_local (false) => true
- RAC에서 인스턴스간에 parallel 처리를 허용할지 여부. 과도한 캐시퓨전에 의한 성능저하 방지.
- 또한, 미해결된 버그 (#1914119.1)로 인해 false 권고. shared_pool_size => <적절한 값>
- sga_target, memory_target 등을 사용하는 경우 자동으로 Oracle Memory(SGA)를 조정하기 때문에 이 파라메타를 세팅하지 않는 경우가 있는데, 그래도 세팅하는게 좋습니다.
- 초기 shared pool 의 크기를 지정합니다. (디폴트는 0) - memory_target 이나 sga_target 을 지정했더라도 이 파라메타를 사용함으로써 최소 shared pool을 확보하여 데이터베이스를 더 안정적으로 만들어줍니다. - 이 파라메타를 세팅했다고 해서 sga_target 을 없애버릴 필요는 없음. 함께 지정. large_pool_size => <적절한 값>
- 위 shared_pool_size 와 마찬가지 경우입니다.
- 초기 large pool 의 크기를 지정합니다. - memory_target 이나 sga_target 을 지정했더라도 이 파라메타를 사용함으로써 최소 large pool을 확보하여 데이터베이스를 더 안정적으로 만들어줍니다. - large pool은 MTS 또는 병렬처리시에 사용됩니다. (병렬처리가 많은 경우 2GB 정도로 설정) streams_pool_size => 200M 이상
- Streams 기능을 사용하지 않더라도 Datapump 사용시 error 및 성능저하에 대비해 200M이상 설정을 권고하고 있음. (MOS 문서 참조 #1596645.1)
sga_target, memory_target
- sga_target 은 shared_pool_size, large_pool_size, db_cache_size 등을 자동으로 조절해주는 10g 신기능입니다.
- memory_target 은 sga_target 의 기능 + pga_aggregate_target 값까지 자동으로 조절해주는 11g 신기능입니다. - 하지만, 이들 파라메타가 메모리값들을 자동으로 조절하는(resize) 순간에 성능저하가 발생할 수 있다고 하여 사용하지 말라는 얘기가 있습니다. - 또, 어떤 글을 보면 memory_target 만 설정하지 말고, sga_target 은 그냥 사용하는 것을 권장하기도 하고 해서 논란의 여지가 있습니다.
- ASM의 경우, 24G for 2 socket servers (ASM의 경우 소켓에 따라 권고하는 메모리 사이즈가 있음. 아마 디폴트가 이걸로 되어 있을듯...) - DB의 경우는 보통 sga_target 을 적당히 잡는데, 전체 physical memory의 50%가 안넘도록 세팅.
오라클 DB 파라메타는 매우 많습니다. 세어보지는 않았지만 히든파라메타까지 합치면 100개는 족히 넘을겁니다.
그 많은 파라메타들을 다 언급하지 않은 이유는 대부분은 디폴트로 놓고 쓰는 것이 좋기 때문입니다.
가끔 어떤 곳은 히든파라메타만 20여개씩 있는 곳도 있습니다. 오래전에 특정 버그로 인해 설정했던 파라메타를 그대로 놔뒀기 때문입니다. 보통 DBMS를 버전업하거나 패치셋을 적용하고 나면 불필요한 히든 파라메타들은 정리해야 하는데, 보통 아무도 안 건들기 때문입니다. 이런 경우에는 설정된 파라메타를 없애도 되는지 쉽게 판단하기 어렵죠~
Oracle ACS 엔지니어 서비스를 받는게 속 편합니다. ACS 서비스 중에 파라메타 진단서비스가 있으니까요~~
[ Oracle Database Enterprise Edition 11g 기준 ]
- 파라메타에 (괄호) 안의 값은 원래 안건드리면 Default로 가지고 있는 값입니다.
_add_col_optim_enabled (true) => false
- column add 시 dictionary만 update 하고 이후 insert시 해당 컬럼이 생성되게 하는 기능
- Wrong result 버그로 인해 false 권고 (MOS 문서 #1492674.1, #19183343.8) _gc_policy_time (10) => 0 _gc_undo_affinity (true) => false
- RAC DRM Remastering 기능을 끄는게 좋음. Remastering 에 따른 오버헤드가 너무 크다고 알려져 있음.
- 1000번이상 다른쪽 노드에서 Writing이 발생하면 Mastering 정보를 옮기는 Remastering이 발생하는데 이때 모든 gc request 가 중단되기 때문에 느려지는 현상이 발생됨. _gc_bypass_readers (true) => false
- ~ 11.2.0.3 버전까지만 해당. 11.2.0.4 는 패치되었음.
- 11gR2 NF 파라메타 - RAC에서 디스크에서 데이터를 읽는대신 다른 노드의 Instance에서 데이터를 읽어옮으로써 성능을 개선하는 기능 - Hang 및 ORA-00600 에러가 발생할 수 있으므로 false 로 세팅할 것을 권고 (MOS 문서 참조 #13787307.8, #13718476.8, #13807411.8, #13614906.8) - 각각의 버그에 대해 Interim Patch가 있으며, DB에 이를 적용한 경우는 상관없음. _optimizer_adaptive_cursor_sharing (true) => false
- bind variable에 따라 Cardinality의 변동이 클 경우, cursor sharing을 사용할 지 결정하는 기능
- 과도한 Mutex Wait 발생으로 인해 false 로 세팅할 것을 권고 (MOS 문서 참조 #11657468.8) - Mutex 문제는 11.2.0.3 에서 패치되었으나, CPU 과다 사용, Memory Leak 문제 등이 여전히 있음.
(MOS 문서 참조 #14772891.8, #2118467.1)
_optimizer_use_feedback (true) => false
- 11gR2 NF 파라메타
- Optimizer가 정확한 cardinality를 계산하지 않을 경우 새로운 실행계획을 수립하는 기능 - 갑작스런 plan 변경 및 잘못된 실행계획수립으로 인해 false 권고 (MOS 문서 참조 #8521689.8, #13648166.8, #13454409.8) _use_adaptive_log_file_sync (true) => false
- ~ 11.2.0.3 버전까지만 해당. 11.2.0.4 는 패치되었음.
- 11g New Feature 로 LGWR 성능개선을 위해 발표된 기능이나 log file sync 의 성능저하 유발시킴으로 FALSE 권고 (MOS 문서 참조 #1462942.1, #13707904.8, #13074706.8) deferred_segment_creation (true) => false
- Create table 수행시 즉시로 table을 생성할 지를 결정
- true(default)로 설정시 library cache: mutex X, exp/imp에 포함되지 않는 문제, ora-600으로 인해 false 권고 (#1352678.1 , #1590806.1, #1352678.1) - 주의 : false 설정 이전에 만든 table은 parameter변경에도 영향을 받지 않으므로 SQL> alter table "테이블" move 또는 SQL> alter table "테이블" allocate extent; 를 수행해야 함. (#1178343.1) parallel_adaptive_multi_user (true) => false
- 요청된 parallel degree 에 대해서 query startup time 시의 system load 에 근거해서 degree 를 축소하는 기능.
- 문제는 이 파라메타로 인해 parallel 프로세스가 의도한대로 충분히 안뜨기 때문에 false 로 권고. - DOP 를 의도한대로 유지하면서 수행되어야 할 Batch Job 등을 운영하는 시스템의 경우 System Resource가 여유가 있다면 False로 운영할 것을 권장함. parallel_force_local (false) => true
- RAC에서 인스턴스간에 parallel 처리를 허용할지 여부. 과도한 캐시퓨전에 의한 성능저하 방지.
- 또한, 미해결된 버그 (#1914119.1)로 인해 false 권고. shared_pool_size => <적절한 값>
- sga_target, memory_target 등을 사용하는 경우 자동으로 Oracle Memory(SGA)를 조정하기 때문에 이 파라메타를 세팅하지 않는 경우가 있는데, 그래도 세팅하는게 좋습니다.
- 초기 shared pool 의 크기를 지정합니다. (디폴트는 0) - memory_target 이나 sga_target 을 지정했더라도 이 파라메타를 사용함으로써 최소 shared pool을 확보하여 데이터베이스를 더 안정적으로 만들어줍니다. - 이 파라메타를 세팅했다고 해서 sga_target 을 없애버릴 필요는 없음. 함께 지정. large_pool_size => <적절한 값>
- 위 shared_pool_size 와 마찬가지 경우입니다.
- 초기 large pool 의 크기를 지정합니다. - memory_target 이나 sga_target 을 지정했더라도 이 파라메타를 사용함으로써 최소 large pool을 확보하여 데이터베이스를 더 안정적으로 만들어줍니다. - large pool은 MTS 또는 병렬처리시에 사용됩니다. (병렬처리가 많은 경우 2GB 정도로 설정) streams_pool_size => 200M 이상
- Streams 기능을 사용하지 않더라도 Datapump 사용시 error 및 성능저하에 대비해 200M이상 설정을 권고하고 있음. (MOS 문서 참조 #1596645.1)
sga_target, memory_target
- sga_target 은 shared_pool_size, large_pool_size, db_cache_size 등을 자동으로 조절해주는 10g 신기능입니다.
- memory_target 은 sga_target 의 기능 + pga_aggregate_target 값까지 자동으로 조절해주는 11g 신기능입니다. - 하지만, 이들 파라메타가 메모리값들을 자동으로 조절하는(resize) 순간에 성능저하가 발생할 수 있다고 하여 사용하지 말라는 얘기가 있습니다. - 또, 어떤 글을 보면 memory_target 만 설정하지 말고, sga_target 은 그냥 사용하는 것을 권장하기도 하고 해서 논란의 여지가 있습니다.
- ASM의 경우, 24G for 2 socket servers (ASM의 경우 소켓에 따라 권고하는 메모리 사이즈가 있음. 아마 디폴트가 이걸로 되어 있을듯...) - DB의 경우는 보통 sga_target 을 적당히 잡는데, 전체 physical memory의 50%가 안넘도록 세팅.