Techdata VINA 지식 공유

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

Example

EXEC DBMS.CREATE_STAT_TABLE('SCOTT','STATSTBL','USERS');

DROP_STAT_TABLE

통계정보를 저장했던 테이블을 삭제한다.

Syntax

DBMS_STATS.DROP_STAT_TABLE (

Ownname VARCHAR2,

Stattab VARCHAR2);

Parameter

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

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

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

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

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

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단위로 값을 지정한다.)