이 자료는 딕셔너리에 생성된 통계정보가 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