Thursday, May 5, 2016

Gathering Statistics In Oracle - Part 1

If a system is completely new, these are the statistics which should be gathered:

  • Dictionary Statistics / SYS schema Statistics - They are the same thing.
    exec dbms_stats.gather_dictionary_stats;
  • Fixed Objects Statistics
    exec dbms_stats.gather_fixed_objects_stats;
  • System Statistics - Do not mix this up with SYS schema statistics
    • First run an IO Calibration:
      dbms_resource_manager.calibrate_io();
      • You will find the results in the following views:
        • V$IO_CALIBRATION_STATUS
        • DBA_RSRC_IO_CALIBRATE
    • Then run the system statistics:
      exec dbms_stats.gather_system_stats(interval=>60);
      • The interval is in minute. The store procedure will execute and run in the background for 60 minutes.
It's important to run all 3 when there's a realistic load on the system. However system statistics have a system wide impact. So if the system is already in production, a test should be performed in an identical staging environment first. If a staging environment is not available, backup the system stats first and get very familiar with how to restore them. 

Some storage hardware vendors with storage tiering and dynamic change of blocks between tiers tend to mess up the IO calibration. I have experienced terrible IO Calibration and system statistics with EMC VMAX Fast technology and I haven't found a nice way for oracle and EMC to communicate about disks IO very well.

No comments :

Post a Comment