출처 :
친절한 SQL 튜닝<조시형>
조동욱님 블로그(https://blog.naver.com/ukja/120040625665)
Consistent mode, Current mode에 대해
저번 실행계획 확인 부분에서 나온 consistent mode와 current mode에 대해 지식이 부족함을 느껴 공부함.
간단하게 정리하면
select -> consistent mode
DML -> 어떤 row를 건드릴지 식별할 때에는 consistent mode, 값을 건드릴 때에는 current mode
Consistent mode
consistent가 select 수행 시에만 일어나는 것은 아니고 DML에서 읽는 단계(where절 등)일 때도 일어난다.
Undo를 이용한 Consistent Read
DML이 들어와서 기존에 존재하던 block을 변경하면 해당 block은 dirty가 된다.
그 상태에서 다른 session이 해당 dirty block에 대해서 select를 시도하면 CR copy가 발생한다.
새로 block을 만들고(=CR block) undo를 이용해서 rollback을 수행한다.
즉, dirty block이 되기 전의 원본 데이터를 가진 CR block이 생성되는 것이다.
select를 수행한 session은 해당 CR block을 가지고 간다.
여기서 CR block은 최대 5개까지만 만들어지고 가장 오래된 block은 사라진다.
이유로는 매 CR 때마다 block을 만들고 rollback을 수행하면 부하가 많이 걸리기 때문에 한 번 만들어둔 걸 가능한 한 재사용하는 느낌.
그리고 만약 부하가 많은 상황에서 CR block이 buffer를 차지하고 있으면 안되니 CR block은 LRU list 끝쪽에 위치되어, 부하가 많은 상황에서는 금방 밀려나도록 한다.
Current mode
현재 시점 버전 block을 읽는 것. 즉, undo는 사용하지 않는다.
행되는 순간은 DML 실행 직전/직후, LOCK 잡을 때, INDEX RANGE SCAN 등에서 현재 값 필요 시.
SQL 처리 과정
1. Parsing
– Syntax check : 문법적 오류(키워드, 순서 등) 확인.
– Semantic check : 의미상 오류(존재하지 않는 object, 권한 등) 확인.
2. Library cache 확인
– 만약 실행계획이 있다면 그 계획으로 query 실행.
– 만약 실행계획이 없다면 아래의 3, 4번 수행.
3. 최적화 (SQL Optimizer)
– Optimizer가 실행계획을 결정. (hard parsing)
4. Row-Source 생성
5. Query 실행
Parsing
SQL 처리 과정을 한 번 거친 내부 프로시저를 재사용할 수 있도록 caching 해두는 메모리 영역을 Library Cache 라고 한다.
Soft Parsing
실행계획이 library cache에 존재하는 경우, 있는 실행계획을 그대로 사용한다. (Soft Parsing)
Hard Parsing
실행계획이 library cache에 존재하지 않는 경우, 실행계획을 만든다. (Hard Parsing) 따라서 대부분이 disk I/O인 DB 작업에서, CPU를 많이 사용하는 몇 안되는 작업 중 하나다. 그렇게 cost를 사용하여 만든 실행계획을 한 번만 사용하고 날린다면 비효율적이기 때문에 Library cache가 존재한다.
실행계획을 만드는 과정은, data dictionary에 있는 통계정보(System, Object)를 이용해서 여러 방법의 실행계획을 만들어보고, 그 중에 cost가 가장 적은 실행계획을 선택해서 해당 계획으로 쿼리를 수행시킨다.
- system 통계 : CPU 속도, Single Block I/O 속도, Multi Block I/O 속도 등.
- obejct 통계 : table, index, column(histogram을 포함) 통계.
Hint
1. table name을 hint에 줄 때 schema name까지 같이 주면 안된다.
select /*+ full(scott.emp) */ -- 무시됨.
from emp;2. table name에 alias를 사용했다면 hint에도 alias를 사용해야 된다.
select /*+ full(e) */
from emp e;3. hint를 여러 개 사용할 때 ,를 쓰면 뒤에 hint는 무시된다.
select /*+ full(e), index(e e_idx1) */ -- 뒤에 index hint는 무시됨.
from emp e;| 분류 | 힌트 | 설명 |
| 최적화 목표 | ALL_ROWS | 전체 처리속도 최적화 |
| FIRST_ROWS(N) | 최초 N건 응답속도 최적화 | |
| 액세스 방식 | FULL | Table Full Scan으로 유도 |
| INDEX | Index Scan으로 유도 | |
| INDEX_DESC | Index 역순으로 유도 | |
| INDEX_FFS | Index Fast Full Scan으로 유도 | |
| INDEX_SS | Index Skip Scan으로 유도 | |
| 조인 순서 | ORDERED | from절에 나열된 순서대로 조인 |
| LEADING | LEADING 힌트 괄호에 기술한 순서대로 조인 | |
| SWAP_JOIN_INPUTS | hash join 시, build input을 명시적으로 선택 | |
| 조인 방식 | USE_NL | NL join으로 유도 |
| USE_MERGE | sort merge join으로 유도 | |
| USE_HASH | hash join으로 유도 | |
| NL_SJ | NL semi join으로 유도 | |
| MERGE_SJ | sort merge semi join으로 유도 | |
| HASH_SJ | hash semi join으로 유도 | |
| 서브쿼리 팩토링 | MATERIALIZE | with문으로 정의한 집합을 물리적으로 생성하도록 유도 |
| INLINE | with문으로 정의한 집합을 INLINE 처리하도록 유도 | |
| 쿼리 변환 | MERGE | 뷰 머징 유도 |
| NO_MERGE | 뷰 머징 방지 | |
| UNNEST | sub query unnesting 유도 | |
| NO_UNNEST | sub query unnesting 방지 | |
| PUSH_PRED | 조인조건 push down 유도 | |
| NO_PUSH_PRED | 조인조건 push down 방지 | |
| USE_CONCAT | or 또는 IN-List 조건을 OR-Expansion으로 유도 | |
| NO_EXPAND | or 또는 IN-List 조건을 OR-Expansion으로 방지 | |
| 병렬 처리 | PARALLEL | table scan 또는 DML을 병렬 처리하도록 유도 |
| PARALLEL_INDEX | index scan을 병렬 처리하도록 유도 | |
| PQ_DISTRIBUTE | 병렬 처리 시 데이터 분배 방식 결정 | |
| 기타 | APPEND | Direct-Path Insert로 유도 |
| DRIVING_SITE | DB Link Remote query에 대한 최적화 및 실행 주체 지정(Local | Remote) | |
| PUSH_SUBQ | subquery를 가급적 빨리 필터링하도록 유도 | |
| NO_PUSH_SUBQ | subquery를 가급적 늦게 필터링하도록 유도 |
논리적, 물리적 저장 구조 및 단위
Oracle에서 block size는 8k(=8192)bytes가 default다. block은 읽고 쓰는 단위.
record 단위로 읽을 수 없다.
extent는 확장 단위.
segment에는 table, index, partition, LOB가 있다.
Access 방식
table이나 index를 읽는 방식으로 2가지가 있다.
Sequential Access
논리적 또는 물리적으로 연결된 순서에 따라서 차례대로 block을 읽는 방식.
index의 경우 : index leaf block(제일 밑단)은 각각 앞뒤를 가리키는 주소값을 통해 서로 논리적으로 연결되어 있다. 이 연결을 따라서 순차적으로 스캔한다.
table의 경우 : Oracle은 segment에 할당된 extent 목록을 segment header에 map으로 관리한다.
extent map은 각 extent의 첫번째 block address를 갖는다. 따라서 map에서 모든 extent의 address를 알 수 있기 때문에 이를 전부 스캔하면 table full scan이 된다.
Random Access
논리적 또는 물리적 순서를 따르지 않고, record 하나를 읽기 위해 한 block씩 접근(touch)하는 방식.
I/O
db buffer cache에 원하는 data가 존재한다면 disk를 가지 않아도 돼서 속도가 빠르다.
db buffer cache에 원하는 data가 없다면, disk에 가서 해당 data를 가져와야 하기 때문에 disk I/O가 발생하면서 query 수행 속도가 느려진다.
한 번 data를 가지고 db buffer cache에 올려놓으면, 해당 data가 오래 사용하지 않아 밀려서 사라지기 전까지는 disk를 다녀올 필요가 없어서, 처음 한 번만 느린 거고 그 뒤에는 빠르게 동작한다.
Logical I/O
SQL을 처리하는 과정에 발생한 총 block I/O를 말한다. 즉, memory I/O + physical I/O.
Physical I/O
disk에서 발생한 block I/O를 말한다.
Buffer Cache Hit Ratio = cache에서 찾은 block 수 / 총 block 수 * 100
= (logical – physical) / logical * 100
= (1 – physical/logical) * 100
위의 식을 조작하면
BCHR / 100 = 1 – physical/logical
– physical/logical = BCHR/100 – 1
physical/logical = 1 – BCHR/100
physical = logical * (1 – BCHR/100)
근데 위의 식에서 BCHR은 시스템 상황에 따라 달라지는 값이므로, physical을 줄이려면 logical을 줄여야 된다. 즉, logical I/O를 줄여서 물리적 I/O를 줄이는 것이 튜닝이다.
Single block I/O
한 block씩 caching 하는 방식.
index를 이용할 때는 기본적으로 index와 table 모두 single block I/O 방식이다.
Multi block I/O
여러 block씩 caching 하는 방식. disk 상에서 찾는 특정 block과 인접한 애들을 한꺼번에 읽어 미리 caching하는 개념이다.
* 인접한 애들 = 같은 extent에 속한 block. 즉, 아무리 multi block I/O여도 extent를 넘기진 못한다는 의미.
table full scan 시 multi block I/O 방식이다.
multi block I/O 단위를 키우면 성능적으로 좋아질 수 있다. OS마다 다르지만 보통 OS단에서는 1MB 단위로 I/O를 한다.
db_file_multiblock_read_count 파라미터 값을 128로 하면 8k(block size) * 128 = 1024KB = 1MB 가 된다.
즉, 최대한의 효율로 caching하게 된다.
Scan 방식
Table Full Scan
Sequential Access + Multi block I/O 방식으로 읽는다.
cache에서 원하는 record를 못 찾아서 disk에 다녀올 경우, 하나의 I/O call로 수십~수백 개 block을 가져온다.
따라서 storage의 scan 성능이 좋아지는 것이 유효하다. 예를 들어 Exadata에서는 index 타는 것보다 full scan이 빠를 수도 있다.
Index Scan
Random Access + Single block I/O 방식으로 읽는다.
cache에서 원하는 record를 못 찾아서 disk에 다녀올 경우, record 하나를 찾기 위해서 하나의 I/O call을 사용해 disk에서 block을 1개 가져오는 개념이므로 많은 데이터를 읽을 때에는 성능이 좋지 않다.
즉 Index는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구다. 읽을 데이터가 일정량을 넘기면 Full Scan이 유리하다.
Cache 탐색 메커니즘
Direct Path I/O를 제외한 모든 block I/O는 db buffer cache를 경유한다.
– Index root block을 읽을 때
– Index root block에서 얻은 address로 branch block을 읽을 때
– Index branch block에서 얻은 address로 leaf block을 읽을 때
– Index leaf block에서 얻은 address로 table block을 읽을 때
– table block을 full scan할 때
DB Buffer cache는 hash 구조로 관리된다.
block을 찾을 때 hash value를 구해서 해당 hash chain을 탐색한다.
탐색했을 때 못 찾으면 disk에서 가져와서 해당 hash chain에 연결하여 읽는다.
hash 구조의 특징은 아래와 같다.
– 같은 입력 값은 항상 동일한 hash chain(=bucket)에 연결된다.
– 다른 입력 값도 동일한 hash chain에 연결될 수 있다.(ex 5로 나누는 hash에서 4와 9)
– hash chain 내에서는 정렬이 보장되지 않는다.
근데 DB Buffer cache는 SGA이므로 모든 process가 접근할 수 있다.
만약 process가 DB Buffer cache를 사용 중인데 다른 process가 접근하려고 할 경우 막는 매커니즘이 필요한데, Latch가 그 매커니즘이다.
각 hash chain의 맨 앞에 latch라는 자물쇠가 있다고 생각하면 된다.
Selectivity & Cardinality
selectivity(선택도) : 전체 레코드 중에서 where절에 의해 선택되는 레코드 비율.
selectivity = 1 / Number of Distinct Values(NDV. 컬럼 값 종류 개수)
cardinality : 전체 레코드 중에서 where절에 의해 선택되는 레코드 개수.
cardinality = total row count * selectivity = total row count / NDV
optimizer는 cardinality를 구한 뒤 그걸 기반으로 cost를 계산해서 실행계획을 결정한다.
cardinality는 결국 NDV에 따라 달라지기 때문에 NDV를 정확히 구하는 것이 중요하다.