I've installed a PHP driven application in a client's 9i server (Oracle9i Release - 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?

  • You might want to post this question on dba.stackexchange.com as well. – DCookie Jun 14 '11 at 18:39

You could try other hints (eg /* + RULE */) to force the optimizer in a particular direction.

But basically you are taking very old software (and an unpatched / unsupported version at that) and forcing it to act like an even older version. I can't really imagine it taking hours to derive an execution plan though, so it sounds like you are hitting a bug (or actually executing the SQL and rolling back).


Going back that far, it used a common table for explain plans, so do a COMMIT afterwards;

If that doesn't return near immediately, check what is happening in v$session. Pretty much all the table stats etc should be in the cache, so I wouldn't expect any disk waits, and I'm hard put to work out what else might cause a very long query parse.

  • I've already tried everything I've figured out in order to make the queries run properly in the client's misconfigured server but I was just getting nowhere. It's impossible to fix anything when a simple EXPLAIN PLAN FOR query is likely to hang the process. I had the hope that OPTIMIZER_FEATURES_ENABLE might be a short-cut for a series of individual switches, or something... – Álvaro González Jun 17 '11 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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