Doug Burns On Tuning Parallel Execution
December 17th, 2004 by Mark Rittman
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 0Misses 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
