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'
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'
