Oracle Database - How to enable parallelism ?

How to enable a parallel execution:

• for a query
• for a DML operation

Small tables/indexes (up to thousands of records; up to 10s of data blocks) should never be enabled for parallel execution. Operations that only hit small tables will not benefit much from executing in parallel, whereas they would use parallel servers that you want to be available for operations accessing large tables.

Best practices when using object sizes as the main driving factor for parallelism are commonly aligning the DOP with some kind of step function for parallelism, e.g.

• objects smaller than 200 MB will not use any parallelism
• objects between 200 MB and 5GB are using a DOP of 4
• objects beyond 5GB are getting a DOP of 32

Needless to say that your personal optimal settings may vary - either in size range or DOP - and highly depend on your target workload and business requirements only.

You can enable parallel execution and determine the DOP in the following priority order:

• hint
• session
• table

The DOP is limited by the Oracle Database Resource Manager (DBRM) settings. For example, if your resource plan has a policy of using a maximum DOP of 4 and you request a DOP of 16 via a hint, your SQL will run with a DOP of 4.

Oracle will make use of Parallel Loading when “Parallel” hints is used in a query block SQL Statement. The requested DOP for this query is DEFAULT.

SELECT /*+ parallel(c) parallel(s) */
c.state_province
, sum(s.amount) revenue
FROM customers c
, sales s
WHERE s.customer_id = c.id
AND s.purchase_date
BETWEEN to_date('01-JAN-2007','DD-MON-YYYY')
AND to_date('31-DEC-2007','DD-MON-YYYY')
AND c.country = 'United States'
GROUP BY c.state_province
/

This method is mainly useful for testing purposes, or if you have a particular statement or few statements that you want to execute in parallel, but most statements run in serial.

The requested DOP for this query is 16 for the s table (sales)

SELECT /*+ parallel(s,16) */ count(*)
FROM sales s ;

In general you should avoid using hints to enable parallel execution because they are hard to maintain.

To enable query parallelization for an entire session, execute the following statement.

ALTER session force parallel query;

The requested DOP for any operation in that session will be DEFAULT parallelism.

This method is useful if your application always runs in serial except for this particular session that you want to execute in parallel. A batch operation in an OLTP application may fall into this category.

ALTER TABLE <table_name> PARALLEL 32;
ALTER TABLE <table_name> PARALLEL ( DEGREE 32 );
ALTER TABLE <table_name> PARALLEL ( DEGREE DEFAULT );

Use this method if you generally want to execute operations accessing these tables in parallel.

Tables and/or indexes in the select statement accessed have the parallel degree setting at the object level. If objects have a DEFAULT setting then the database determines the DOP value that belongs to DEFAULT.

For a query that processes objects with different DOP settings, the object with the highest parallel degree setting accessed in the query determines the requested DOP.

To enable parallelization of Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE , execute the following statement.

ALTER session enable parallel dml;

To see the rule on parallel DML, see this article Oracle Database - Parallel DML

• Oracle SQL Parallel Execution - An Oracle White Paper - June 2008