출처 : http://cafe.naver.com/prodba/5213
[ Oracle Query Optimizer 관련 Parameter ]
1. optimizer_max_permutations
optimizer_max_permutations integer 2000
: 옵티마이저가 실행계획을 수립할 때 플랜의 경우의 수 내에서 실행계획을
수립하게 된다. 이러한 경우 플랜이 훨씬 좋은 경우가 있더라도, 그 플랜은
적용이 안될수 있다. 8i까지는 Default가 80000만, 9i부터 Default가 2000 임.
alter session set optimizer_max_permutations = 80000 ;
2. optimizer_index_cost_adj
default : 100
이 파라메터는 1~10000사이의 값을 설정할수 있으며 Index access와 Full Table Scan
에서 사용되는 Physical I/O 의 Cost의 상대적인 비율을 설정하는 파라메터이다.
100인경우에는 두개의 access 모두 동일한 비율로 Cost를 계산하게되며 50으로 설정하
여 Index access를 하는 경우 기존의 cost의 1/2로 cost를 계산하게 되어, Index
access 방식으로 execution plan이 수립되어질 확률이 높아지게 된다. Optimizer_mode
를 first_rows로 설정하는 경우에는 내부적으로 optimizer_index_cost_adj 는 10으로
계산되어진다. 따라서 Index access를 이용하여 execution plan을 수립할 확률이 높아
지게 되면 Join인 경우에는 Nested Loops Join으로 execution plan이 수립될 가능성이
높아지게 된다.
3. optimizer_index_caching
default : 0
이 파라메터는 0~99 사이의 값을 설정 할 수 있으며, 0일 경우에는 Index를 이용해서
Access되는 block 들이 SGA의 Buffer Cache 영역에서 찾을 수 있는 비율이 0%란
의미이다.
즉 모든 index access는 DISK I/O 를 발생하여 Physical Reads를 수행 한 후에 Buffer
cache로부터 Logical reads를 수행한다는 의미가 된다. 이 파라메터는 CBO에서 Index
block을 access할때 소요되는 Cost를 계산하기 위한 비율로서 사용되어진다..
3. optimizer_dynamic_sampling
< 10g에서의 RBO Optimizer Mode를 사용할 수 있는가? >
- Database Level에서 RBO 지원하지 않으며, Session Level에서 /*+ Rule */ 힌트에
의해서 일부의 RBO Path가 지원이 되고 있음. 이후 버전에서는 RBO를 지원하지 않
을 것임.
RBO는 하위 버전(v7.3이전)과의 호환성을 위하여 지원하는 것이며, 10g의 init
Parameter에 의해서 기본적으로 통계정보가 없는 테이블에 대한 Plan 해석도
RBO로 되지 않으며, CBO로 해석이 된다.
< Init Parameter >
- optimizer_dynamic_sampling = 2 (default)
이 파라미터가의 값이 2로 설정되어 있을 경우 Query parsing time에
64 blocks가 Query Optimization을 위해서 Sampling 되어 진다. 기본적으로
활성화 되어 있으며, 아래와 같이 설정할 경우에는 비활성화 시킬 수 있다.
< 변경방법 >
- optimizer_dynamic_sampling = 0
- optimizer_features_enable = (9.0.1 or 이하 버전)
4. _optim_peek_user_binds=FALSE
: _optim_peek_user_binds=TRUE일 경우 Bind Value가 있는 SQL의 해석과
FALSE일 경우에 해석되는 것에 많은 차이가 있다. TRUE일 경우 Bind Value에
인덱스가 있고, 해당 SQL이 Bind Value를 이용해서 해석이 되어야 하는 경우
Query Optimizer가 Bind Value의 Column Histogram을 참조하여 SQL문을 해석
하고 실행하게 됨. 이때, 평상시 조회가 되지 않는 조건으로 Binding이 될경우
평상시의 PLAN과 상이하게 해석될 수 있으며, 해당 SQL의 Cursor가 Aging Out
되지 않는 동안에는 PLAN이 유지되게 된다. Aging Out 된 이후에는 또다시
Column Histogram을 참조하여 PLAN을 세우게 되어 이전과는 상이한 실행계획
이 수립되어 SQL이 실행될 수 있다. False일 경우에는 일반적인 Query Optimizing
을 하게 된다. (필수적용사항)
alter session set "_optim_peek_user_binds"=false ;
5. _b_tree_bitmap_plans=FALSE
: Query Optimizer가 SQL 해석할 때 Where절에 여러 조건이 있고, 해당 조건
컬럼들에 Index가 각각 생성되어 있을 경우 B*tree Index를 Bitmap으로
conversion하여 PLAN을 수립하여 실행함. 이럴경우 TYPE이나 Code성 컬럼의
경우 B*tree Index range scan으로 해석되는 경우보다 성능이 나을수 있으나
일반적으로 성능이 저하되는 경우가 더 많음.(필수적용사항)
6. optimizer_mode='FIRST_ROWS_100'
: 10g R1에서는 optimizer_mode를 first_rows로 설정과 관련된 Bug이 있었음.
그리고, 온라인(OLTP) 환경에서는 First_rows 설정이 아닌 First_rows_100
정도 세팅하는것이 유리함.
7. _optimizer_sortmerge_join_enabled=FALSE
: Merge Join Cartesian(Merge Join)을 없애기 위한 파라미터 세팅으로,
Cartesian Product는 Join Ker가 없이 Join이 발생할 경우 발생되는게
정상이나 비정상적으로 Merge Join Cartesian이 발생되어 SQL들의 실행계획
이 비정상적으로 수립 및 실행되어 성능저하가 많이 발생하여 적용을 함.
주의할 점은 cartesian Product가 발생할 경우 Nested Loop로 PLAN에 해석되
므로, PLAN 해석할 때에 유의하여야 함.(필수적용사항)
8. _optimizer_skip_scan_enabled=FALSE
: Index Skip Scan이 되지 않게 하기위한 파라미터임.
-> Index Skip Scan이 필요한 경우에는 10g부터 지원되는 opt_param() 힌트를
사용하여 SQL Level에서 Index Skip Scan이 되게끔 바꿔주면 됨.
( /*+ opt_param('_OPTIMIZER_SKIP_SCAN_ENABLED','FALSE') */ )
-> Index Skip Scan이 False일 경우 Skip Scan이 되지 않지만, Index Column인
경우에 Index Filter가 되기때문에 성능상 많은 영향은 없음)
[ Hash Join ]
1. HASH_JOIN_ENABLED
Hash Join이 가능하도록 하기 위해서는 다음 방법 중의 하나를 사용한다 .
init.ora화일에 이 값을 TRUE로 지정한다.
ALTER SESSION SET HASH_JOIN_ENABLED = TRUE로 지정한다.
힌트(use_hash)를 이용한다.
2. HASH_AREA_SIZE
Hash Join에서 사용되는 메모리의 최대 크기
Default는 SORT_AREA_SIZE의 2배
DSS시스템에서는 매우 크게 지정하는 것이 바람직(30MB)
3. HASH_MULTIBLOCK_IO_COUNT
Hash Join시 한번에 I/O하는 블럭 수
Default는 DB_MULTIBLOCK_READ_COUNT
[ DB Links init parameter ]
1. open_links
|| Parameter type Integer
|| Default value 4
|| Parameter class Static
|| Range of values 0 to 255
한 세션에서 Remote Database로 동시에 연결할 수 있는 최대 수
- 8.1.7 이전 버전에서 open_links의 설정값을 255를 초과하여 설정시
ORA-600 [k2rcbk: null gti] Internal Error 발생함. open_links 설정시
255를 초과하면 안됨. (Bug no. 1035278)
- ora-600 [npiane0] Internal Error 발생 (Bug 1092735)
open_links 값을 초과하여 connection 시도시 발생
2. open_links_per_instance
Parameter type Integer
Default value 4
Parameter class Static
Range of values 0 to 4294967295 (4 GB -1)
Oracle9i Real Application Clusters Multiple instances can have different values
[ Curosr 관련 init parameter ]
: Cursors는 library cache(shared SQL area)에 할당된 memory 공간으로 LRU
알고리즘에 의해서 관리된다.
< Cursor 정보 >
- 구문 분석된 명령문(정적, 동적 및 순환 SQL, 그리고 프로시저나 데이터베이스
트리거등의 프로그램 단위) : P-Code
- Execution-Plan
- 참조 객체 목록 (원본 TEXT)
-------------- Session cached cursors Hit Ratio ------------
select round((hit.value/tot.value)*100, 5) session_cache_hit_ratio
from v$sysstat tot,
v$sysstat hit,
v$sysstat cnt
where tot.name = 'parse count (total)'
and hit.name = 'session cursor cache hits'
and cnt.name = 'session cursor cache count'
session_cache_hit_ratio는 최소 50%이상이어야 한다.
select gethitratio, pinhitratio
from v$librarycache
where namespace='SQL AREA'
gethitratio, pinhitratio > 90% 이어야 하고,
sum(pins) / sum(reloads) <= 1% 이어야 한다.
-------------- Session cached cursors Hit Ratio ------------
1. Open_cursors
: 한 세션이 열수있는 최대 cursor 개수
2. Session_cached_cursors
: 열려있는 세션이 가질 수 있는 최대 Cursors 개수
SESSION_CACHED_CURSORS 파라메터는 동일한 SQL을 반복수행(3회이상)하는 경우에
유리하며, 보통 softer parse 라고 한다.
모듈별로 특정 SQL 들을 반복 수행하는 세션들에 설정시 SOFT 파싱부하를
감소시켜준다. 시스템이 내부 수행하는 ReCursive SQL 도 포함되므로
최소 30 이하로 설정하는것은 효과가 없으며 보통 50 이상을 권장한다.
동일한 SQL이 동일세션에서 3회이상 수행 시 PGA에 해당 SQL의 Handle Address를
Caching하게 되며, Caching 정보를 토대로 해당 Bucket의 모든 Handle을 모두
검색하지 않고, Caching 되어 있는 Handle Address를 가지고 해당 Handle의 LCO
에 Direct하게 탐색을 하게 되어 일반적인 Soft Parsing 보다 개선의 효과가
더 크다. 단, parse count가 자체가 줄어드는 것은 아니며, 탐색하는 시간 즉
parsing time이 절감하는 효과가 있다. Session_cached_cursors 의 설정은 PGA에
해당 세션의 SQL(3회이상 수행)을 Caching 하는 것으로, Library cache object를
pinned하지 않기 때문에 Soft Parsing이 발생하게 되는 것이다. 반면에
PL/SQL에서 사용되는 Hold Cursor(Static SQL)의 경우에는 library cache object를
Pinned한 상태에서 반복수행되므로, soft parsing이 발생하지 않게 되는 것이다.
3. cursor_space_for_time = true[false]
: 세션에서 사용된 Cursor를 세션이 닫힐 때까지 SGA에 남겨놓는다.
4. cursor_shaing = [ EXACT, FORCE, SIMILAR ]
: cursor_sharing(FORCE, SIMILAR)을 설정 시 처음 수행되는 literal value를
bind value로 대체를 하게 되는데, 해당 cursor가 Memory에서 Aging out 되지 않을
경우 이후 수행되는 literal value에 대해서는 peek at the bind로 수행되게 된다.
peek at the bind로 해석되는 것은 "_optim_peek_user_bind"=TRUE 로 해석되는것과
동일하게 해석되는 것이다.
위와 같이 해석될 경우 system level에서 cursor_sharing를 설정하는 것은 상당히
위험해 질 수 있으므로, 해당 파라미터 세팅(FORCE,SIMILAR) 시에는 필히 SESSION
LEVEL OR SQL LEVEL에서 제어를 할 필요가 있음.
부가적으로, cursor_sharing = force로 설정할 경우에는 rownum 사용에 주의를 하여
야 한다. rownum = 1 과 같이 프로그램 작성시 cursor_sharing=force를 설정할 경우
rownum = :b1 과 같이 Oracle 내부적으로 변경되므로 전체범위 처리후에 해당 1건의
row를 추출하게 된다. 이때에는 rownum <= 1과 같이 변경 후 cursor_sharing=force
를 설정하여야 한다.
[ Freelist & Freegroup ]
1. _bump_highwater_mark_count = 0 (내부적으로 5 )
자동관리방식이 아닐경우 : Table의 Freelists * _bump_highwater_mark_count
[ Scalar Subquery ]
: 9i 부터 설정가능
1. _query_execution_cache_max_size=4194304
: SQL 수행 시 select list clause에서 function call 과다에 의한 성능부하가 심할
경우가 많이 있다. 그럴경우에는 function call 부분을 스칼라서브쿼리로 변경하여
function call에 의한 부하를 최소화 할 필요가 있다. 이럴경우 스칼라 서브쿼리를
위한 buffer cache 영역을 늘려주어야 하며, _query_execution_cache_max_size 로
메모리 사이즈를 늘려주어야 한다. 일반적으로 OLTP 환경에서는 4M 정도가 적당하며
Function Call을 스칼라 서브쿼리로 변경할 경우 스칼라 서브쿼리 buffer cache
변경외에 또다른 주의할 사항이 있는데, Function Call에 의해 데이터를 리턴받을 때
옵티마이저는 리턴받는 데이터를 위하여 varchar2(4000) 만큼 Memory 영역을
점유하게되므로, substr()로 적정하게 사이즈를 두어 비효율적으로 옵티마이저가
메모리를 점유하지 않게 하여야 한다.
[ Parallel ]
1. _PX_use_large_pool=TRUE
: Use Large Pool as source of PX buffers
2. parallel_automatic_tuning=TRUE
3. parallel_execution_message_size=64k
4. parallel_instance_group='A'
5. parallel_max_servers=80
[ I/O 관련 init parameter ]
1. _hash_multiblock_io_count=32
2. _sort_multiblock_read_count=32
3. db_file_multiblock_read_count=32
4. _index_join_enabled=FALSE
: Index Join Disable
5. _fast_full_scan_enabled=TRUE (default가 TRUE)
6. _disable_image_check
: DB Server의 alert_SID.ora file과 UDUMP내의 trc file에 위의 로그를 과다하게
기록하면서 DB Server의 Disk I/O를 과점유 하게 되면서 Disk I/O의 부하율이
증가함. (10.2.0.2에서 로드런너 테스트 시 문제가 발생하였음.)
WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these
messages
( 원인도출 )
=> Ioctl ASYNC_CONFIG error, errno = 1
HP-UX에 async driver가 enabled 되어 있을 경우 /dev/async 디렉토리에 접근을 시도
하는데, 이때 MLOCK 권한이 DBA Group에 있어야 하며, MLOCK 권한이 없을 경우 문제의
로그가 *.trc 파일에 과다하게 발생되어 Disk I/O를 과다하게 점유함.
관련 버그 : Bug 2913373
=> 에러메시지 : WARNING: Oracle executable binary mismatch detected.
( 문제해결방법 )
=> Ioctl ASYNC_CONFIG error, errno = 1
(1) # /usr/sbin/setprivgrp dba MLOCK <----------- dba group에 권한부여
(2) # vi /etc/privgroup
dba MLOCK RTSCHED RTPRIO -> 입력된 사항 체크
(3) # cat /etc/privgroup
dba MLOCK RTSCHED RTPRIO -> 입력된 사항 체크
=> WARNING: Oracle executable binary mismatch detected.
SQL> alter system set "_disable_image_check" = true ;
[ Partition 관련 init parameter ]
1. skip_unusable_indexes = TRUE
partition index가 IU 상태가 되었을때 그 partition index을 사용 하게 되면
다음과 같은 에러가 발생하게 된다.
SQL>SELECT /*+ INDEX(A A_IDX1) */ *
FROM SALES A WHERE PROD_ID >= 10000
ORA-01502: 인덱스 'A_IDX1' 또는 인덱스 분할영역은 사용할 수없는
상태이다
이와 같은 에러가 발생시 해당 파티션을 해당 인덱스를 통해서 엑세스 할 수 없게
됨으로 해당 어플리케이션의 수행을 유지 할 수 없는 상황이 발생하게 된다.
물론 인덱스를 사용하지 않고 해당 파티션을 TABLE FULL SCAN 하면 에러가 발생
하지 않는다.
하지만 옵티마이져가 실행계획 수립시에 INDEX UNUSABLE 상태 여부를 체크하지
않으므로 해당 인덱스를 사용하는 실행계획이 수립되고 실제 수행시 오류가 발생하게
됨으로 이미 운용중인 어플리케이션이나 SQL이 정상 수행되지 않는다.
이런 경우에는 skip_unusable_indexes = TRUE 파라메터를 지정하면 수행시에 해당
인덱스가 UNUSABLE이면 이를 사용하지 않고 TABLE FULL SCAN 등을 한다.
경우에 따라서 유용하게 사용 가능하므로 고려 해 볼 수 있다.
[ Buffer Pool(KEEP) 관련 init parameter ]
1. db_keep_cache_size
: Buffer Pool에 Keep Buffer를 사용하기 위한 Keep Buffer에 Size를 할당
하기 위한 init parameter임.
alter system set DB_KEEP_CACHE_SIZE = 2G ;
================================================================================
shared_pool_reserved_size
: 일반적으로 5% * Shared_pool_size
_shared_pool_reserved_min_alloc
: Reserved Area를 사용할 수 있는 최소 크기
10.2.0.1의 Default 4400 bytes(다른 DBMS Version도 동일할 것임)
_kghdsidx_count
: Shared Pool 분할 개수
Default가 1 , Rac 2Node의 경우에는 Default가 2 이다.
SGA_MAX_SIZE
: 오라클 9i부터의 파리미터이다.
SGA의 Size는 SGA_MAX_SIZE를 초과할 수 없다.
SGA_TARGET
: SGA내의 Shared Pool, Buffer Cache등 각 Memory Component의 Memory Size를
자동 설정하게 됨. 이 파라미터가 설정되어 있으면 Show sga 시 출력되는
Variable Size에 Free Memory가 포함되지 않음.
PGA_AGGREGATE_TARGET
[ Buffer Cache 관련 Init parameter ]
_db_block_hash_latches
: data block hash latches로 buffer cache의 bucket 내의 hash chain 탐색하는것을
제어하는 cache buffers chains latch 의 개수를 정의하는 파라미터이다.
select count(*)
from v$latch_children
where name like 'cache buffers chains%'
위의 쿼리가 실제 cache buffers chains latche가 생성되어 있는
개수(1024)를 알 수 있다. (10.2.0.1)
_db_block_hash_bucket
: buffer cache의 총 bucket의 개수를 의미한다.
총 bucket의 개수 / cache buffers chains latch 총 개수
=> 1 lache 당 관리대상 bucket 개수
_db_block_bufferss
: buffer cache내에 cached buffer 수
_db_block_lru_latches
: Working Set(LRU list, LRUW(dirty) list)의 개수를 결정하는 파라미터이다.
이 파라미터의 설정값만큼 Working Set은 Instance가 기동될때 Buffer Pool에
할당되게 된다. (최소 8개)
select count(*)
from v$latch_children
where name like 'cache buffers lru chain%'
위의 쿼리가 실제 cache buffers lru latches가 생성되어 있는
개수(8)를 알 수 있다.
sort_area_size
sort_area_retained_size
pga_aggregate_target
: Oracle 9i부터 제공된 파라미터로 설정값은 최대 설정 가능 사이즈가 아니고
한 세션에서 가질 수 있는 값을 할당하기 위한 지표로 사용이 된다.
[ ADVISOR 관련 init Parameter ]
1. db_cache_advice
: Dynamic SGA 기능을 이용해서 buffer cache의 크기를 조절하는 것은 사용자의
판단에 의해 이루어져야 한다. 이때 사용자가 참조할 수 있는 통계 정보 생성에
사용되어지며 DB_CACHE_ADVICE = ON인 경우 내역은
V$DB_Cache_Advice View에 기록 된다
- 이 통계정보를 바탕으로 사용자가 현재의 작업부하를 고려해서 적당한 크기로
buffer cache의 크기를 조절할 수 있다
- V$DB_Cache_Advice View에는 buffer cache별로 현재 크기의 10%에서 200%까지
20개의 크기에 대한 simulation정보를 기록한다. 각 크기별로 기존 block
참조 정보를 이용해서 예상되는 물리적 읽기 수를 제공한다
- Buffer Cache Advisory 기능 사용은 다음 두 가지의 오버헤드를 일으킨다.
-> Advisory 기능은 buffer cache별로 bookkeeping을 위한 아주 약간의
CPU 오버헤드가 필요하다.
-> MEMORY: Advisory 기능은 buffer block 당 shared pool에서
약 700 byte 정도의 메모리를 할당한다.
- parameter는 ON, OFF, READY 세가지 값을 가질 수 있는데(default: OFF),
각각의 상태의 의미는 다음과 같다.
-> OFF: Advisory 기능이 disable되고, CPU나 MEMORY 오버헤드가 없음
-> ON: Advisory 기능이 enable되고, CPU나 MEMORY 오버헤드가 발생
-> READY: Advisory 기능은 disable되나, shared pool의 메모리는 할당
- READY 나 ON 시에는 shared pool의 Contention이 발생 하므로 충분한 여유공간을
확인 후 작업을 한다. 물론 Overhead가 발생 함을 주위 해야 한다.
-> Buffer cache size: 3G (400,000block ,block size 8k)
400,000 * 700 = 70M shared pool추가필요
-> V$sgaarea 에서 shared pool의 "sim memory heap" Size로 확인 할 수 있다.
- 오라클에서의 권고사항은 업무 운영시에는 반드시 OFF 상태로 변경 ~!