Difference between GATHER_FIXED_OBJECT_STATS and GATHER_DICTIONARY_STATS

There are two procedures in the DBMS_STATS package for gathering statistics on Oracle native objects:

GATHER_FIXED_OBJECT_STATS
GATHER_DICTIONARY_STATS

There is a very good thread on asktom.oracle.com regarding this. I am borrowing from that thread to explain the difference between the two and when they need to be run:

GATHER_FIXED_OBJECT_STATS collects statistics for fixed X$ and K$ objects. It needs to be run whenever any init.ora parameter is changed. Fixed objects are the magic tables that are not tables - they are not "dictionary" tables. The x$ tables would change size in response to init.ora setting changes generally. Setting processes higher will add rows to various x$ views for example.

So, they could be considered a one time thing unless you make a big change to your parameter
settings.

GATHER_DICTIONARY_STATS collects statistics for SYS schema. It needs to be run whenever you do "big things" to the dictionary (loaded a schema - not put data into the schema, but rather did things in the schema that affect the dictionary like creating and dropping objects...) - you would consider gathering statistics.

Look at last_analyzed for the sys tables to see when the dictionary was gathered against. Metalink Note 281793.1 states that the DBA_OPTSTAT_OPERATIONS view may be used to determine the start and end time of all DBMS_STATS operations executed at the schema and database level.

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.