Home Page

Back to Papers


Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Other Resources

The best source of information on Parallel Execution is the Oracle documentation. It's amazing how often I find the (free) manuals far superior to (paid-for) 3rd party books! Specifically, the Data Warehousing Guide contains a couple of relevant chapters :-

Using Parallel Execution

Parallelism and Partitioning in Data Warehouses

There are also a number of useful resources on Metalink, including a specific section containing the most useful notes that you can access by selecting 'Top Tech Docs', 'Database', 'Performance and Scalability' and then ‘Parallel Execution’ from your Metalink home page. The following documents are particularly relevant to this paper.

184417 – Where to track down the information on Parallel Execution in the Oracle documentation!

203238 – Summary of how Parallel Execution works.

280939 - Checklist for Performance Problems with Parallel Execution (but also contains a useful explanation of DFO effects on number of PX slaves used)

119103 – Parallel Execution Wait Events (contains links to event-specific information)

201799 – init.ora parameters for Parallel Execution

240762 – pqstat PL/SQL procedure to help monitor all of the PX slaves running on the instance or for one user session

202219 – Script to map PX slaves to Query Co-ordinator (An alternative to using the procedure in note 240762.1)

275240 – Discusses Bug no. 2533038 in the Parallel Adaptive Multi-User algorithm (fixed in 9.2.0.3) which makes it sensitive to idle sessions, leading to an under-utilised system.

238680 – Investigating ORA-4031 errors caused by lack of memory for queues.

242374 – Discusses some of the issues around PX session tracing (but not in any great depth)

237328 – Direct Path Reads (brief discussion)

Bibliography

The following books contain some useful information about Parallel Execution.

Oracle SQL High Performance Tuning. - Harrison, Guy - Prentice Hall

Effective Oracle by Design. - Kyte, Thomas - Oracle Press

Practical Oracle 8i – Building Efficient Databases. - Lewis, Jonathan - Addison Wesley

Oracle Parallel Processing. - Mahapatra,Tushar and Mishra Sanjay - O’Reilly & Associates, Inc.

There’s also an excellent Parallel Execution conference paper by Jeff Maresh. As well as covering server configuration it contains more developer-orientated information about using PX within your application than I’ve covered in this document.

Parallel Execution Facility Configuration and Use

And, as usual Jonathan Lewis has some previous articles on this subject! Although these are geared to Oracle 7.3, much of the content makes sense across versions.

Parallel Execution Articles

Finally, here’s a nice PX slave monitoring query from the “Co-operative Oracle Users’ FAQ”

http://www.jlcomp.demon.co.uk/faq/pq_proc.html

Acknowledgements

There were many people who contributed to this article to make it a lot better than it was originally, pointing out at least one major error and several smaller ones. It was an education to have my work reviewed by such talented individuals, so my thanks to (in alphabetical order) :-

Glyn Betts, Sun Microsystems

Andrew Campbell, Sun Microsystems

Carl Dudley, University of Wolverhampton

Jari Kuhanen, Sun Microsystems

Tom Kyte, Oracle Corp.

Jonathan Lewis, JL Computing

Tony Marlow, BSkyB

If you spot any errors or can suggest improvements, please feel free to drop me an email at dougburns@yahoo.com

Previous


Technical Papers Utilities and Scripts Book Reviews Links
My Resume Fun & Games Email Home