이번 시간에는 파티션 테이블의 통계정보 운영관리 방법에 대해 소개하겠다.
단 소개하기전에 사전에 미리 언급해 둘 사항이 있다.
지금 필자가 소개하는 이 방법이 절대 표준은 아니라는 것을 미리 말씀드리고 싶다.
그럼 표준도 아닌데 왜 소개를 하냐 하겠지만...
필자가 운영을 위해 고민하고 고민한 결과 우리 사이트는 이 방법을 선택했고 지금 이런 방법으로 아주 안정적으로 잘 운영하고 있기 때문에 필자와 같은 고민을 하는 사람들을 위해 공유하고픈 마음이다.
그럼 시작하도록 하겠다.
먼저 파티션 테이블은 일반테이블과 달리 통계정보 갱신을 딱 한번, 한달치의 데이터가 정상적으로 들어갔을때만 실행하고 평상시에는 통계정보 lock 을 걸어둔다.
그리고 그 한달치의 파티션 통계정보를 가지고 매달 동일하게 넣어준다.
하지만 필자가 몸담은 사이트 같은 경우는 파티션 테이블만 100 여개다.
그렇기에 매달 작업하는 것은 여간 번거로운 작업일뿐더러 파티션 테이블은 관리 포인트도 많고 그만큼 무지하게 짜증난다. 그래서 필자가 몸담은 사이트 같은 경우는 매년 말 다음 년도의 1년치의 파티션을 미리 추가하고 파티션 추가작업이 끝날경우 마찬가지로 통계정보도 1년치를 미리 만들어 놓는다.
이렇게 함으로써 파티션 작업이 누락되는 상황을 최소화 하고 있고 1년에 딱 한번을 제외한 평상시에는 파티션 작업으로 인한 스트레스를 안받고 있다.
다시 한번 말씀드리지만 지금 소개한 부분은 필자가 몸담은 사이트의 관리 방식일 뿐이고 이게 정답은 아니라는 것을 말씀드린다.
혹시라도 필자가 운영하는 것보다 더 좋은 방법이 있다면 알려주길 바란다. ^^
■ 통계정보를 생성에는 2가지 방법이 있다.
첫째 ANALYZE 사용과
둘째 DBMS_STATS 패키지 사용 (오라클 8i 버전에서 새롭게 추가된 DBMS_STATS 패키지를 사용하는 방법)
의 두가지 방법이 있는데
오라클에서는 DBMS_STATS 패키지 사용을 권고한다.
■ DBMS_STATS 를 사용하면 좋은 점
- PARALLEL 을 사용할수 있다.
- 현재시점의 통계정보를 백업 하고 RECOVERY 할수 있다. (통계정보 이관시 사용한다)
■ 통계정보 이관 방법 ( 파티션 테이블 )
1. 통계정보를 담을 백업 테이블 생성
|
exec dbms_stats.create_stat_table(ownname=>'SYSTEM',stattab=>'stats_backup',tblspace=>'USERS'); |
2. 통계정보를 생성하기 전 한달치의 통계정보가 존재하는지 확인
해당 값이 NULL 이면 통계정보 생성이 안되어 있는 것임
|
SQL> select TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT, LAST_ANALYZED, NUM_ROWS from dba_tab_partitions where table_name = 'HISTORY' order by PARTITION_NAME; SQL> select TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT, LAST_ANALYZED, NUM_R OWS from dba_tab_partitions 2 where table_name = 'HISTORY' 3 order by PARTITION_NAME 4 / TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT LAST_ANA NUM_ROWS --------------- --------------- ------------------ -------- ---------- HISTORY P_200810 0 HISTORY P_200811 0 HISTORY P_200812 0 HISTORY P_200901 0 4 rows selected. |
3. 통계정보 생성 ( 현재 10월달 한달치의 데이터만 들어가 있다 )
|
exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'HISTORY', method_opt=> 'FOR ALL COLUMNS', cascade=>true , degree=>8, granularity=>'ALL'); |
4. 테이블과 인덱스에 통계정보 존재하는지 확인
|
SQL> select TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT, LAST_ANALYZED, NUM_R OWS from dba_tab_partitions 2 where table_name = 'HISTORY' 3 order by PARTITION_NAME; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT LAST_ANA NUM_ROWS --------------- --------------- ------------------ -------- ---------- HISTORY P_200810 0 HISTORY P_200811 0 HISTORY P_200812 0 HISTORY P_200901 0 |
5. 생성된 통계정보를 운영DB에 import 하기위해 export 실행
|
EXEC DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'HISTORY', STATTAB=> 'STATS_BACKUP', STATID=> 'STATS_BACKUP',STATOWN=>'SYSTEM', cascade=>true); |
6. 현재달을 제외한 0건으로 생선된 통계정보 삭제
|
delete from system.STAT_BACKUP where c1 in ('HISTORY', 'HISTORY_IDX') and c2 in ( 'P_200811'); commit |
7. 10 월달 통계정보를 11월달로 UPDATE
|
update system.STATS_BACKUP SET c2 = 'P_200811' where c1 in ('HISTORY','HISTORY_IDX') and c2 = 'P_200810'; commit; |
8. 파티션 통계정보를 UNLOCK
|
exec dbms_stats.unlock_table_stats('SCOTT','HISTORY'); |
9. 새로운 파티션으로 IMPORT 작업
|
Exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'HISTORY',partname=>'P_200811', stattab=>'STATS_BACKUP', statid=>'STATS_BACKUP', statown=>'SYSTEM',cascade=>true); |
10. 파티션 통계정보를 LOCK
|
exec dbms_stats.lock_table_stats('SCOTT','HISTORY'); |
11. IMPORT 한 통계정보 테이블에서 EMP 테이블의 통계정보를 IMPORT 한다..
|
SQL> select TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT, LAST_ANALYZED, NUM_R OWS from dba_tab_partitions 2 where table_name = 'HISTORY' 3 order by PARTITION_NAME; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT LAST_ANA NUM_ROWS --------------- --------------- ------------------ -------- ---------- HISTORY P_200810 0 HISTORY P_200811 0 HISTORY P_200812 0 HISTORY P_200901 0 |
