딕셔너리 통계정보 확인 방법(DBMS_STATS.SET_TABLE_STATS)

이 자료는 딕셔너리에 생성된 통계정보가 RDBMS가 생성한 것인지, 아니면 DBMS_STATS.SET_XXX_STATS procedure에 의해서 생성된 것인지 구분하는 방법에 대한 자료이며, DBMS_STATS package의 사용 예에 대하여 알아본다. 

Explanation 

----------- 

Oracle 8까지는 테이블, 인덱스에 대한 통계정보를 생성하기 위해 ANALYZE 명령만을 사용할 수 있었으나 Oracle 8i 버젼부터는 ANALYZE 명령 외에 DBMS_STATS Package를 제공한다. 

ANALYZE 명령을 사용할 경우에는 신규로 생성한 통계정보가 성능 저하를 유발시키더라도 이전과 같은 통계정보를 추출하기 위해서는 다시 ANALYZE 작업을 수행해야 되기 때문에 테이블의 크기가 클 경우 통계정보를 생성하는 데 많은 시간을 소비하게 된다. 

또한 일부 Partitioned Table에 대해 부정확한 통계정보를 추출할 수도 있기 때문에 Oracle 8i 부터는 DBMS_STATS Package를 사용하여 통계정보를 생성하고 관리할 것을 권고하고 있다. 

DBMS_STATS Package를 사용할 경우 통계정보에 대한 이력 관리가 가능하다. 

GATHER_TABLE_STATS, GATHER_INDEX_STATS, EXPORT_TABLE_STATS, IMPORT_TABLE_STATS procedure를 사용하여 관리가 가능하다. 

Application을 개발하는 단계 동안 통계정보를 생성하는 다양한 방법을 이용하여 SQL statement의 성능을 측정해볼 수 있다. 이 때 딕셔너리에 생성된 통계정보가 RDBMS가 생성한 것인지, USER가 정의한 것인지 확인해 볼 필요가 있을 수 있다. 

1. 딕셔너리 통계정보를 생성하기 위해서는 다음과 같은 방법들을 사용할 수 있다. 

1) RDBMS-generated 

ANALYZE SQL command 

DBMS_UTILITY.ANALYZE_SCHEMA procedure 

DBMS_UTILITY.ANALYZE_DATABASE procedure 

DBMS_DDL.ANALYZE_OBJECT procedure 

8.1 DBMS_STATS.GATHER_XXX_STATS procedure 

2) USER-generated 

DBMS_STATS.SET_XXX_STATS procedure에 의해 user가 정의하는 방법 

2. 각 OBJECT들에 대한 통계정보 생성 방법 

1) TABLE에 대한 통계정보 생성 

- ANALYZE TABLE ... COMPUTE/ESTIMATE STATISTICS 

or 

- DBMS_STATS.SET_TABLE_STATS procedure 

2) INDEX에 대한 통계정보 생성 

- ANALYZE TABLE/INDEX ... COMPUTE/ESTIMATE STATISTICS 

or 

- DBMS_STATS.SET_INDEX_STATS procedure 

3) COLUMN에 대한 통계정보 생성 

- ANALYZE TABLE ... COMPUTE/ESTIMATE STATISTICS 

or 

- DBMS_STATS.SET_COLUMN_STATS procedure 

3. 딕셔너리 뷰 정보 

DBA_TABLES, ALL_TABLES, USER_TABLES 뷰를 보면 USER_STATS라는 컬럼이 있다. 

YES : 통계정보가 USER에 의해 직접 생성된 경우 

NO : 통계정보가 ANALYZE command를 통해서 RDBMS에 의해 생성된 경우 

다음에 나열한 뷰들은 통계정보에 대해 같은 컬럼 정보들을 갖는다. 

1) TABLE 

DBA_ALL_TABLES : object와 연관된 table 통계정보 

DBA_OBJECT_TABLES : object table 통계정보 

DBA_TAB_PARTITIONS : table partition 통계정보 

DBA_TAB_SUBPARTITIONS : table subpartition 통계정보 

2) INDEX 

DBA_INDEXES : index 통계정보 

DBA_IND_PARTITIONS : index partition 통계정보 

DBA_IND_SUBPARTITIONS : index subpartition 통계정보 

3) COLUMN 

DBA_TAB_COLUMNS : table column 통계정보 

위와 연관된 USER_% 뷰와 ALL_% 뷰 내에도 같은 통계정보가 저장된다. 

Example 

------- 

이 테스트는 Oracle 8i Enterprise Edition Release V8.1.7.4.0에서 이루어졌다. 

1. Table에 대한 example 

1) COMPUTE STATISTICS option으로 table을 ANALYZE하기 

SQL> analyze table scott.emp compute statistics; 

Table analyzed. 

SQL> select table_name,num_rows,blocks,avg_row_len,user_stats 

FROM dba_tables 

where table_name='EMP'; 

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN USER_STATS 

----------------- -------- ------ ----------- ---------- 

EMP 14 1 40 NO 

2) SET_TABLE_STATS procedure를 사용하여 통계정보 생성하기 

SQL> execute sys.dbms_stats.set_table_stats( - 

ownname => 'SCOTT', - 

tabname => 'EMP', - 

partname => null, - 

stattab => null, - 

statid => null, - 

numrows => 100, - 

numblks => 10, - 

avgrlen => 33); 

PL/SQL procedure successfully completed. 

SQL> SELECT table_name,num_rows,blocks,avg_row_len,user_stats 

FROM dba_tables 

where table_name='EMP'; 

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN USER_STATS 

----------------- -------- ------ ----------- ---------- 

EMP 100 10 33 YES 

2. Index에 대한 example 

1) COMPUTE STATISTICS option으로 index를 ANALYZE하기 

SQL> analyze index pk_emp compute statistics; 

Index analyzed. 

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, 

CLUSTERING_FACTOR, NUM_ROWS 

from dba_indexes 

where table_name='EMP'; 

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS 

---------- ---------- ------ ----------- ----------------- -------- 

PK_EMP NO 0 1 1 14 

2) SET_INDEX_STATS procedure를 사용하여 통계정보 생성하기 

SQL> execute sys.dbms_stats.set_index_stats ( - 

ownname => 'SCOTT', - 

indname => 'PK_EMP', - 

numrows => 100 , - 

numlblks => 10, - 

clstfct => 4, - 

indlevel => 3); 

PL/SQL procedure successfully completed. 

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, 

CLUSTERING_FACTOR, NUM_ROWS 

from dba_indexes where table_name='EMP'; 

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS 

---------- ---------- ------ ----------- ----------------- -------- 

PK_EMP YES 3 10 4 100 

주의 

---- 

만약, index 또는 전체 table을 reanalyze하면 비록 통계정보가 RESET 되어도 USER_STATS는 여전히 YES로 나타난다. 

SQL> analyze index pk_emp compute statistics; 

Index analyzed. 

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, 

CLUSTERING_FACTOR, NUM_ROWS 

from dba_indexes where table_name='EMP'; 

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS 

---------- ---------- ------ ----------- ----------------- -------- 

PK_EMP YES 0 1 1 14 

SQL> analyze table emp compute statistics; 

Table analyzed. 

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, 

CLUSTERING_FACTOR, NUM_ROWS 

from dba_indexes where table_name='EMP' 

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS 

---------- ---------- ------ ----------- ----------------- -------- 

PK_EMP YES 0 1 1 14 

3. Column에 대한 example 

1) COMPUTE STATISTICS option으로 ANALYZE하기 

SQL> select num_distinct, num_nulls, avg_col_len, user_stats 

from dba_tab_columns 

where table_name='EMP' and column_name= 'SAL'; 

NUM_DISTINCT NUM_NULLS AVG_COL_LEN USER_STATS 

------------ --------- ----------- ---------- 

12 0 3 NO 

2) SET_COLUMN_STATS procedure를 사용하여 통계정보 생성하기 

SQL> execute sys.dbms_stats.set_column_stats(- 

ownname => 'SCOTT', - 

tabname => 'EMP',- 

colname => 'SAL', - 

distcnt => 1500, - 

nullcnt => 1, - 

avgclen => 9); 

PL/SQL procedure successfully completed. 

SQL> select num_distinct, num_nulls, avg_col_len, user_stats 

from dba_tab_columns 

where table_name='EMP' and column_name= 'SAL'; 

NUM_DISTINCT NUM_NULLS AVG_COL_LEN USER_STATS 

------------ --------- ----------- ---------- 

1500 1 9 YES