Oracle SQL Tuning 교육 정리 - Day 4

7. Interpreting Execution Plans and Enhancements

실행 계획(execution plan)은 row sources들의 tree 형태로 나타낼 수 있다.

  pid란, 해당 step의 부모 step의 id

  pos란, 해당 step의 부모 기준으로 붙인 child number

  예시) id=5, pid=2, pos=1 → 5번 step은 2번 step의 첫 번째 child

 

Execution plan example 1.

3 → 5 → 4 (→ 3 → 5 → 4 → ... 3 → 5 → 4) → 2 → 6 → 1 

SQL> alter session set statistics_level=ALL;
Session altered.
SQL> select /*+ RULE to make sure it reproduces 100% */ last_name,job_id,salary,department_name
FROM employees e,departments d WHERE d.department_id = e.department_id and not exists(SELECT * FROM jobs
WHERE e.salary between min_salary and max_salary);
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST'));

statistics_level=ALL : row sources 별 통계 정보 수집 (시스템 부하를 주기 때문에 운영DB에 사용할 때 유의, SQL Trace에서도 확인 가능한 내용)

format=TYPICAL IOSTATS LAST : 기본적인 정보와 I/O 관련 통계값을 보여줘

Starts: step이 실행된 횟수

A-Rows: 실제 검색된 rows수 (vs E-Rows: 옵티마이저가 예측한 rows수)

Buffers: 사용된 캐시 버퍼 block수

 

Execution plan example 2.

4 → 3 → 6 → 5 → 2 → 7 → 1

 

Execution plan example 3.

3 → 5 → 6 → 4 → 2 → 1

SWP_JOIN_INPUTS(c) : c를 driving table로 사용해라

 

Execution plan example 4.

SQL> select /*+ GATHER_PLAN_STATISTICS */ *
from oe.inventories where
warehouse_id = 1;
SQL> select plan_table_output
from table(dbms_xplan.display_cursor(format=> 'allstats last'));

/*+ GATHER_PLAN_STATISTICS */* : row sources 별 통계 정보 수집

format=ALLSTATS LAST

A-Rows vs E-Rows 를 비교해서 옵티마이저가 실행 계획을 잘 세웠는지 확인

 

Adaptive Query Optimization

옵티마이저가 plan에 대한 run-time 조정을 할 수 있게 해준다

SQL문 plan은 hard parse 할 때 작성된다 그러므로 SQL문 실행 중에는 plan이 변경되지 않는다 (원래는)

실행 중에 plan을 변경할 수 있다 when 기존 통계정보가 충분하지 않을 때

Adaptive Plan을 사용하면,

run-time까지 조인 방법 결정 하지 않는다

현재 통계 정보 기반으로 default plan을 계산해두고

다른 sub-plan들을 미리 계산해두고 cursor에도 저장해둔다

실행 중에 수집 된 통계 정보 기반으로 최종 plan을 결정한다

실행 중 plan이 변경되면 기존 plan으로 검색한 데이터는 swap point 근처 buffer에 올려둔다

 

실제 수행된 plan이 예상 실행 plan과 다른 경우, adaptive plan 때문에 변경된 건지 확실하지 않음

아래 format parameter를 설정하면 adaptive plan로 만들어진 건지 확인 가능

 

 

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format='+adaptive'));

병렬 작업은 대량 데이터를 한번에 처리해야 할 때 사용

Hybrid 방식: 데이터 분산 방식을 변경한다 → 자세한 설명은 생략...! 알아서 구글링

 

8. Optimizer: Table and Index Access Paths

Row source: 실행 계획에서 각 step 별로 return되는 row 집합- Unary operation 테이블 접근 방법- Binary operation 조인 방법- N-ary operation 합집합, 교집합 등

 

*Table Access Path· Full table scan: 테이블 전체 데이터를 읽는다  많은 양의 데이터를 검색할 때는 index range scan보다 빠르다  db_file_multiblock_read_count=4 --> 한번에 4개 blocks씩 읽어라   HWM까지 모든 blocks들을 읽는다  filter 작업을 하면서 full table scan을 하기도 한다· ROWID Scan

  인덱스에 저장된 읽어서 rowid로 조회한다

  rowid를 알려면 우선 select로 rowid를 확인해야 한다

  rowid로 조회하면 저장된 위치를 바로 찾아갈 수 있으므로 빠르다 --> 거의 안씀

*Index

B-tree 인덱스, Bitmap 인덱스, Cluster 인덱스

· B-tree index

  index entry - header, key column length, key column value, rowid(애를 통해 데이터 block을 찾아간다)

· Index Unique Scan

  unique 제약조건 컬럼이나 또는 PK 제약조건 컬럼으로 조회할 때

  equality 조건에서 사용

· Index Range Scan

· Index Full Scan

  order by 절의 컬럼들이 모두 인덱스에 포함되어 있을 경우 

· Index Fast Full Scan

  db_file_multiblock_read_count=4 요걸 사용할 수 있다

· Index Skip Scan

  결합 인덱스에서 특정 컬럼만 조건을 거는 경우 (불필요한 index block을 skip 한다는 것)

· Index Join Scan

  인덱스들끼리 조인한다

 

Index와 NULL

  col1에는 NULL이 있고, col2에는 NOT NULL 제약 조건 걸려있음

  col1에 조건이 없는 경우, full table scan 

  col1=10인 경우, index range scan

  col2에 조건이 없는 경우, index fast full scan

· Index-Organized Table

  테이블 자체가 인덱스와 같이 B-tree 구조로 저장

  각 Leaf block에 record가 들어간다 

  header, key column, non-key columns

  key column 조건 검색할 때 인덱스와 데이터를 각각 별도로 조회할 필요 없음 

  테이블을 조회 용도 (주로 select)인 경우 유용하다

  근데 update 작업 때 데이터 사이즈가 더 크다면 기존 공간이 부족하면 overflow 공간에 별도로 저장하므로 데이터를 1번에 읽는 장점을 놓치고 두개 공간에서 데이터를 읽어야 한다

 

인덱스 관리 가이드라인

- 데이터 insert 후 인덱스를 만든다 (insert 시 인덱스 구조가 변경되어야 하므로 insert 작업이 오래 걸린다)

- 꼭 필요한 경우에만 인덱스를 만든다

- 인덱스 TBS 분리 (vs 테이블 TBS)

- 인덱스 생성할 땐 nologging 옵션을 사용한다

- 인덱스 coalescing(조각 모음) or rebuilding

Full Table Scan 사용하는 경우	Full Table Scan 사용하지 않는 경우	Index Scan을 사용하지 않는 경우
parameter 설정 낮은 selectivity를 가지는 많은 양의 데이터 조회	sort 연산을 피하기 위해 index full scan 옵티마이저 모드 또는 힌트에 FIRST_ROWS 또는 FIRST_ROWS_n를 사용 적절하지 않은 USE_NL을 사용한 경우 병렬 처리를 하지 않음	인덱스 가진 컬럼에 함수 적용 컬럼 타입과 조건 값 타입이 맞지 않음 통계 정보가 오래됨 컬럼이 NULL 값을 포함 cluster factor가 높음 (I/O 발생이 많음)

 

9. Join Operators

· Nested Loop Join

driving table을 먼저 검색한 row에 대해 inner table을 검색하여 join

조인 데이터가 적은 경우 유용

driving table은 검색 조건이 좁은 테이블로 설정하면 쪼아 

 

 

 

12c부터 테이블 조건 검색을 위해 nested loop로 검색하기도 한다

넒은 범위 데이터 검색할 때 조건에 대해 미리 걸러두는 거지

 

 

 

 

· Sort-merge Join

(예시)

department는 인덱스를 통해 데이터 정렬

employee는 인덱스를 통해 데이터 집합을 만든다 sort 연산은 업음

 

테이블이 정렬되어 저장된 경우

그치만 요즘에는 별로 안씀

데이터 양이 많은 수록 Hash join이 유리한 경우가 많음

그치만 <, <=, >, >= 사용하면 hash join을 사용할 수 없으므로 sort-merge 해야해

 

 

· Hash Join

driving table을 memory로 올려서 hash table 생성 (새로운 테이블 만드는거야)

레코드 rowid 대신 hash value로 구분

(예시)

order_item은 hash table 만든다 (full table scan)

inventories는 인덱스를 통해 검색 (index fast full scan)

해시 값을 통해 데이터 확인해서 조인

 

 

10. Other Optimizer Operators Oracle Internal & Oracle Academy Use

*Results Cache

SGA에 Result Cache 영역 있음

SQL문 결과를 메모리에 올려두는 기능 (재사용 목적)

V$RESULT_CACHE_OBJECTS

사용 방법

테이블 생성 시 default로 RESULT_CACHE 올리지 않는다.

RESULT_CACHE(MODE FORCE)로 설정하면 테이블에 대한 select문 결과는 result cache에 올라감

Result Cache 공간이 가득 차면 데이터가 지워진다 (근데 LRU 알고리즘은 아님) -- 이게 왜 장점임?Plan을 통해 result cache에서 읽었는지 확인 가능DBMS_RESULTS_CACHE 패키지를 통해 results cache 관리

 

*Cluster클러스터 테이블? 처음 들어봄2개의 테이블을 1개의 cluster 테이블로 만드는 것cluster key에 대해 조인된 결과를 block안데 담아둔다 즉 다른 테이블 데이터를 같은 block에 담아둠인덱스 생성 가능조인해서 조회 용도로 많이 쓰는 테이블에 적용하면 좋다· Index cluster  테이블이 cluster key로 조인되는 경우  테이블의 크기를 잘 모르는 경우 (기존 테이블을 cluster로 만들 때 cluster 스키마를 만들어 놓고 데이터를 insert하는게 더 좋대 왜...? 인덱스 키가 가질 수 있는 사이즈가 있음. 

· Hash cluster

  모든 cluster key에 대한 저장공간을 초기에 할당

  index cluster와는 다르게 cluster 만들면서 cluster key id를 셋업하고... 그래서 데이터랑 같이 만들어두면 좋다

어떤 방법으로 만들던지 크게 상관없지만, 두개 cluster 만드는 방법 마다 유리한 cluster가 있다는거

· Single-table hash cluster

  1개 테이블로 cluster 만든거. 즉 테이블에 대해 hash table을 만든거얌 (hash value를 row id 대신 사용)

  테이블 데이터가 많은 경우 조회 속도 향상 기대 (등호 조건으로 조회)

· Sorted hash cluster

  특정 조건으로 정렬해서 cluster 만드는거

 

SQL tuning에서는 SQL문을 직접 튜닝하는 것도 중요하지만

table, index 구조를 IOT 또는 cluster 형태로 변경해서 사용하는 것도 성능 향상 방법 중 하나이다

 

examples)

employees2를 조회 할 때 hash이다 -> single-table hash cluster

employees3 인덱스 cluster로 조회 (인덱스 또는 해시 cluster 명시하는 것 아님. object가 아니라 저장공간의 개념)

 

* Sorting Operators

SORT AGGREGATE : 그룹 함수(max,min) 

SORT UNIQUE : 중복되는 값을 없애는 것(distinct, union)

SORT JOIN : sort-merge 조인

SORT GROUP BY, ORDER BY

HASH GROUP BY : hash value를 통해 group by 작업

HASH UNIQUE : hash value를 통해 중복되는 값을 없애는 것

 

* Buffer Sort Operator

2개 테이블을 조인 조건 없이 조인하면서 cartesian join을 하는데 sort-merge join으로

테이블 전체 데이터를 사용해야 하면서 인덱스를 사용할 수 없는 경우

데이터를 미리 buffer에 올려두겠다 (buffer=임시 테이블을 만드는 개념)

buffer sort는 UGA 즉 PGA에 들어감 (즉 해당 사용자만 사용할 수 있음)

temporary table은 SGA에 있음 물론 데이터는 공유 안되지만 스키마를 모두가 확인할 수 있음

대부분 cartersian join 하는 경우에만 buffer sort 연산한다...! 그러니까 buffer가 보이면 cartesian 연산 하는지 의심

 

* Inlist IteratorIN 연산자를 쓰면 inlist iterator라고 나온대IN 조건이 많으면 성능 저하 (OR문으로 변형)

 

* View Operator/*+ NO_MERGE */만약 merge를 한다면 plan에는 view가 아니라 table명이 보일거다merge vs no merge (view 통해 조회) 성능 비교해보는게 좋아인라인 뷰 

 

* Count Stop Key Operator전체 데이터 중 일부만 검색하고 있다 (rownum 조건 검색)

 

* Min/Max and First Row Operators인덱스 컬럼을 통해 min을 구한다

 

* Filter데이터 중 검색 조건에 맞는 데이터를 걸러낸다

 

* CONCATENATION

or 연산을 2개로 나눠서 union all 하기도 한다