SQL튜닝 - Chpater07. SQL옵티마이저

7.1 선택도와 카디널리티

 

선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

선택도 구하는 공식

선택도 = 1 / NDV

카디널리티(Cardinality)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수.

카디널리티 구하는 공식

카디널리티 = 총 로우 수 * 선택도 = 총 로우수 / NDV

 

7.2 통계정보

 

통계정보에는 오브젝트 통계와 시스템 통계

 

오브젝트 통계는 테이블 통계, 인덱스 통계, 컬럼 통계로 나뉜다

 

시스템 통계 : 애플리케이션 및 하드웨어 성능 특성을 측정한 것. 아래 항목들을 포함한다

 

- CPU 속도

- 평균적인 Single Block I/O 속도

- 평균적인 Multiblock I/O 속도

- 평균적인 Multiblock I/O 개수

- I/O 서브시스템의 최대 처리량 ( Throughput )

- 병렬 Slave의 평균적인 처리량 ( Throughput )

 

 

7.3 옵티마이저 종류

 

비용기반 옵티마이저 : 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정, 그 중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저

 

더이상 사용하지 않지만 과거에 사용했던 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 규칙기반 옵티마이저도 있다.

 

옵티마이저 모드

 

옵티마이저 모드를 ALL_ROWS로 설정하면 옵티마이저는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스를 가장 적게 사용하는 실행계획을 선택.  전체 처리속도 최적화가 목표

 

FIRST_ROWS로 설정하면 옵티마이저는 전체 결과집합 중 '앞쪽 일부만 읽다가 멈추는 것을 전제로' 응답 속도가 가장 빠른 실행계획을 선택.  최소 응답속도 최적화가 목표

 

ALL_RWS와 비교하면, Table Full Scan 보다 인덱스를 더 많이 선택하고, 해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보인다.

 

옵티마이저 모드 설정이 중요한 이유

예를 들어, 100만 건의 결과집합 전체를 파일로 저장하려는데, 옵티마이저 모드가 FIRST_ROWS로 설정돼 있으면 인덱스를 이용한 소트 연산을 생략하는 실행계획이 선택된다. 앞서 설명한 Order By 컬럼에 인덱스가 있으면 무조건 인덱스를 이용하는 RBO 규칙과 비슷하다. 그런데 사용자가 중간에 멈추지 않고 결과집합을 끝까지 읽는다면 테이블 스캔에 비해 전체 수행 속도가 더 느려지는 결과를 초래하기 때문에 옵티마이저 모드 설정이 중요하다.

 

7.4 옵티마이저의 한계

 

DBA가 통계정보를 아무리 잘 수집하고 개발자가 SQL을 아무리 잘 작성해도 실수가 나오기 마련이다. 같은 SQL인데도 DBMS에 따라, 버전에 따라 옵티마이저가 다른 실행계획을 생성한다는 사실을 통해 그 선택이 항상 최선이 아님을 쉽게 짐작 할 수 있다.

 

옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 '필요한 만큼 충분히' 확보하는 것부터가 불가능한 일이다. 정보가 많으면 많을수록 좋지만, 그것을 수집하고 관리하는 데 어마어마한 시간과 비용이 들기 때문에 통계정보를 아무리 완벽하게 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점도 있다

 

OLTP 시스템에서 동작하는 SQL에는 대부분 바인드 변수를 사용하므로 이와 관련한 성능 이슈를 어떻게 해서든 해결해야 하는데, DBMS 벤더들이 그동안 노력을 많이 기울였음에도 불구하고 아직 완벽한 해결방안은 찾지 못했다.

 

기본적으로 비용기반으로 작동하지만, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 한다는 사실도 옵티마이저가 한계를 보이는 원인 중 하나다. 어느 쪽이 나은지 판단하기 애매할 때 사용하는 가정과 규칙들이 현실에 맞지 않을 수 있기 때문이다. 또한, 최적화에 허용되는 시간이 매우 짧은 것도 중요한 제약 중 하나다.

 

= SQL 최적화 기법이 과거와 비교하면 아주 많이 좋아졌고 발전하는 중이지만, 위와 같은 한계와 제약으로 인해 앞으로도 옵티마이저는 불완전할 수밖에 없다.

 

7.5 개발자의 역할

 

내비게이션 정보는 참고용일 뿐이지 운전은 운전자 본인의 판단과 선택에 따라 운전해야한다.

 

데이터베이스 세계에서도 불완전한 옵티마이저에 의존할 것이 아니라 개발자 스스로 옵티마이저가 되어 

옵티마이저에게 능력이 없어서 맡기는 것이 아닌 바빠서 맡긴다고 생각해야 한다.

( 내가 할 수 없는 것을 맡기는 것이 아닌 할 수 있는 것을 맡기는 것 )

 

SQL 성능을 높이기 위해 개발자가 할 일 목록

 

- 필요한 최소 블록만 읽도록 쿼리를 작성

 

 = SQL 작성자 스스로 결과집합을 논리적으로 잘 정의하고, 결과집합을 만들기 위해 DB 프로세스가 최소한의 일만 하도록 쿼리를 효율적으로 작성하는 것이무엇보다 중요.  데이터베이스 성능은 I/O 효율에 달려있기 때문에 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소 블록만 읽도록 해야 한다.

 

- 최적의 옵티마이징 팩터를 제공

 

 = 전략적인 인덱스 구성.

 = DBMS가 제공하는 다양한 기능 활용

 = 옵티마이저 모드 설정

 = 정확하고 안정적인 통계 정보

 

 == 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해 주는 것이 무엇보다 중요. 이것이 기본이 된 상태에서 전략적인 인덱스 구성이 필수적이어야 한다. 그리고 DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단을 제공해 주어야 한다.

 

- 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

 

= 옵티마이저가 최적의 실행계획을 수립하지 못할 때, 개발자기 힌트를 이용해 직접 데이터 엑세스 경로를 선택해 줄 수 있다. DB 애플리케이션 개발자라면 옵티마이저가 미처 생각하지 못한 최적의 액세스 경로를 찾아내고, 실행계획을 그 방식으로 유도할 수 있는 능력을 반드시 갖추어야 한다.