 |
|
SQL Wait Event: PX Deq Credit: send blkd
Oracle Tips by Burleson Consulting
May 1, 2008
|
Question:
I'm running a 20 SQL count batch job on a 4-node RAC
cluster, and one of the queries takes much longer to complete when run as part
of a 20 SQL batch job count. (It completes successfully when run on it's own)
The Top User wait event that I'm seeing is PX Deq Credit: send blkd..
Is there any way I could reduce the time taken for these last few queries to
complete (or reduce this wait event)?
Answer:
To start make sure you are dedicating 5 to each node. The
goal with batch jobs is to load-down the server without overloading it.
See the following on tuning large batch jobs to help reduce your wait event:
http://www.dba-oracle.com/t_tuning_large_o..._batch_jobs.htm
Also, we can assume that there may have been contention for
a shared resource increasing your wait event. You will have to determine
exactly what that is:
http://www.dba-oracle.com/t_tuning_sql_large_batch_jobs.htm
Having PX Deq Credit: send blkd as your top user wait event
is normal. My guess would be that you have maxed the CPU's with the parallel
processes.
You can take a five minute STATSPACK report and paste it into
http://www.statspackanalyzer.com
There are ways to reduce the time taken for these last few
queries to complete, but we would have to open it up and have a look at the
innards.
Also, Take a look at these tips for tuning Oracle parallel query execution
as a means of reducing the PX Deq Credit: send blkd wait event when running DW
SQL on RAC here:
http://www.dba-oracle.com/oracle_tips_parallel_query_execution_tuning.htm
|
