[Oracle]COST BASED OPTIMIZER 오라클 비용기반 옵티마이저 통계정보

[Oracle]COST BASED OPTIMIZER 

오라클 비용기반 옵티마이저

오라클 7버전 부터 COST BASED OPTIMIZER라는 것이 등장했습니다.

RULE BASED는 정해진 규칙으로만 실행계획을 작성했는데 COST BASED는

테이블,인덱스 통계정보를 갖고 분석을 통해 좋은쪽으로 실행계획을 작성해주는 옵티마이저입니다.

그렇다고 이게 최선의 방법을 제시해주느냐.. 그것은 아닙니다.

옵티마이저가 도와주지만 어쨋든 SQL문을 보고 최적을 찾는것은 사람 몫입니다.

ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;

이라 해주면 비용기반 옵티마이저 사용준비는 끝입니다.

DEFAULT는 COST BASED입니다.

DEPTNO와 EMPNO에 인덱스를 각각 만들어 줬습니다.

AUTOTRACE를 키고 위와같은 SQL문을 실행시키면 EMPNO로 INDEX RANGE SCAN이 된걸 확인하실 수 있습니다.

이는 COST BASED가 테이블과 인덱스 통계정보를 보고 결정한 것인데요. 

그렇다면 테이블 , 인덱스 통계정보는 어디있을까요

ANALYZE TABLE BIG_EMP COMPUTE STATISTICS라를 명령어를 주시면

EMP테이블을 분석해서 통계정보를 저장해둔다는 것을 뜻합니다.

테이블을 분석하면 테이블에 해당하는 인덱스는 자동으로 분석이됩니다.

위에 인덱스를 분석해준것은 인덱스도 따로 할 수 있다는 것을 보여드리는 것입니다.

통계는 9i때는 사용자가 직접해주는 것이 기본이었고, dbms_job + dbms_stats을 이용해서

정해진 날짜 시간에 자동으로 하도록 지정해줄 수 있었습니다.

10i부터는 자동이 기본이고, 원한다면 수동으로도 해줄 수 있습니다.

자동으로 통계정보가 저장된다고 하더라도 중요한 테이블은 직접 수동으로 해주는것이 좋습니다.

ANALYZE TABLE BIG_EMP DELETE STATISTICS;

테이블에 있는 분석정보를 지워보겠습니다.

통계정보를 지우자 DEPTNO로 만들어진 인덱스를 먼저 스캔한뒤 테이블 엑세스를 하는걸

볼 수 있습니다. 통계정보를 지우자 COST BASED OPTIMIZER가 제대로 말을 듣지않습니다.

이처럼 COST BASED OPTIMIZER는 통계정보를 기반으로 실행계획을 만듭니다.

때문에 통계정보가 잘못되있다면 치명적이겠죠.

위와같이 통계정보를 지우게되면 DEFAULT로 되어있는 통계정보를 통해 분석을 합니다.

USER_TABLE을 보시면 어떤 테이블이 통계분석이 언제됬는지 알 수 있습니다.

LAST_ANA에 날짜가 있는것은 마지막으로 그 날짜에 통계정보를 저장했다는 뜻이고

아무것도 없는것은 통계정보가 한번도 저장되지않은 것을 뜻합니다.

USER_TABLES에는 이러한 정보도 볼 수 있는데요

테이블이 몇개의 블락에 데이터가 저장되어있는지와

총 ROW수, ROW길이의 평균값을 보여주고있습니다. 이것말고도 다양한 정보를 제공하니

USER_TABLES 칼럼들을 살펴보세요

여기

USER_TAB_COLUMNS에도 테이블 정보를 볼 수 있는데요

칼럼에 들어있는 최소값 최대값이 암호화된것을 볼 수 있고, 

중복되지않고 유일한 값이 몇개가 있는지도 볼 수 있습니다.

더 자세한 정보는 여기를 참고해주세요

여기

USER_INDEXES에는 인덱스 정보를 볼 수 있습니다.

LEAF_BLOCKS에는 리프블락이 몇개가 있는지 보여주고있습니다.

I_BIG_EMP_DEPTNO는 리프블락 57개에 데이터를 담고있다는 뜻이죠

DISTINCT_KEYS는 키갯수를 나타내주는데 키갯수와 로우 갯수가 같은것은 UNIQUE INDEX라는것을 뜻하겠죠

CLUSTERING_FACTOR는 인덱스의 정렬순서와 테이블의 정렬순서가 얼마나 일치하는지를 보여주는 계수입니다.

인덱스 정렬순서와 테이블 정렬순서가 일치하지않고 랜덤하게 있다면 INDEX RANGE SCAN을 할때 더많은 

블락을 읽게 되겠죠~ 이 수치는 낮을 수록 데이터가 잘 밀집되어있다, 클러스터링팩터가 좋다고 할 수 있습니다.

성능상 중요한 부분이니 잘 확인해두세요~

CBO문제요소

1.데이터분포

2. 잘못된 통계

3. 조인순서 방법

4. 잘못된 인덱스선택

5. 너무많은 테이블조인

6. INIT.ORA파라미터 잘못설정.

cbo를 사용할때 제대로된 실행계획을 세우지못한다면 위의 문제점을 고려해보세요