Oracle 10g performance workaround?

[Brian Hunt]

We have recently upgrade to Oracle 10g, from 9.2, and have found a number of issues. In particular, we have found that query performance can be seriously degraded. One workaround is to revert to Oracle 9.2 optimiser behaviour by wrapping the query with ...

EXECUTE IMMEDIATE 'alter session set optimizer_features_enable=''9.2.0''';

Offending query/loop/etc..

EXECUTE IMMEDIATE 'alter session set optimizer_features_enable=''10.2.0.2''';

Is there any way of implementing this workaround for Query Reporter reports? We have one that has gone from 10 minute runtime to in excess of 4 hours.

 

[Marco Kalter]

It is currently not possible to execute multiple statements in the Query Reporter, so you cannot change the optimizer behavior before execution (this is on the to-do list though). You can only tune the SQL and/or add hints.