ORACLE 11g SET STATS MANUALLY 1.0
|
|
|
|
|
|
|
|
|
|
|
|
1.
SET STATS MANUALLY
Statistics 를 수동으로 설정하는 경우는 매우 한정적이다.
그 한정적인 경우는 다음과 같은 경우가 있지 않을까 싶다.
a.
Optimizer
의 행동을 관측
b.
테스트 데이터 만들기가 힘들 때
a 는 말그대로 연구 목적이다. Optimizer 가 다음과 같은
지표의 변화에 대해 어떻게 반응하는지 그것을 관찰하고 연구한다는 이야기다.
b 는 말그대로 테스트 데이터를 만들어 사용하기 위한 노력이 크게 들 때이다. 이런경우 간단히 Stats 을 수정해 원하는 Plan 으로 유도할 수 있을 것이다
2.
DBMS_STATS 를 이용한 Stats 초기화
A.
ORACLE 은 다음과 같은 삭제 프로시저를 제공한다.
|
DELETE_COLUMN_STATS Procedure DELETE_DATABASE_STATS Procedure DELETE_DICTIONARY_STATS Procedure DELETE_FIXED_OBJECTS_STATS Procedure DELETE_INDEX_STATS Procedure DELETE_SCHEMA_STATS Procedure DELETE_SYSTEM_STATS Procedure DELETE_TABLE_STATS Procedure |
프로시저에 대한 자세한 설명은 생략한다 !
이름 그대로가 타겟에 대한 설명이다.
그 타겟을 보면
COLUMN, DATABASE, DICTIONARY, FIXED_OBJECTS, INDEX, SCHEMA, SYSTEM, TABLE 등이
있다.
B.
DELETE_*_STATS
에 대한 추가적인 설명
DELETE_*_STATS 의 타겟 중 의문이 되는 항목이 있다.
DATABASE,
DICTIONARY, FIXED_OBJECTS, SYSTEM 이다.
-
DELETE_DATABASE_STATS
Database 의 모든 테이블을 삭제대상으로 한다.
-
DELETE_DICTIONARY_STATS
이 프로시저는 모든 Dictionary
Schemas 의 Statistics 을 삭제한다.
(SYS, SYSTEM)
-
DELETE_FIXED_OBJECTS_STATS
/*+ x$ view 를 지칭하는 듯… 자세한 설명은 생략한다. (정보부족) */
3.
DELETE_TABLE_STATS 의 사용
여기서는 Table
Statistics 를 삭제해 보겠다.
|
DBMS_STATS.DELETE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2,
partname VARCHAR2
DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE); |
위 프로시저의 메소드를 설명하자면 아래와 같다.
OWNNAME -> OWNER
TABNAME -> TABLE NAME
PARTNAME -> Partitioned Table 의 Partitioned Segment Name 을 지칭
STATTAB -> 어느 통계지표를 찾아야 할지 그 위치를
표현하는 유저 통계 테이블 식별자
/*+ 의미 불명 추후 직접 사용해 볼 필요가 있음 */
STATID /*+ 의미 불명 */
CASCADE_PARTS -> TABLE 이 PARTITIONED 이며 PARTNAME 이 NULL 일 때 이 값을 TRUE 로 한다. 이것은 테이블 밑의 파티션의 통게정보 삭제를 하게
된다.
CASCADE_COLUMNS -> 설정 시 DELETE_COLUMN_STATS 를 호출하게 된다.
CASCADE_INDEXES -> 설정 시 DELETE_INDEX_STATS 를 호출하게 된다.
STATOWN -> stattab 은 소유한 owner 를 지정한다.
NO_INVALIDATE -> TRUE 로 Seting 하는 경우 해당 객체에 dependent 한 Cursor 에 대해 invalidate 작업을 실시하지 않는다.
FORCE -> TRUE 로 설정시 잠금설정을 무시하고 통계정보를 삭제한다.
4.
DELETE_TABLE_STATS 가 주는 영향
DBA_TAB_STATISTICS
가 영향을 받음
|
SQL> exec dbms_stats.delete_table_stats(OWNNAME=>'SYS',TABNAME=>'SAMP',CASCADE_PARTS=>FALSE,
CASCADE_COLUMNS=>FALSE, CASCADE_INDEXES=>FALSE); PL/SQL procedure successfully completed. SQL> select * from dba_tab_statistics 2 where table_name = 'SAMP'; OWNER TABLE_NAME ------------------------------
------------------------------ PARTITION_NAME PARTITION_POSITION
SUBPARTITION_NAME ------------------------------ ------------------ ------------------------------ SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE --------------------- ------------ ----------
---------- ------------ ---------- CHAIN_CNT
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ----------- -------------------------
------------------- AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE
LAST_ANAL GLO USE STATT STA ----------------- ------------------- -----------
--------- --- --- ----- --- SYS SAMP
TABLE OWNER TABLE_NAME ------------------------------
------------------------------ PARTITION_NAME PARTITION_POSITION
SUBPARTITION_NAME ------------------------------ ------------------
------------------------------ SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE --------------------- ------------ ----------
---------- ------------ ---------- CHAIN_CNT
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ----------- -------------------------
------------------- AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE
LAST_ANAL GLO USE STATT STA ----------------- ------------------- -----------
--------- --- --- ----- ---
NO NO SQL> exec dbms_stats.gather_table_stats('SYS','SAMP'); PL/SQL procedure successfully completed. SQL> select * from dba_tab_statistics 2 where table_name = 'SAMP'; OWNER
TABLE_NAME ------------------------------
------------------------------ PARTITION_NAME PARTITION_POSITION
SUBPARTITION_NAME ------------------------------ ------------------
------------------------------ SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE --------------------- ------------ ----------
---------- ------------ ---------- CHAIN_CNT
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ----------- -------------------------
------------------- AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE
LAST_ANAL GLO USE STATT STA ----------------- ------------------- -----------
--------- --- --- ----- --- SYS SAMP
TABLE 3
1 0 0 OWNER TABLE_NAME ------------------------------
------------------------------ PARTITION_NAME PARTITION_POSITION
SUBPARTITION_NAME ------------------------------ ------------------
------------------------------ SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE --------------------- ------------ ----------
---------- ------------ ---------- CHAIN_CNT
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ----------- -------------------------
------------------- AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE
LAST_ANAL GLO USE STATT STA ----------------- ------------------- -----------
--------- --- --- ----- --- 0 3 0 0 3 13-AUG-11 YES NO NO |
delete_table_stats 를 실행함으로써 dba_tab_statistics 가 영향받았음을
알 수 있다.
5.
SET_TABLE_STATS 의 사용
|
DBMS_STATS.SET_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows NUMBER DEFAULT NULL, numblks NUMBER DEFAULT NULL, avgrlen NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type
(
get_param('NO_INVALIDATE')),
cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFUALT NULL, force BOOLEAN DEFAULT FALSE); |
위 파라미터에 대해 간단히 설명하자면 다음과 같다.
ownname -> owner
tabname -> table name
partname -> partition name in
Partitioned-Table
stattab / statid /*+ 자세한 설명을 생략한다! */
numrows -> rows of table
numblks -> blocks of table
avglen -> average lenth for the table
flags -> 오라클이 내부적으로 사용( 반드시 null 로 두어야
한다.)
statown / statid /*+ 자세한 설명을 생략한다! */
no_invalidate -> 프로시저 수행 후 Cursor 의 Invalidate 여부. True 로 두는 경우 Invalidate 하지 않는다.
cachedblk -> buffer cache 에 caching 되는 평균숫자
force -> True 인 경우 Table 이
Locked 상태여도 Stat 을 변경한다.
6.
SET_TABLE_STATS 가 주는 영향
SET_TABLE_STATS 는 DELETE_TABLE_STATS 와 다르게 INDEX, COLUMN, PARTS 와 같이 연쇄적으로 영향을 주진 않는다.
이 프로시저 역시 타겟이 TABLE 인 만큼 DBA_TAB_STATISTICS 가 영향을 받는다.
|
SQL> select * from samp; Execution Plan ---------------------------------------------------------- Plan hash value: 609948581 -------------------------------------------------------------------------- | Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT
STATEMENT | |
3 | 9 | 2 (0)|
00:00:01 | | 1 | TABLE ACCESS FULL| SAMP | 3 |
9 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic
sampling used for this statement (level=2) SQL> exec
dbms_stats.set_table_stats(OWNNAME=>'SYS',TABNAME=>'SAMP',numrows=>2000000,
numblks=> 200000); PL/SQL procedure successfully completed. SQL> select * from samp; Execution Plan ---------------------------------------------------------- Plan hash value: 609948581 -------------------------------------------------------------------------- | Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT
STATEMENT | |
2000K| 5859K| 54222 (1)| 00:10:51 | | 1 | TABLE ACCESS FULL| SAMP | 2000K|
5859K| 54222 (1)| 00:10:51 | -------------------------------------------------------------------------- |
7.
References
A.
DBMS_STATS
| http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm