I. Histogram을 생성하지 않고 조작
II. TCF(Tuning by Cardinality Feedback) 방법론
create table t1(c1 int);
---------------------------------------------------------------------------
Histogram을 수동으로 생성하는 pl/sql을 작성
dbms_stats.statrec, dbms_stats.numarray객체에 값을 담고
dbms_stats.prepare_column_values함수로 매칭시키는 것이 포인트
---------------------------------------------------------------------------
woong:WOONG >
t1 declare
2 v_srec dbms_stats.statrec;
3 v_numvals dbms_stats.numarray;
4 begin
5 v_srec.epc := 5;
6 v_srec.eavs := null;
7 v_numvals := dbms_stats.numarray(1, 2, 3, 4, 5);
8 v_srec.bkvals := dbms_stats.numarray(10000, 20000, 30000, 40000, 50000);
9 dbms_stats.prepare_column_values(v_srec, v_numvals);
10
11 dbms_stats.set_table_stats(
12 ownname=>user,
13 tabname=>'t1',
14 numrows=>150000,
15 numblks=>1000,
16 avgrlen=>150
17 );
18
19 dbms_stats.set_column_stats(
20 ownname=>user,
21 tabname=>'t1',
22 colname=>'c1',
23 distcnt=>5,
24 density=>1/2/150000,
25 nullcnt=>0,
26 srec=>v_srec
27 );
28 end;
29 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.01
------------------------------------------------------------
테이블을 create한 이후 통계정보를 gathering하지 않았는데도
통계정보가 생성되어 있는것을 확인할 수 있다.
------------------------------------------------------------
woong:WOONG >
t1 @tab_stat t1
01. table stats
TABLE_NAME : T1
NUM_ROWS : 150000
BLOCKS : 1000
SAMPLE_SIZE : 2000
LAST_ANAL : 2009/04/24 22:49:48
-----------------
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.14
02. column stats
TABLE_NAME : T1
COLUMN_NAME : C1
NUM_DISTINCT : 5
NUM_NULLS : 0
DENSITY : .00000333333333333333
LOW_VALUE : C102
HIGH_VALUE : C106
HISTOGRAM : FREQUENCY
-----------------
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.04
03. histogram stats
TABLE COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
----- ----------- --------------- --------------
T1 C1 10000 1() <= 10000
T1 C1 30000 2() <= 20000
T1 C1 60000 3() <= 30000
T1 C1 100000 4() <= 40000
T1 C1 150000 5() <= 50000
5 개의 행이 선택되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 explain plan for
2 select *
3 from t1
4 where c1 = 1;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 10000 | 126K| 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1)
13 개의 행이 선택되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 explain plan for
2 select *
3 from t1
4 where c1 = -1;
해석되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 32500 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2500 | 32500 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=(-1))
13 개의 행이 선택되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 explain plan for
2 select *
3 from t1
4 where c1 = :b1;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30000 | 380K| 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 30000 | 380K| 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=TO_NUMBER(:B1))
13 개의 행이 선택되었습니다.
경 과: 00:00:00.01
통계정보를 조작함으로써 Optimizer의 선택을 바꿀 수 있으며, 실행계획을 제어할 수 있다.
?
1. 작은 크기의 Table을 실제 Data의 변경없이 매우 큰 크기의 Table로 바꾸고자 할 때
2. 통계정보수집없이 통계정보를 갱신하고자 하는 경우
3. 통계정보를 변경함으로써 실행계획을 제어하고자 하는 경우