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
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s