SQL Tuning

 

SQL Tuning 이란 ?

 - 쓸데 없는 일을 시키지 않는 것

 - 병목현상을 없애어 모든 처리 과정이 최상의 성능을 발휘하도록 하는 것

 - 최소한 자원으로 최대의 일을 시키는 것

 - 놀고 있는 자원이 없도록 하는 것

 

 

SQL Tuning을 함으로써 튜닝이란 아래와 같이 3가지를 지칭할 수 있다. 

 

SQL Tuning 선결 과제

 1) 오라클 서버 튜닝 (DBA)

  - SQL 특성에 맞도록 오라클 서버 튜닝

  - 데이터베이스 메모리 및 I/O 튜닝

  2) 운영체제 튜닝(SA)

   - 오라클 서버가 운용되는데 필요한 기본적인 리소스 파라미터 튜닝

  3) 업무 기능 분석(개발자)

   - User Data 에 대한 정확한 이해자

   - 명확한 업무 분석 및 설계자

 

오라클의 SQL 튜닝을 하기 위해 우리는 어느 영역을 건드리는지 알 필요성이 있다. 

그러기 위해 SELECT 실행 원리를 알아야 한다. 

 

 

 

SELECT 실행 원리

*UP : User Process    ex) orange, toad, sql developer 등...

 SP : Server Process

 

1) 구문분석(Parse) : 실행계획을 만드는 단계

①문법 검사 (Syntax Check) : PGA

②의미 검사 (Semantic Check) : PGA, Dictionary cache(Row Cache)

③권한 검사 (Sharaed Pool Check) : PGA

④실행 계획 : Optimizer 실행계획 생성 : SGA -Shared pool- library cach

        a.Soft parse : library cache 실행계획이 있는 경우

        b.Hard parse : library cache 실행계획이 없는 경우

                I.Optimizer를 통해 실행 계획 생성

                 - Data Dictionary(statictics , Clustering Factor, Histogram 등..)

 

SQL 튜닝을 한다는 것은 구문분석(Parse) 건드리는 영역이며 새로운 실행계획을 만드는 Hard parse 과정이라고 생각 할 수 있다.  그렇다면 실행계획을 만들기 위해 Optimizer(옵티마이저)는 어떤 기준으로 실행계획을 만드는 걸까?

 

Optimizer 란 다양한 후보 경로에서 주어진 목적 함수를 최소화하는 후보 대한을 결정하는 추세이다. 

 

 

Optimizer의 동작 모드

1. RBO (Rule Based Optimizer) : 규칙 기반 최적화기

2. CBO (Cost Based Optimizer)  : 비용 기반 최적화기

 

 

RBO (Rule Based Optimizer)

- 9i부터 오라클에서는 더 이상 개선하고 있지 않으며, 사용을 권장하지 않음

- 11g 부터 RBO 기술 지원 중단

- RBO를 사용해서 발생하는 비지니스상의 문제에 대해서 책임지지 않음

- RBO가 동작하기 위한 필수 조건 - 사전에 지정된 우선순위 규칙

 

 

RBO의 장점과 단점 

- 장점 : 데이터 분포 또는 오라클 버전에 대하여 액세스 경로가 안정됨. 최소한의 성능 보장

- 단점 : 데이터 분포 또는 오라클 버전에 따라 최악의 경로가 발생 할 수 있음

 

RBO 우선 규칙 

1. ROWID에 의한 단일행 실행

2. Cluster-Join에 의한 단일행 실행

3. Unique-Key, Primary-Key를 사용한 Hash-Cluster에 의한 단일행 실행

4. Unique-Key에 의한 단일행 실행

5. Cluster 조인

6. Hash-Cluster Key

7. 인스턴스화 된 Cluster-Key

8. 복합 인덱스

9. 단일 컬럼 인덱스(EQUAL)

10. 인덱스가 구축된 컬럼에 대한 제한된 범위 검색(BETWEEN, …)

11. 인덱스가 구축된 컬럼에 대한 무제한 범위의 검색(>=, <= 표현식)

12. 정렬-병합 조인

13. 인덱스가 구축된 컬럼에 대한 MAX, MIN

14. 인덱스가 구축된 컬럼에 대한 ORDER BY

15. FULL TABLE SCAN

 

 

 

 

 

CBO (Cost Based Optimizer)

- 오라클 버전에 따라 기능이 업그레이드되며, 사용을 적극 추천함

- 비용(Cost)에 따라 실행계획 수립

  * 비용 계산되는 과정 확인 - 10053 trace 이용한 옵티마이저 덤프

    2022.01.24 - [Oracle/통계정보] - Histogram 사용하다가 중지할 경우 발생되는 이슈, 장애처리

 

CBO의 장점과 단점

- 장점 ; 데이터 분포 또는 오라클 버전에 대하여 최상의 경로가 선택 될 수 있도록, 액세스 경로가 변경됨

- 단점 : 데이터가 자주 업데이트되는 환경에서는 통계자료도 자주 업데이트해주어야 함

 

비용(Cost) = 수행 시간

 - 여러 후보 실행 계획을 비교하기 위해 예측한 작업 단위 값

 Elapsed Time = Service Time + Wait Time

 

Elapsed Time = 총 소요 시간 (DB Time)

Service Time = 실제로 일한 시간(CPU Time)

Wait Time = 일을 계속하기 위해 대기 했던 시간 (I/O 포함)

 

CBO에서 비용(COST) 산정하기 위한 파단 자료

- 테이블 구성 정보

- 인덱스 구성 정보

- 하드웨어 성능

 

비용(Cost) 을 이해하기 위해 알아야 할 용어
* 블록
  - 오라클 데이터베이스에서 읽기 및 쓰기의 단위
  - DB_BLOCK_SIZE 파라메터에 지정. (예: 8KB)

* Single Block I/O
  - 운영체제의 I/O 서브시스템에 대하여 I/O 요청 1회당 1개의 블록에 액세스하는 연산
  - FULL TABLE SCAN과 INDEX FAST FULL SCAN 연산을 제외한 모든 연산에 사용

  - 예) select * from emp where empno=7844
  - emp_enmpno_idx의 인덱스 높이가 2이었다면, 인덱스 블록 2개와 테이블에서 7844 데이터가 있는 블록 1개를 읽음
  - 즉, 3회의 I/O가 발생

* Multi Block I/O
  - 운영체제의 I/O 서브시스템에 대하여 I/O 요청 1회당 여러 개의 블록에 액세스하는 연산
  - 테이블 전체를 FULL TABLE SCAN 방식으로 액세스 할 때, 발생하는 블록 읽기 횟수
  - 인덱스 전체를 INDEX FAST FULL SCAN 방식으로 액세스 할 때, 발생하는 블록 읽기 횟수
  - DB_FILE_MULTIBLOCK_READ_COUNT 파라메터에 지정. (예: 128)

  - 예) select * from emp where empno=7844
  - emp 테이블의 전체 데이터가 10개의 블록에 저장되어 있다면, 1회의 I/O로 10개 블록을 전부 읽고, 9개의 블록을 버린 후, 1개의 블록 선택
  - 즉, 1회의 I/O 발생