[2025-07-05] enable_parallel_dml 힌트 (12cR1 New features) (12cR2에서 테스트)
[해당 테스트의 결론]
PDML(Parallel DML)은 대용량 DML을 빠르게 처리하지만(INSERT는 direct-path), 강한 TM 락으로 동시성 제약이 크다.
PDML은 UPDATE/DELETE/MERGE에도 적용되지만 direct-path는 INSERT에만 해당한다
테이블에 인덱스가 존재하면 PDML 성능은 급격히 저하될 수 있다.
동시성이 최우선이면 PDML/APPEND 없이(Conventional path) 적재한다.
성능이 최우선이면 인덱스를 끊고(Unusable) PDML+NOLOGGING 후 인덱스를 REBUILD한다. (APPEND 힌트를 생략 가능)
(단, FORCE LOGGING/Standby 환경, PK/UK 제약·글로벌 인덱스, 파티션 특성은 운영 정책에 맞게 처리)
[실습
환경]
OS
: Oracle Linux Server 7.9 (Linux 5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle
Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
(Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128))
RAC1 : rdb01p(192.168.0.11) : PRDB1
RAC2 :
rdb02p(192.168.0.12) : PRDB2
1. 테스트 테이블 생성 및 데이터 입력
select count(*) from tuner.TB_ORD_DTL;
--> 여기는 5천만건이 들어있는 상황임
--drop
table tuner.tb_ord_dtl_bak purge;
CREATE TABLE
TUNER.TB_ORD_DTL_bak
(
CUST_NO VARCHAR2(10) NOT
NULL,
ORD_DT VARCHAR2(14) NOT
NULL,
ORD_DTL_SN NUMBER(6)
NOT NULL,
ORD_DTL_STS_CD VARCHAR2(6) NOT
NULL,
ORD_AMT NUMBER(15,2) NOT
NULL,
ITEM_NO VARCHAR2(10) NOT
NULL,
DLVY_SN
VARCHAR2(14),
INPUT_ID VARCHAR2(20) NOT
NULL,
INPUT_DT
VARCHAR2(14) NOT NULL,
UPDT_ID
VARCHAR2(20),
UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1
PARTITION BY RANGE
(ORD_DT)
SUBPARTITION BY HASH (CUST_NO)
(
PARTITION
P_BEF_2020 VALUES LESS THAN ('20200101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2020 VALUES LESS THAN ('20210101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2021 VALUES LESS THAN ('20220101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2022 VALUES LESS THAN ('20230101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2023 VALUES LESS THAN ('20240101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2024 VALUES LESS THAN ('20250101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2025 VALUES LESS THAN ('20260101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2026 VALUES LESS THAN ('20270101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2027 VALUES LESS THAN ('20280101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2028 VALUES LESS THAN ('20290101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2029 VALUES LESS THAN ('20300101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P2030 VALUES LESS THAN ('20310101')
TABLESPACE
TUNER_DATA1
SUBPARTITIONS 8,
PARTITION P_AFT_2030 VALUES LESS THAN (MAXVALUE)
TABLESPACE TUNER_DATA1
SUBPARTITIONS
8
);
2. 전통적인 parallel dml 테스트
alter session enable parallel dml ;
alter table TUNER.TB_ORD_DTL_BAK nologging;
ALTER SESSION SET STATISTICS_LEVEL = ALL;
INSERT /*+ parallel(t 4) */ INTO TUNER.TB_ORD_DTL_BAK t
SELECT /*+
parallel(a 4) full(a) */ * FROM TUNER.TB_ORD_DTL
a;
*************************[Explain Plan Time: 2025/07/05
19:26:29]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=47K
Card=50M Bytes=4G)
1 0 PX
COORDINATOR
2 1 PX
SEND (QC (RANDOM)) OF 'SYS.:TQ10000' (Cost=47K Card=50M Bytes=4G)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
3
2 LOAD AS SELECT ((HYBRID TSM/HWMB)) OF
'TB_ORD_DTL_BAK' (PARALLEL_COMBINED_WITH_PARENT)
4 3 PX
PARTITION HASH (ALL) (Cost=47K Card=50M Bytes=4G)
(PARALLEL_COMBINED_WITH_CHILD)
5
4 TABLE ACCESS
(FULL) OF 'TB_ORD_DTL' (TABLE) (Cost=47K Card=50M Bytes=4G)
(PARALLEL_COMBINED_WITH_PARENT)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows'));
SQL_ID cm445dhy309dc, child number
0
-------------------------------------
INSERT /*+ parallel(t 4) */ INTO
TUNER.TB_ORD_DTL_BAK t SELECT /*+
parallel(a 4) full(a) */ * FROM
TUNER.TB_ORD_DTL a
Plan hash value: 1250007386
--------------------------------------------------------------------------------------------------------------------------------
|
Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers | OMem | 1Mem |
Used-Mem
|
--------------------------------------------------------------------------------------------------------------------------------
|
0 | INSERT
STATEMENT
|
| 1 | 84 |00:02:46.46
| 631 |
|
| |
| 1
| PX
COORDINATOR
|
| 1 | 84 |00:02:46.46
| 631 | 73728 | 73728
| |
| 2
| PX SEND QC
(RANDOM)
| :TQ10000 | 0
| 0 |00:00:00.01
| 0 |
|
| |
| 3
| LOAD AS SELECT (EQUI-PARTITION)| TB_ORD_DTL_BAK
| 0 | 0 |00:00:00.01
| 0 | 720K|
720K| 720K (0)|
| 4 | PX PARTITION
HASH ALL
|
| 0 | 0 |00:00:00.01
| 0 |
|
| |
| 5
| TABLE ACCESS
FULL |
TB_ORD_DTL | 0
| 0 |00:00:00.01
| 0 |
|
|
|
--------------------------------------------------------------------------------------------------------------------------------
--> LOAD AS SELECT가 QC 프로세스
하위에 속함 INSERT 연산 까지 Parallel Insert되고 있는 것임을 알 수
있음
Note
-----
- Degree of Parallelism is 4 because of
table property
SELECT
DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'cm445dhy309dc', event_detail=>'YES')
FROM DUAL;
SQL Monitoring Report
SQL
Text
------------------------------
INSERT /*+ parallel(t 4) */ INTO
TUNER.TB_ORD_DTL_BAK t SELECT /*+ parallel(a 4) full(a) */ * FROM
TUNER.TB_ORD_DTL a
Global
Information
------------------------------
Status
: DONE
Instance
ID :
1
Session
: TUNER (23:22874)
SQL
ID
: cm445dhy309dc
SQL Execution ID :
16777218
Execution Started : 07/05/2025
19:33:44
First Refresh Time : 07/05/2025
19:33:44
Last Refresh Time : 07/05/2025
19:36:30
Duration
: 166s
Module/Action
: Orange for ORACLE DBA /6.0.2
(Build:133,S)
Service
:
PRDB
Program
: OrangeMain.exe
--> 166초 소요
Global
Stats
==========================================================================================================================
|
Elapsed | Cpu | IO |
Application | Concurrency | Cluster | Other | Buffer |
Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) |
Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Gets | Reqs | Bytes | Reqs | Bytes
|
==========================================================================================================================
|
663 | 89 | 316
| 0.00
| 50
| 33 | 175
| 1M | 6186 | 5GB | 22173 | 7GB
|
==========================================================================================================================
Parallel
Execution Details (DOP=4 , Servers
Allocated=4)
===========================================================================================================================================================================================================
|
Name | Type | Server# | Elapsed
| Cpu | IO |
Application | Concurrency | Cluster | Other | Buffer |
Read | Read | Write | Write
|
Wait
Events
|
|
|
| | Time(s) | Time(s) | Waits(s)
| Waits(s) | Waits(s) | Waits(s) | Waits(s)
| Gets | Reqs | Bytes | Reqs | Bytes
|
(sample
#)
|
===========================================================================================================================================================================================================
|
PX Coordinator | QC
| | 0.27
| 0.26 |
| 0.00
|
|
| | 631
| | .
| | .
|
|
| p000 | Set 1
| 1 | 166
| 22 | 76
|
| 16
| 8.32 | 43
| 359K | 1523 | 1GB | 5040 | 1GB | gc
current grant 2-way
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | gc current multi block request
(7)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | buffer busy waits
(14)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log buffer space
(2)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch (checkpoint incomplete)
(3)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch completion
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(2)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(79)
|
| p001 | Set 1
| 2 | 165
| 22 | 75
|
| 17
| 8.56 | 42
| 358K | 1525 | 1GB | 5003 | 1GB | gc
current multi block request (13)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | buffer busy waits
(14)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log buffer space
(4)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch (checkpoint incomplete)
(3)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch completion
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(2)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(73)
|
| p002 | Set 1
| 3 | 166
| 22 | 77
|
| 16
| 7.96 | 43
| 362K | 1522 | 1GB | 5048 | 1GB | gc
current multi block request (10)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | buffer busy waits
(14)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log buffer space
(4)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch (checkpoint incomplete)
(3)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch completion
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(2)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(79)
|
| p003 | Set 1
| 4 | 166
| 23 | 87
|
| 0.45 | 8.28
| 47 | 367K | 1616 |
1GB | 7082 | 3GB | gc current multi block request
(5)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: HW - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log buffer space
(3)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch (checkpoint incomplete)
(3)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch completion
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | KSV master wait
(2)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: redo allocation
(1)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Data file init write
(11)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(6)
|
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(81)
|
===========================================================================================================================================================================================================
SQL Plan
Monitoring Details (Plan Hash
Value=1250007386)
================================================================================================================================================================================================================
|
Id
|
Operation
| Name |
Rows | Cost | Time | Start
| Execs | Rows | Read | Read | Write | Write |
Activity
|
Activity
Detail
|
|
|
|
| (Estim) | | Active(s) | Active
| | (Actual) | Reqs | Bytes | Reqs |
Bytes | (%)
|
(#
samples)
|
================================================================================================================================================================================================================
|
0 | INSERT
STATEMENT
|
|
| | 165
| +2 | 5
| 84 |
| |
| | 18.85 | gc current
grant 2-way
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | gc current multi block
request (35)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | buffer busy waits
(42)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: HW - contention
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log buffer space
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Cpu
(23)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | KSV master wait
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(5)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Data file init write
(11)
|
| 1 | PX
COORDINATOR
|
|
|
| 1 | +166
| 5 | 84
| |
| |
|
|
|
| 2 | PX SEND QC
(RANDOM)
| :TQ10000 | 50M |
46932 | 2 | +165
| 4 | 84
| |
| |
|
|
|
| 3 | LOAD AS SELECT (EQUI-PARTITION) |
TB_ORD_DTL_BAK |
| | 166
| +2 | 4
| 84 |
| | 20093 | 5GB
| 71.34 | log buffer space
(12)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch
(checkpoint incomplete) (12) |
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch
completion
(4)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Cpu
(117)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: redo allocation
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(312)
|
| 4 | PX PARTITION HASH
ALL
|
| 50M | 46932 | 165
| +2 | 4
| 50M |
| |
| | 2.34 | Cpu
(15)
|
| 5 | TABLE ACCESS
FULL |
TB_ORD_DTL | 50M | 46932
| 165 | +2
| 104 | 50M | 6093 | 5GB
| |
| 7.17 | Cpu
(35)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(11)
|
================================================================================================================================================================================================================
commit;
alter table TUNER.TB_ORD_DTL_BAK
logging;
--insert 작업 동안 타 세션에서 동시성 테스트
SELECT /*+
INDEX(A IDX_TB_ORD_DTL_BAK_01) */ * FROM TUNER.TB_ORD_DTL_BAK A WHERE CUST_NO =
'C000000218';
UPDATE /*+ INDEX(A IDX_TB_ORD_DTL_BAK_01) */
TUNER.TB_ORD_DTL_BAK A SET INPUT_ID = 'INPUT_ID_UPDATE' WHERE CUST_NO =
'C000000218';
DELETE /*+ INDEX(A IDX_TB_ORD_DTL_BAK_01) */
TUNER.TB_ORD_DTL_BAK WHERE CUST_NO = 'C000000218';
INSERT INTO
TUNER.TB_ORD_DTL_BAK t SELECT * FROM TUNER.TB_ORD_DTL a WHERE ROWNUM <=
10;
SELECT
DECODE(V.HOLD_SID, NULL,
'', '('|| V.INST_ID || ')' || V.HOLD_SID) "Hold Sid"
, DECODE(V.WAIT_SID, NULL, '', '^', '▽', '(' || V.INST_ID || ')' || V.WAIT_SID)
"Wait Sid"
, V.GB
,
SW.SECONDS_IN_WAIT AS "Wait Time"
, V.TYPE "Lock
Type"
, DECODE(V.LMODE, 0,
'None'
, 1,
'Null'
, 2, 'Row
Sh'
, 3, 'Row
Ex'
, 4,
'Share'
, 5, 'Sh R
X'
, 6,
'Ex'
, TO_CHAR(V.LMODE)) "Hold Lock Mode"
,
DECODE(V.REQUEST, 0,
'None'
, 1,
'Null'
, 2, 'Row
Sh'
, 3, 'Row
Ex'
, 4,
'Share'
, 5, 'Sh R
X'
, 6,
'Ex'
, TO_CHAR(V.REQUEST)) "Request Lock Mode"
, ( SELECT
D0.OBJECT_NAME || '(' || SUBSTR(D0.OBJECT_TYPE, 1, 1) ||
')'
FROM
DBA_OBJECTS D0
WHERE
D0.OBJECT_ID = S.ROW_WAIT_OBJ#
) AS
"Locked Object"
, SUBSTR(S.EVENT, 1, 25) AS "Wait
Event"
, LAST_CALL_ET AS
LCE
, TRIM(( SELECT SUBSTR(SQL_TEXT, 1,
20)
FROM GV$SQL
SQ
WHERE SQ.INST_ID =
S.INST_ID
AND SQ.SQL_ID =
S.SQL_ID
AND ROWNUM <= 1)) "Sql Text"
FROM
(
SELECT
ROWNUM
, A.INST_ID
,
A.SID
, DECODE(A.REQUEST, 0, TO_CHAR(A.SID))
HOLD_SID
, DECODE(A.REQUEST, 0, '^', TO_CHAR(A.SID))
WAIT_SID
, DECODE(A.REQUEST, 0, 'holding', 'waiting')
GB
, A.ID1
,
A.ID2
, A.LMODE
,
A.REQUEST
, A.TYPE
FROM
GV$LOCK A
WHERE (A.ID1,
A.ID2, TYPE) IN (
SELECT S1.ID1, S1.ID2,
S1.TYPE
FROM GV$LOCK S1
WHERE (S1.REQUEST !=
0)
)
)
V
, GV$SESSION S
, GV$SESSION_WAIT
SW
, GV$PROCESS P
WHERE V.SID = S.SID
AND
V.INST_ID = S.INST_ID
AND S.SID = SW.SID
AND S.INST_ID =
SW.INST_ID
AND S.PADDR = P.ADDR
AND S.INST_ID =
P.INST_ID
ORDER BY V.ID1, V.REQUEST, SW.SECONDS_IN_WAIT
DESC
;
Hold
Sid Wait Sid
GB Wait Time Lock Type Hold Lock Mode
Request Lock Mode Locked
Object
Wait
Event
LCE Sql Text
--------- ----------------
------- --------- --------- ---------------- -------------------
----------------------------- ---------------------------- ---------
----------------------------------------
(1)23
▽
holding 115
TM
Ex
None
PX Deq: Execute
Reply
314 INSERT /*+ enable_pa
(1)28
▽
holding 0
TM
Null
None
gc current
request
314 INSERT /*+ enable_pa
(1)26
▽
holding 0
TM
Null
None
IDX_TB_ORD_DTL_BAK_01(I) gc current
request
314 INSERT /*+ enable_pa
(1)1654
▽
holding 0
TM
Null
None
gc current grant
2-way
314 INSERT /*+ enable_pa
(1)569
▽
holding 0
TM
Null
None
gc current
request
314 INSERT /*+
enable_pa
(1)1112
waiting 66
TM
None Row
Ex
TB_ORD_DTL_BAK(T)
enq: TM -
contention
159 UPDATE TUNER.TB_ORD_
Hold
Sid Wait Sid
GB Wait Time Lock Type Hold Lock
Mode Request Lock Mode Locked
Object
Wait
Event
LCE Sql Text
------------ ------------
------- --------- --------- ----------------- -------------------
----------------------------------- -------------------------- ---------
----------------------------------------
(1)23
▽
holding 342
TM
Ex
None
PX Deq: Execute
Reply 540
INSERT /*+ enable_pa
(1)28
▽
holding 0
TM
Null
None
IDX_TB_ORD_DTL_BAK_01(I)
db file sequential read
540 INSERT /*+ enable_pa
(1)26
▽
holding 0
TM
Null
None
IDX_TB_ORD_DTL_BAK_01(I)
db file sequential read
540 INSERT /*+ enable_pa
(1)1654
▽
holding 0
TM
Null
None
IDX_TB_ORD_DTL_BAK_01(I)
db file sequential read
540 INSERT /*+ enable_pa
(1)569
▽
holding 0
TM
Null
None
gc current grant
2-way 540 INSERT /*+
enable_pa
(1)1112
waiting 34
TM
None
Row
Ex
TB_ORD_DTL_BAK(T)
enq: TM -
contention
33 DELETE /*+ INDEX(A I
--> parallel dml을 하게 되면 TM락이 걸리므로 동시성을 보장 받지 못한다.
SELECT
b.inst_id
, b.session_id AS
sid
, NVL(b.oracle_username, '(oracle)') AS
username
, a.owner AS
object_owner
,
a.object_name
, Decode(b.locked_mode, 0, 'None', 1,
'Null (NULL)', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share (S)', 5, 'S/Row-X
(SSX)', 6, 'Exclusive (X)', b.locked_mode)
locked_mode
, b.os_user_name
FROM
dba_objects a
, gv$locked_object b
WHERE
a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
INST_ID SID
USERNAME OBJECT_OWNER OBJECT_NAME
LOCKED_MODE OS_USER_NAME
--------- ---------
--------- ------------- --------------- ---------------
------------
1 23 TUNER
TUNER TB_ORD_DTL_BAK
Exclusive (X) leeko
1 26 TUNER
TUNER TB_ORD_DTL_BAK Null
(NULL)
leeko
1 26 TUNER
TUNER TB_ORD_DTL_BAK Share
(S)
leeko
1 28 TUNER
TUNER TB_ORD_DTL_BAK Null
(NULL)
leeko
1 28 TUNER
TUNER TB_ORD_DTL_BAK Share
(S)
leeko
1 569 TUNER
TUNER TB_ORD_DTL_BAK Null
(NULL)
leeko
1 569 TUNER
TUNER TB_ORD_DTL_BAK Share
(S)
leeko
1 1654 TUNER
TUNER TB_ORD_DTL_BAK Null
(NULL)
leeko
1 1654 TUNER
TUNER TB_ORD_DTL_BAK Share
(S) leeko
--> parallel dml을 하게 되면 TM락이 걸리므로 동시성을 보장 받지 못한다.
--DML 작업 모니터링 쿼리
SELECT
C.XIDUSN
, C.USED_UREC
,
C.USED_UBLK
, C.STATUS as tx_status
, A.INST_ID
,
A.SID
, A.SERIAL#
,
A.PADDR
, A.USERNAME
,
A.LOCKWAIT
, A.STATUS
,
A.SCHEMANAME
, A.OSUSER
, A.PROCESS
, A.MACHINE
, A.PORT
, A.TERMINAL
,
A.PROGRAM
,
A.SQL_HASH_VALUE
,
A.SQL_ID
, (SELECT SUBSTR(L.SQL_FULLTEXT, 1, 20)
FROM GV$SQL L WHERE L.INST_ID = A.INST_ID AND L.SQL_ID = A.SQL_ID AND ROWNUM
<= 1) AS SQL_TEXT_20
,
A.SQL_CHILD_NUMBER
,
A.SQL_EXEC_START
,
A.PREV_HASH_VALUE
,
A.PREV_SQL_ID
, (SELECT SUBSTR(L.SQL_FULLTEXT, 1,
20) FROM GV$SQL L WHERE L.INST_ID = A.INST_ID AND L.SQL_ID = A.PREV_SQL_ID AND
ROWNUM <= 1) AS PREV_SQL_TEXT_20
,
A.PLSQL_ENTRY_OBJECT_ID
,
A.PLSQL_ENTRY_SUBPROGRAM_ID
,
A.PLSQL_OBJECT_ID
,
A.PLSQL_SUBPROGRAM_ID
,
A.MODULE
, A.ACTION
,
A.LOGON_TIME
,
A.BLOCKING_SESSION_STATUS
,
A.BLOCKING_INSTANCE
,
A.BLOCKING_SESSION
,
A.EVENT
, A.P1TEXT
,
A.P1
, A.P2TEXT
,
A.P2
, A.P3TEXT
,
A.P3
, A.WAIT_TIME
,
A.SECONDS_IN_WAIT
,
A.STATE
, B.ADDR
,
B.SPID
,
B.EXECUTION_TYPE
,
B.PNAME
, B.USERNAME
,
B.TERMINAL
, B.PROGRAM
,
ROUND(B.PGA_USED_MEM/1024/1024, 2) AS
PGA_USED_MEM_MB
, ROUND(B.PGA_ALLOC_MEM/1024/1024,
2) AS PGA_ALLOC_MEM_MB
,
ROUND(B.PGA_FREEABLE_MEM/1024/1024, 2) AS
PGA_FREEABLE_MEM_MB
, ROUND(B.PGA_MAX_MEM/1024/1024,
2) AS PGA_MAX_MEM_MB
, (SELECT ROUND(SUM(C.BLOCKS) *
MAX(D.BLOCK_SIZE)/1024/1024,
2)
FROM
GV$TEMPSEG_USAGE C, DBA_TABLESPACES
D
WHERE A.SADDR =
C.SESSION_ADDR
AND A.SERIAL# =
C.SESSION_NUM
AND C.TABLESPACE =
D.TABLESPACE_NAME
AND C.INST_ID = A.INST_ID) AS TEMP_USAGE_MB
,
c.used_ublk * TO_NUMBER((select k.value from v$parameter k where k.name =
'db_block_size'))/1024||'K' as "Undo"
,
B.TRACEFILE
FROM GV$SESSION A
, GV$PROCESS
B
, GV$TRANSACTION C
WHERE
1=1
AND A.SADDR = C.SES_ADDR
AND A.INST_ID =
C.INST_ID
AND A.PADDR = B.ADDR
AND A.INST_ID =
B.INST_ID
ORDER BY C.USED_UBLK DESC
;
SELECT
TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#)
sid_serial,
NVL(s.username,
'None') orauser,
s.program,
r.name
undoseg,
t.used_ublk *
TO_NUMBER(x.value)/1024||'K' "Undo"
FROM
sys.v_$rollname
r,
sys.v_$session
s,
sys.v_$transaction
t,
sys.v_$parameter
x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND
x.name = 'db_block_size'
/
3. 12cR1부터 enable_parallel_dml 힌트가 추가됨
alter
table TUNER.TB_ORD_DTL_BAK nologging;
alter
session disable parallel dml;
--> 위 작업을 해주지 않아도(disable이어도) sql hint만으로 parallel insert가 되는 것임
ALTER SESSION SET STATISTICS_LEVEL = ALL;
INSERT /*+
enable_parallel_dml parallel(t 4) */ INTO TUNER.TB_ORD_DTL_BAK t
SELECT /*+
parallel(a 4) full(a) */ * FROM TUNER.TB_ORD_DTL a;
--> 예상 실행 계획만
확인
*************************[Explain Plan Time: 2025/07/05
19:28:04]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=47K
Card=50M Bytes=4G)
1 0 PX
COORDINATOR
2 1 PX
SEND (QC (RANDOM)) OF 'SYS.:TQ10000' (Cost=47K Card=50M Bytes=4G)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
3
2 LOAD AS SELECT ((HYBRID TSM/HWMB)) OF
'TB_ORD_DTL_BAK' (PARALLEL_COMBINED_WITH_PARENT)
4 3 PX
PARTITION HASH (ALL) (Cost=47K Card=50M Bytes=4G)
(PARALLEL_COMBINED_WITH_CHILD)
5
4 TABLE ACCESS
(FULL) OF 'TB_ORD_DTL' (TABLE) (Cost=47K Card=50M Bytes=4G)
(PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
alter table TUNER.TB_ORD_DTL_BAK logging;
--여기서 신규 인덱스를 생성함
CREATE INDEX TUNER.IDX_TB_ORD_DTL_BAK_01 ON TUNER.TB_ORD_DTL_BAK (CUST_NO,ORD_DT) LOCAL;
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST
-rows'));
SQL_ID 5nsfdzyc3hwz6, child number
0
-------------------------------------
INSERT /*+ enable_parallel_dml
parallel(t 4) */ INTO
TUNER.TB_ORD_DTL_BAK t SELECT /*+
parallel(a 4) full(a) */ * FROM
TUNER.TB_ORD_DTL a
Plan hash value: 1250007386
--------------------------------------------------------------------------------------------------------------------------------
|
Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers | OMem | 1Mem |
Used-Mem
|
--------------------------------------------------------------------------------------------------------------------------------
|
0 | INSERT
STATEMENT
|
| 1 | 84 |00:25:51.07
| 631 |
|
| |
| 1
| PX
COORDINATOR
|
| 1 | 84 |00:25:51.07
| 631 | 73728 | 73728
| |
| 2
| PX SEND QC
(RANDOM)
| :TQ10000 | 0
| 0 |00:00:00.01
| 0 |
|
| |
| 3
| LOAD AS SELECT (EQUI-PARTITION)| TB_ORD_DTL_BAK
| 0 | 0 |00:00:00.01
| 0 | 730K|
730K| 730K (0)|
| 4 | PX PARTITION
HASH ALL
|
| 0 | 0 |00:00:00.01
| 0 |
|
| |
| 5
| TABLE ACCESS
FULL |
TB_ORD_DTL | 0
| 0 |00:00:00.01
| 0 |
|
|
|
--------------------------------------------------------------------------------------------------------------------------------
--> parallel insert가 잘 유도되는 것을 확인 (enable_parallel_dml 힌트 사용 가능 확인)
Note
-----
- Degree of Parallelism is 4 because of
table property
commit;
alter table TUNER.TB_ORD_DTL_BAK
logging;
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'5nsfdzyc3hwz6', event_detail=>'YES') FROM DUAL;
SQL Monitoring Report
SQL
Text
------------------------------
INSERT /*+ enable_parallel_dml
parallel(t 4) */ INTO TUNER.TB_ORD_DTL_BAK t SELECT /*+ parallel(a 4) full(a) */
* FROM TUNER.TB_ORD_DTL a
Global
Information
------------------------------
Status
: DONE
Instance
ID :
1
Session
: TUNER (23:22874)
SQL
ID
: 5nsfdzyc3hwz6
SQL Execution ID :
16777216
Execution Started : 07/05/2025
19:44:52
First Refresh Time : 07/05/2025
19:44:52
Last Refresh Time : 07/05/2025
20:12:14
Duration
: 1642s
Module/Action :
Orange for ORACLE DBA /6.0.2
(Build:133,S)
Service
:
PRDB
Program
: OrangeMain.exe
--> 갑자기 1642초가 소요됨 기존 166초에서 급격하게 성능이 저하됨
--> 인덱스가 있어서 느린 것임
Global
Stats
==========================================================================================================================
|
Elapsed | Cpu | IO |
Application | Concurrency | Cluster | Other | Buffer |
Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) |
Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Gets | Reqs | Bytes | Reqs | Bytes
|
==========================================================================================================================
|
6426 | 841 | 2523
| 0.00
| 220 |
2214 | 628 | 11M | 299K |
12GB | 57551 | 16GB
|
==========================================================================================================================
Parallel
Execution Details (DOP=4 , Servers
Allocated=4)
============================================================================================================================================================================================================
|
Name | Type | Server# | Elapsed
| Cpu | IO |
Application | Concurrency | Cluster | Other | Buffer |
Read | Read | Write | Write
|
Wait
Events
|
|
|
| | Time(s) | Time(s) | Waits(s)
| Waits(s) | Waits(s) | Waits(s) | Waits(s)
| Gets | Reqs | Bytes | Reqs | Bytes
|
(sample
#)
|
============================================================================================================================================================================================================
|
PX Coordinator | QC
| | 0.53
| 0.53 |
| 0.00
|
|
| | 631
| | .
| | .
|
|
| p000 | Set 1
| 1 | 1556
| 200 | 612
|
| 54
| 538 | 151
| 3M | 70924 | 3GB | 13572 | 4GB
| gc current grant 2-way
(491)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current grant congested
(10)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current multi block request (11)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
buffer busy waits
(50)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
row cache lock
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
row cache mutex
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch (checkpoint incomplete) (2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch completion
(8)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: FB - contention
(16)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: US - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch free
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch: enqueue hash chains
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
reliable message
(19)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
control file sequential read
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
Data file init write
(7)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | db
file sequential read
(454)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read
(3)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read temp
(41)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write
(46)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write temp
(33)
|
| p001 | Set 1
| 2 | 1606
| 207 | 634
|
| 59
| 549 | 156
| 3M | 74887 | 3GB | 15390 | 5GB
| gc current grant 2-way
(510)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current grant congested
(7)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current multi block request (13)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
buffer busy waits
(58)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch (checkpoint incomplete) (2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch completion
(7)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
KSV master wait
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: FB - contention
(9)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: IV - contention
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: US - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch: ges resource hash list
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
reliable message
(22)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
Data file init write
(11)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | db
file sequential read
(459)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read
(5)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read temp
(56)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write
(38)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write temp
(37)
|
| p002 | Set 1
| 3 | 1643
| 219 | 643
|
| 54
| 566 | 162
| 3M | 78262 | 3GB | 14879 | 4GB
| gc current grant 2-way
(547)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current grant congested
(10)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current multi block request (17)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
buffer busy waits
(56)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
row cache mutex
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch (checkpoint incomplete) (2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch completion
(7)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
KSV master wait
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: FB - contention
(16)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: US - contention
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch free
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch: ges resource hash list
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
reliable message
(18)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
control file sequential read
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
Data file init write
(8)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | db
file sequential read
(446)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read
(3)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read temp
(48)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write
(41)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write temp
(33)
|
| p003 | Set 1
| 4 | 1621
| 214 | 634
|
| 53
| 561 | 159
| 3M | 74671 | 3GB | 13710 | 3GB
| gc current grant 2-way
(513)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current grant congested
(11)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | gc
current multi block request (11)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
buffer busy waits
(58)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch (checkpoint incomplete) (2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
log file switch completion
(7)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
KSV master wait
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: FB - contention
(22)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
enq: US - contention
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch: gc element
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
latch: ges resource hash list
(1)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
reliable message
(16)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
Data file init write
(3)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | | db
file sequential read
(467)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read
(2)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path read temp
(43)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write
(54)
|
|
|
|
|
|
|
|
|
|
|
|
| |
| | |
direct path write temp
(33)
|
============================================================================================================================================================================================================
SQL
Plan Monitoring Details (Plan Hash
Value=1250007386)
===============================================================================================================================================================================================================
|
Id
|
Operation
| Name |
Rows | Cost | Time | Start
| Execs | Rows | Read | Read | Write | Write |
Activity
|
Activity
Detail
|
|
|
|
| (Estim) | | Active(s) | Active
| | (Actual) | Reqs | Bytes | Reqs |
Bytes | (%)
|
(#
samples)
|
===============================================================================================================================================================================================================
|
0 | INSERT
STATEMENT
|
|
| | 1628
| +15 | 5
| 84 |
| |
| | 7.00 | gc
current grant 2-way
(4)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | gc current multi block
request (6)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | buffer busy waits
(222)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | row cache lock
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | row cache mutex
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch
completion
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Cpu
(72)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | KSV master wait
(5)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: IV - contention
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: US - contention
(5)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch free
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: enqueue hash
chains
(1)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: ges resource
hash list (2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | reliable message
(75)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | control file
sequential read
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Data file init write
(29)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | db file sequential
read
(5)
|
| 1 | PX
COORDINATOR
|
|
|
| 38 | +1605
| 5 | 84
| |
| |
|
|
|
| 2 | PX SEND QC
(RANDOM)
| :TQ10000 | 50M |
46932 | 1461 | +182
| 4 | 84
| |
| |
|
|
|
| 3
| LOAD AS SELECT (EQUI-PARTITION) | TB_ORD_DTL_BAK
|
| | 1643
| +1 | 4
| 84 | 292K | 7GB | 50791
| 10GB | 92.00 | gc current grant 2-way
(2057)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | gc current grant
congested
(38)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | gc current multi block
request (46)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch
(checkpoint incomplete) (8) |
|
|
|
|
|
|
|
|
|
| |
| |
| | log file switch
completion
(28)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | Cpu
(1160)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | enq: FB - contention
(63)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: gc element
(2)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | latch: ges resource
hash list (3)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | db file sequential
read
(1821)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read temp
(188)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write
(179)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path write temp
(136)
|
| 4 | PX PARTITION HASH
ALL
|
| 50M | 46932 | 199
| +1 | 4
| 50M |
| |
| | 0.27 | Cpu
(17)
|
| 5 | TABLE ACCESS
FULL |
TB_ORD_DTL | 50M | 46932
| 198 | +2
| 104 | 50M | 6093 | 5GB
| |
| 0.69 | Cpu
(30)
|
|
|
|
|
|
|
|
|
|
| |
| |
| | direct path read
(13)
|
===============================================================================================================================================================================================================
--> PDML 자체가 느린 게 아니라 인덱스가 있는 상태의 병렬 대량 적재가 느린
것
4. disable_parallel_dml를 쓰는 경우 append 힌트까지 줘야 hwm 위부터 데이터를 쌓게됨
--> 기존의 병렬 insert를 유도시에는
direct path write이므로 자연스레 hwm위에서부터 데이터를 쌓게됨
--> 병렬 DML(병렬 Insert)를 disable 시키면 CONVENTIONAL INSERT가 기본임
ALTER SESSION SET STATISTICS_LEVEL = ALL;
alter table TUNER.TB_ORD_DTL_BAK nologging;
alter session disable parallel dml ;
INSERT
/*+ append disable_parallel_dml
parallel(t 4) */ INTO TUNER.TB_ORD_DTL_BAK t
SELECT /*+ parallel(a 4) full(a)
*/ * FROM TUNER.TB_ORD_DTL a;
--> parallel dml 을 비활성화하는 경우 append 힌트를 줘야 hwm 위부터 데이터를 쌓음
--> append hint를 주면 TM락이
걸리므로 주의해야함
INSERT /*+
disable_parallel_dml parallel(t 4) */ INTO TUNER.TB_ORD_DTL_BAK t
SELECT /*+
parallel(a 4) full(a) */ * FROM TUNER.TB_ORD_DTL a;
--> 이렇게 append를 빼면 동시성이 보장됨 (dml가능)
commit;
alter table
TUNER.TB_ORD_DTL_BAK logging;
--SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST
-rows'));
결국 동시성이 중요하다면 disable parallel dml & no append를 해야 된다는 것임
대신 당연히 insert 속도는 저하됨
5. 대용량 INSERT 시 성능이 최우선 이라면 아래와 같이 작업 전 INDEX UNUSABLE시키고 TABLE NOLOOING, PDML로 INSERT 후 인덱스 REBUILD할 것
-- 1) 제약/인덱스
끊기
ALTER INDEX TUNER.IDX_TB_ORD_DTL_BAK_01 UNUSABLE;
-- 2)
적재
ALTER SESSION ENABLE PARALLEL DML;
ALTER TABLE TUNER.TB_ORD_DTL_BAK
NOLOGGING;
INSERT /*+ PARALLEL(t 8) */ INTO TUNER.TB_ORD_DTL_BAK t
SELECT /*+
PARALLEL(a 8) */ * FROM TUNER.TB_ORD_DTL a;
--> PARLALLE INSERT 유도 자체가 APPEND까지 되는 것이므로 APPEND 힌트를 생략해도 됨
COMMIT;
-- 3) 인덱스
재빌드
ALTER INDEX TUNER.IDX_TB_ORD_DTL_BAK_01 REBUILD NOLOGGING PARALLEL
8;
ALTER INDEX TUNER.IDX_TB_ORD_DTL_BAK_01 LOGGING;
-- 4) 통계
수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER','TB_ORD_DTL_BAK',
DEGREE=>8);
6. 실습 테이블 정리
--마지막에 테스트 테이블 drop하면서 테스트 끝냄
drop table
tuner.tb_ord_dtl_bak purge;