꿈꾸는 개발자, DBA 커뮤니티 구루비

8. 오브젝트 통계

8.1 dbms_stats 패키지

ANALYZE : 9i 이전, 오브젝트 통계를 수집하기 위한 명령어(하위호환성을 위해 남겨둔 기능, dbms_stats 사용권장)
dbms_stats 패키지의 주요기능 :
- 오브젝트의 통계수집 및 백업
- 딕셔너리에 저장된 통계의 잠금 및 해제
- 오브젝트 통계를 다른 파티션(또는 서브파티션)으로 복사
- 과거 통계정보를 딕셔너리로 복원
- 백업된 통계의 삭제
- 딕셔너리에서 백업 테이블로 export
- 백업테이블에서 딕셔너리로 import
- 통계정보를 얻거나 설정

기능	데이터베이스	딕셔너리	스키마	테이블	인덱스
Gather/Delete	O	O	O	O	O
Lock/Unlock			O	O
Copy	O	O	O	O
Resotre	O	O	O	O	O
Export / Import	O	O	O	O	O
Get/Set				O	O

8.2 이용할 수 있는 오브젝트 통계에는 어떠한 것들이 있는가?

테이블 통계, 컬럼 통계, 인덱스 통계의 세가지 유형이 있다.
각 유형에 대해 테이블/인덱스 레벨, 파티션 레벨, 서브파티션 레벨이라는 하위 유형이 존재한다.
테이블의 오브젝트 통계를 보여주는 데이터 딕셔너리 뷰

오브젝트	테이블/인덱스 레벨	파티션 레벨	서브파티션 레벨
테이블	user_tab_statistics	user_tab_statistics	user_tab_statistics
컬럼	uesr_tab_col_statistics	user_part_col_statistics, user_part_histograms	user_subpart_col_statistics, user_subpart_histograms
인덱스	user_ind_statistics	user_ind__statistics	user_ind_statistics

8.2.1 테이블통계


CREATE TABLE t
AS
SELECT rownum AS id,
       50+round(dbms_random.normal*4) AS val1,
       100+round(ln(rownum/3.25+2)) AS val2,
       100+round(ln(rownum/3.25+2)) AS val3,
       dbms_random.string('p',250) AS pad
FROM dual
CONNECT BY level <= 1000
ORDER BY dbms_random.value;

UPDATE t SET val1 = NULL WHERE val1 < 0;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

CREATE INDEX t_val1_i ON t (val1);

CREATE INDEX t_val2_i ON t (val2);

BEGIN
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'T',
                                estimate_percent => dbms_stats.auto_sample_size,
                                method_opt       => 'for columns size skewonly id, val1 size 15, val2, val3 size 5, pad',
                                cascade          => TRUE);
END;
/


SYS@2017-11-19 21:21:02> SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
  2  FROM user_tab_statistics
  3  WHERE table_name = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1000         44            0          0          0         266

num_rows : 테이블에 저장된 로우의 개수
blocks : 하이 워터마크 아래에 있는 블록수(사용된 블록)
empty_blocks : 하이워터마크 위에 있는 블록의 개수(미사용 블록)
avg_space : 데이터 블록에 있는 평균 여유공간(바이트)
chain_cnt : 다른 블록으로 체이닝되거나 마이그레이션된 로우의 합계
avg_row_len : 로우의 평균 크기(바이트)

8.2.2 칼럼 통계


SYS@2017-11-19 21:28:18> SELECT column_name AS "NAME",
  2         num_distinct AS "#DST",
  3         low_value,
  4         high_value,
  5         density AS "DENS",
  6         num_nulls AS "#NULL",
  7         avg_col_len AS "AVGLEN",
  8         histogram,
  9         num_buckets AS "#BKT"
 10  FROM user_tab_col_statistics
 11  WHERE table_name = 'T';

NAME  #DST LOW_VALUE           HIGH_VALUE             DENS #NULL AVGLEN HISTOGRAM        #BKT
---- ----- ------------------- ------------------- ------- ----- ------ --------------- -----
ID    1000 C102                C20B                 .00100     0      4 NONE                1
VAL1    22 C128                C140                 .03884     0      3 HYBRID             15
VAL2     6 C20202              C20207               .00050     0      4 FREQUENCY           6
VAL3     6 C20202              C20207               .00050     0      4 TOP-FREQUENCY       5
PAD   1000 202623436F294373342 7E79514A202D4946493  .00100     0    251 HYBRID            254
           37B426574336E4A5B30 66C744E253F36264C69
           2E4F4B53236932303A2 27557A57737C6D4B225
           1215F462B7667457032 9414C442D2544364130
           694174782F7749393B6 612F5B3447405A4E714
           5735646366D20736939 A403B6237592B3D7B67
           335D712B233B3F      7D4D594E766B57

num_distinct : 고유한 값의 개수
low_value : 컬럼에서 가장 낮은 값(문자열컬럼의 경우 첫 32바이트만 사용, utl_raw 패키지나 convert_raw_value를 이용하여 조회가능)
high_value : 컬럼에서 가장 높은 값(문자열컬럼의 경우 첫 32바이트만 사용, utl_raw 패키지나 convert_raw_value를 이용하여 조회가능)
density : 0~1사이의 십진수, 1/num_distinct 이다. 0에 가까울수록 필터되고, 1에 가까울수록 select 된다.
num_nulls : null 값의 개수
avg_col_len : 평균 칼럼의 크기(바이트)
histogram : 히스토그램의 존재여부
num_buckets : 히스토그램에서 버킷의 개수(11.2 이하버전 : 254개, 12.1 이상버전 : 2,048개)


SYS@2017-11-19 21:30:46> SELECT utl_raw.cast_to_number(low_value) AS low_value,
  2         utl_raw.cast_to_number(high_value) AS high_value
  3  FROM user_tab_col_statistics
  4  WHERE table_name = 'T'
  5  AND column_name = 'VAL1';

LOW_VALUE HIGH_VALUE
--------- ----------
       39         63


SYS@2017-11-19 21:31:47> WITH
  2    FUNCTION convert_raw_value(p_value IN RAW, p_datatype IN VARCHAR2) RETURN VARCHAR2 IS
  3      l_ret VARCHAR2(64);
  4      l_date DATE;
  5      l_number NUMBER;
  6      l_binary_float BINARY_FLOAT;
  7      l_binary_double BINARY_DOUBLE;
  8      l_nvarchar2 NVARCHAR2(64);
  9      l_rowid ROWID;
 10    BEGIN
 11      IF p_datatype = 'VARCHAR2' OR p_datatype = 'CHAR'
 12      THEN
 13        dbms_stats.convert_raw_value(p_value, l_ret);
 14      ELSIF p_datatype = 'DATE'
 15      THEN
 16        dbms_stats.convert_raw_value(p_value, l_date);
 17        l_ret := to_char(l_date, 'YYYY-MM-DD HH24:MI:SS');
 18      ELSIF p_datatype LIKE 'TIMESTAMP%'
 19      THEN
 20        dbms_stats.convert_raw_value(p_value, l_date);
 21        l_ret := to_char(l_date, 'YYYY-MM-DD HH24:MI:SS');
 22      ELSIF p_datatype = 'NUMBER'
 23      THEN
 24        dbms_stats.convert_raw_value(p_value, l_number);
 25        l_ret := to_char(l_number);
 26      ELSIF p_datatype = 'BINARY_FLOAT'
 27      THEN
 28        dbms_stats.convert_raw_value(p_value, l_binary_float);
 29        l_ret := to_char(l_binary_float);
 30      ELSIF p_datatype = 'BINARY_DOUBLE'
 31      THEN
 32        dbms_stats.convert_raw_value(p_value, l_binary_double);
 33        l_ret := to_char(l_binary_double);
 34      ELSIF p_datatype = 'NVARCHAR2'
 35      THEN
 36        dbms_stats.convert_raw_value(p_value, l_nvarchar2);
 37        l_ret := to_char(l_nvarchar2);
 38      ELSIF p_datatype = 'ROWID'
 39      THEN
 40        dbms_stats.convert_raw_value(p_value, l_nvarchar2);
 41        l_ret := to_char(l_nvarchar2);
 42      ELSE
 43        l_ret := 'UNSUPPORTED DATATYPE';
 44      END IF;
 45      RETURN l_ret;
 46    END;
 47  SELECT column_name,
 48         convert_raw_value(low_value, data_type) AS low_value,
 49         convert_raw_value(high_value, data_type) AS high_value
 50  FROM user_tab_columns
 51  WHERE table_name = 'T'
 52  ORDER BY column_id
 53  /

COLUMN_NAME
------------------------------
LOW_VALUE                      HIGH_VALUE
------------------------------ ------------------------------
ID
1                              1000

VAL1
39                             63

VAL2
101                            106

VAL3
101                            106

PAD
 &#Co)Cs4#{Bet3nJ[0.OKS#i20:!! ~yQJ -IFI6ltN%?6&Li'UzWs|mK"YA
_F+vgEp2iAtx/wI9;esVF6m si93]q LD-%D6A0a/[4G@ZNqJ@;b7Y+={g}MY
+#;?                           NvkW

8.3 히스토그램


SYS@2017-11-19 21:32:40> 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 프리퀀시 히스토그램, 하이브리드 히스토그램을 추가로 도입했다.

프리퀀시 히스토그램


SYS@2017-11-19 21:32:43> 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 컬럼은 누적 프리퀀시를 나타낸다.


SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SYS@2017-11-19 21:32:44> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SYS@2017-11-19 21:32:44>
SYS@2017-11-19 21:32:44> COLUMN statement_id FORMAT A12
SYS@2017-11-19 21:32:44>
SYS@2017-11-19 21:32:44> 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

히스토그램에서 제공하는 값의 범위를 벗어나는 경우


SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val2 = 96;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val2 = 98;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val2 = 100;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val2 = 103.5;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SYS@2017-11-19 21:32:45> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SYS@2017-11-19 21:32:45>
SYS@2017-11-19 21:32:45> COLUMN statement_id FORMAT A12
SYS@2017-11-19 21:32:45>
SYS@2017-11-19 21:32:45> 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 참조)


SYS@2017-11-19 21:32:45> 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

SYS@2017-11-19 21:32:46> 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  /


SYS@2017-11-19 21:32:48> 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 컬럼은 버킷의 번호를 알려준다.
- 값의 프리퀀시(도수분포 값)을 저장하지 않는다.


SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SYS@2017-11-19 21:32:49>
SYS@2017-11-19 21:32:49> COLUMN statement_id FORMAT A12
SYS@2017-11-19 21:32:49>
SYS@2017-11-19 21:32:49> 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)
- 값이 히스토그램의 범위를 벗어나는 경우 최소값/최대값으로부터의 거리에 따라 달라진다.


SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val2 = 96;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val2 = 98;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val2 = 100;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val2 = 103.5;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SYS@2017-11-19 21:32:49> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SYS@2017-11-19 21:32:49>
SYS@2017-11-19 21:32:49> COLUMN statement_id FORMAT A12
SYS@2017-11-19 21:32:49>
SYS@2017-11-19 21:32:49> 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 프리퀀시 히스토그램과 하이브리드 히스토그램이 높이균형 히스토그램을 대체했다.


SYS@2017-11-19 21:32:50> UPDATE t SET val2 = 105 WHERE val2 = 106 AND rownum <= 20;

SYS@2017-11-19 21:32:53> 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  /

SYS@2017-11-19 22:18:18> 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


SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SYS@2017-11-19 22:19:08> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SYS@2017-11-19 22:19:08>
SYS@2017-11-19 22:19:08> COLUMN statement_id FORMAT A12
SYS@2017-11-19 22:19:08>
SYS@2017-11-19 22:19:08> 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 프리퀀시 히스토그램에 필요한 최소한 로우의 비율)


SYS@2017-11-19 22:38:36> 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)


SYS@2017-11-19 22:41:01> 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
SYS@2017-11-19 22:41:01>

상위 n개에 속하지 않는 값은 폐기되고 최소값과 최대값의 프리퀀시는 1로 설정된다.
위 예제에서는 102의 값이 폐기되고 101과 같이 1로 설정되었다.


SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val3 = 101;
SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val3 = 102;
SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val3 = 103;
SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val3 = 104;
SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val3 = 105;
SYS@2017-11-19 22:46:12> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val3 = 106;
SYS@2017-11-19 22:46:12>
SYS@2017-11-19 22:46:12> COLUMN statement_id FORMAT A12
SYS@2017-11-19 22:46:12>
SYS@2017-11-19 22:46:12> 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의 값에 대한 쿼리 옵티마이저 추정값이 프리퀀시 히스토그램과 다르다는 사실을 알 수 있다.


SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val3 = 96;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val3 = 98;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val3 = 100;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val3 = 103.5;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val3 = 107;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val3 = 109;
SYS@2017-11-19 22:48:09> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val3 = 111;
SYS@2017-11-19 22:48:09>
SYS@2017-11-19 22:48:09> COLUMN statement_id FORMAT A12
SYS@2017-11-19 22:48:09>
SYS@2017-11-19 22:48:09> 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

위 예제에서는 히스토그램의 범위를 벗어나는 값이 조건절에 올 경우, 프리퀀시 히스토그램과 같은 방식으로 처리하는 것을 알 수 있다.

08. 오브젝트 통계(1) (by jiginet) [2017.11.24]

8. 오브젝트 통계

8.1 dbms_stats 패키지

8.2 이용할 수 있는 오브젝트 통계에는 어떠한 것들이 있는가?

8.2.1 테이블통계

8.2.2 칼럼 통계

8.3 히스토그램

프리퀀시 히스토그램

높이균형 히스토그램

TOP 프리퀀시 히스토그램