Oracle Database - How to (enable|disable) parallel query and get (degree of parallelism|DOP) ?

Card Puncher Data Processing

About

How to enable a parallel execution:

Don't enable parallelism for small objects

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.

Type of execution

Query

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

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.

Hint

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 ;

Session

Reference: Oracle® Database SQL Reference - 10g Release 2 (10.2) - Alter Session

Enable

The PARALLEL parameter determines whether all subsequent query statements in the session will be considered for parallel execution.

Force: If no parallel clause or hint is specified, then a DEFAULT degree of parallelism is used.

alter session force parallel query;

This force 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 session enable parallel query;
Disable
alter session disable parallel query;
Get
SELECT DISTINCT px.req_degree "Req. DOP",
  px.degree "Actual DOP"
FROM v$px_session px
WHERE px.req_degree IS NOT NULL
Req. DOP               Actual DOP             
---------------------- ---------------------- 
16                     16                    

Table

Enable
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.

Disable
ALTER TABLE table_name NOPARALLEL;
Get
SELECT table_name, degree FROM user_tables WHERE table_name='MyTableName';

DML

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - (Degree|Degree of Parallelism (DOP))

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it. A degree of 1 is the default...
Card Puncher Data Processing
Oracle Database - Parallel DML

To enable parallelization of Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE , see this article How to enable parallelization ? The following rules apply when determining...
Oracle Database Sql Parallel Execution Principle
Oracle Database - SQL Parallel Execution

Parallel execution was first introduced in Oracle Version 7.3 in 1996 Oracle will make use of Parallel when a table or a statement is marked as “Parallel”. The concept of parallelism is when many...



Share this page:
Follow us:
Task Runner