Workaround for lousy 8.0.5 optimizer

Last week I posted an entry regarding the optimizer in 8.0.5. See "Oracle 8.0.5 optimizer error?", dec. 7th. To rephrase, the problem is that when you have optimizer_goal=first_rows recursive SQLs are executed using the CBO even though sys' objects aren't analyzed. This leads to severe performance problems - at least in our case. In conjunction with Oracle support, I've found a workaround which I thought might be of use to anyone experiencing the same problems. If you need to use first_rows, do the following:

set optimizer_mode=choose in init.ora. Then issue an "alter session set optimizer_goal=first_rows" from within your application.

When you have optimizer_mode=choose in init.ora, recursive SQLs will also be executed using CHOOSE, which in turn will be RULE, since sys' objects aren't analyzed. Hence, your "own" SQLs will be executed using first_rows while recursive SQLs will be executed using rule.

Regards,
Ivan Bajon, ocp Received on Mon Dec 13 1999 - 07:33:21 CST