Tuning Oracle Parallel Query Execution Oracle Tips by Burleson Consulting

By Mark Rittman

Tuning Oracle Parallel Query 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 = :b1

call     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’s documentation states that these are idle events because they indicate the normal behaviour of a process waiting for another process to do its work, so it’s easy to ignore them. However, if you have excessive wait times on these events it could indicate a problem in the slaves. To give you a real-world example, here is the top timed events section of a Statspack report from a production system I worked on.

Event                        Waits   Timeouts   Time (s)   (ms)     /txn
direct Path read         2,249,666          0    115,813     51     25.5
PX Deq: Execute Reply      553,797     22,006     75,910    137      6.3
PX qref latch               77,461     39,676     42,257    546      0.9
library cache pin           27,877     10,404     31,422   1127      0.3
db file scattered read   1,048,135          0     25,144     24     11.9

The absolute times aren’t important here, just the events. First, it’s worth knowing that PX slaves perform direct path reads rather than db file scattered reads. You may already be used to direct path reads because they’re used with temporary segments for example. On this system, which was a European-wide Data Warehouse, we were performing long-running SELECT statements as part of the overnight batch run, so a high level of disk I/O was inevitable. (Whether an average wait time of 51 ms is acceptable when you’ve spent a small fortune on a Hitachi SAN is another matter!)

The next event is PX Deq: Execute Reply, which Oracle considers to be an idle event as I’ve mentioned. So we ignore that and move down to the next event. The PX qref latch event can often mean that the Producers are producing data quicker than the Consumers can consume it. On this particular system, very high degrees of parallelism were being used during an overnight batch run so a great deal of messaging was going on. Maybe we could increase parallel_execution_message_size to try to eliminate some of these waits or we might decrease the DOP.

But the real problem that we were able to solve was the next event – library cache pin. This event represents Oracle trying to load code into the Library Cache so you wouldn’t normally expect to see a significant percentage of wait time for this event unless the Shared Pool is really struggling (which it was on this system).

So next we drill down and start to try session tracing to establish the source of these events. Initially I was unsuccessful in tracking them down until I realized that the PX Deq: Execute Reply was a useful hint. The fact is that many of these wait events were happening in the PX slaves and many of the PX Deq: Execute Reply events were caused by the QC waiting for the PX slaves, which were waiting for the library cache pin latch! So sometimes idle events are important.

Eventually it turned out to be a pretty bad bug in earlier versions of 9.2 (fixed in 9.2.0.5) that caused some of our 2-minute SQL statements to occasionally take 2 hours to run. (Yes, that really does say 2 hours.) Anyway, back to more wait events."

If you've been looking at parallel execution and you've got a spare half an hour, take a look at the paper as it's the best I've recently seen on this Oracle RDBMS feature.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.