Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, March 31, 2008

ORA-01986: OPTIMIZER_GOAL is obsolete

I got a call for issue with a form. After the 10g upgrade, a standard form Launch Contracts which takes 1 second to launch was taking 30 minutes to open. A trace on this form showed

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 2)
unable to set optimizer goal
ORA-01986: OPTIMIZER_GOAL is obsolete

parse error offset: 33

Bug 5854184 describes this issue and advises:

5386974.992 KAT Alt 100 05-May-2006 11.5.10 LAUNCH CONTRACTS IS VERY SLOW
Fix: Check if you have index WF_NOTIFICATIONS_N1 for table WF_NOTIFICATIONS
If you have this index please rebuild the index, analyze the table and the index. This will allow the optimizer to choose the best plan to execute the
Query.

Rebuilding the index didn’t help. Analyzing the table and index did not help. Gathering statistics with DBMS_STATS package on table did the trick:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME =>'WF_NOTIFICATIONS');

PL/SQL procedure successfully completed.

SQL> exit

Once I ran this, the form started opening in less than 1 second.

No comments: