[ORACLE] 데이터 없는 파티션 SPLIT 진행 시 오래 걸리는 상황 테스트
파티션키를 포함한 글로벌 인덱스가 있는 대용량 파티션인 경우
발생 가능한데 관련 테스트 입니다.
MAX 파티션 통계정보 수집됨
MAX 파티션 통계정보 수집되어 0건으로 되어있는 경우 해당 파티션만 엑세스하여 바로 종료됩니다
-- MAX 파티션 데이터 0건
SELECT count(*) FROM sk.p_t1 PARTITION(PMAX)
COUNT(*)|
--------+
0|
1 row(s) fetched.
-- MAX 파티션 통계정보 0건
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_T1 |P202401 |PARTITION | 986182|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202402 |PARTITION | 985025|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202403 |PARTITION | 992827|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202404 |PARTITION | 1004973|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202405 |PARTITION | 1000716|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202406 |PARTITION | 988317|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202407 |PARTITION | 1002040|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202408 |PARTITION | 997505|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202409 |PARTITION | 1028524|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202410 |PARTITION | 999600|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202411 |PARTITION | 1005808|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202412 |PARTITION | 1006256|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |PMAX |PARTITION | 0|2024-09-16 14:22:30.000|YES | |NO |
13 row(s) fetched.
-- SPLIT PARTITION
ALTER TABLE SK.P_T1 SPLIT PARTITION PMAX AT ('20260201') INTO (PARTITION P202601, PARTITION PMAX) UPDATE INDEXES ;
Table altered.
Elapsed: 00:00:00.07
-- 아래는 10046 trace의 SPLIT 시 수행되는 내부 SQL
********************************************************************************
SQL ID: gt287mxb1h4pr Plan Hash: 1470121245
select /*+ FIRST_ROWS(1) PARALLEL("P_T1", 1) */ 1
from
NO_CROSS_CONTAINER("SK"."P_T1") PARTITION ("PMAX") where ( ( ( (
"PARTITION_DATE_STR" < '20260201' ) ) ) ) and rownum < 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=47 us starts=1)
0 0 0 PARTITION RANGE SINGLE PARTITION: 109 109 (cr=3 pr=0 pw=0 time=46 us starts=1 cost=2 size=6 card=1)
0 0 0 TABLE ACCESS FULL P_T1 PARTITION: 109 109 (cr=3 pr=0 pw=0 time=42 us starts=1 cost=2 size=6 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 2 0.00 0.00
********************************************************************************
SQL ID: 24xt5s36xjhu7 Plan Hash: 1470121245
select /*+ FIRST_ROWS(1) PARALLEL("P_T1", 1) */ 1
from
NO_CROSS_CONTAINER("SK"."P_T1") PARTITION ("PMAX") where ( ( ( (
"PARTITION_DATE_STR" >= '20260201' OR "PARTITION_DATE_STR" IS NULL ) ) )
) and rownum < 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=14 us starts=1)
0 0 0 PARTITION RANGE SINGLE PARTITION: 109 109 (cr=3 pr=0 pw=0 time=14 us starts=1 cost=2 size=6 card=1)
0 0 0 TABLE ACCESS FULL P_T1 PARTITION: 109 109 (cr=3 pr=0 pw=0 time=11 us starts=1 cost=2 size=6 card=1)
MAX 파티션 통계정보 NULL
MAX 파티션 통계가 NULL인 경우 SPLIT 작업의 내부 SQL이 GLOBAL INDEX SCAN으로 실행계획을 세워 인덱스 SCAN 시간만큼 오래 수행됩니다.
테스트 시 통계정보 num_rows가 100건이 넘어가면 글로벌 인덱스 SCAN으로 수행되었습니다
-- MAX 파티션 데이터 0건
SELECT count(*) FROM sk.p_t1 PARTITION(PMAX)
COUNT(*)|
--------+
0|
1 row(s) fetched.
-- 파티션별 통계정보, MAX 파티션 통계 NULL
OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK |P_T1 |P202401 |PARTITION | 986182|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202402 |PARTITION | 985025|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202403 |PARTITION | 992827|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202404 |PARTITION | 1004973|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202405 |PARTITION | 1000716|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202406 |PARTITION | 988317|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202407 |PARTITION | 1002040|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202408 |PARTITION | 997505|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202409 |PARTITION | 1028524|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202410 |PARTITION | 999600|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202411 |PARTITION | 1005808|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |P202412 |PARTITION | 1006256|2024-09-16 12:51:19.000|YES | |NO |
SK |P_T1 |PMAX |PARTITION | | |NO | | |
13 row(s) fetched.
-- SPLIT PARTITION
ALTER TABLE SK.P_T1 SPLIT PARTITION PMAX AT ('20260201') INTO (PARTITION P202601, PARTITION PMAX) UPDATE INDEXES ;
Table altered.
Elapsed: 00:00:31.25
-- 아래는 10046 trace의 SPLIT 시 수행되는 내부 SQL
********************************************************************************
SQL ID: gt287mxb1h4pr Plan Hash: 4037643406
select /*+ FIRST_ROWS(1) PARALLEL("P_T1", 1) */ 1
from
NO_CROSS_CONTAINER("SK"."P_T1") PARTITION ("PMAX") where ( ( ( (
"PARTITION_DATE_STR" < '20260201' ) ) ) ) and rownum < 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 31.05 31.19 452907 452909 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 31.06 31.19 452907 452909 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=452909 pr=452907 pw=0 time=31194013 us starts=1)
0 0 0 INDEX RANGE SCAN P_T1_IX01 (cr=452909 pr=452907 pw=0 time=31194009 us starts=1 cost=224758 size=9 card=1)(object id 76762)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 2 0.00 0.00
db file sequential read 452907 0.00 0.90
********************************************************************************
SQL ID: 24xt5s36xjhu7 Plan Hash: 4037643406
select /*+ FIRST_ROWS(1) PARALLEL("P_T1", 1) */ 1
from
NO_CROSS_CONTAINER("SK"."P_T1") PARTITION ("PMAX") where ( ( ( (
"PARTITION_DATE_STR" >= '20260201' OR "PARTITION_DATE_STR" IS NULL ) ) )
) and rownum < 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 2 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 2 4 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=4 pr=2 pw=0 time=33 us starts=1)
0 0 0 INDEX RANGE SCAN P_T1_IX01 (cr=4 pr=2 pw=0 time=31 us starts=1 cost=133 size=18 card=2)(object id 76762)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
댓글
댓글 쓰기