ORACLE 11g Dictionary Views and Stats for Partitioned table v1.0
|
2013.01.03 |
1.0 |
|
|
|
|
|
|
|
|
|
1. Dictionary Views and Stats for Partitioned table
이 문서에서는 다음과 같은 내용을 다루려 한다.
l Composite Partitioned Table ( list-range ) 과 인덱스를 생성
l 시스템 레벨에서의 통계정보 수집 정책을 확인
l 테이블 레벨에서 통계정보 수집 정책을 확인
l 파티션 테이블 구조 및 통계정보 조회하기
l 파티션 테이블과 연관된 인덱스 및 통계정보 조회하기
l 배치 및 신규 파티션 추가시 조치사항
여기서는 Global, Local Index, prefix, non-prefix 인덱스에 대한 내용은 다루지 않겠다.
2. Composite Partitioned Table ( list-range ) 과 인덱스를 생성
|
create table range_list_tab ( id number(8), rand1 number(2), rand2 number(20), dttm varchar2(21)) partition by range(id) subpartition by list (rand1) (partition range_tab_100000 values less than (100000) (SUBPARTITION range_tab_100000_1 values (1), SUBPARTITION range_tab_100000_2 values (2), SUBPARTITION range_tab_100000_3 values (3)), partition range_tab_200000 values less than (200000) (SUBPARTITION range_tab_200000_1 values (1), SUBPARTITION range_tab_200000_2 values (2), SUBPARTITION range_tab_200000_3 values (3))) enable row movement; |
list-range 파티션 테이블로 파티션 키는 id + rand1 이다. id 는 Unique 값으로 range partition 의 키컬럼이다. rand1 은 추후 insert 에서 1 ~ 3 의 값을 랜덤으로 입력 예정이며 이는 list partition 의 키컬럼이 된다.
rand2 컬럼은 1 ~ 99999999999999999999 의 값을 랜덤하게 입력받을 예정이다.
|
declare begin for i in 1..199999 loop insert into range_list_tab values (i, trunc(dbms_random.value(1,4)), trunc(dbms_random.value(1,99999999999999999999)), to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')); end loop; end; / |
테스트 데이터를 for loop 를 이용해 입력한다.
|
create unique index range_list_tab_pk on range_list_tab(id, rand1) local;
alter table range_list_tab add constraint range_list_tab_pk primary key (id,rand1);
create index range_list_tab_ix1 on range_list_tab(rand2, rand1) local;
create index range_list_tab_ix2 on range_list_tab(id, rand2) local;
create index range_list_tab_ix3 on range_list_tab(rand2) local;
create index range_list_tab_ix4 on range_list_tab(dttm); |
id, rand1 파티션 키 컬럼에 대해 primary key index 를 생성하고, 추가로 인덱스를 생성했다.
3. 시스템 레벨에서의 통계정보 수집 정책을 확인
11g 에서는 자동화된 작업으로 oracle 이 통계정보를 수집한다.
이 내용에 대해서는 dba_autotask_operation 을 조회함으로써 확인 가능하다.
|
SQL> SELECT client_name, status FROM dba_autotask_operation;
CLIENT_NAME STATUS ----------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED |
위의 auto optimizer stats collection 이 위에 언급한 통계정보 수집 작업이며, 이의 활성화 / 비활성화는 다음과 같이 수행 가능하다.
|
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / |
흔히 통계정보 수집을 수동으로 하는 경우 DBMS_STATS.GATHER_TABLE_STATS 를 사용하게 되는데 별도 셋팅하지 않는 경우 default 값을 가져가게 된다. 이 default 값은 변수로 사전 셋팅이 가능한 것들로 시스템 레벨의 정책으로 볼 수도 있다.
|
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type(get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE); |
dbms_stats 의 다른 메소드는 살펴보지 않겠다.
위 내용중에 Bold 로 표시한 변수의 내용을 확인하면 다음과 같다. (ORACLE default 값이다.)
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE // ORACLE 이 샘플링 % 를 자동 결정
METHOD_OPT => FOR ALL COLUMNS SIZE AUTO // ORACLE 이 Column 통계수집과 히스토그램 생성을 컨트롤
DEGREE => NULL
GRANULARITY => AUTO // 해당 파라미터는 파티션 테이블에 대해 적용되는 옵션이다.
AUTO 는 통계정보 수집단위를 ORACLE 이 판단한다는 의미이다.
- ALL : subpartition + partition + global statistics
- AUTO
- DEFAULT : OBSOLETE OPTION
- GLOBAL : global statistics
- GLOBAL AND PARTITION : partition + global statistics
- PARTITION : partition statistics
- SUBPARTITION : subpartition-level statistics
- PUBLISH : /*+ 11.1 이전에는 통계정보 수집이 완료되면 dictionary 에 바로 적용이 되었다. 하지만 이후 버전부터는 수집된 통계정보를 바로 적용하는게 아니라 컨트롤이 가능하다 (pending) */
- INCREMENTAL : Incremental Global Stats 를 사용한다. 다음의 환경이 구성되어 있어야 한다.
n 파티션 테이블에 대한 INCREMENTAL 값이 TRUE
n 파티션 테이블에 대한 PUBLISH 값이 TRUE
n 통계 수집 시 ESTIMATE_PERCENT = AUTO_SAMPLE_SIZE, GRANULARITY = AUTO 를 사용
CASCADE => DBMS_STATS.AUTO_CASCADE // Index 를 통계수집 과정의 하나로 진행할지 말지를 ORACLE 이 판단.
NO_INVALIDATE => DBMS_STATS.AUTO_INVALIDATE // 통계정보와 관련있는 커서를 언제 INVALIDATE 할 지 ORACLE 이 판단한다.
4. 테이블 레벨에서 통계정보 수집 정책을 확인
위에 언급한 DBMS_STATS.GATHER_TABLE_STATS 에서 DEFAULT 값으로 DBMS_STATS 의 변수를 사용하는 항목들이 있었고, 이러한 항목들은 스키마 + 테이블 레벨로 셋팅이 가능하다.
/*+ 통계정보 레벨에 따른 의존성에 대해서는 여기선 언급하지 않겠다. ex) DB, SCHEMA, TABLE */
테이블 레벨로 셋팅 된 정보는 다음의 쿼리로 확인할 수 있다.
/*+ 다른 확인 방법이 있는지 추가 조사할 것 */
|
select 'AUTOSTATS_TARGET=>'||dbms_stats.get_prefs('AUTOSTATS_TARGET','TEST','RANGE_LIST_TAB') contents from dual union all select 'CASCADE=>'||dbms_stats.get_prefs('CASCADE','TEST','RANGE_LIST_TAB') contents from dual union all select 'DEGREE=>'||dbms_stats.get_prefs('DEGREE','TEST','RANGE_LIST_TAB') contents from dual union all select 'ESTIMATE_PERCENT=>'||dbms_stats.get_prefs('ESTIMATE_PERCENT','TEST','RANGE_LIST_TAB') contents from dual union all select 'METHOD_OPT=>'||dbms_stats.get_prefs('METHOD_OPT','TEST','RANGE_LIST_TAB') contents from dual union all select 'NO_INVALIDATE=>'||dbms_stats.get_prefs('NO_INVALIDATE','TEST','RANGE_LIST_TAB') contents from dual union all select 'GRANULARITY=>'||dbms_stats.get_prefs('GRANULARITY','TEST','RANGE_LIST_TAB') contents from dual union all select 'PUBLISH=>'||dbms_stats.get_prefs('PUBLISH','TEST','RANGE_LIST_TAB') contents from dual union all select 'INCREMENTAL=>'||dbms_stats.get_prefs('INCREMENTAL','TEST','RANGE_LIST_TAB') contents from dual union all select 'STALE PERCENT=>'||dbms_stats.get_prefs('STALE_PERCENT','TEST','RANGE_LIST_TAB') contents from dual; |
위 쿼리의 결과로 다음과 같은 내용을 확인할 수 있었다.
|
CONTENTS -------------------------------------------------------------------------------- AUTOSTATS_TARGET=>AUTO CASCADE=>DBMS_STATS.AUTO_CASCADE DEGREE=>NULL ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE METHOD_OPT=>FOR ALL COLUMNS SIZE AUTO NO_INVALIDATE=>DBMS_STATS.AUTO_INVALIDATE GRANULARITY=>AUTO PUBLISH=>TRUE INCREMENTAL=>FALSE STALE PERCENT=>10 |
5. 파티션 테이블 구조 및 통계정보 조회하기
파티션 테이블 구조를 확인하기 위해 다음과 같은 질문을 할 수 있다.
l 파티션 키 컬럼은 어떻게 되는가?
l 테이블에 구성된 인덱스는 어떤게 있는가?
위 질문과 연관해 다음의 view 를 사용할 수 있다.
dba_part_key_columns
dba_subpart_key_columns
dba_indexes
dba_ind_partition
|
SQL> select name, object_type, column_name 2 from dba_part_key_columns 3 where name = 'RANGE_LIST_TAB';
NAME OBJECT_TYPE COLUMN_NAME -------------------- --------------- -------------------- RANGE_LIST_TAB TABLE ID |
위와같이 partition key 에 대해 조회가능하다.
|
SQL> select name, object_type, column_name 2 from dba_subpart_key_columns 3 where name = 'RANGE_LIST_TAB';
NAME OBJECT_TYPE COLUMN_NAME -------------------- --------------- -------------------- RANGE_LIST_TAB TABLE RAND1 |
위와같이 subpartition key 에 대해 조회가능하다.
composite 의 경우 subpartition key 에 대한 정보가 있지만 기본 partitioned table 은 그렇지 않다.
6. 파티션 테이블과 연관된 인덱스 및 통계정보 조회하기
테이블에 구성된 인덱스에 대해서는 다음과 같이 조회가능하다.
|
SQL> select index_name, partitioned from dba_indexes 2 where table_name = 'RANGE_LIST_TAB';
INDEX_NAME PAR ------------------------------ --- RANGE_LIST_TAB_PK YES RANGE_LIST_TAB_IX1 YES RANGE_LIST_TAB_IX2 YES RANGE_LIST_TAB_IX3 YES RANGE_LIST_TAB_IX4 NO |
위의 index 를 구성하는 컬럼에 대해서는 dba_ind_columns 에서 조회할 수 있다.
자세한 내용은 생략하겠다.
통계정보는 다음과 같은 dictionary view 를 사용하여 조회할 수 있다.
l DBA_IND_STATISTICS
l DBA_TAB_STATISTICS
7. 배치 및 신규 파티션 추가시 조치사항
파티션 세그먼트가 신규로 추가되거나, 기존의 세그먼트에 대량의 배치가 일어난 경우 통계정보 재수집이 필요하다.
다음의 쿼리로 결과값의 변화추이를 확인해 보겠다.
|
select table_name, partition_name, subpartition_name, num_rows, to_char(last_analyzed, 'hh24:mi:ss') time, stale_stats from dba_tab_statistics where table_name = 'RANGE_LIST_TAB'; |
<신규 파티션 세그먼트 추가시>
|
select index_name, partitioned from dba_indexes where table_name = 'RANGE_LIST_TAB'; |
위와 같이 파티션 추가 이후 다음과 같이 결과가 나온다.
|
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS TIME STA ------------------------------ ------------------------------ ------------------------------ ---------- -------- --- RANGE_LIST_TAB 199999 06:00:07 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_LIST_TAB RANGE_TAB_100000 99999 06:00:20 NO RANGE_LIST_TAB RANGE_TAB_200000 100000 06:00:21 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_1 RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_2 RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_3 RANGE_LIST_TAB RANGE_TAB_100000 RANGE_TAB_100000_1 33428 06:00:23 NO
~~~~~ 중략 ~~~~~~ |
통계정보가 비어있는 테이블은 새로 추가된 RANGE_TAB_300000 파티션과 연관된 정보들이다.
subpartition 에 대한 정보 3개와 partition 에 대한 정보 1개가 비어있다.
이 정보는 다른 파티션 세그먼트로부터 복제하는 것도 가능하다.
흔히 range 로 partitioned 된 table 을 생각할 때 보통 이력으로 관리하는 경우가 많고 시간 순서상 이전 이력의 파티션 세그먼트와 데이터의 통계정보가 유사할 가능성이 크다. 이런 경우 위와같이 통계정보가 누락된 대로 두는게 아니라 다른 파티션 세그먼트로부터 복제할 수 있다.
이를 도와주는 패키지는 다음과 같다.
DBMS_STATS.COPY_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
srcpartname VARCHAR2,
dstpartname VARCHAR2,
scale_factor VARCHAR2 DEFAULT 1,
force BOOLEAN DEFAULT FALSE);
위 프로시저를 통해 소스 파티션 세그먼트로부터 타겟 파티션 세그먼트로 통계정보 복제가 가능하다.
RANGE_TAB_200000 로부터 RANGE_TAB_300000 으로 복제한다 했을 때 실제 사용은 다음과 같다.
|
SQL> exec DBMS_STATS.COPY_TABLE_STATS(ownname=>'TEST',tabname=>'RANGE_TAB',srcpartname=>'RAN GE_TAB_200000',dstpartname=>'RANGE_TAB_300000');
PL/SQL procedure successfully completed. |
위 결과로 앞서 봤던 통계정보에 대한 결과에 공백이 사라졌다. (이를 테면 NUM_ROWS 부분)
하지만 신규로 추가된 테이블에 대한 rows 가 0으로 나온다. 통계정보를 복제했음에도 불구하고 NUM_ROWS 가 0 건으로 나오는 걸 보아 오라클은 해당 파티션 세그먼트에 데이터가 입력된 적이 없다는 걸 아는 것으로 추정된다.
|
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS TIME STA ------------------------------ ------------------------------ ------------------------------ ---------- -------- --- RANGE_LIST_TAB 199999 06:00:07 NO RANGE_LIST_TAB RANGE_TAB_300000 0 22:03:02 NO RANGE_LIST_TAB RANGE_TAB_100000 99999 06:00:20 NO RANGE_LIST_TAB RANGE_TAB_200000 100000 06:00:21 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_1 0 22:03:02 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_2 0 22:03:02 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_3 0 22:03:03 NO RANGE_LIST_TAB RANGE_TAB_100000 RANGE_TAB_100000_1 33428 06:00:23 NO RANGE_LIST_TAB RANGE_TAB_100000 RANGE_TAB_100000_2 33209 06:00:23 NO RANGE_LIST_TAB RANGE_TAB_100000 RANGE_TAB_100000_3 33362 06:00:23 NO RANGE_LIST_TAB RANGE_TAB_200000 RANGE_TAB_200000_1 33218 06:00:24 NO RANGE_LIST_TAB RANGE_TAB_200000 RANGE_TAB_200000_2 33260 06:00:24 NO RANGE_LIST_TAB RANGE_TAB_200000 RANGE_TAB_200000_3 33522 06:00:24 NO |
어느정도 rows 가 들어가야 값이 온전하게 복제되는가 확인을 위해 다음을 수행 후 복제를 다시 수행해 보았다. 프로시저의 내용은 RANGE_TAB_300000 파티션 세그먼트에 데이터를 넣어준다.
|
declare begin for i in 200000..299999 loop insert into range_list_tab values (i, trunc(dbms_random.value(1,4)), trunc(dbms_random.value(1,99999999999999999999)), to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')); end loop; end; / |
수행 이후 통계정보를 조회했을 때 아까와 마찬가지로 신규 생성 된 파티션에 대해 통계정보가 공백인 걸 확인할 수 있었다.
|
exec DBMS_STATS.COPY_TABLE_STATS(ownname=>'TEST',tabname=>'RANGE_LIST_TAB',srcpartname=>'RANGE_TAB_200000',dstpartname=>'RANGE_TAB_300000'); |
PARTITION => PARTITION 으로 통계정보를 복제 후 통계정보를 조회했을 때 아까와 다른점이 있다.
RANGE_LIST_TAB 의 RANGE_TAB_300000 파티션에 대해 통계정보가 갱신되었다.
반면 subpartition 정보는 아직도 비어있다.
이번에는 subpartition 단위로 RANGE_TAB_200000_1 에서 RANGE_TAB_300000_1 로 복제를 해 보았다.
|
RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_1 33218 06:00:24 NO RANGE_LIST_TAB RANGE_TAB_200000 RANGE_TAB_200000_1 33218 06:00:24 NO |
num_rows 와 last_analyzed 정보가 복제되어 변경됨을 확인하였다.
정말 이것이 복제된 값이 확실한가 RANGE_TAB_300000 에 대해 통계정보 수집을 해 보았다.
DBMS_STATS.GATHER_TABLE_STATS ('TEST','RANGE_LIST_TAB','RANGE_TAB_300000',100,FALSE);
|
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS TIME STA ------------------------------ ------------------------------ ------------------------------ ---------- -------- --- RANGE_LIST_TAB 299999 23:32:46 NO RANGE_LIST_TAB RANGE_TAB_300000 100000 23:32:45 NO RANGE_LIST_TAB RANGE_TAB_300000 RANGE_TAB_300000_1 33229 23:32:44 NO RANGE_LIST_TAB RANGE_TAB_200000 RANGE_TAB_200000_1 33218 06:00:24 NO |
RANGE_TAB_200000_1 과 RANGE_TAB_300000_1 의 num_rows 와 time 정보가 달라진걸 확인할 수 있었다.
한가지 특이점으로는 subpartition 에 대해 통계정보를 수집한 결과가 subpartition 을 포함하는 partition 의 통계정보, partition 을 포함하는 partitioned table 의 통계정보가 연쇄적으로 영향 받는 다는 것이다.
전체에서 부분이 변경되면 당연히도 전체에 영향이 가는 걸 생각하면 당연한 수순이기도 하다.
8. References
A. DBMS_STATS | http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#BEIBJJHC