히스토그램이 있다면 오라클인 더 정확한 카디널리티를 구할 수 있다.
특히, 분포가 균일하지 않은 컬럼으로 조회할 경우 효과를 발휘한다.
오라클이 사용하는 히스토그램으로는 아래 두가지 유형이 있다.
히스토그램을 생성하려면 컬럼 통계 수집 시 버킷 개수를 2이상으로 지정하면 된다.
히스토그램 정보는 dba_histograms 또는 dba_tab_histograms 뷰를 통해 확인할 수 있다.
특히 10g에서는 dba_tab_columns 뷰에 histogram 컬림이 추가되어 히스토그램 유형을 쉽게 파악할 수 있게 되었다.
두 히스토그램 유형을 설명할 예제로 편항된 데이터를 가진 member 테이블을 생성하였다.
CREATE SEQUENCE seq ;
CREATE TABLE member( memb_id NUMBER , age NUMBER( 2 ) ) ;
EXEC dbms_random.seed( 0 ) ;
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(1,19) from dual connect by level <= 50;
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(20,29) from dual connect by level <= 270;
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(30,39) from dual connect by level <= 330;
insert into member(memb_id, age)
select seq.nextval, 40 from dual connect by level <= 1000; --> popular value
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(41,49) from dual connect by level <= 200;
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(50,59) from dual connect by level <= 100;
insert into member(memb_id, age)
select seq.nextval, dbms_random.value(60,99) from dual connect by level <= 50;
| 연령대 | 인원수 |
|---|---|
| 20대 미만 | 50 |
| 20대 | 270 |
| 30대 | 330 |
| 40대 | 1200 |
| 50대 | 100 |
| 60대 이상 | 50 |
'value-based 히스토그램'으로도 불리며 빈도수(frequency number)를 저장하는 히스토그램을 말한다.
도수분포 히스토그램은 컬럼마다 하나의 버킷을 할당(값의 수 = 버킷 개수)한다. 사용자가 요청한 버킷 개수가 컬럼이 가진 갑을 수보다 많거나 같을 때 사용되며, 최도 254개의 버킷암 허용하므로 값을 수가 254개가 넘는 컬럼에는 이 히스토그램을 사용할 수 없다.
SQL> SELECT COUNT( * ) ,
2 COUNT( DISTINCT age )
3 FROM member ;
COUNT(*) COUNT(DISTINCTAGE)
---------- ------------------
2000 89
age 값이 89개이므로 도수분포 히스토그램을 생성할 수 있다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 'member', method_opt => 'for all columns size 100') ;
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> SELECT num_distinct ,
2 num_buckets ,
3 histogram
4 FROM user_tab_col_statistics
5 WHERE table_name = 'MEMBER'
6 AND column_name = 'AGE' ;
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
89 89 FREQUENCY
100개의 버킷을 요청했는데 89개의 버킷만 생성된 이유는 종류가 89개이기 때문이다.
254개를 요청하더라도 값으 수만큼만 버킷을 할당하므로 정확한 히스토그램을 위해서라면 항상 254개를 요청하는 것이 좋다.
히스토그램 정보를 조회할 수 있는 dba/all/user_histograms 뷰에는 아래 두 컬럼이 있다.
endpoint_number가 누적수량을 의미하므로 바로 앞 endpoint_value까지의 누적수량을 차감함으로써 해당 버킷의 값 빈도수를 알 수 있다.
아래 쿼리는 endpoint_value 값별로 빈도수를 구하고서 member 테이블에서 구한 연령별 실제 인원수와 비교한 쿼리인데, 값이 서로 일치하고 있다.
SQL> SELECT t.age AS "연령" ,
2 t.cnt AS "인원수(명)" ,
3 NVL2( h.prev , h.running_total - h.prev , h.running_total ) AS frequency ,
4 h.running_total
5 FROM (
6 SELECT age ,
7 COUNT( * ) AS cnt
8 FROM member
9 GROUP BY age
10 ) t ,
11 (
12 SELECT endpoint_value AS age ,
13 endpoint_number AS running_total ,
14 LAG( endpoint_number ) over (
15 ORDER BY endpoint_value
16 ) AS prev
17 FROM user_histograms
18 WHERE table_name = 'MEMBER'
19 AND column_name = 'AGE'
20 ) h
21 WHERE h.age = t.age ;
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
1 2 2 2
2 3 3 5
3 8 8 13
4 2 2 15
5 3 3 18
6 2 2 20
7 3 3 23
8 5 5 28
9 3 3 31
10 1 1 32
11 1 1 33
12 1 1 34
13 1 1 35
14 2 2 37
15 3 3 40
16 4 4 44
17 1 1 45
18 4 4 49
19 1 1 50
20 17 17 67
21 39 39 106
22 32 32 138
23 26 26 164
24 23 23 187
25 14 14 201
26 24 24 225
27 30 30 255
28 46 46 301
29 19 19 320
30 28 28 348
31 47 47 395
32 30 30 425
33 34 34 459
34 21 21 480
35 44 44 524
36 30 30 554
37 45 45 599
38 39 39 638
39 12 12 650
40 1000 1000 1650
41 19 19 1669
42 27 27 1696
43 15 15 1711
44 36 36 1747
45 38 38 1785
46 22 22 1807
47 22 22 1829
48 17 17 1846
49 4 4 1850
50 5 5 1855
51 15 15 1870
52 11 11 1881
53 8 8 1889
54 8 8 1897
55 14 14 1911
56 9 9 1920
57 11 11 1931
58 12 12 1943
59 7 7 1950
61 2 2 1952
62 1 1 1953
63 1 1 1954
64 2 2 1956
67 3 3 1959
68 4 4 1963
69 1 1 1964
70 1 1 1965
71 1 1 1966
73 1 1 1967
74 1 1 1968
75 2 2 1970
77 1 1 1971
78 1 1 1972
79 1 1 1973
80 3 3 1976
81 1 1 1977
82 2 2 1979
84 1 1 1980
85 3 3 1983
87 2 2 1985
88 1 1 1986
90 1 1 1987
91 2 2 1989
92 1 1 1990
94 2 2 1992
96 2 2 1994
97 2 2 1996
98 3 3 1999
99 1 1 2000
89 개의 행이 선택되었습니다.
'equi-depth 히스토그램'으로 불린다. 컬럼이 가진 갑의 수보다 적은 버킷을 요청할 때 만들어 진다.
때문에 하나의 버킷이 여러 개의 값을 담당한다.
빈도 수가 많은 값(popular value)을 포함할 때는 두 개 이상의 버킷이 할당된다.
높이 균형 히스토그램을 만들기 위해 89개보다 적은 20개의 버킷을 할당한다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 'member', method_opt => 'for all columns size 20');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT num_distinct ,
2 num_buckets ,
3 histogram
4 FROM user_tab_col_statistics
5 WHERE table_name = 'MEMBER'
6 AND column_name = 'AGE' ;
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
89 20 HEIGHT BALANCED
user_tabl_col_statistics에는 사용자가 요청한 대로 20개의 버킷이 생성된 것으로 조회되지만 실제 히스토그램을 보면 아래와 같이 버킷이 11개만 할당되어 있다.
이는 오라클이 popluar value를 압축해서 저아하기 때문이다.
SQL> SELECT endpoint_number ,
2 endpoint_value
3 FROM user_histograms
4 WHERE table_name = 'MEMBER'
5 AND column_name = 'AGE'
6 ORDER BY 1 ;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1 <= 첫번째 레코드는 버킷이 아니라 '최소 값'을 표현하는 용도
1 21
2 25
3 28
4 32
5 35
6 38
16 40 <= popular value
17 43
18 46
19 55
20 99
12 개의 행이 선택되었습니다.
높이균형 히스토그램일 때 endpoint_number와 endpoint_value의 의미는 아래와 같다.
endpoint_number는 버킷 번호이므로 1씩 증가하지만 위의 쿼리 결과에서 39~40 연령대 구간의 버킷 번호는 6에서 16으로 10칸을 건너 뛰었다.
이처럼 2칸 이상을 건너 뛰는 버킷은 popular value를 포함함을 의미하며, 여기서는 10칸이나 건너 뛰었으므로 연령대가 10개 버킷만큼의 비중을 차지함을 압축해서 표현하고 있다.
압축하더라도 연령대별 비중(ratio)과 인원수를 구하는 데는 지장이 없다.(아래 쿼리를 참조)
SQL> SELECT '~' || age "연령대" ,
2 endpoint_number ,
3 diff ,
4 ROUND( 100 * diff /sum( diff ) over( ) ) AS "RATIO(%)" ,
5 ROUND( t.num_rows * diff /sum( diff ) over( ) ) AS "인원수(명)"
6 FROM (
7 SELECT table_name ,
8 endpoint_value AS age ,
9 endpoint_number ,
10 endpoint_number - LAG( endpoint_number ) over (
11 ORDER BY endpoint_value
12 ) AS diff ,
13 LAG( endpoint_number ) over (
14 ORDER BY endpoint_value
15 ) AS prev
16 FROM user_histograms
17 WHERE table_name = 'MEMBER'
18 AND column_name = 'AGE'
19 ) h ,
20 user_tables t
21 WHERE h.endpoint_number > 0
22 AND t.table_name = h.table_name
23 ORDER BY 1 ;
연령대 ENDPOINT_NUMBER DIFF RATIO(%) 인원수(명)
--------------- --------------- ---------- ---------- ----------
~21 1 1 5 100
~25 2 1 5 100
~28 3 1 5 100
~32 4 1 5 100
~35 5 1 5 100
~38 6 1 5 100
~40 16 10 50 1000
~43 17 1 5 100
~46 18 1 5 100
~55 19 1 5 100
~99 20 1 5 100
11 개의 행이 선택되었습니다.
연령별 인원수(카디널리티)를 실제로도 위와 같이 구한다면 39세와 40세가 각각 500명으로 계산될 것이다.
하지만 오라클은 popular value(40)에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고 나머지(non-popular value)는 미리 구해 놓은 density 값을 이요한다.
popular value에 대한 선택도/카디널리티 계산
조건절 값이 두 개 이상 버킷을 가진 popular value이면 아래 공식을 따른다.
선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)
popular value 40은 총 20개의 버킷 중 10개를 사용하므로 선택도는 1/2(=0.5)이다
카디널리티 = 총 로우 수 X 선택도
= (총 로우 수) X (조건절 값의 버킷 개수) / (총 버킷 개수)
= 2,000 X 10 / 20 = 1,000
총 로우수 2,000을 곱하면 카디널리티가 1,000으로 아래 실행계획의 Rows와 정확히 일치한다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM member
5 WHERE age = 40 ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3441279308
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MEMBER | 1000 | 6000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("AGE"=40)
13 개의 행이 선택되었습니다.
non-popular value에 대한 선택도/카디널리티 계산
non-popular value일 때는 미리 구해 놓은 density 값을 이용한다.
카디널리티 = 총 로우 수 X 선택도 = 총 로우 수 X density
density 값은 아래와 같다.
SQL> begin
2 dbms_stats.set_column_stats(ownname => 'SCOTT', tabname => 'MEMBER', colname => 'AGE', density => 0.2567285 );
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT 1 /num_distinct ,
2 density ,
3 ROUND( density * 2000 ) AS cardinality
4 FROM user_tab_col_statistics
5 WHERE table_name = 'MEMBER'
6 AND column_name = 'AGE' ;
1/NUM_DISTINCT DENSITY CARDINALITY
-------------- ---------- -----------
.011235955 .2567285 513
density가 0.2567285이므로 총 로우수 2,000을 곱한 카디널리티는 513이다.
실제 non-popular value를 조회하는 조건절에 대한 실행계획을 보면, 아래와 같이 Row가513임을 확인할 수 있다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM member
5 WHERE age = 39 ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3441279308
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 513 | 3078 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MEMBER | 513 | 3078 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("AGE"=39)
13 개의 행이 선택되었습니다.
바인드 변수를 사용할 때 옵티마이저는 평균 분포를 가정한 실해예획을 생성한다.
때문에 컬럼 분포가 균일할 때는 문제될 것니 없지만 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 최적이 아닌 실행계획이 수립될 수 있어 문제가 있다.
'=' 조건일 때
바인드 변수로 '='로 검색할 때 선태고는 아래와 같이 구해진다.
범위검색 조건일 때
범위검색 조건이 사용되면 옵티마이저는 고정된 규칙으로 선택도를 추정하는데, 운이 좋아 그 값이 맞을 수 있지만 틀릴 가능성이 높다.
구체적으로 보면, 아래 1~4번은 선택도를 5%로 계산하고, 5~8번까지는 0.25로 계산한다.
| 번호 | 조건절 | 번호 | 조건절 |
|---|---|---|---|
| 1 | 번호 > :no | 5 | 번호 between :no1 and :no2 |
| 2 | 번호 < :no | 6 | 번호 > :no1 and 번호 <= :no2 |
| 3 | 번호 >= :no | 7 | 번호 >= :no1 and 번호 < :no2 |
| 4 | 번호 <= :no | 8 | 번호 > :no1 and 번호 < :no2 |
따라서 테이블에 1,000개 로우가 있을 때 옵티마이저는 1~4번 조건절에 대해서는 50개 로우가 출력될 것으로 예상하고, 5~8번 조건절에 대해서는 3개 로우가 출력될 것으로 예상한다.
아래 결과로 이런 사실을 확인할 수 있다.
SQL> CREATE TABLE t1 AS
2 SELECT ROWNUM as no
3 FROM dual
4 CONNECT BY LEVEL <= 1000 ;
테이블이 생성되었습니다.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't', method_opt => 'for all columns size 254');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t1
5 WHERE no <= :no ;
해석되었습니다.
SQL> SELECT *
2 FROM TABLE( dbms_xplan.display( NULL , NULL , 'basic rows' ) ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 50 |
| 1 | TABLE ACCESS FULL| T1 | 50 |
------------------------------------------
8 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t1
5 WHERE no BETWEEN :no1
6 AND :no2 ;
해석되었습니다.
SQL> SELECT *
2 FROM TABLE( dbms_xplan.display( NULL , NULL , 'basic rows' ) ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3332582666
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 3 |
| 1 | FILTER | | |
| 2 | TABLE ACCESS FULL| T1 | 3 |
-------------------------------------------
9 개의 행이 선택되었습니다.
바인드 변수를 사용했을 때 옵티마이저는 각각 50개와 3개의 카디널리티(rows)를 예상하였다.
사죵자가 실제 입력한 값과 전혀 맞지 않은 결과일 수 있지만 옵티마이저로서는 어쩔 수 없는 선택을 한 것이다.
반면 아래와 같이 상수를 사용할 때는 거의 정확한 카디널리티를 계산해 낸다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t1
5 WHERE no <= 100 ;
해석되었습니다.
SQL> SELECT *
2 FROM TABLE( dbms_xplan.display( NULL , NULL , 'basic rows' ) ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 100 |
| 1 | TABLE ACCESS FULL| T1 | 100 |
------------------------------------------
8 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t1
5 WHERE no BETWEEN 500
6 AND 600 ;
해석되었습니다.
SQL> SELECT *
2 FROM TABLE( dbms_xplan.display( NULL , NULL , 'basic rows' ) ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 101 |
| 1 | TABLE ACCESS FULL| T1 | 101 |
------------------------------------------
8 개의 행이 선택되었습니다.
좋은 실행계획을 위해서라면 DW, OLAP, 배치 프로그램(Loop 내에서 수행되는 쿼리 제외)에서 수행되는 쿼리는 바인드 변수보다는 상수를 사용하는 것이 좋고, 날짜 컬럼처럼 부등호, between 같은 범위 조건으로 자주 검색되는 컬럼일 때 특히 그렇다.
OLTP성 쿼리이더라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 유용할 수 있다.
동적 샘플링(Dynamic Sampling)
SQL> CREATE TABLE 사원 AS
2 SELECT empno AS 사원번호 ,
3 ename AS 사원명 ,
4 sal AS 급여 ,
5 sal*0.1 AS 상여
6 FROM emp ;
테이블이 생성되었습니다.
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user, '사원', method_opt => 'for columns 급여 size 254 상여 size 254');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace on explain ;
SQL>
SQL> SELECT *
2 FROM 사원
3 WHERE 급여 >= 2000
4 AND 상여 >= 200 ;
사원번호 사원명 급여 상여
---------- ---------- ---------- ----------
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300
6 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1520453074
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| 사원 | 2 | 34 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("급여">=2000 AND "상여">=200)
{code:sql}
SQL> SELECT /*+ dynamic_sampling(4) */
2 *
3 FROM 사원
4 WHERE 급여 >= 2000
5 AND 상여 >= 200 ;
사원번호 사원명 급여 상여
---------- ---------- ---------- ----------
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300
6 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1520453074
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 102 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| 사원 | 6 | 102 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("급여">=2000 AND "상여">=200)
Note
-----
- dynamic sampling used for this statement
다중 컬럼 통계(Multi-column Statistics)
11g에서는 '확장형 통계(extended statistics)'라고 불리는 기능을 통해 다중 컬럼에 대한 히스토그램을 생성할 수 있다.
컬럼 그룹을 생성하는 방법은 아래와 같다.
<방법1>
var ret varchar2(30);
exec :ret := dbms_stats.create_extended_stats(user, '사원', '(급여, 상여)');
print ret;
<방법2>
begin
dbms_stats.gather_table_stats(user, '사원', method_opt > 'for all columns size skewonly for columns (급여, 상여) size 254');
end;
/
컬럼 그룹을 삭제하는 방법은 아래와 같다.
exec dbms_stats.drop_extended_stats(user, '사원', '(급여, 상여)');
확장형 통계를 확인하는 방법은 아래와 같다
SELECT extension_name ,
extension
FROM dba_stat_extensions
WHERE owner = uesr
AND table_name = '사원' ;
아래와 같은 방법으로도 컬럼 그룹명을 확인할 수 있고 위의 extension_name과 일치한다.
SELECT dbms_stats.show_extended_stats_name( USER , '사원' , '(급여, 상여)' ) AS col_group_name
FROM dual ;
COL_GROUP_NAME
--------------------------------------------------------------------------------------
SYS_STU5RQ9BZ2FE#O59QYNL$VC960
위의 컬럽 그룹명을 가지고 다중 컬럼의 히스토그램을 조회해 볼 수 있다.
SELECT *
FROM dba_histograms
WHERE owner = USER
AND table_name = '사원'
AND column_name = 'SYS_STU5RQ9BZ2FE#O59QYNL$VC960' ;