파티션키를 포함한 글로벌 인덱스가 있는 대용량 파티션인 경우 발생 가능한데 관련 테스트 입니다.
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
댓글 쓰기