Alternative for OPTIMIZER_FEATURES_ENABLE

oracleoracle-9i

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?

Best Answer

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.

Related Solutions

A database trigger is an alternative for

I don't expect the writer of the question meant it this way, but words mean things and the question specifies a "database trigger", therefore there is no correct answer.

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event. (PL/SQL Language Reference - DATABASE Triggers)

Unlike Row and Statement triggers, Database triggers fire for things like AFTER STARTUP ON DATABASE, AFTER SERVERERROR ON DATABASE and AFTER LOGON ON DATABASE.

Oracle Alternative to EXISTS EXCEPT

This should be the Oracle equivalent to the accepted answer:

SELECT *
FROM a a1 
  JOIN b b1 
    ON a1.id = b1.id 
   AND EXISTS(SELECT * FROM a a2 where a2.id = a1.id
              MINUS 
              SELECT * FROM b b2 where b2.id = b1.id);

I have never seen a select a.* before (i.e. a select without a FROM clause using an alias instead of constant values) but I assume it maps to a co-related subquery

Note that onedaywhen's answer would work as well in Oracle (when replacing except with minus):

WITH a_minus_b AS (
  SELECT * 
  FROM A
  minus 
  SELECT *
  FROM B
)
SELECT * 
FROM a_minus_b T 
   JOIN B ON T.ID = B.ID;

SQLFiddle: http://sqlfiddle.com/#!4/3f369/1

Just for completeness: in standard SQL this could be written as:

SELECT *
FROM a 
  JOIN b 
    ON a.id = b.id 
where (a.string, a.dt, a.b, a.num) is distinct from (b.string, b.dt, b.b, b.num);

which is e.g. supported by Postgres: http://sqlfiddle.com/#!15/b431b/1

Related Question