Enabling parallel DML on Oracle Exadata

When using Oracle Exadata you can make use of parallelism on a number of levels and within a number of processes. However, when doing an massive data import the level of parallelism might be a bit disappointing at first. Reason for this is that by default not all parallel options are activated. When you do a data import you do want parallel DML (Data Manipulation Language) to be enabled. 

You can check the current setting of parallel DML by querying V$SESSION for PDML_STATUS and PDML_ENABLED as an example you can see the query below

SELECT pq_status, pdml_satus, pddl_status, pdml_enabled FROM v$session WHERE sid = SYS_CONTEXT(‘userenv’,’sid’);

this will give you the overview of the current settings applied on your session. If you find that PDML_STATUS = DISABLED and PDML_ENABLED = NO then you can change this by executing an alter session as shown below:

ALTER SESSION ENABLE PARALLEL DML;

when you rerun the above query you should now see that PDML_STATUS = ENABLED and PDML_ENABLED = YES. Now you have set this flags correct you can provide hints to your statements to ensure you will make optimal use of parallelism. Do note that only enabling parallel DML is not solving all your issues, you will still have to look at the code you will be using during your load process of the data into the Exadata.