I am totally new to oracle and stuck in one application specific issue which was using oracle db.So below is the query regarding the issue which i faced :
I was trying a very simple query ( insert into DIM select * from FLAGGING ) with 18 million records in FLAGGING.
It was taking a huge time around 5 hours.
So i created DOP (degree of parallism ) on target table and below is the explain plan.
Target table has 4 indexes with one constarint primary key as index in table defination itself.(SO total 5 indexes). <CONSTRAINT "ABC" PRIMARY KEY ("KEY") USING INDEX TABLESPACE "INDEX_TEST" ENABLE>
I also created all the indexes parallel except the constaraint which is in table DDL using index.(Tried to alter it for parallism) .
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 18M| 4838M| 46989 (1)| 00:00:02 | | | |
| 1 | LOAD TABLE CONVENTIONAL | DIM | | | | | | | |
| 2 | SEQUENCE | SEQ | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 18M| 4838M| 46989 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 18M| 4838M| 46989 (1)| 00:00:02 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | FLAGGING | 18M| 4838M| 46989 (1)| 00:00:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
I am getting below in note :
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 4 because of table property
- PDML disabled because index is not parallel ready
Could you please help us to understand this? as per my understanding it is not using parallelism while loading.
Except the constraint index i created all the index parallel.Also tried to alter the constraint index for parallel still getting "PDML disabled because index is not parallel ready".
How to proceed in this case?
OK, first of all an insert of 18million rows taking 5 hours is typically something wrong right there, eg
SQL> create table t as
2 select e.* from scott.emp e,
3 ( select 1 from dual connect by level <= 20000000/14 );
Table created.
SQL> create table t1 as select * from scott.emp where 1=0;
SQL> set timing on
SQL> insert /*+ APPEND */ into t1
2 select * from t;
19999994 rows created.
Elapsed: 00:00:11.83
11 seconds for 20 million rows and that's on my laptop. Which goes to show the impact constraints, indexes, triggers, etc might be having on your load. So ideally, if your requirements allow, you want to disable that stuff, load the data, then re-enable.
When it comes to doing ins/upd/del modifications in parallel, you must enable it at session level first, ie
alter session enable parallel dml;