Parrallel hint in RAC making ETL run slow

First Question is when we should use parallel hints ??

Without parallel it makes the query wait on the indexes data to be read. So we are just waiting for data to be read , so parallel hint makes sense.

 Link to Article on Understanding Parallel Explain plans




When you write parallel hint your session becomes coordinator processor and there are several slave processes which are created. Slave read data and give to coordinator which does the loading.Below is a scenario in which Parallel hint was actually slowing the query .Below analysis is for DML statements. Inserts were taking time

select sid, state,event,SECONDS_IN_WAIT,BLOCKING_SESSION,BLOCKING_INSTANCE,
row_wait_obj#,
       row_wait_file#,
       row_wait_block#,
       row_wait_row#,p1,p2
from v$session
where username = 'ABC' and
OSUSER='kkk'
and event = 'enq: TX - row lock contention'

Note -- There are 66 parallel session on Measure val

select event, total_waits, (time_waited*10)/1000 tw_ms,
       average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid in (
select sid from v$session
where username = 'ABC' and
OSUSER='kkk' )
order by 3 desc





There are two events which point highly in the favour of this .
    1) PX Deq Credit: send blkd---- This means the slave are waiting for coordinator process to be free to supply their data
    2) PX Deq: Execution Msg---This means once slave processes have supplied their data they are waiting for more request from coordinator process.
    3) So both of them point that the coordinator is not able to handle the data provided by slaves, which means there are more slave processes that the coordinator can handle


Note -- On average every parallel read wait quarter of second to read

select name, sum(value)
from v$sesstat s, v$statname n
where n.statistic# = s.statistic# and
sid in (
select sid from v$session
where username = 'ABC' and
OSUSER='kkk' )
group by name
order by 2 desc

Note - The interconnnect bytes seems to be a large number















 

 
To do insert in parallel we need to have parallel DML enabled which enables the slaves to do the insert other wise only coordinator processor does the insert and it will be bottleneck you will all the slaves waiting for one coordinator process do the load


Below is analysis for Report query which was taking time due to degree of parallelism

Analysis for  logic in report

1) Changing the parallel degree in report  logic from parallel(8,1) to parallel. Brings down the query time from 100 to 11 seconds

    1. PX Deq: Execution Msg---This means once slave processes have supplied their data they are waiting for more request from coordinator process.
    2. So both of them point that the coordinator is not able to handle the data provided by slaves, which means there are more slave processes that the coordinator can handle

select sid, state,event,SECONDS_IN_WAIT,BLOCKING_SESSION,BLOCKING_INSTANCE,
row_wait_obj#,
       row_wait_file#,
       row_wait_block#,
       row_wait_row#,p1,p2
from v$session
where username = 'ABC' and

OSUSER='kkk'