옵티마이저의 복잡한 비용계산 원리를 알 필요는 없다.
5,6,7절은 8절의 통계정보 수집전략의 필요성을 느끼기 위해 필요한 내용이다.
선택도(Selectivity)는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다.
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 인덱스 사용 여부, 조인 순서와 방법 등을 결정하므로 선택도는 최적의 실행계획을 수립하는데 있어 중요한 팩터이다.
선택도 -> 카디널리티 -> 비용 -> 액세스 방식, 조인 순서,. 조인 방법 등 결정
히스토그램이 있으면 그것으로 선택도를 산정한다.
히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구한다.
여기에서는 히스토그램이 없는 경우를 기준으로 설명한다.
히스토그램 없이 등치(=)조건에 대한 선택도를 구하는 공식은 다음과 같다.
1 1
선택도 = --------------------- = --------------
Distinct Value 개수 num_distinct
히스토그램 없이 부등호, between 같은 범위 검색 조건에 대한 선택도를 구하는 공식은 다음과 같다.
조건절에서 요청한 값 범위
선택도 = ---------------------------
전체 값 범위
분자, 분모에 사용된 두개의 '값 범위'는 컬럼 통계로서 수집된 high_value, low_value, num_distinct 등을 이용해 구한다.(dba_tab_col_statistics 참조)
| num_rows | num_distinct | low_value | high_value |
|---|---|---|---|
| 1,000,000 | 100 | 1 | 1,000 |
예를 들어, 컬럼 통계가 위 표와 같다면 부등호 조건에 대한 선택도는 아래와 같은 방식으로 구한다.
| 조건절 | 선택도 |
|---|---|
| no > 500 | (1,000-500) / (1,000-1) = 0.5 |
| no >= 500 | (1,000-500) / (1,000-1) + 1/100 = 0.5 |
| no between 500 and 1000 | (1,000-500) / (1,000-1) + 1/100 + 1/100 = 0.5 |
1) no > 500에 대한 계산식
high_value - 비교값 1,000 - 500
선택도 =
2) no < 500에 대한 계산식
비교값 - low_value 500 - 1
선택도 =
컬럼 히스토그램이 없다면 옵티마이저는 '조건절에서 요청한 값 범위'에 속한 값들이 '전체 값 범위'에 고르게 분포돼 있을을 가정하고 선택도를 구한다.
두번째는 1)의 선택도 + 등치조건 선택도(1 / num_distinct)를 더한 값이다.
키다널리티(Cardinality)는 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말하며, 아래와 같이 총 로우 수에 선택도를 곱해서 구한다.
카디널리티 = 총 로우 수 X 선택도
컬럼 히스토드램이 없은 경우 등치 조건(=)에 대한 선택도
카디널리티 = 총 로우 수 X 선택도 = num_rows X (1 / num_distinct)
num_rows는 테이블 통계에서, num_distinct는 컬럼 통계에서 확인
선택도 및 카디널리티 계산식 테스트
SQL> CREATE TABLE t_emp AS
2 SELECT b.no ,
3 a.*
4 FROM (
5 SELECT *
6 FROM scott.emp
7 WHERE ROWNUM <= 10
8 ) a ,
9 (
10 SELECT ROWNUM no
11 FROM dual
12 CONNECT BY LEVEL <= 100
13 ) b ;
테이블이 생성되었습니다.
SQL> -- 통계정보 수집(size를 1로 설정했으므로 히스토그램은 생성하지 않음)
SQL> begin
2 dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 1');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT job ,
2 COUNT( * )
3 FROM t_emp
4 GROUP BY job
5* ORDER BY job
SQL> /
JOB COUNT(*)
--------- ----------
ANALYST 100
CLERK 100
MANAGER 300
PRESIDENT 100
SALESMAN 400
히스토그램이 없는 경우 옵티마이저가 CLERK, SALESMAN 두 값의 카디널리티를 어떻게 추정하는지 확인
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = 'CLERK' ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 8200 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 200 | 8200 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"='CLERK')
13 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = 'SALESMAN' ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 8200 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 200 | 8200 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"='SALESMAN')
13 개의 행이 선택되었습니다.
히스토그램이 없는 경우 동일한 선택도를 가진다는 가정하에 두 값 모두 200으로 추정하였다.
SQL> SELECT t.num_rows ,
2 c.num_nulls ,
3 c.num_distinct ,
4 1/c.num_distinct AS selectivity ,
5 num_rows/c.num_distinct AS cardinality
6 FROM user_tables t ,
7 user_tab_columns c
8 WHERE t.table_name = 'T_EMP'
9 AND c.table_name = t.table_name
10 AND c.column_name = 'JOB' ;
NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
---------- ---------- ------------ ----------- -----------
1000 0 5 .2 200
히스토그램이 있는 경우를 확인해 보자.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 5'); -- 도수분포 히스토그램
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = 'CLERK' ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4100 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 100 | 4100 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"='CLERK')
13 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = 'SALESMAN' ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 16400 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 400 | 16400 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"='SALESMAN')
13 개의 행이 선택되었습니다.
각각 100과 400으로 예측하였고, 실제값과 정확히 일치 한다.
히스토그램이 있고 바인드 변수를 사용한 경우를 보자
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = :job ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 8200 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 200 | 8200 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"=:JOB)
13 개의 행이 선택되었습니다.
(3) NULL 값을 포함할 때
조건절 컬럼이 Null값을 포함할 때는 카디널리티를 어떻게 구할지를 테스트해 보자.
no <= 50인 500개 레코드의 job을 NULL로 치환한다.
SQL> UPDATE t_emp
2 SET job = NULL
3 WHERE no <= 50 ;
500 행이 갱신되었습니다.
SQL> COMMIT ;
커밋이 완료되었습니다.
통계정보를 다시 생성하고 job 컬럼의 통계치를 확인해 보면, 아래와 같이 num_nulls가 0에서 500으로 바뀌었지만 num_distinct를 그대로 5이다.
오라클에서 NULL은 값의 종류로 간주하지 않음을 알 수 있고, 따라서 NULL 값이 아닌 로우 비중을 곱해 주기만 하면 된다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 1'); -- 히스토그램 제거
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT num_nulls ,
2 num_distinct
3 FROM user_tab_columns
4 WHERE table_name = 'T_EMP'
5 AND column_name = 'JOB' ;
NUM_NULLS NUM_DISTINCT
---------- ------------
500 5
Null 값을 고려해 조정된 선택도 공식은 다음과 같다.
1 Null 값을 제외한 로우 수
선택도 = --------------------- X --------------------------
Distinct Value 개수 총 로우 수
1 (num_rows - num_nulls)
= -------------- X ------------------------
num_distinct num_rows
1 - (num_nulls / num_rows)
= ----------------------------
num_distinct
t_emp 테이블과 job 컬럼에 대해 수집된 통계 정보를 공식에 대입해 보면 아래와 같다.(등치인 경우의 선택도와 카디널리티)
1 - (500 / 1000)
선택도 = ------------------ = 0.1
5
카디널리티 = 1000 X 0.1 = 100
옵티마이저가 구한 카디널리타와 일치함을 아래와 같이 확인할 수 있다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = :job ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3700 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 100 | 3700 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"=:JOB)
13 개의 행이 선택되었습니다.
조건줄이 두 개 이상일 경우 카디널리티를 구하는 공식은 각 컬럼의 선택도와 전체 로우수를 곱이다.
SQL> SELECT c.column_name ,
2 t.num_rows ,
3 c.num_nulls ,
4 c.num_distinct ,
5 ( 1 - c.num_nulls /t.num_rows ) /c.num_distinct AS selectivity ,
6 num_rows/c.num_distinct AS cardinality
7 FROM user_tables t ,
8 user_tab_columns c
9 WHERE t.table_name = 'T_EMP'
10 AND c.table_name = t.table_name
11 AND c.column_name IN ( 'DEPTNO' , 'JOB' ) ;
COLUMN_NAME NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
------------------------------ ---------- ---------- ------------ ----------- -----------
JOB 1000 500 5 .1 200
DEPTNO 1000 0 3 .333333333 333.333333
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t_emp
5 WHERE job = :job
6 AND deptno = :deptno ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 1221 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 33 | 1221 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("JOB"=:JOB AND "DEPTNO"=TO_NUMBER(:DEPTNO))
13 개의 행이 선택되었습니다.
범위검색 조건일 때의 선택도는 아래와 같다.
조건절에서 요청한 값 범위
선택도 = ---------------------------
전체 값 범위
SQL> CREATE TABLE t AS
2 SELECT ROWNUM no1 ,
3 CASE
4 WHEN ROWNUM <= 1000
5 OR ROWNUM > 9000
6 THEN ROWNUM
7 ELSE 5000
8 END no2
9 FROM dual
10 CONNECT BY LEVEL <= 10000 ;
테이블이 생성되었습니다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't', method_opt => 'for all columns size 1');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
no1, no2 모두 low_value와 high_value는 각각 1과 10,000이다.
no1은 고르게 분포되어 있지만 no2는 그렇지 않다.
하지만 히스토 그램이 없기 때문에 아래 두 쿼리의 키디널리티는 항상 똑같이 계산된다.
SELECT *
FROM
WHERE no1 >( 상수 ) ;
SELECT *
FROM
WHERE no2 >( 상수 ) ;
아래와 같이 no2에 없는 값을 조회하는 경우도 1,010으로 계산되는 것을 볼 수 있다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t
5 WHERE no2 BETWEEN 3000
6 AND 4000 ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1010 | 7070 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1010 | 7070 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("NO2"<=4000 AND "NO2">=3000)
13 개의 행이 선택되었습니다.
아래와 같이 히스토그램을 생성하고 나면 거의 근사치(Card=5)로 카디널리티를 계산해 내는 것을 확인할 수 있다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't', method_opt => 'for all columns size 254');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t
5 WHERE no2 BETWEEN 3000
6 AND 4000 ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 35 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 5 | 35 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("NO2"<=4000 AND "NO2">=3000)
13 개의 행이 선택되었습니다.
옵티마이저가 계산한 카디널리티가 부정확할 때 힌트를 이용하여 사용자가 직접 카디널리티의 정보를 제공할 수 있다.
아래 쿼리는 dept의 카디널리티가 4이고, emp의 카디널리티가 14이므로 Build Input으로 dept 테이블을 선택된다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT /*+ use_hash(d e) */
4 *
5 FROM dept d ,
6 emp e
7 WHERE d.deptno = e.deptno;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 728 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 448 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 개의 행이 선택되었습니다.
하지만 dept의 실제 카디널리타가 16이고 우리는 emp 테이블을 Build Input으로 삼고자 한다면 아래와 같이 cardinality나 opt_estimate 힌트를 이용할 수 있다.
opt_estimate 힌트는 옵티마이저가 예상한 카디너리티에 4를 곱하는 뜻이다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT /*+ use_hash(d e) cardinality(d 16) */
4 *
5 FROM dept d ,
6 emp e
7 WHERE d.deptno = e.deptno;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 2912 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 56 | 2912 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 16 | 320 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT /*+ use_hash(d e) opt_estimate(table, d, scale_rows=4) */
4 *
5 FROM dept d ,
6 emp e
7 WHERE d.deptno = e.deptno ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 2912 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 56 | 2912 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 16 | 320 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 개의 행이 선택되었습니다.