Parallel Execution

Parallel Execution

Case Study

Recently we came across very awkward oracle behavior where We found the 

same sql is blocking itself.Upon analyzing we found It was because of parallelism

 enabled in oracle Dataware house   

Checking table degree for parallelizing

select OWNER,TABLE_NAME,TABLESPACE_NAME,PARTITIONED,NUM_ROWS ,
  last_analyzed ,DEGREE from dba_tables where table_name='WC_S360_INVOICE_DIST_F';

  Query to Identify Parallel Slaves

 select

     s.inst_id,

     decode(px.qcinst_id,NULL,s.username,

           ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",

     decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

     to_char( px.server_set) "Slave Set",

     to_char(s.sid) "SID",

     decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

     px.req_degree "Requested DOP",

    px.degree "Actual DOP",s.module,s.sql_id,s.event,s.status

  from

    gv$px_session px,

    gv$session s

  where

    px.sid=s.sid (+) and

    px.serial#=s.serial# and

    px.inst_id = s.inst_id

 order by 2 desc;

Main /Slave process

  Note that the process flag comes along with p001 and so on which suggests 
that session is having parallel execution components .

 

Points to be noted .

 For bulk load use parallelism . In Informatica It’s an inbuilt feature which can be provoked during RTL run .

Below diagram shows that for bulk load optimizer uses high DOP . So the respective tables have been enabled for higher value.

Points to Note
For bulk load use parallelism . In Informatica It’s an inbuilt feature  which can be provoked during 

ETL run .Below diagram shows that for bulk load optimizer uses high DOP . 
So the respective tables have been  enabled for higher value.

 Below is the typical example of explain plan of a parallelism enabled  SQL statement