Oracle 11g에서는 "Preference"라는 새로운 개념을 통해
다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.
원하는 테이블에 대해 통계정보 수행시 CASCADE, DEGREE,
ESTIMATE_PERCENT, METHOD_OPT, NO_INVALIDATE,
GRANULARITY, PUBLISH, INCREMENTAL, STALE_PERCENT
등 개별로 설정하여 좀더 편리성을 제공 해 주고 있다.
-- 특정 테이블에 대한 설정 ( new 11g )
Syntax
DBMS_STATS.SET_TABLE_PREFS (
ownname IN VARCHAR2,
tabname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2 );
pname Parameters
Preference name. The default value for following preferences can be set:
CASCADE, DEGREE, ESTIMATE_PERCENT, METHOD_OPT, NO_INVALIDATE, GRANULARITY, PUBLISH, INCREMENTAL, STALE_PERCENT
-- 아래와 같이 한번만 설정
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'SHWOO_T1', 'ESTIMATE_PERCENT', '30');
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'SHWOO_T1', 'DEGREE', '10');
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'SHWOO_T1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');
-- 개별 통계정보 설정 내용확인. ( new 11g )
Syntax
DBMS_STATS.GET_PREFS (
pname IN VARCHAR2,
ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT' , USER, 'SHWOO_T1') FROM DUAL;
SELECT DBMS_STATS.GET_PREFS('DEGREE' , USER, 'SHWOO_T1') FROM DUAL;
SELECT DBMS_STATS.GET_PREFS('METHOD_OPT' , USER, 'SHWOO_T1') FROM DUAL;
SELECT DBMS_STATS.GET_PREFS('GRANULARITY' , USER, 'SHWOO_T1') FROM DUAL;
-- 전체 현재 통계정보 설정 내용확인.
Syntax
DBMS_STATS.GET_PARAM (
pname IN VARCHAR2)
RETURN VARCHAR2;
SELECT DBMS_STATS.GET_PARAM('DEGREE') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('GRANULARITY') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('CASCADE') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('NO_INVALIDATE') FROM DUAL;
-- Schema 또는 TABLE 레벨에서 통계 정보 수집
Syntax
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
Syntax
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), -- or 'FOR ALL COLUMNS SIZE 1',
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
EX Syntax )
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SHWOO', tabname => 'SHWOO_T1', -
estimate_percent=> 100, granularity=>'ALL', degree=> 4 , cascade=>true );
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER); -- USER : 수행하는 기본 DEFAULT 유저
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SHWOO_T1');
아래 참고 사이트에서 더 많은 DBMS_STATS 에 대한 New 11g 를 확인 할 수 있다.
참고사이트 : http://psoug.org/reference/dbms_stats.html
http://ukja.tistory.com/86
2010년 3월 24일 수요일
히스토그램이 있는경우의 density 계산 방법
히스토그램이 있는경우의 density 계산 방법 -- Oracle 10.2.0.4 이상
다음과 같이 테이블을 만듭니다.
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
다음과 같이 테이블을 만듭니다.
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
2010년 3월 18일 목요일
원하는 파티션의 통계정보 import 하기 [ DBMS_STATS.COPY_TABLE_STATS ]
#####################################
# 원하는 파티션의 통계정보 import 하기
# DBMS_STATS.COPY_TABLE_STATS
#####################################
10.2.0.4 부터 지원되는 Package 입니다.
11G R2 에서 테스트 결과 아주 잘 되는것을 확인 되었습니다.
위의 패키지가 지원되기 전에는 아주 불편했던 기억이 나네요.
이 것은 이제 그만 머리속에서 지워보도록 하겠습니다. ㅋㅋㅋ
-- 1. Range 파티션 생성
drop table T1 purge;
CREATE TABLE T1(C1, C2)
PARTITION BY RANGE (C1) (
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000)
)
AS
SELECT LEVEL-1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3000
;
commit;
-- 2. 데이타 확인
select * from T1;
-- 3. 통계정보 수행
--exec dbms_stats.gather_table_stats(user, 't1', granularity=>'all');
exec dbms_stats.gather_table_stats('SHWOO', 't1', granularity=>'all');
-- 4. 통계정보 확인
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME ='T1';
/**
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- -------------------
SHWOO T1 3000 2010-03-18 18:18:46
통계정보 수행전까지는 NUM_ROWS 와 LAST_ANALYZED 값은 없을 것입니다.
**/
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ------------ --------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
**/
-- 5. 파티션 ADD 및 데이타 입력
alter table t1
add partition p4 values less than (4000)
;
insert into t1
select level+3000-1, level
from dual
connect by level <= 1000
;
COMMIT;
-- 6. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
---------------- ------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4
**/
-- 7. 해당 파티션만 통계정보 수행
exec dbms_stats.gather_table_stats('SHWOO', 't1', 'p4');
-- 8. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
위의 방법은 데이타가 존재 하지 않을시에는 NUM_ROWS가 0 값 입니다.
한마디로 데이타가 존재시에만 효과가 있겠네요.
**/
-- 9. 파티션 ADD
alter table t1
add partition p5 values less than (5000)
;
-- 10. 해당 파티션 정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
---------------- -------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
SHWOO T1 P5
**/
-- 11. 원하는 파티션의 통계정보 데이타 넣기
exec dbms_stats.copy_table_stats('SHWOO', 't1', srcpartname=>'p4', dstpartname=>'p5');
-- 해당 srcpartname 의 값은 P1 ~ P4 까지 아무거나 넣으시면 됩니다.
-- dstpartname 의 값은 srcpartname 의 통계정보를 그대로 copy를 해 줍니다.
-- 12. 통계정보 확인 하기
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
-------------- ------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
SHWOO T1 P5 1000 2010-03-18 17:57:37
NUM_ROWS 가 기존 P4 의 파티션 정보를 그대로 copy를 해 주었네요.
PLAN 이 변경되지 않겠네요.
**/
-- 13. 원하는 통계정보 삭제하기
exec dbms_stats.delete_table_stats ( 'SHWOO', 'T1', 'P5');
########### 참고 내용 ################################################
11g 에서부터는 아래 버그를 잘 처리 되었다고 하네요.
버그 내용은 이러 합니다. LOW_VALUE , HIGH_VALUE 의 값이 COPY를 하게 되면
원본 그대로 변경 없이 승계를 받는 것이라고 하네요.
예로 P3을 P4로 COPY 시 LOW_VALUE : 2000 , HIGH_VALUE:2999 의 값이
P4에 LOW_VALUE : 3000 , HIGH_VALUE:3999 로 세팅 되는것이 아니라
LOW_VALUE : 2000 , HIGH_VALUE:2999 로 세팅이 됩니다.
SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P3';
TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE
------------- --------------- ---------------- ----------- ----------- -----------
T1 P3 C1 1000 2000 2999
T1 P3 C2 1000 2001 3000
SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P4';
출처 : http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/
# 원하는 파티션의 통계정보 import 하기
# DBMS_STATS.COPY_TABLE_STATS
#####################################
10.2.0.4 부터 지원되는 Package 입니다.
11G R2 에서 테스트 결과 아주 잘 되는것을 확인 되었습니다.
위의 패키지가 지원되기 전에는 아주 불편했던 기억이 나네요.
이 것은 이제 그만 머리속에서 지워보도록 하겠습니다. ㅋㅋㅋ
-- 1. Range 파티션 생성
drop table T1 purge;
CREATE TABLE T1(C1, C2)
PARTITION BY RANGE (C1) (
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000)
)
AS
SELECT LEVEL-1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3000
;
commit;
-- 2. 데이타 확인
select * from T1;
-- 3. 통계정보 수행
--exec dbms_stats.gather_table_stats(user, 't1', granularity=>'all');
exec dbms_stats.gather_table_stats('SHWOO', 't1', granularity=>'all');
-- 4. 통계정보 확인
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME ='T1';
/**
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- -------------------
SHWOO T1 3000 2010-03-18 18:18:46
통계정보 수행전까지는 NUM_ROWS 와 LAST_ANALYZED 값은 없을 것입니다.
**/
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ------------ --------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
**/
-- 5. 파티션 ADD 및 데이타 입력
alter table t1
add partition p4 values less than (4000)
;
insert into t1
select level+3000-1, level
from dual
connect by level <= 1000
;
COMMIT;
-- 6. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
---------------- ------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4
**/
-- 7. 해당 파티션만 통계정보 수행
exec dbms_stats.gather_table_stats('SHWOO', 't1', 'p4');
-- 8. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
위의 방법은 데이타가 존재 하지 않을시에는 NUM_ROWS가 0 값 입니다.
한마디로 데이타가 존재시에만 효과가 있겠네요.
**/
-- 9. 파티션 ADD
alter table t1
add partition p5 values less than (5000)
;
-- 10. 해당 파티션 정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
---------------- -------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
SHWOO T1 P5
**/
-- 11. 원하는 파티션의 통계정보 데이타 넣기
exec dbms_stats.copy_table_stats('SHWOO', 't1', srcpartname=>'p4', dstpartname=>'p5');
-- 해당 srcpartname 의 값은 P1 ~ P4 까지 아무거나 넣으시면 됩니다.
-- dstpartname 의 값은 srcpartname 의 통계정보를 그대로 copy를 해 줍니다.
-- 12. 통계정보 확인 하기
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';
/**
TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
-------------- ------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
SHWOO T1 P5 1000 2010-03-18 17:57:37
NUM_ROWS 가 기존 P4 의 파티션 정보를 그대로 copy를 해 주었네요.
PLAN 이 변경되지 않겠네요.
**/
-- 13. 원하는 통계정보 삭제하기
exec dbms_stats.delete_table_stats ( 'SHWOO', 'T1', 'P5');
########### 참고 내용 ################################################
11g 에서부터는 아래 버그를 잘 처리 되었다고 하네요.
버그 내용은 이러 합니다. LOW_VALUE , HIGH_VALUE 의 값이 COPY를 하게 되면
원본 그대로 변경 없이 승계를 받는 것이라고 하네요.
예로 P3을 P4로 COPY 시 LOW_VALUE : 2000 , HIGH_VALUE:2999 의 값이
P4에 LOW_VALUE : 3000 , HIGH_VALUE:3999 로 세팅 되는것이 아니라
LOW_VALUE : 2000 , HIGH_VALUE:2999 로 세팅이 됩니다.
SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P3';
TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE
------------- --------------- ---------------- ----------- ----------- -----------
T1 P3 C1 1000 2000 2999
T1 P3 C2 1000 2001 3000
SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P4';
TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE
------------- --------------- ---------------- ----------- ----------- -----------
T1 P4 C1 1000 3000 3999
T1 P4 C2 1000 1 1000
SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P5';
TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE
------------- --------------- ---------------- ----------- ----------- -----------
T1 P5 C1 1000 4000 5000
T1 P5 C2 1000 1 1000
10G 이하에서는 아래 처럼 처리 하면 된다고 하네요.
1. 샘플 입니다..
10g에서는 어쩔 수 없이 수작업으로 보정하는 수 밖엔 없네요...
다음의 파티션 기준 컬럼에 대한 예제 스크립트입니다.
DECLARE
V_SREC DBMS_STATS.STATREC;
V_ArrVals DBMS_STATS.CHARARRAY;
BEGIN
V_SREC.epc := 2;
V_SREC.eavs := null;
V_SREC.bkvals := DBMS_STATS.NUMARRAY(2.60592219238428E35, 2.60592219238428E35);
V_ArrVals := DBMS_STATS.CHARARRAY('20090401', '20090430');
DBMS_STATS.PREPARE_COLUMN_VALUES(V_SREC, V_ArrVals);
DBMS_STATS.SET_COLUMN_STATS
(
OWNNAME=>'owner명',
TABNAME=>'테이블명',
COLNAME=>'SALEDATE',
PARTNAME=>'P200904',
DISTCNT=>30,
DENSITY=>0.0357142857142857,
NULLCNT=>0,
AVGCLEN=>9,
SREC=>V_SREC
);
END;
/
2. 패치
버그번호는 8719831
다음의 파티션 기준 컬럼에 대한 예제 스크립트입니다.
DECLARE
V_SREC DBMS_STATS.STATREC;
V_ArrVals DBMS_STATS.CHARARRAY;
BEGIN
V_SREC.epc := 2;
V_SREC.eavs := null;
V_SREC.bkvals := DBMS_STATS.NUMARRAY(2.60592219238428E35, 2.60592219238428E35);
V_ArrVals := DBMS_STATS.CHARARRAY('20090401', '20090430');
DBMS_STATS.PREPARE_COLUMN_VALUES(V_SREC, V_ArrVals);
DBMS_STATS.SET_COLUMN_STATS
(
OWNNAME=>'owner명',
TABNAME=>'테이블명',
COLNAME=>'SALEDATE',
PARTNAME=>'P200904',
DISTCNT=>30,
DENSITY=>0.0357142857142857,
NULLCNT=>0,
AVGCLEN=>9,
SREC=>V_SREC
);
END;
/
2. 패치
버그번호는 8719831
출처 : http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/