SQL Performance (MOSC)

MOSC Banner

Gathering stats - best practices question

edited May 23, 2013 3:57AM in SQL Performance (MOSC) 10 commentsAnswered
 I've read the documentation, but wanted to verify what I *think* I read.
My question is about when and how exactly to use gathering:

exec dbms_stats.gather_schema_stats (‘SYS’);
exec dbms_stats.gather_database_stats (gather_sys=>TRUE);
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;

First of all, in what order should the above four statements be run after an upgrade? I recently did an upgrade to 11.2.0.3 from 10.2.0.4, and I read that the GATHER_FIXED_OBJECTS_STATS should ideally be done twice; once during a representative workload after the instance has been brought up after the upgrade, and once a week later, also during a representative (peak) workload. Should all of these other statements also be run again, and if so in what order? 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center