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

Wednesday, September 3, 2008

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.

1 comment:

Victoria Conwell said...

Hi there!

Thanks for explaining the differences of these two, I admit that it confuses me sometimes.
Let me add, if you insist, There is another procedure, delete_dictionary_stats, which allows you to remove data dictionary stats.
That when you execute the gather_dictionary_stats procedure, it gathers statistics from the SYS and SYSTEM schemas, as well as any other schemas that are related, such as OUTLN or DBSNMP schemas.

Thanks for this post and your blog.
Victoria Conwell
http://www.dermagistreview.org/