Waits for: "PX Deq: Slave Session Stats"

Symptoms

 A wait named 'PX Deq: Slave Session Stats' is listed in the Top 5 Timed Events of an AWR:

 

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
db01	AIX-Based Systems (64-bit)	32	8		60.00

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	4946	24-Jul-12 20:00:07	158	2.6
End Snap:	4947	24-Jul-12 20:15:09	160	2.7
Elapsed:		15.03 (mins)
DB Time:		61.97 (mins)

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
PX Deq: Slave Session Stats	153	7,154	46756	192.40	Other
db file sequential read	638,810	1,507	2	40.53	User I/O
log file sync	96,534	1,070	11	28.78	Commit
PX Deq: Signal ACK EXT	89	522	5860	14.03	Other
DB CPU		156		4.21

  

Waits for: "PX Deq: Slave Session Stats"

When a query that is using parallel execution finishes, the Query Coordinator (QC) process signals to the slaves, and waits for them to send back all their session statistics. While these are being sent the QC waits on the "PX Deq: Slave Session Stats" event.  The request for session statistics happens for all the slaves in a particular parallel query at the same time.  If you have a lot of high Degree of Parallelism (DOP) queries and a lot of queries starting/ending during this time period, the aggregated information in an AWR report for this period could be significant.

Waits for: "PX Deq: Slave Session Stats" are a normal part of parallel execution activity and are not an issue per se; in this normal case, you would tend to observe many more waits than time, which means this wait can be ignored.  If you encounter performance issues, and this is the top wait, then it may be worth investigating the cause.

In the example above, there was a performance issue; there are relatively few waits, but the wait time (Time(s)) is quite a bit larger than the elapsed time when converted to seconds (7154 seconds is around 120 minutes). Remember that in parallel, because multiple processes are being timed concurrently, the wait time can easily exceed the elapsed).

Resolution

Normally, the cause is inefficient SQL. where there is a correlated subquery that runs in parallel while the main query runs serially, making setup and teardown of the slaves happen thousands or millions of times; it can also be caused by a very small main query executing in parallel many times in a short period of time (look for a high number of executions in the section "Top SQL by Execution" in the AWR.)  In this case, you can try adding a /*+ no_parallel */ hint to the subquery (or main query if there is no subquery), and make sure that the column predicates of the subquery (or main query if there is no subquery) are indexed; that should also help resolve the high io. 

Further Troubleshooting

If taking the actions in the last paragraph did not resolve the issue, before opening a Service Request with Support, you should run these routine checks.   If you are still not able to determine the cause, then you will already have this information ready to upload to the Service Request. A number of these checks require the diagnostic pack license (AWR & ASH) and some require the Tuning pack.

1. Run an ASH report.  This will show you the top SQL that was running at the time and likely causing the top event.
   
   SQL> @?/rdbms/admin/ashrpt
   
   The sql_id for the top SQL should be identified in the ASH report.  Follow any recommendations.
2. If recommendations in the ASH report do not exist, or did not help, check that the query in question is running in the best environment possible by running a SQL Health Check SQLHC against the SQL_ID. To Run SQLHC, refer to the following documents:
   
   Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video
   Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)
    
   Make changes according to the observations.  
3. I these changes do not help (and you have the SQL Tuning Pack license) you can use the SQL Tuning Advisor from within the SQLTEXPLAIN (SQLT) report to help you. To do this run SQLT in sqltxtract mode using the sql_id.
   To do this :
   a. Download and install a current version of sqlt:
   
   Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
   
   b. Make sure the SQL statement is in the shared pool (you may need to re-execute it if it has aged out)
   c. Run the sqltxtract following the instructions in the "sqlt_instructions.html"
   d. Unzip the .zip file that is produced and look at the main.html file.  Check to see the the execution plan has not changed.   If it has (and you have the SQL Tuning Pack license), SQL Tuning Advisor may have generated advice or an associated SQL plan baseline for each.   If you have the appropriate license, apply the outline to the SQL and see if the issue still occurs.
   Refer to : 
   
   Document 1359841.1 Master Note: Plan Stability Features (Including SQL Plan Management (SPM))
   
   If you do not have the required licenses then manually tuning the SQL may alleviate the issue.