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/px3.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Important - I've tried to be helpful in suggesting some initial values for these parameters, but your database and application is unique, so you should use these suggestions as starting points. There are no easy answers

Switching Parallel Execution on at the Instance Level is surprisingly easy and only requires a few parameters to be set. However, you need to think carefully about the overall effects on the server first. It’s worth reading this document and as many of the references listed at the end of this paper as you can before deciding on the values that you think are best for your particular application.

parallel_automatic_tuning

Default Value - FALSE

Recommended Value - TRUE

This parameter was first introduced in Oracle 8i and its very presence is instructive. I remember in version 6 being able to tune the various areas of the dictionary (or row) cache using the dc_ parameters. When 7 came along, the facility was taken away and that particular cache became self-tuning. Oracle has attempted much the same at various stages in the development history of the server, with varying degrees of success! Other examples include automatic PGA management and System Managed Undo. To me, this parameter is a sign that users have experienced difficulty in configuring PX themselves so Oracle is trying to make the job easier. In this case, I think they probably have a point. When parallel_automatic_tuning=true, it has several effects

• The message buffers are stored in the Large Pool rather than the Shared Pool to improve efficiency. However, you need to be aware of this and set large_pool_size accordingly. The calculation for this is in the documentation (see the list of references at the end)
• It sets various parameters to more sensible values than their defaults (e.g. parallel_execution_message_size is increased from 2Kb to 4KB)
• Importantly, it enables the parallel adaptive multi-user algorithm (see next section)

According to Oracle, this parameter is deprecated in 10G as the default values for parallel configuration parameters are optimal. However, when I’ve tried changing the value, apart from generating a warning, it seems to me that the behaviour is the same as previous versions! Perhaps this is one of those situations where Oracle will be making some far-reaching changes in approach in future versions and this is an early sign.

parallel_adaptive_multi_user

Default Value - FALSE

Automatic Tuning Default - TRUE

Recommended Value - TRUE

Parallel_adapative_multi_user is one of the most significant PX configuration parameters and you need to be fully aware of its effects. Imagine a situation where perhaps we have an Oracle Discoverer report running against a Data Warehouse that takes 8 minutes to run. So we modify the DOP setting on the relevant tables to see if using PX will improve performance. We find that a DOP of 4 gives us a run-time of 90 seconds and the users are extremely happy. However, to achieve this, the report is using a total of nine server processes. We decide to stress test the change to make sure that it’s going to work, don’t we? Or maybe we just decide to release this to production because it’s such a fantastic improvement! The only difference is going to be whether we have a disastrous test, which might be bearable, or dozens of unhappy users, which is usually unbearable.

The problem is that we’ve just multiplied the user population by nine and, whilst this worked fantastically well with just one report running, it probably won’t scale to large user populations unless you have some extremely powerful hardware. The likelihood is that it won’t take long before Oracle manages to suck every last millisecond of CPU away and the effect on the overall server performance will be very noticeable!

To give you an example, one day we were testing our overnight batch run that used multiple job streams running in parallel (using the scheduling tool) each of which was parallelised using PX. In effect, we had over a hundred server processes running on a server with a handful of CPUs. It ran quickly enough for our purposes but, while it was running, it was difficult to do anything else on the server at all. It was so slow that it appeared to be dead.

Clearly, the last thing we want is for our server to grind to a halt. The users wouldn’t be getting their reports back at all, never mind in 8 minutes! So Oracle introduced the Adaptive Multi-User facility to address this problem. Effectively, Oracle will judge how busy the server is when it’s doling out the PX slaves and, if it decides the machine is too busy, it will give you a smaller degree of parallelism than requested. So the feature acts as a limiter on PX activity to prevent the server from becoming overloaded (and everyone losing out), but allows PX to be used when the number of concurrent PX users drops and it will be more beneficial.

Initially this seems like an excellent idea and, on balance, I think it probably is because if the server is absolutely saturated, everyone loses out. However, let’s question and be clear on the impact of this. To help me do this, I’m going to use a quote:-

‘This provides the best of both worlds and what users expect from a system. They know that when it is busy, it will run slower.’
Effective Oracle by Design. Thomas Kyte

Tom Kyte is a man that I admire very much for all the work he’s done for the Oracle community and for his considerable technical and communication skills. It’s difficult for me to find anything he has to say about PX that I’d disagree with. In fact, I've revised this section based on a short but thought-provoking dialogue between us. However, what I’m interested here is in different opinions and perspectives (including the user perspective!) and I’m not questioning any of the technical detail of Tom’s argument.

Ask yourself this question – ‘Do my users expect the same report to run 4 or 8 times more slowly depending on what else is going on on the server?’. I’m not talking about 90 seconds versus 100 seconds, more like 90 seconds against 8 minutes, at unpredictable moments (from the point of view of the user). How about two users invoking the same report, one a minute or two after the other, but experiencing very different response times?

In my opinion, the statement doesn’t reflect what a lot of users are like at all. The one thing they don’t want is unpredictable performance. In fact, I worked at a site recently where the managers were very particular about the fact that they wanted a reasonable but, more important, consistent level of performance. Then again, you might argue that users would also be extremely unhappy if the server becomes overloaded and everyone's reports stop returning any data until someone steps in and kills some of them!

It's a complex balance that Oracle are trying to address so I think their solution is very sensible and I recommend it. Just remember the implications and be able to articulate them to your users

Previous