Doug Burns On Tuning Parallel Execution
The other week I wrote an article on a database tuning engagement I'd been on, and one of the things that I looked at during the visit was a SELECT statements that was being executed in parallel. After running the statement with an event 10046 trace, the TKPROF output looked like this:
SELECT [list of account fields]
FROM ACCOUNTS WHERE CUSTOMERID = :b1call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 2 0 0
Fetch 1 0.01 0.11 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.14 0 5 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 60 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 5 0.00 0.00
enqueue 3 0.00 0.00
PX Deq Credit: send blkd 6 0.01 0.02
PX Deq: Parse Reply 2 0.00 0.00
PX Deq: Execute Reply 7 0.00 0.00
PX Deq: Table Q Normal 1 0.00 0.00
PX Deq: Signal ACK 2 0.10 0.10
********************************************************************************
At the time I mentioned that the PX wait events were down to the parallel slaves keeping themselves in sync, and that these wait events are generally considered "idle" events that you can't really tune. Niall Litchfield agreed with this in a subsequent blog post and made the additional observation that, whilst they were in fact idle events, you should also have a think about whether parallel query is in fact appropriate in this case. Quite.
With all of this fresh in my mind I was therefore interested when I came across Doug Burns' recent UKOUG Conference paper "Suck It Dry - Tuning Parallel Execution" that takes a pretty in-depth look at parallel execution, and has this introduction:
"Oracle Parallel Execution can help utilise the power of your hardware and yet remains under-used. This paper discusses the configuration of Parallel Execution and examines some of the potential difficulties. It contains a brief overview of parallel execution architecture and how it expands the standard instance architecture. Strengths, weaknesses and appropriate uses are explained, along with configuration for maximum benefit and relevant initialisation parameters. Finally, performance monitoring using views, Statspack reports and trace files is addressed, including parallel-specific wait events."
Parallel execution is an interesting technology that is particularly suited to data warehousing, in that it allows a single user (or small set of users) to effectively soak up all of the server resources to satisfy a particular query. I remember reading an AskTom article by Tom Kyte on parallel query that said that this isn't always what you want - you wouldn't for example want individual OTLP users soaking up all resource for regular transactions or queries - but parallel query is an excellent way to effectively use up all the available CPUs and disk units when you've got a particularly big warehouse query. Anyway, the paper goes into how an instance is setup for parallel query, what all of the various parameters do, what views you can query to check whether queries have executed in parallel, and, most interestingly, what the various parallel-specific wait events all mean (and when you should in fact look to tune them). One of the examples is around the "PX Deq : Execute Reply" and the "PX Deq : Table Q Normal" wait events that occurred in my trace file:
"Events indicating Consumers are waiting for data from Producers
- PX Deq : Execute Reply
- PX Deq : Table Q Normal
Oracle