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