트러블슈팅 오라클 퍼포먼스 2판 (2017년)
히스토그램
SQL> SELECT val2, count(*)
2 FROM t
3 GROUP BY val2
4 ORDER BY val2;
VAL2 COUNT(*)
---------- ----------
101 8
102 25
103 68
104 185
105 502
106 212
- 히스토그램 : 데이터 분포도가 균일하지 않을 경우, 쿼리 옵티마이저에게 제공되는 데이터 분포정보
- 12.1 이전 버전에서는 프리퀀시 히스토그램과 높이 균현 히스토그램을 사용하며, 이후 버전에서는 top 프리퀀시 히스토그램, 하이브리드 히스토그램을 추가로 도입했다.
프리퀀시 히스토그램
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS frequency
4 FROM user_tab_histograms
5 WHERE table_name = 'T'
6 AND column_name = 'VAL2'
7 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
101 8 8
102 33 25
103 101 68
104 286 185
105 788 502
106 1000 212
주요특징
- 버킷의 개수는 distinct 값의 개수와 동일하다.
- endpoint_value 컬럼은 값 자체를 수자로 표한 것으로 데이터 타입이 숫자가 아닐경우 실제 값을 숫자로 변환해야 한다.
- 히스토그램에 저장된 값은 첫 32바이트만 저장되므로, 값의 길이가 긴 경우 히스토그램의 효과가 크게 떨어진다.
- endpoint_number 컬럼은 누적 프리퀀시를 나타낸다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
101 8
102 25
103 68
104 185
105 502
106 212
- 히스토그램에서 제공하는 값의 범위를 벗어나는 경우
SQL> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val2 = 96;
SQL> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val2 = 98;
SQL> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val2 = 100;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val2 = 103.5;
SQL> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SQL> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SQL> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
096 1
098 2
100 3
103.5 4
107 3
109 2
111 1
- 조건값이 최소값과 최대값 사이에 있는 경우, 히스토그램에서 제공하는 최소 프리퀀시를 2로 나눈다.(위 예제에서 조건값이 103.5인 경우 : 최소값 8을 2로 나눈 4)
- 히스토그램에서 제공되는 값의 범위는 벗어나는 경우 최소값/최대값으로부터의 거리에 의존한다.
높이균형 히스토그램
- distinct 값의 개수가 허용된 버킷의 최대수보다 클 때 사용한다.(교재 292페이지 그림 8-3 참조)
SQL> SELECT count(*), max(val2) AS endpoint_value, endpoint_number
2 FROM (
3 SELECT val2, ntile(5) OVER (ORDER BY val2) AS endpoint_number
4 FROM t
5 )
6 GROUP BY endpoint_number
7 ORDER BY endpoint_number;
COUNT(*) ENDPOINT_VALUE ENDPOINT_NUMBER
---------- -------------- ---------------
200 104 1
200 105 2
200 105 3
200 106 4
200 106 5
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 'T',
5 estimate_percent => 100,
6 method_opt => 'for columns val2 size 5',
7 cascade => TRUE
8 );
9 END;
10 /
SQL> SELECT endpoint_value, endpoint_number
2 FROM user_tab_histograms
3 WHERE table_name = 'T'
4 AND column_name = 'VAL2'
5 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
101 0
104 1
105 3
106 5
높이 균형 히스토그램의 주요특징
- popular value : 히스토그램에 여러번 나오는 값
- 버킷의 개수가 distinct 값의 개수보다 적다.(엔드포인트 번호 0은 최소값을 나타낸다)
- endpoint_value 컬럼은 값 자체를 숫자로 표현한 것이다.
- endpoint_number 컬럼은 버킷의 번호를 알려준다.
- 값의 프리퀀시(도수분포 값)을 저장하지 않는다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
101 50
102 50
103 50
104 50
105 400
106 300
- 높이균형 히스토그램은 프리퀀시 히스토그램과 비교해서 정확도가 더 낮다.
- 높이균형 히스토그램에서 제공되는 값의 범위를 벗어나는 경우
- 값이 최소값과 최대값 사이에 있는 경우 다른 non-popular value와 동일한 프리퀀시를 사용(예 : 103.5는 50)
- 값이 히스토그램의 범위를 벗어나는 경우 최소값/최대값으로부터의 거리에 따라 달라진다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val2 = 96;
SQL> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val2 = 98;
SQL> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val2 = 100;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val2 = 103.5;
SQL> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SQL> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SQL> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
096 1
098 20
100 40
103.5 50
107 40
109 20
111 1
- 높이균형 히스토그램의 가장 큰 문제점은 데이터 분포도에 아주 사소한 변화일지라도 히스토그램 및 추정치가 바뀔 수 있다.
- 아래 예제에서 전체 로우의 2%에 해당하는 20건의 로우를 없데이트하니, 히스토그램의 값이 크게 바뀌었다.
- 이러한 문제로 12.1 버전부터는 TOP 프리퀀시 히스토그램과 하이브리드 히스토그램이 높이균형 히스토그램을 대체했다.
SQL> UPDATE t SET val2 = 105 WHERE val2 = 106 AND rownum <= 20;
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 'T',
5 estimate_percent => 100,
6 method_opt => 'for columns val2 size 5',
7 cascade => TRUE
8 );
9 END;
10 /
SQL> SELECT endpoint_value, endpoint_number
2 FROM user_tab_histograms
3 WHERE table_name = 'T'
4 AND column_name = 'VAL2'
5 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
101 0
104 1
105 4
106 5
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
101 80
102 80
103 80
104 80
105 600
106 80
TOP 프리퀀시 히스토그램
- 개념 : 전체 데이터에 대해 차지하는 비중이 적은 일부 값들은, 통계적으로 무의미하기 때문에 폐기해서 사용되는 버킷을 줄인다는 개념
- 공식 : p = 100 - 100/n (n : 버킷개수, p : TOP 프리퀀시 히스토그램에 필요한 최소한 로우의 비율)
SQL> SELECT val3, count(*) AS frequency, ratio_to_report(count(*)) OVER ()*100 AS percent
2 FROM t
3 GROUP BY val3
4 ORDER BY val3;
VAL3 FREQUENCY PERCENT
---------- --------- -------
101 8 0.8
102 25 2.5
103 68 6.8
104 185 18.5
105 502 50.2
106 212 21.2
- 위 예제에서는 버킷이 5개면 충분하다. 상위 3개의 값이 전체로우의 80% 이상을 차지하고 있기 때문이다.( 80 = 100 - 100/5)
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS frequency
4 FROM user_tab_histograms
5 WHERE table_name = 'T'
6 AND column_name = 'VAL3'
7 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
101 1 1
103 69 68
104 254 185
105 756 502
106 968 212
SQL>
- 상위 n개에 속하지 않는 값은 폐기되고 최소값과 최대값의 프리퀀시는 1로 설정된다.
- 위 예제에서는 102의 값이 폐기되고 101과 같이 1로 설정되었다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val3 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val3 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val3 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val3 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val3 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val3 = 106;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
101 32
102 32
103 68
104 185
105 502
106 212
- 101과 102의 값에 대한 쿼리 옵티마이저 추정값이 프리퀀시 히스토그램과 다르다는 사실을 알 수 있다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val3 = 96;
SQL> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val3 = 98;
SQL> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val3 = 100;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val3 = 103.5;
SQL> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val3 = 107;
SQL> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val3 = 109;
SQL> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val3 = 111;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
096 1
098 13
100 26
103.5 32
107 26
109 13
111 1
- 위 예제에서는 히스토그램의 범위를 벗어나는 값이 조건절에 올 경우, 프리퀀시 히스토그램과 같은 방식으로 처리하는 것을 알 수 있다.
하이브리드 히스토그램
- 프리퀀시와 높이균형 히스토그램의 일부 특징을 결합한 것이다.
- 높이균형 히스토그램과 달리 버킷마다 서로 다른 건수의 로우를 가질 수 있다.
- 각 버킷의 엔드포인트 값에 프리퀀시(엔드포인트 값의 건수)가 추가되었다.
SELECT val1, count(*), ratio_to_report(count(*)) OVER ()*100 AS percent
2 FROM t
3 GROUP BY val1
4 ORDER BY val1;
VAL1 COUNT(*) PERCENT
---------- ---------- -------
39 2 0.2
41 4 0.4
42 13 1.3
43 21 2.1
44 26 2.6
45 54 5.4
46 66 6.6
47 86 8.6
48 81 8.1
49 97 9.7
50 102 10.2
51 103 10.3
52 80 8.0
53 64 6.4
54 76 7.6
55 50 5.0
56 30 3.0
57 21 2.1
58 12 1.2
59 6 0.6
60 5 0.5
63 1 0.1
- 위 예제처럼 22개의 distinct값을 가진 val1 컬럼을 10개의 버킷으로 히스토그램을 생성하도록 명령한다면, 하이브리드 히스토그램이 만들어질 것이다.
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS count,
4 endpoint_repeat_count
5 FROM user_tab_histograms
6 WHERE table_name = 'T'
7 AND column_name = 'VAL1'
8 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER COUNT ENDPOINT_REPEAT_COUNT
-------------- --------------- ---------- ---------------------
39 2 2 2
44 66 64 26
45 120 54 54
46 186 66 66
47 272 86 86
48 353 81 81
49 450 97 97
50 552 102 102
51 655 103 103
52 735 80
- endpoint_number : 버킷과 관련된 로우수를 의미한다.
- endpoint_repeat_count : 엔드포인트 값의 프리퀀시를 제공한다.
SQL> EXPLAIN PLAN SET STATEMENT_ID '44' FOR SELECT * FROM t WHERE val1 = 44;
SQL> EXPLAIN PLAN SET STATEMENT_ID '50' FOR SELECT * FROM t WHERE val1 = 50;
SQL> EXPLAIN PLAN SET STATEMENT_ID '56' FOR SELECT * FROM t WHERE val1 = 56;
SQL>
SQL> COLUMN statement_id FORMAT A12
SQL>
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
44 26
50 102
56 30
- 하이브리드 히스토그램에서 제공하는 정보가 높이균형 히스토그램보다 더 정확하다.
- 그러므로 12.1 버전부터는 높이균형 히스토그램은 사용하지 말도록 하자.
히스토그램이 존재하지 않을 경우
SQL> SELECT endpoint_value, endpoint_number
2 FROM user_tab_histograms
3 WHERE table_name = 'T'
4 AND column_name = 'ID'
5 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
1 0
1000 1
SQL>
- 최소값과 최대값이 각각 0과 1로 조회된다.
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4374
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.