다음과 같이 테이블을 만듭니다.
CREATE TABLE SHWOO_T1
AS
SELECT
CASE
WHEN LEVEL BETWEEN 1 AND 10000 THEN 1
WHEN LEVEL BETWEEN 10001 AND 15000 THEN 2
WHEN LEVEL BETWEEN 15001 AND 20000 THEN 3
END AS C1,
CASE
WHEN LEVEL BETWEEN 1 AND 10000 THEN 1
WHEN LEVEL BETWEEN 10001 AND 15000 THEN 2
WHEN LEVEL BETWEEN 15001 AND 20000 THEN LEVEL
END AS C2
FROM DUAL
CONNECT BY LEVEL <= 20000;
Table created.
히스토그램없이 통계 정보를 수집합니다.
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SHWOO_T1', METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
통계 정보는 다음과 같습니다.
-- 01. table stats
SELECT TABLE_NAME,NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME='SHWOO_T1'
;
TABLE_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZE
------------------ ---------- ----------- ------------
SHWOO_T1 20000 20000 24-MAR-10
-- 02. column stats
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LOW_VALUE, SAMPLE_SIZE, HIGH_VALUE, HISTOGRAM
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME='SHWOO_T1';
TABLE_NAME COLUMN_NAME NUM_DISTINC DENSITY NUM_NULLS LOW_VALUE SAMPLE_SIZE HIGH_VALUE HISTOGRAM
--------------- ------------ ----------- ----------- ----------- --------------- ----------- ----------------- ---------------
SHWOO_T1 C2 5002 0.000199920031987205 0 C102 20000 C303 NONE
SHWOO_T1 C1 3 0.333333333333333 0 C102 20000 C104 NONE
-- 03. histogram stats
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM DBA_TAB_HISTOGRAMS
WHERE TABLE_NAME='SHWOO_T1';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------- --------------- ---------------- ---------------
SHWOO_T1 C1 0 1
SHWOO_T1 C2 0 1
SHWOO_T1 C1 1 3
SHWOO_T1 C2 1 20000
Bind 변수에 b1에 값 1을 대입하고 그 값을 이용해 Explain Plan 결과와 Runtime Plan을 비교해보겠습니다
SELECT COUNT(*) FROM SHWOO_T1 WHERE C1 = :B1;
Density(c1) = 0.33.. 이므로 예측 로우 건수는 20000*0.33.. = 6667이 됩니다. Explain Plan과 Runtime Plan이 모두 동일합니다.
-> column stats 에서 C1의 값 SMAPLE_SIZE 와 DENSITY 서로 곱한값.
-- Explain Plan
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM SHWOO_T1 WHERE C1 = :B1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 10 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 6667 | 20001 | 10 (10)| 00:00:01 |
-------------------------------------------------------------------------------
SELECT COUNT(*) FROM SHWOO_T1 WHERE C1 = :B1
COUNT(*)
-----------
10000
-- Runtime Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 6667 | 20001 | 10 (10)| 00:00:01 |
-------------------------------------------------------------------------------
이번에는 히스토그램을 수집해보겠습니다.
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SHWOO_T1', -
METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY', NO_INVALIDATE=>FALSE);
PL/SQL procedure successfully completed.
다음과 같이 컬럼 c1에 대해서는 Frequency 히스토그램이, 컬럼 c2에 대해서는 Height-Balanced 히스토그램이 수집되었습니다
SELECT TABLE_NAME,NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME='SHWOO_T1';
TABLE_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ----------- ----------- -------------------
SHWOO_T1 20000 20000 2010-03-24 09:55:26
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LOW_VALUE, SAMPLE_SIZE, HIGH_VALUE, HISTOGRAM
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME='SHWOO_T1';
TABLE_NAME COLUMN_NAME NUM_DISTINC DENSITY NUM_NULLS LOW_VALUE SAMPLE_SIZE HIGH_VALUE HISTOGRAM
----------------- -------------- ----------- ----------- ----------- ---------------- ----------- ----------------------- ---------------
SHWOO_T1 C2 5002 0.00005 0 C102 20000 C303 HEIGHT BALANCED
SHWOO_T1 C1 3 0.000025 0 C102 20000 C104 FREQUENCY
* Without a histogram density = 1/NDV ( Number of Distinct Values = DBA_TAB_COLUMNS.NUM_DISTINCT, DBA_TAB_COL_STATISTICS.NUM_DISTINC )
* With a height-balanced histogram density = sum(square(num_not_popular_rows_) / ( num_rows * num_not_popular_rows)
* With a frequency histogram density =1/( 2 * num_rows )
Column c2:
1 = 10000개(Popular)
2 = 5000개(Popular)
15001 ~ 20000 = 각 1개(Non Popular)
density(c1)(Frequency Histogram) = 1 / ( 2 * 20000) = 0.000025
density(c2)(Height-Balanced Histogram) = (1*1 + 1*1 + ... + 1*1[총 5천개]) / (20000*5000) = 1/20000 = 0.00005
-> 총 5천개 는 NULL 값을 갖지 않는 값을 말함.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM DBA_TAB_HISTOGRAMS
WHERE TABLE_NAME='SHWOO_T1';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------ --------------- --------------
SHWOO_T1 C1 10000 1
SHWOO_T1 C1 15000 2
SHWOO_T1 C1 20000 3
SHWOO_T1 C2 126 1
SHWOO_T1 C2 189 2
SHWOO_T1 C2 190 15008
SHWOO_T1 C2 191 15086
SHWOO_T1 C2 192 15164
SHWOO_T1 C2 193 15242
SHWOO_T1 C2 194 15320
SHWOO_T1 C2 195 15398
SHWOO_T1 C2 196 15476
SHWOO_T1 C2 197 15554
SHWOO_T1 C2 198 15632
SHWOO_T1 C2 199 15710
SHWOO_T1 C2 200 15788
SHWOO_T1 C2 201 15866
SHWOO_T1 C2 202 15944
SHWOO_T1 C2 203 16022
SHWOO_T1 C2 204 16100
SHWOO_T1 C2 205 16178
SHWOO_T1 C2 206 16256
SHWOO_T1 C2 207 16334
SHWOO_T1 C2 208 16412
SHWOO_T1 C2 209 16490
SHWOO_T1 C2 210 16568
SHWOO_T1 C2 211 16646
SHWOO_T1 C2 212 16724
SHWOO_T1 C2 213 16802
SHWOO_T1 C2 214 16880
SHWOO_T1 C2 215 16958
SHWOO_T1 C2 216 17036
SHWOO_T1 C2 217 17114
SHWOO_T1 C2 218 17192
SHWOO_T1 C2 219 17270
SHWOO_T1 C2 220 17348
SHWOO_T1 C2 221 17426
SHWOO_T1 C2 222 17504
SHWOO_T1 C2 223 17582
SHWOO_T1 C2 224 17660
SHWOO_T1 C2 225 17738
SHWOO_T1 C2 226 17816
SHWOO_T1 C2 227 17894
SHWOO_T1 C2 228 17972
SHWOO_T1 C2 229 18050
SHWOO_T1 C2 230 18128
SHWOO_T1 C2 231 18206
SHWOO_T1 C2 232 18284
SHWOO_T1 C2 233 18362
SHWOO_T1 C2 234 18440
SHWOO_T1 C2 235 18518
SHWOO_T1 C2 236 18596
SHWOO_T1 C2 237 18674
SHWOO_T1 C2 238 18752
SHWOO_T1 C2 239 18830
SHWOO_T1 C2 240 18908
SHWOO_T1 C2 241 18986
SHWOO_T1 C2 242 19064
SHWOO_T1 C2 243 19142
SHWOO_T1 C2 244 19220
SHWOO_T1 C2 245 19298
SHWOO_T1 C2 246 19376
SHWOO_T1 C2 247 19454
SHWOO_T1 C2 248 19532
SHWOO_T1 C2 249 19610
SHWOO_T1 C2 250 19688
SHWOO_T1 C2 251 19766
SHWOO_T1 C2 252 19844
SHWOO_T1 C2 253 19922
SHWOO_T1 C2 254 20000
Explain Plan은 바인드 피킹을 하지 않기 때문에 여전히 예측 로우 건수는 Base Cardinality/NDV = 20000/3 = 6667이 됩니다.
Frequency Histogram이 있을 경우에는 Density가 아닌 NDV를 이용해서 Cardinality를 계산합니다.
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM SHWOO_T1 WHERE C1 = :B1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 10 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 6667 | 20001 | 10 (10)| 00:00:01 |
-------------------------------------------------------------------------------
하지만 Runtime Plan은 Bind Peeking을 하기 때문에 c1 = 1 조건과 동일합니다.
Frequency Histogram이 있기 때문에 Bucket안에 들어간 10,000개를 예측 로우 건수로 사용합니다.
만약 ._optim_peek_user_binds=FALSE 로 되어 있다면 기존의 6667 개를 예측 로우 건수로 리턴.
SELECT COUNT(*) FROM SHWOO_T1 WHERE C1 = :B1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 10000 | 30000 | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------
반면에 c2 = :b1 조건은 어떻게 될까요? Explain Plan은 바인드 피킹을 하지 않으므로
Cardinality = Base Cardinality/NDV = 20000/5002 = 3.99 = 4가 됩니다.
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM SHWOO_T1 WHERE C2 = :B1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 4 | 16 | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------
반면에 Runtime Plan은 c2 = 1과 동일한 조건으로 처리됩니다.
Cardinality = Base Cardinality * (Bucket #) / (Total Bucket #) = 20000 * 126 / 254 = 9921이 됩니다.
SELECT COUNT(*) FROM SHWOO_T1 WHERE C2 = :B1;
COUNT(*)
-----------
10000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 4 | 16 | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| SHWOO_T1 | 9921 | 39684 | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------
위의 테스트 케이스로 간단한게 정리가 될 것으로 봅니다. 버전에 따라 다른 결과가 나올 수 있으므로 현재 사용 중인 시스템에서 비슷한 방법으로 확인해보시면 좋겠습니다.
출처 : http://121.254.172.39:8080/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:5443200346684724
댓글 없음:
댓글 쓰기