Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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 utilize 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 initialization 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 behavior 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."

 

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational