[ORACLE] 통계정보 복사(dbms_stats.copy_table_stats) 진행 시 자동백업 테스트
dbms_stats.copy_table_stats 사용한 통계정보 복사에서도 자동 백업되는지 테스트
입니다.
테스트 전 확인
-- 통계정보 확인
SELECT
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first
OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
| |SK |STAT_TAB | |TABLE | | |NO | | |
| |SK |P_TAB1 | |TABLE | 3000|2024-09-16 05:50:46.000|YES | |YES |
76526| 76557|SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 05:49:58.000|YES | |NO |
76527| 76558|SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 05:50:46.000|YES | |NO |
76528| 76559|SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 05:49:58.000|YES | |NO |
76529| 76601|SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 07:45:03.000|YES | |NO |
6 row(s) fetched.
-- 통계정보 자동백업 테이블 조회
SELECT * FROM SYS.WRI$_OPTSTAT_TAB_HISTORY
WHERE obj# = '76529'
ORDER BY savtime
OBJ# |SAVTIME |FLAGS|ROWCNT|BLKCNT|AVGRLN|SAMPLESIZE|ANALYZETIME |CACHEDBLK|CACHEHIT|LOGICALREAD|SPARE1|IM_IMCU_COUNT|IM_BLOCK_COUNT|SCANRATE|SPARE2|SPARE3|SPARE4|SPARE5|SPARE6|
-----+-----------------------------+-----+------+------+------+----------+-----------------------+---------+--------+-----------+------+-------------+--------------+--------+------+------+------+------+------+
76529|2024-09-16 07:45:03.094 +0900| 10| 1000| 1006| 8| 1000|2024-09-16 05:49:58.000| | | | 0| | | 0| | | | | |
통계정보 복사 시 백업여부 테스트
통계정보 복사 작업에서도 자동 백업 정상 작동
-- P1000 파티션 통계 P_MAX로 복사
exec dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P_MAX');
-- 통계정보 확인
SELECT
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first
OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
| |SK |STAT_TAB | |TABLE | | |NO | | |
| |SK |P_TAB1 | |TABLE | 3000|2024-09-16 05:50:46.000|YES | |YES |
76526| 76557|SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 05:49:58.000|YES | |NO |
76527| 76558|SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 05:50:46.000|YES | |NO |
76528| 76559|SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 05:49:58.000|YES | |NO |
76529| 76601|SK |P_TAB1 |P_MAX |PARTITION | 1000|2024-09-16 05:49:58.000|YES | |NO |
6 row(s) fetched.
-- 통계정보 자동백업 테이블 조회
SELECT * FROM SYS.WRI$_OPTSTAT_TAB_HISTORY
WHERE obj# = '76529'
ORDER BY savtime
OBJ# |SAVTIME |FLAGS|ROWCNT|BLKCNT|AVGRLN|SAMPLESIZE|ANALYZETIME |CACHEDBLK|CACHEHIT|LOGICALREAD|SPARE1|IM_IMCU_COUNT|IM_BLOCK_COUNT|SCANRATE|SPARE2|SPARE3|SPARE4|SPARE5|SPARE6|
-----+-----------------------------+-----+------+------+------+----------+-----------------------+---------+--------+-----------+------+-------------+--------------+--------+------+------+------+------+------+
76529|2024-09-16 07:45:03.094 +0900| 10| 1000| 1006| 8| 1000|2024-09-16 05:49:58.000| | | | 0| | | 0| | | | | |
76529|2024-09-16 07:45:38.963 +0900| 10| 0| 0| 0| 0|2024-09-16 07:45:03.000| | | | 0| | | 0| | | | | |
-- 동일 복사 작업 재수행
exec dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P_MAX');
-- 통계정보 자동백업 테이블 조회
SELECT * FROM SYS.WRI$_OPTSTAT_TAB_HISTORY
WHERE obj# = '76529'
ORDER BY savtime
OBJ# |SAVTIME |FLAGS|ROWCNT|BLKCNT|AVGRLN|SAMPLESIZE|ANALYZETIME |CACHEDBLK|CACHEHIT|LOGICALREAD|SPARE1|IM_IMCU_COUNT|IM_BLOCK_COUNT|SCANRATE|SPARE2|SPARE3|SPARE4|SPARE5|SPARE6|
-----+-----------------------------+-----+------+------+------+----------+-----------------------+---------+--------+-----------+------+-------------+--------------+--------+------+------+------+------+------+
76529|2024-09-16 07:45:03.094 +0900| 10| 1000| 1006| 8| 1000|2024-09-16 05:49:58.000| | | | 0| | | 0| | | | | |
76529|2024-09-16 07:45:38.963 +0900| 10| 0| 0| 0| 0|2024-09-16 07:45:03.000| | | | 0| | | 0| | | | | |
76529|2024-09-16 07:47:56.084 +0900| 10| 1000| 1006| 8| 1000|2024-09-16 05:49:58.000| | | | 0| | | 0| | | | | |
댓글
댓글 쓰기