DBMS_STATS 사용
개요
Oracle8까지는 테이블, 인덱스에 대한 통계정보를 생성하기 위해 『ANALYZE』
명령만을 사용할 수 있었으나 Oracle8i 부터는 『ANALYZE』
명령 외에『DBMS_STATS』Package를 제공한다.
ANALYZE명령을 사용할 경우에는 신규로 생성한 통계정보가 성능저하를 유발시키더라도 이전과 같은 통계정보를 추출하기 위해서는 다시 ANALYZE 작업을 수행해야 되기 때문에 테이블의 크기가 클 경우 많은 시간을 통계정보를 생성하는데 소비하게 된다.
또한 일부 Partitioned Table에 대해 부정확한 통계정보를 추출할 수도 있기 때문에 Oracle8i부터는『DBMS_STATS』Package를 사용하여 통계정보를 생성하고 관리할 것을 권고하고 있다.
『DBMS_STATS』Package를 사용할 경우 통계정보에
대한
이력관리가
가능하다.
ANALYZE명령을 사용할 때는 통계정보가 잘못될 경우 다시 ANALYZE작업을 수행해야 했고 이전과 동일한 통계정보를 생성하는 데에도 많은 어려움이 있었다.(테이블 크기가 클수록 문제는 더 심각해 진다)
그러나『DBMS_STATS』Package를 사용하면 문제가 발생하더라도 현재 Data Dictionary에 있는 통계정보를 삭제한 후 User Statistics Table에 저장되어 있는 이전 통계정보를 IMPORT하여 단시간 내에 이전과 동일한 환경으로 돌아가는 것이 가능하다.
여기에서는 일반적으로 가장 많이 쓰이는 Procedure에 대한 Syntax 및 사용 예를 설명한다. (자세한 내용은 “Oracle8i supplied PL/SQL Packages Reference” 매뉴얼 참조)
CREATE_STAT_TABLE
통계정보를 저장할 테이블을 생성한다.
Syntax
DBMS_STATS.CREATE_STAT_TABLE (
Ownname VARCHAR2,
Stattab VARCHAR2,
Tblspace VARCHAR2 DEFAULT NULL);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Table Owner |
|
Stattab |
Table Name |
|
Tblspace |
Tablespace Name |
Example
EXEC DBMS.CREATE_STAT_TABLE(‘SCOTT’,’STATSTBL’,’USERS’);
DROP_STAT_TABLE
통계정보를 저장했던 테이블을 삭제한다.
Syntax
DBMS_STATS.DROP_STAT_TABLE (
Ownname VARCHAR2,
Stattab VARCHAR2);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Table Owner |
|
Stattab |
Table Name |
Example
EXEC DBMS.DROP_STAT_TABLE(‘SCOTT’,’STATSTBL’);
GATHER_TABLE_STATS
테이블, 컬럼에 대한 통계정보를 생성한다. CASCADE parameter에 TRUE를 지정할 경우 인덱스에 대한 통계정보도 생성한다.
Syntax
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT ‘FOR ALL COLUMNS SIZE 1’,
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT ‘DEFAULT’,
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Schema of table to analyze |
|
Tabname |
Table Name |
|
Partname |
Name of Partition |
|
Estimate_percent |
Percentage of Rows to estimate |
|
Block_sample |
Whether or not to use random block sampling instead of random row sampling |
|
Method_opt |
Options used to create histograms. |
|
Degree |
Degree of parallelism |
|
Granularity |
Granularity of statistics to collect ( only pertinet if the table is partitioned ) |
|
Cascade |
Gather statistics on the indexes for this table. |
|
Stattab |
User stat table identifier describing where to save the current statistics. |
|
Statid |
Identifier (optional) to associate with these statistics within stattab. |
|
STATOWN |
STATTAB TABLE OWNER |
Example
EXEC DBMS_STATS.GATHER_TABLE_STATS( –
OWNNAME=>’SCOTT’ –
,TABNAME=>’EMP’ –
,PARTNAME=>NULL –
,ESTIMATE_PERCENT=>25 –
,BLOCK_SAMPLE=>TRUE –
,METHOD_OPT=>’FOR ALL COLUMNS SIZE 1′ –
,DEGREE=>4 –
,GRANULARITY=>’DEFAULT’ –
,CASCADE=>TRUE –
,STATTAB=>’STATSTBL’ –
,STATID=>’EMP_011229′ –
,STATOWN=>’SCOTT’);
GATHER_INDEX_STATS
인덱스에 대한 통계정보를 생성한다.
Syntax
DBMS_STATS.GATHER_INDEX_STATS (
Ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Schema of index to analyze |
|
Indname |
Index Name |
|
Partname |
Name of Partition |
|
Estimate_percent |
Percentage of Rows to estimate |
|
Stattab |
User stat table identifier describing where to save the current statistics. |
|
Statid |
Identifier (optional) to associate with these statistics within stattab. |
Example
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>’scott’
,indname=>’emp’
,partname=>NULL
,stattab=>’STATSTBL’
,estimate_percent=>25
,statid=>’indemp_20010520′
);
DELETE_TABLE_STATS
테이블, 인덱스에 대한 통계정보를 Data Dictionary 또는 User Statistics Table로 부터 삭제한다. (Default로 테이블에 대한 통계정보를 삭제할 경우 인덱스에 대한 통계정보도 삭제된다)
Syntax
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);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Schema of index to analyze |
|
Tabname |
Index Name |
|
Partname |
Name of Partition |
|
Stattab |
User stat table identifier describing where to save the current statistics. |
|
Statid |
Identifier (optional) to associate with these statistics within stattab. |
Example
EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>NULL
,statid=>NULL
);
EXPORT_TABLE_STATS
테이블 및 인덱스에 대한 통계정보를 Data Dictionary로 부터 Export하여 User Statistics Table에 저장한다.
Syntax
DBMS_STATS.EXPORT_TABLE_STATS (
Ownname VARCHAR2,
Tabname VARCHAR2,
Partname VARCHAR2 DEFAULT NULL,
Stattab VARCHAR2 DEFAULT NULL,
Statid VARCHAR2 DEFAULT NULL,
Cascade BOOLEAN DEFAULT TRUE,
Statown VARCHAR2 DEFAULT NULL);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Schema of index to analyze |
|
Tabname |
Table Name |
|
Partname |
Name of Partition |
|
Stattab |
User stat table identifier describing where to save the current statistics. |
|
Statid |
Identifier (optional) to associate with these statistics within stattab. |
|
Cascade |
If true, then column and index statistics for this table are also exported.. |
Example
EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>’statstab’
,statid=>’emp_20010321′
,cascade=>TRUE
);
IMPORT_TABLE_STATS
User Statistics Table에 저장되어 있는 테이블 및 인덱스에 대한 통계정보를 Data Dictionary로 Import한다.
Syntax
DBMS_STATS.IMPORT_TABLE_STATS (
Ownname VARCHAR2,
Tabname VARCHAR2,
Partname VARCHAR2 DEFAULT NULL,
Stattab VARCHAR2 DEFAULT NULL,
Statid VARCHAR2 DEFAULT NULL,
Cascade BOOLEAN DEFAULT TRUE,
Statown VARCHAR2 DEFAULT NULL);
Parameter
|
Parameter |
Descrition |
|
Ownname |
Schema of index to analyze |
|
Tabname |
Table Name |
|
Partname |
Name of Partition |
|
Stattab |
User stat table identifier describing where to save the current statistics. |
|
Statid |
Identifier (optional) to associate with these statistics within stattab. |
|
Cascade |
If true, then column and index statistics for this table are also exported.. |
Example
EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>’statstab’
,statid=>’emp_20010321′
,cascade=>TRUE
);
유형별
작업사례
User Statistics Table생성
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘SCOTT’,’STATSTBL’,
‘USERS’);
- 통계정보를
저장하기
위한 User Statistics Table을
생성한다.
- SCOTT Schema에 “STATSTBL” 테이블을
생성한다. (Tablespace Name: “USERS”)
User Statistics Table삭제
EXEC DBMS_STATS.DROP_STAT_TABLE(‘SCOTT’,’STATSTBL’)
- SCOTT Schema에서 “STATSTBL” 테이블을
삭제한다.
테이블에
대한
통계정보
생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,estimate_percent=>25
,method_opt=>
‘FOR ALL INDEXED COLUMNS’
,degree=>1
,block_sample=>TRUE
,cascade=>TRUE
,stattab=>’STATSTBL’
,statid=>’emp_20010520′
);
- SCOTT Schema내 EMP 테이블에
대한
통계정보를
생성한다.(ownname=>’scott’, tabname=>’emp’)
- Partition이
아닌
전체
테이블에
대한
통계정보를
생성한다.(partname=>NULL)
- Data Dictionary내에
테이블, 인덱스에
대한
통계정보가
이미
존재하면
새로운
통계정보를
생성하기
전에 stattab파라미터에
지정된 “STATSTBL” 테이블에
이전
통계정보를
저장한다.(stattab=>’STATSTBL’)
- Random Block Sampling 방식이
사용된다.(block_sample=>TRUE, FLASE가
사용될
경우 Random Row Sampling이
사용된다.)
- 인덱스에
대한
통계정보도
같이
생성한다.(cascade=>TRUE)
- ’emp_20010520′라는 Unique ID를
사용하여
기존
통계정보를 User Statistics Table저장한다. 이 ID는
후에
삭제
또는 Data Dictionary로 Import시에
사용된다.
테이블의
특정 PARTITION에
대한
통계정보
생성
EXEC DBMS_STATS.GATHER_TABLE_STATS( –
OWNNAME=>’SCOTT’ –
,TABNAME=>’EMP’ –
,PARTNAME=>NULL –
,ESTIMATE_PERCENT=>25 –
,BLOCK_SAMPLE=>TRUE –
,METHOD_OPT=>’FOR ALL COLUMNS SIZE 1′ –
,DEGREE=>4 –
,GRANULARITY=>’DEFAULT’ –
,CASCADE=>TRUE –
,STATTAB=>’STATSTBL’ –
,STATID=>’EMP_011229′ –
,STATOWN=>’SCOTT’);
- 전체
테이블이
아닌 “P06” Partition에
대한
통계정보를
생성한다.(partname=>’P06′)
- “P06” Partition에
대응되는 Local Index Partition에
대한
통계정보는
생성하지
않는다.(cascade=>FALSE)
인덱스에
대한
통계정보
생성
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>’scott’
,indname=>’ix_emp’
,partname=>NULL
,estimate_percent=>25
,stattab=>’STATSTBL’
,statid=> ‘ix_emp_p06_20010520’
);
- SCOTT Schema내 IX_EMP 인덱스에
대한
통계정보를
생성한다.(ownname=>’scott’, tabname=>’ix_emp’)
- 파티션에
대한
통계정보가
이미
존재하면
새로운
통계정보를
생성하기
전에 stattab파라미터에
지정된 “STATSTBL” 테이블에
이전
통계정보를
저장한다.(stattab=>’STATSTBL’)
-
‘ix_emp_p20010520’라는 Unique ID를
사용하여
기존
통계정보를 User Statistics Table저장한다. 이 ID는
후에
삭제
또는 Data Dictionary로 Import시에
사용된다.
전체
테이블에
대한
통계정보
삭제
EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>NULL
,statid=>NULL
);
- SCOTT Schema내 EMP 테이블에
대한
통계정보를
삭제한다.(ownname=>’scott’, tabname=>’emp’)
- Partition이
아닌
전체
테이블에
대한
통계정보를
삭제한다.(partname=>NULL)
- User Statistics Table이
아닌 Data Dictionary로
부터
통계정보를
삭제한다.(stattab=>NULL) 만약 stattab에 User Statistics Table을
지정할
경우 User Statistics Table에서만
테이블에
대한
통계정보를
삭제한다.
-
Data Dictionary로
부터
통계정보를
삭제하기
때문에 statid에는 Default값을
사용한다.
테이블에
대한
통계정보 EXPORT
EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>’statstab’
,statid=>’emp_20010321′
,cascade=>TRUE
);
- Data Dictionary로
부
터SCOTT Schema내 EMP 테이블에
대한
통계정보를 EXPORT한다.(ownname=>’scott’, tabname=>’emp’)
- 특정 Partition이
아닌
전체
테이블에
대한
통계정보를 EXPORT한다.(partname=>NULL)
- EXPORT한
통계정보를 “statstab” 테이블에 “emp_20010321”라는 Key값을
가지고
저장한다.(stattab=>’statstab’, statid=>’emp_20010321′)
-
인덱스에
대한
통계정보도
같이 EXPORT한다. (cascade=>TRUE)
테이블에
대한
통계정보 IMPORT
EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname=>’scott’
,tabname=>’emp’
,partname=>NULL
,stattab=>’statstab’
,statid=>’emp_20010321′
,cascade=>TRUE
);
- SCOTT Schema내 EMP 테이블에
대한
통계정보를 IMPORT한다.(ownname=>’scott’, tabname=>’emp’)
- “statstab” User Statistics Table로
부
터 “emp_20010321”의 Key를
갖는
통계정보를 IMPORT한다.(stattab=>’statstab’, statid=>’emp_20010321′)
- 특정 Partition이
아닌
전체
테이블에
대한
통계정보를 IMPORT한다.(partname=>NULL)
-
인덱스에
대한
통계정보도
같이 IMPORT한다. (cascade=>TRUE)
참고사항
- 신규로 작업을 수행하기 전에 현재 ANALYZE 명령을 통해 생성되어 있는 통계정보를 위에서 설명한 EXPORT명령을 사용하여 저장할 것을 권고함.
- DBMS_STATS Package는 실행 중 내부적으로 다량의 SORT작업이 있으므로 실행하기 전에 다음 명령을 실행하여 RESOURCE를 충분히 할당할 경우 실행시간을 단축시킬 수 있다.
ALTER SESSION SET SORT_AREA_SIZE = XXXX;
(byte단위로 값을 지정한다.)