3. 옵티마이저의 한계 - P

조회 수 3262 추천 수 0 2011.04.17 17:37:11
휘휘 *.10.12.4

- 옵티마이저가 완벽한 실행계획을 낼수는 없다. 왜 !

정보를 저장할 공간과 시간제약(예로 OLTP 환경에서 파싱하는 시간이 짧음)

자동튜닝 옵티마이저는 시간제약없이 동적샘플링을 포함한 여러기법을 활용한다.

자동튜닝옵티마이저(Automatic Tuning Optimizer) :

오프라인옵티마이저로 온라인옵티마이저(runtime optimizer)의 상대적 개념이다.

통계를 분석하고, SQL Profiling하며, 액세스 경로 및 SQL 구조분석을 실시한다.

튜닝모드에서 작동하며 긴 시간이 주어진다(약 10분 정도)

SQL 단위로 프로파일을 수집하며 런타임 옵티마이저가 사용하도록 정보를 저장한다.

사용방법은 SQL Tuning Optimizer를 사용한다.

 

■ 옵티마이저의 현실적인 어려움

(1) 부족한 옵티마이징 팩터

- 적절한 옵티마이징 팩터를 제공해야한다.(예를 들면 인덱스 생성)

 

(2) 부정확한 통계

- 옵티마이저는 샘플링을 사용하여 통계를 내기 때문에 실제 데이터와 오차가 있다.

- 분포가 균등하지 않은 컬럼은 수집할 때마다 통계량이 바뀐다.

- 통계 수집 주기가 중요하다. 통계 수집후 데이터 변경이 많으면 다시해주어야 한다.

 

(3) 히스토그램의 한계

- 히스토그램의 버킷 크기가 254개 제한되어 있어 더 많은 Distinct Value를 가질 경우 발생 빈도가 낮은 값들은 반영이 어렵다.

- 버킷 개수를 늘리는 일은 시간과 공간의 제약이 있다.

 

(4) 바인드변수 사용시 균등분포 가정

- SQL 문의 바인드 변수는 균등 분포를 가정하기 때문에 최적화 어렵다.

   (OLTP 환경에서 라이브러리 캐시 부하를 피하기 위하여 바인드변수를 사용하는 것이 권장된다.)

- 11g에서는 적응적 커서공유기법을 도입해서 해결을 시도하고 있지만 완벽해결은 어렵다.

 

(5) 결합선택도 산정의 어려움

- 조건절 컬럼이 서로 상관관계에 있으면 데이터 분포와 카디널리티 산정이 어렵다.(예)

예 : select * from 사원 where 직급=‘부장’ and 연봉 >= 5000;

직급 = {부장, 과장, 대리, 사원}, 각각 25%

전체사원 = 1000명, 연봉 >= 5000 10%

=> 옵티마이저의 카디널리티 예측 = 1000*0.25*0.1

=> 상식적인 예측으로는 부장이 대부분 고액 연봉이므로 카디널리티가 훨씬 크다.

 

결과적으로 옵티미이저의 예측은 틀리고 조인을 여러번 거치면 실행계획이 틀릴 수있다.

- 11g 부터는 동적샘플링을 사용한다. 두 개이상의 커럼의 결합분포도는 동적샘플링 레벨을 4이상 설정하면 작동한다.

   또 사용자가 지정할 경우 결합선택도를 미리 수집해둔다.

 

(6) 비현실적인 가정

- CBO가 질의 비용을 평가할 때 사용하는 가정 중 비현실적인 것들이 있다.

  예) Single Block I/O와 Multiblock I/O 비용을 같다고 산정한다.

  예) 다른 세션이나 쿼리문에 의하여 버퍼캐시에 이미 저장된 데이터 블록은 없다고 가정한다. DW 환경에서 적합한 가정이다.

- 8i에서는 2개의 파라메터를 사용한다.

   optimizer_index_caching, optimizer_index_cost_adj(7절에서 설명 예정)

 

(7) 규칙에 의존하는 CBO

- 비용기반 옵티마이저도 일부는 규칙을 사용한다.

  예1) 원격테이블, External 테이블의 카디널리티, 행 길리, 블록 수, 인덱스 관련 통계는 고정된 상수 값을 사용한다.

  예2) 옵티마이저 모드가 first_rows 이면 order by 소트를 대치할 인덱스가 있으면 무조건 인덱스를 사용한다.

  예3) 뷰 또는 서브쿼리는 무조건 뷰머징을 시도한다. 10g에서는 비용 기반으로 개선되었다.

 

- 알파벳 순 인덱스 선택 규칙 - 2가지 인덱스의 예상 비용이 같을 경우 알파벳 순으로 선택한다.

SQL> create table t

2 as

3 select rownum a, rownum b from dual

4 connect by level <= 10000;

테이블이 생성되었습니다.

열이름

값 list

a

1..10000

b

1..10000

 

SQL> create index t_x01 on t(a);

SQL> create index t_x02 on t(b);

 

SQL> select * from t where a = 1 and b = 1;

Execution Plan

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T_X01 |     1 |       |     1   (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("B"=1)

2 - access("A"=1)

 

SQL> alter index t_x01 rename to t_x03;

인덱스가 변경되었습니다.

 

SQL> select * from t where a = 1 and b = 1;

Execution Plan

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T_X02 |     1 |       |     1   (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("A"=1)

2 - access("B"=1)

 

(실제 발생할 수 있는 사례)

- 테이블명_PK, 테이블명_N01, 테이블명_N02

- 고객이 평균적으로 두세달에 한번씩 주문한다면?

- 질의 문

where 고객번호=:cust_no and 주문일자=:ord_dt

- ORDER_PK와 ORDER_01 인덱스의 예상 비용은 둘다 카디널리티가 1 미만이기 떄문에 같다.

- 상식적으로 PK 인덱스를 사용해야하지만 옵티마이저는 N01 인덱스를 사용한다.

- 거의 매일 주문하는 경우 N01 인덱스는 주문일자 필터링에 많은 Random Access 일으킨다(정말?)

- 인덱스명을 운영중인 시스템에서 바꾸기 어렵기 때문에 인덱스명은 PK, X01, X02... 순이 좋다.

 

(8) 하드웨어 성능 특성

- 하드웨어의 사양(+ 애플리케이션 I/O 부하 등)에 따라 실행계획이 달라질 수 있다.

- 9i부터 시스템 통계를 수집하는 기능이 있어서 문제를 해결한다.

- 그러나 실무에서 기본값인 NoWorkLoad 시스템 통계를 사용한다.

 

동적 실시간 최적화(Dynamic Runtime Optimization)

- 정적인 통계정보와 옵티마이저 모델은 동적인 상황에서 틀릴 수 있으므로 최적화 필요하다.

- 런타임때 시스템 리소스를 배분한다. 예를 들어 시스템부하에 따라 병렬쿼리 프로세스 조절, PGA 메모리자동 조절(9i), SGA 서브메모리 자동조절(10g) 등이 있다.

- 동적 실시간 최적화는 단일 SQL 최적화보다는 여러 SQL의 전체 시스템 최적화에 초점을 맞춘다.








  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 박우창 (balto)
  • 최초작성일: 2011년 4월 17일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^