This is ugly, but sometimes you need to get code working after a migration from 9i to 10g that somehow got overlooked or where you have run out of time to tune sql or statistics.
In the past 4 years, I’ve seen this deployed twice to fix small issues that went unnoticed during testing.
In one case, a query that had been tuned for the 9i optimizer did not work well with the 10g optimizer enabled. The rest of the queries did work fine. In another case, a seldom used delete trigger included a query against v$session that began using parallel query with the 10g optimizer. As a result, the select into clause of the query failed when the select into began fetching multiple rows.
The ugly workaround to get things working fast? Revert the optimizer only within the poorly performing block of code.
alter session set optimizer_features_enable=”9.2.0″
Use execute immediate for PL/SQL code.
execute immediate ‘alter session set optimizer_features_enable=”9.2.0″ ‘;
If you want to be extra careful, you can query the value of optimizer_features_enable, store it in a variable, and then run alter session again to reset the value back to the original setting immediately after the problem code completes.
Note that this is a temporary solution, and it would be better to identify the root cause of the problem and get things to work with 10g.