Oracle Parallel Query Execution Options

Ref: Oracle Database 10g Performance Tuning Tips & Techniques, Oracle Press.

 

Ø       Can be used in DDL & DML.

Ø       Query with Degree of Parallelism (DOP) n may acquire upto 2*n+1 processes.

Ø       Parallel Option benefits the most when table is hosted on multiple disks or is partitioned. It might improve performance a bit for a large table, even if it’s not partitioned or present on multiple disks.

Ø       If DOP is not specified in queries or table DDL then a default value is taken. Default value of DOP is derived from initialization parameters CPU_COUNT and PARALLEL_THREADS_PER_CPU.

Ø       A good practice for time-sensitive queries is to specify degree of parallelism in the query itself instead of relying on DB default value or CREATE TABLE parallel value.

Ø       To use a table with parallel option for all queries, specify parallel in create table DDL.

CREATE TABLE EMP (   Column1 NUMBER(12) NOT NULL,

                           Column2 varchar2(255) )

PARALLEL (DEGREE 4);                        

Ø       To specify parallel in DML statements use:

SELECT /*+ PARALLEL ( TABLENAME , 6) */ 

    ENTITY_ID, APP_VALUE_ID

FROM    TABLENAME

ORDER BY  APP_VALUE_ID;

 

SELECT /*+ PARALLEL_INDEX ( TABLENAME , INDEXNAME, 6) */

    ENTITY_ID, APP_VALUE_ID

FROM    TABLENAME

ORDER BY  APP_VALUE_ID;

 

Ø       To turn-off use of parallel operations use /*+ NO_PARALLEL (TABLENAME) */    hint.

Ø       Create table & index using parallel:

CREATE TABLE TABLENAME

PARALLEL (DEGREE 4)

  AS     SELECT /*+ PARALLEL (TABLENAME_SRC, 4) */

          A.COL1, A.COL2,

          FROM TABLENAME_SRC A;

 

            CREATE INDEX XI1_TABLENAME ON TABLENAME (COL2)

PARALLEL (DEGREE 4);

 

Note: On running query only with parallel in create table and without parallel hint for tablename_src performance still improves considerably over non-parallel query.

 

Ø       Although parallel queries increase the performance of operations that modify data, redo log entries are written serially and could cause a bottleneck. NOLOGGING clause can be used in table & index creation to avoid this. Note: check other limitations of NOLOGGING clause before using it.

Ø       To gain mileage from Parallel operations make sure data is distribute across multiple disks. Parallel Execution can degrade performance in some cases where either query is already fast or there is an existing I/O bottleneck.

Ø       Following statements prevent parallel DML – SELECT for UPDATE, LOCK TABLE, EXPLAIN PLAN.

Ø       Parallel DML Restrictions

o        UPDATE, MERGE, and DELETE cannot be parallelized on non-partitioned tables.

o        After a table has been modified by a parallel DML command, no DML operation or query can access the same table within the same transaction. You need to execute a commit or rollback command between such transactions.

o        If a table has triggers on it, parallel DML operations are not supported. Likewise, tables involved in replication cannot be the subject of parallel DML operations.

o        Deletes on tables having a foreign key with DELETE CASCADE will not be parallelized; nor will deletes on tables having deferred constraints or self-referencing foreign keys if the primary keys are involved in DML.

o        DML against clustered tables cannot be parallelized.

Ø       Limitation example:

commit; alter session enable parallel dml;

UPDATE /*+ PARALLEL (costs,4) */ COSTS

set     unit_price = unit_proice *1.15

where   prod_id > 4000;

22702 rows updated

 

SELECT count(*)

from    COSTS;

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

 

Ø       Sample query and execution times:

CREATE TABLE tablename AS SELECT  A.col1, A.col2, A.col3 FROM src_table A WHERE A.col1 > 4000 ORDER BY A.col1, A,col2 desc, A.col3	5:56min
CREATE TABLE tablename AS SELECT /*+ PARALLEL ( src_table , 4) */ A.col1, A.col2, A.col3 FROM src_table A WHERE A.col1 > 4000 ORDER BY A.col1, A,col2 desc, A.col3	4:03
CREATE TABLE tablename PARALLEL (DEGREE 4) AS SELECT   A.col1, A.col2, A.col3 FROM src_table A WHERE A.col1 > 4000 ORDER BY A.col1, A,col2 desc, A.col3	2:13mins
CREATE TABLE tablename PARALLEL (DEGREE 4) AS SELECT /*+ PARALLEL ( src_table , 4) */ A.col1, A.col2, A.col3 FROM src_table A WHERE A.col1 > 4000 ORDER BY A.col1, A,col2 desc, A.col3	2:07mins
NOTE: src_table does not have PARALLEL defined in its DDL, also I am not sure whether it spans across multiple disks or lie on same. These might be some reasons of performance not increasing significantly between 1 & 2, and also between 3rd & 4th cases.

 

Ø       IMPORTANT NOTE: Pay attention to ROLLBACK, UNDO, TEMP segment sizes & usage when using parallel operations .