테이블 파티션 통계정보 갱신 시 인덱스 글로벌 통계정보가 NULL로 변경되는 경우
테스트입니다.
이 경우 플랜 변경 가능성이 아주 커 주의가 필요 합니다.
파티션 SPLIT 후 통계정보 갱신
SPIT 후 인덱스 글로벌 통계 변경은 없지만 파티션 통계정보 갱신 시 인덱스
글로벌 통계 NULL로 변경됨
-- 테이블 통계정보 조회
SELECT
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.table_name = 'P_TAB1'
ORDER BY owner, table_name, partition_position NULLS first
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:42:12.000|NO | |NO |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 08:19:13.000|YES | |NO |
5 row(s) fetched.
-- 인덱스 통계정보 조회
SELECT
a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_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.table_name = 'P_TAB1'
ORDER BY owner, index_name, partition_position NULLS FIRST
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 08:24:49.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 09:41:35.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 08:19:13.000|YES | |NO |
6 row(s) fetched.
-- 파티션 SPLIT
ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) UPDATE indexes
0 row(s) modified.
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:42:12.000|NO | |NO |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | | |NO | | |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 08:24:49.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 09:41:35.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | | |NO | | |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P_MAX 파티션 통계정보 갱신
EXEC dbms_stats.gather_table_stats (ownname => 'SK', tabname => 'P_TAB1', partname => 'P_MAX', granularity => 'partition') ;
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:43:13.000|NO | |NO |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | | |NO | | |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 09:43:13.000|YES | |NO |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 08:24:49.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | | |NO | | |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:34.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | | |NO | | |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 09:43:13.000|YES | |NO |
7 row(s) fetched.
파티션 split 후 통계정보 복사
split 시 변경없지만 신규 파티션에 통계정보 복사 시 인덱스 글로벌 통계가
NULL로 변경되며 모든 파티션에 통계정보 복사 완료 시 인덱스 글로벌 통계
채워짐
-- 테이블 통계정보 조회
SELECT
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.table_name = 'P_TAB1'
ORDER BY owner, table_name, partition_position NULLS first
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:54:48.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 09:54:48.000|YES | |NO |
5 row(s) fetched.
-- 인덱스 통계정보 조회
SELECT
a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_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.table_name = 'P_TAB1'
ORDER BY owner, index_name, partition_position NULLS FIRST
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 09:54:48.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 09:54:48.000|YES | |NO |
6 row(s) fetched.
-- 파티션 SPLIT
ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) UPDATE indexes
0 row(s) modified.
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:54:48.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | | |NO | | |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 09:54:48.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | | |NO | | |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P1000 파티션 통계정보 P4000 파티션으로 복사
EXEC dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P4000');
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 4000|2024-09-16 09:56:11.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | | |NO | | |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P1000 파티션 통계정보 P_MAX 파티션으로 복사
EXEC dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P_MAX');
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 5000|2024-09-16 09:56:55.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 5000|2024-09-16 09:56:55.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
7 row(s) fetched.
파티션 split 후 통계정보 복사 + max 파티션 통계 수집
모든 파티션 통계정보 채워질 때 인덱스 글로벌 통계도 채워짐
-- 테이블 통계정보 조회
SELECT
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.table_name = 'P_TAB1'
ORDER BY owner, table_name, partition_position NULLS first
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 09:59:59.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 09:59:59.000|YES | |NO |
5 row(s) fetched.
-- 인덱스 통계정보 조회
SELECT
a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_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.table_name = 'P_TAB1'
ORDER BY owner, index_name, partition_position NULLS FIRST
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 09:59:59.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 09:59:59.000|YES | |NO |
6 row(s) fetched.
-- 파티션 SPLIT
ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) UPDATE indexes
0 row(s) modified.
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 10:00:51.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | | |NO | | |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 10:00:51.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | | |NO | | |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P1000 파티션 통계정보 P4000 파티션으로 복사
EXEC dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P4000');
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 4000|2024-09-16 10:01:45.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |YES |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | | |NO | | |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |YES |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P_MAX 파티션 통계정보 갱신
EXEC dbms_stats.gather_table_stats (ownname => 'SK', tabname => 'P_TAB1', partname => 'P_MAX', granularity => 'partition') ;
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 4000|2024-09-16 10:02:00.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |YES |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 10:02:00.000|YES | |NO |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 4000|2024-09-16 10:02:00.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |YES |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 10:02:00.000|YES | |NO |
7 row(s) fetched.
인덱스 통계정보 수집 후 파티션 split
인덱스 통계가 수집된 경우(GLOBAL_STATS='YES')는 파티션 통계일부가 없도고
NULL로 변경되지 않음
-- 테이블 통계정보 조회
SELECT
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.table_name = 'P_TAB1'
ORDER BY owner, table_name, partition_position NULLS first
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 10:14:43.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 10:02:00.000|YES | |NO |
5 row(s) fetched.
-- 인덱스 통계정보 조회
SELECT
a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_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.table_name = 'P_TAB1'
ORDER BY owner, index_name, partition_position NULLS FIRST
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 4000|2024-09-16 10:02:00.000|NO | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 10:02:00.000|YES | |NO |
6 row(s) fetched.
-- 인덱스 통계정보 수집
EXEC dbms_stats.gather_index_stats (ownname => 'SK', indname => 'P_TAB2_LOC') ;
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 10:14:43.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 10:02:00.000|YES | |NO |
5 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 10:15:25.000|YES | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 10:15:25.000|YES | |NO |
6 row(s) fetched.
-- 파티션 SPLIT
ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) UPDATE indexes
0 row(s) modified.
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 3000|2024-09-16 10:14:43.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | | |NO | | |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 10:15:25.000|YES | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | | |NO | | |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P1000 파티션 통계정보 P4000로 복사
EXEC dbms_stats.copy_table_stats('SK', 'P_TAB1', srcpartname=>'P1000', dstpartname=>'P4000');
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 4000|2024-09-16 10:18:19.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | | |NO | | |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 10:15:25.000|YES | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | | |NO | | |
7 row(s) fetched.
-- P_MAX 파티션 통계정보 갱신
EXEC dbms_stats.gather_table_stats (ownname => 'SK', tabname => 'P_TAB1', partname => 'P_MAX', granularity => 'partition') ;
-- 테이블 통계정보 조회
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB1 | |TABLE | 4000|2024-09-16 10:18:48.000|NO | |YES |
SK |P_TAB1 |P1000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P2000 |PARTITION | 1000|2024-09-16 09:41:32.000|YES | |NO |
SK |P_TAB1 |P3000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P4000 |PARTITION | 1000|2024-09-16 09:41:18.000|YES | |NO |
SK |P_TAB1 |P_MAX |PARTITION | 0|2024-09-16 10:18:48.000|YES | |NO |
6 row(s) fetched.
-- 인덱스 통계정보 조회
OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_TAB2_GBL| |INDEX | 3000|2024-09-16 09:55:19.000|YES | |YES |
SK |P_TAB2_LOC| |INDEX | 3000|2024-09-16 10:15:25.000|YES | |YES |
SK |P_TAB2_LOC|P1000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P2000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P3000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P4000 |PARTITION | 1000|2024-09-16 10:15:25.000|YES | |NO |
SK |P_TAB2_LOC|P_MAX |PARTITION | 0|2024-09-16 10:18:48.000|YES | |NO |
7 row(s) fetched.
댓글 쓰기