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. 

Configure Exadata storage cell mail notification

The Oracle Exadata database machine from Oracle gets a lot its performance not specifically from the compute nodes or the infiniband switches, one of the main game changers are the storage cells used within the Exadata. The primary way, for command line people, to interact with the storage cells if the “cell command line interface” or commonly called the CellCLI.

When you like to ensure you storage cell is informing you via mail notifications you can configure or alter the configuration of the storage cell email notification using the CellCLI command.

When doing changes to the configuration you do want to first know the current configuration of a storage cell. You can execute the following command to request the current configuration

CellCLI> list cell detail

For mail configuration you will primarily want to look at:

• smtpServer
• smtpFromAddr
• smtpFrom
• smtpToAddr
• notificationMethod

To test the current configuration you can have the storage cell send a test mail notification to see if the current configuration works like you expect. The VALIDATE MAIL operation sends a test message using the e-mail attributes configured for the cell. You can use the below CellCLI command for this:

CellCLI> ALTER CELL VALIDATE MAIL

If you want to change something you can use the “ALTER CELL” option from the CellCLI. As an example we use the below command to set all information for a full configuration to ensure that your Exadata storage cell will send you mail notifications. As you can see it will also send SNMP notifications however the full configuration for SNMP is not shown in this example.

CellCLI> ALTER CELL smtpServer='smtp0.seczone.internal.com',            -
                    smtpFromAddr='huntgroup@internal.com',         -
                    smtpFrom='Exadata-Huntgroup',                         -
                    notificationMethod='mail,snmp'