Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Please note this page has moved to http://oracledoug.com/px9.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Some Common Sense

One of my favourite descriptions of performance tuning, although I can’t remember where I first heard it, is that it is based on ‘informed common sense’. That really captures my own experiences of performance tuning. Yes, you need to use proper analysis techniques and often a great deal of technical knowledge, but that’s all devalued if you’re completely missing the point. So let’s take a step away from the technical and consider the big picture.

• Don’t even think about implementing Parallel Execution unless you are prepared to invest some time in initial testing, followed by ongoing performance monitoring. If you don’t, you might one day hit performance problems either server-wide or on an individual user session that you’d never believe (until it happens to you).
• Parallel Execution is designed to utilise hardware as heavily as possible. If you are running on a single-CPU server with two hard disk drives and 512Mb RAM, don’t expect significant performance improvements just because you switch PX on. The more CPUs, disk drives, controllers and RAM you have installed on your server, the better the results are going to be.
• Although you may be able to use Parallel Execution to make an inefficient SQL statement run many times faster, that would be incredibly stupid. It’s essential that you tune the SQL first. In the end, doing more work than you should be, but more quickly, is still doing more work than you should be! To put it another way, don’t use PX as a dressing for a poorly designed application. Reduce the workload to the minimum needed to achieve the task and then start using the server facilities to make it run as quickly as possible. Seems obvious, doesn’t it?
• If you try to use PX to benefit a large number of users performing online queries you may eventually bring the server to its knees. Well, maybe not if you use the Adaptive Multi-User algorithm, but then it’s essential that both you and, more important, your users understand that response time is going to be very variable when the machine gets busy.
• Using PX for a query that runs in a few seconds is pointless. You’re just going to use more resources on the server for very little improvement in the run time of the query. It might well run more slowly!
• Sometimes when faced with a slow i/o subsystem you might find that higher degrees of parallelism are useful because the CPUs are spending more time waiting for i/o to complete. Therefore they are more likely to be available for another PX slave that isn’t waiting on i/o to use. This was certainly my experience at one site. However, it’s also true that using PX can lead to a busier i/o subsystem because the server is likely to favour full scans over indexed retrieval. There are no easy answers here - you really need to carry out some analysis of overall system resource usage to identify where the bottlenecks are and adjust the configuration accordingly.

Final Thoughts

Oracle's Parallel Execution capability can improve the performance of long-running tasks significantly by breaking the tasks into smaller sub-tasks that can execute in parallel. The intention is to use as much hardware resource as possible to deliver results more quickly. However, it works best

• On a server which has spare CPU, RAM and i/o throughput capacity
• For tasks which run for more than a few seconds
• For a limited number of concurrent users

If you can meet all of these requirements then the performance improvements can be dramatic but you should consider the potential downsides carefully

• Tracing sessions becomes more difficult, although things are supposed to become easier with 10g
• Unless you are using the Adaptive Multi-user facility you may find your server grinding to a halt one day.
• If you are using the Adaptive Multi-user facility you may find one or more user sessions slowing down dramatically under heavy server workloads.

As with many aspects of Oracle, it’s important to plan an effective implementation and test it as thoroughly as possible before inflicting it on your users but when used appropriately, parallel execution is hard to beat.

On the next page you'll find details of further sources of information on Parallel Execution

Previous