Long running JOB due to parallel execution

Today I met a long running job. ETL developer said that this job had been running for about 6 hours,

request DBA's help. 

 

I asked them to provide SID , which is 4861.

According the providing sid, I checked the wait event is PX Deq: Execute Reply , which means the job is

running in parallel mode.

For parallel running SQL, we can check wait event by using SQL_ID.So I use below SQL to check what is waiting for.

 

select (sysdate-logon_time)*24*60 minutes, username,machine,sid,serial#,event,p1,p2,p3,

row_wait_obj# obj#,row_wait_file# file#,row_wait_block# block#,sql_id,sql_child_number from

gv$session where sql_id='9tuyvbacwkc5k';

 

Below is the formatted query result.

MINUTES USERNAME MACHINE SID SERIAL# EVENT P1 P2 P3 OBJ# FILE# BLOCK# 442 ADWU_OPTIMA_WE11_ETL bdhp4462 2237 180 PX Deq: Execution Msg 268566527 10600 0 -1 0 0 442 ADWU_OPTIMA_WE11_ETL bdhp4462 2303 19672 read by other session 80 982406 1 61027312 80 982406 442 ADWU_OPTIMA_WE11_ETL bdhp4462 2375 29536 read by other session 80 982342 1 61027312 80 982342 442 ADWU_OPTIMA_WE11_ETL bdhp4462 2402 56932 PX Deq: Execution Msg 268566527 10600 0 -1 0 0 442 ADWU_OPTIMA_WE11_ETL bdhp4462 4816 60934 read by other session 80 982406 1 61027312 80 982406 442 ADWU_OPTIMA_WE11_ETL bdhp4462 4818 5555 unspecified wait event 0 0 0 61027312 80 982406 442 ADWU_OPTIMA_WE11_ETL bdhp4462 4827 51041 PX Deq: Execution Msg 268566527 10601 0 -1 0 0 443 ADWU_OPTIMA_WE11_ETL bdhp4462 4861 8746 PX Deq: Execute Reply 200 768 0 65764751 217 54823 442 ADWU_OPTIMA_WE11_ETL bdhp4462 4867 42001 PX Deq: Execution Msg 268566527 10600 0 -1 0 0

 

Pls notice  session 2303 and 4816, wait event is read by other session.P1 , P2, P3 are the same.

 

What is read by other session? below is the explain from Metalink 732891.1

When information is requested from the database, Oracle will first read the data from disk into the database buffer cache.

If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait.

In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher,

this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to

several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing

full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

 

Then I checked the execution plan, I find that one table will scan 8 times in parallel mode.

Here I can't post the execution plan for some reason.

Now it's clear that the SQL hung is due to read by other session. read by other session is due to one table need scan 8 times in parallel mode.

So I suggest disable parallel query. Finally the SQL can be finished within 30minutes.