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 .

 

 

DataStage NULL comparison

Small update on DataStage not equal to operator. In DataStage var1 <> ‘Y’ does not include cases where var1 can be NULL. If you want to check if var1 is not Y but can be NULL then you should use: var1 <> ‘Y’ or isNull(var1 )

Options to be specified in SQL control files which call multiple scripts

SET DEFINE OFF - if there are special characters to be inserted

SET ECHO ON - to get SQL statements executed along with their execution results.

SPOOL <path\file_name> - file name for spooling output. Make sure the user which runs the script has write permission in the directory where spool file will be written, else after scripts are complete, you will find yourself scratching your head for that missing spool file : )

SET TIMING ON - to get timings of various sql statements executed.

SET TERMOUT ON

SET TIME ON – to get time on command prompt. Quite useful when master control file runs many many queries and takes hours/minutes to run.

Oracle Multi - Table Inserts

Many a times we forget the power of SQL and resort to PL/SQL for simple requirements. Conditional insert is one such example which can be handled easily by Multi-Insert SQL queries as against writing PL/SQL blocks. Sample:

INSERT

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

ELSE

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

 

Check out below sample taken from http://www.pythian.com/news/463/oracles-little-known-multi-table-insert by Babette Turner-Underwood

 

INSERT

   when ( id is null )

       insert into dept_exception

       values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees')

   when ( comm > sal )

       insert into emp_exception

       values ( rec.id, rec.name, rec.sal, rec.comm, rec.dept_id, 'comm greater than sal')

   when ( first_dept = 'Y')

       insert into new_dept

       values ( dept_id, dept_name)

   when ( nvl(sal,0) >= nvl(comm,0) )

           insert into new_emp

           values ( id, name, dept_id, sal, comm)

SELECT dept.dept_id, dept_name, emp_id id, emp_name name, sal, comm

               from dept , emp

              where dept.dept_id = emp.dept_id(+);

 

A variation of INSERT WHEN is INSERT ALL which unconditionally inserts into all tables specified.

INSERT ALL

INTO <table_name> VALUES <column_name_list>

INTO <table_name> VALUES <column_name_list>

...

SELECT Statement;