[SQL학원,오라클학원☞탑크리에듀][오라클옵티마이저통계정보란?]Oracle Optimizer Statistics(Table/Column 통계정보)

[오라클옵티마이저통계정보란?]Oracle Optimizer Statistics(Table/Column 통계정보)

n  Optimizer Statistics(옵티마이저 통계)는 데이터베이스 모든 오브젝트에 대한 자료를 모아 기술한 통계인데 이는데이터 딕셔너리(Data Dictionary)에 저장되며 이 통계정보를 바탕으로 오라클 옵티마이저는 SQL문장 실행을 위한 효율적인 실행계획을 만들어 낸다.

n  테이블 또는 칼럼의 통계정보는 USER_TAB_STATISTICS, USER_TAB_COL_STATISTIC, USER_TABLES, USER_TAB_COLUMNS와 같은 딕셔너리 뷰에서 확인할 수 있다.

n  테이블 통계정보는 데이터 건수, 블록의 수, 레코드의 평균 길이, 마지막 통계정보 생성일자 등이며 이러한 통계정보를 이용하여 옵티마이저는 실행계획상의 다양한 연산(Operations)의 비용(Cost)을 결합되는 다른 통계정보를 바탕으로 산정하며 오퍼레이션마다 추출되는 로우(ROW, RECORD)의 수를 예측한다.

n  테이블 액세스의 비용(COST)은 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터를 이용하여 테이블의 데이터 블록의 수를 이용하여 계산을 하게된다.

SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT

NAME                            TYPE     VALUE

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

db_file_multiblock_read_count      integer     128

n  칼럼 통계정보는 칼럼의 DISTINCT VALUES의 수(NUMBER OF DISTINCT VALUES, NDV), 칼럼의 최소값, 최대값과 같은 정보를 가지고 있다.

n  옵티마이저는 테이블 통계정보(데이터 건수등)와 결합하여 SQL의 오퍼레이션에서  리턴되는 로우 수(레코드 건수)를 예측한다. 예를들어 테이블이 100개의 로우를 가지고 있고 DISTINCT VALUE가 10이라면 대략 한값당 10개의 로우를 가지고 있으므로 아래 쿼리에서 오라클은 값이 10개라면 추측을 할 것이다.

SQL> SET AUTOTRACE ON;

SQL> CREATE TABLE ROWS_TEST ( A NUMBER );

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

-- 10으로 나눈 나머지 0~9까지 10개의 값을 100건의 데이터로 채움. 값의 분포도는 대략 10%정도

SQL> INSERT INTO ROWS_TEST

SELECT MOD(ROWNUM, 10) FROM DUAL

CONNECT BY ROWNUM <= 100;

100 개의 행이 만들어졌습니다.

SQL> SELECT * FROM ROWS_TEST WHERE A = 5;

         A

----------

         5

5

        ……

10 개의 행이 선택되었습니다.

경   과: 00:00:00.00

Execution Plan

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |            |    10 |   130 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| ROWS_TEST |    10 |   130 |     3   (0)| 00:00:01 |

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

SQL> DROP TABLE ROWS_TEST;