Büyük bir tablonun partition by partition taşımasını yaparken , istenilen hızda olmadığını ve insert işlemini parallel olarak yapılamadığını fark ettim, planları inceledim ve alter session ile parallel DML i etkinleştirsemde planda parallel olarak işlem yapılamadığını gözlemledim.
insert /*+APPEND */ into DMDB.TMM_MT_1_2 select * from DMDB.MSC_MT_1_2 partition(P20231031);
--Plan
Plan hash value: 306016712
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 62M| 30G| 149K (4)| 00:00:06 | | | | | |
| 1 | LOAD AS SELECT | TMM_CDR_MSC_MT_1_2 | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 62M| 30G| 149K (4)| 00:00:06 | | | Q1,00 | P->S | QC (RAND) |
| 4 | OPTIMIZER STATISTICS GATHERING | | 62M| 30G| 149K (4)| 00:00:06 | | | Q1,00 | PCWC | |
| 5 | PX BLOCK ITERATOR | | 62M| 30G| 149K (4)| 00:00:06 | 1 | 16 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS STORAGE FULL | CDR_MSC_MT_1_2 | 62M| 30G| 149K (4)| 00:00:06 | 1 | 16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):İlgili insert için 12c ile birlikte gelen enable_parallel_dml hint ifadesi kullanıldığında plan aşağıdaki gibi değişmekte ve INSERT işlemi bariz bir şekilde hızlanmaktadır;
Plan hash value: 216181064
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 62M| 30G| 5183 (4)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 62M| 30G| 5183 (4)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HIGH WATER MARK)| TMM_CDR_MSC_MT_1_2 | | | | | | | Q1,01 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 62M| 30G| 5183 (4)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 62M| 30G| 5183 (4)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND PARTITION (KEY) | :TQ10000 | 62M| 30G| 5183 (4)| 00:00:01 | | | Q1,00 | P->P | PART (KEY) |
| 7 | PX BLOCK ITERATOR | | 62M| 30G| 5183 (4)| 00:00:01 | 1 | 16 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS STORAGE FULL | CDR_MSC_MT_1_2 | 62M| 30G| 5183 (4)| 00:00:01 | 1 | 16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of hint
Bir yanıt yazın