5

I've installed a PHP driven application in a client's 9i server (Oracle9i Release 9.2.0.1.0 - 64bit Production). Certain queries are having an awful performance (it can use from 15 minutes to hours just to calculate the execution plan!) and I've tracked the issue to a non-default value of the OPTIMIZER_FEATURES_ENABLE parameter: the default value for 9i is 9.2.0 but the customer changed it to 8.1.7. When I make the same change in my development box, I experience the same performance issues.

If they were running Oracle 10 or greater, I could change it myself for my own sessions but in 9i it's a static parameter that needs to be set for the whole instance. The change was made some time ago in order to support a very important legacy program. The client is currently waiting for an answer from the third-party supplier but I have the feeling that there's little chance of having it changed.

So, what are my options if the param needs to remain untouched? Can its effects be emulated with other changeable settings? Any other idea?

Improve this question

3 Answers 3

Reset to default
5

Because you have OPTIMIZER_FEATURES_ENABLE at 8.1.7, OPTIMIZER_MAX_PERMUTATIONS is defaulting to a very high value. Fortunately this parameter is dynamic so you can set it just for your session without affecting your legacy application.

Improve this answer
2
  • Thanks for the tip. However, I've been testing with several values (4, 2000 and 80000) and I couldn't find any difference neither in time to calculate the plan nor the plan itself :(
    – Álvaro González
    Commented Jul 20, 2011 at 7:00
  • The docs say: "Setting this parameter to a value less than 1000 normally ensures parse times of a few seconds or less" - Perhaps you are hitting a parsing bug? I suggest raising a TAR at this point or experimenting with refactoring the queries to work around the problem. Can you post any of them?
    – Jack Douglas
    Commented Jul 20, 2011 at 7:51
4

In an extreme case such as this one, where the optimizer makes a poor choice of plan or can not build a plan in a reasonable amount of time, you could try to force the plan yourself with the use of hints.

In a scenario similar to yours with a legacy app and an old version database, I've managed to work around an apparent bug of the optimizer by following the method described in the post Full Hinting by Jonathan Lewis.

Improve this answer
1
  • I tried that for a couple of weeks but finally gave up. It's hard to try out hints when your session is likely to lock for hours while calculating the explain plan. At this point, it's either find a way to undo the OPTIMIZER_FEATURES_ENABLE setting or convince the client to install Oracle XE in a spare computer. Thanks anyway to the link, the explanation is quite simple to follow.
    – Álvaro González
    Commented Jul 20, 2011 at 7:28
2

You could try to fiddle with optimizer_max_permutations (default 80000) to reduce the number of combinations that are considered to build a plan.

To my opinion your customer needs good help, migrating to 11gR2 would be a step in the better direction. There is an awfull lot of automated help in query tuning that in the previous release had to be done by hand. My guess is that this old version is also the reason why it takes so long to respond on a problem for the supplier.

Improve this answer
2
  • Actually, the supplier's app finds 9i too new to work properly... sigh
    – Álvaro González
    Commented Jul 20, 2011 at 7:01
  • In that case I would look for an other solution. They don't understand Oracle, they are a risk for your customer. They cost your customer a lot of time and money. I can understand they have problems with 9i, costing improved tremendous in 11g. Adaptive cursor sharing alone is worth upgrading.
    – user953
    Commented Jul 20, 2011 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.