Back to Papers
Instance Configuration II
Dictionary Views II
Session Tracing and Wait Events
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.
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