System Statistics in the Oracle Database

If you can, refer to Oracle’s Metalink note: 149560.1

Firstly, you must have DBA privileges or GATHER_SYSTEM_STATISTICS role to update dictionary system statistics.

Secondly, Oracle recommends that you gather system statistics during peak load in order to give the optimizer the ability to choose the best plan based on system resource usage and throughput as well as the normal information about the database objects.

Read more about it in the Oracle docs for stats.

In general, there are two ways to gather system statistics:

  1. During a normal workload period
  2. while there is noworkload on the system

When workload system statistics are gathered, noworkload system statistics will be ignored. Noworkload system statistics are initialized to default values at the first database startup. In short, you will just have some basic system statistics unless you gather them.

Workload statistics differ all the time with varying amount of load. If your system statistics are gathered at a time when there is significant load on the I/O system, the statistics will reflect that and query execution plans could be affected, adversely or not, as a result.

To delete system statistics, run exec dbms_stats.delete_system_stats(). Workload statistics will be deleted and reset to the default noworkload statistics.

Real World Solution for System Statistics

Moving from not using any system statistics in your database to collecting and using system statistics can be tricky. If you have a good enough stress tool, the same database layout on disk (use RMAN to restore your data), the same disks, structure & number, the same hardware, etc. Basically, if you have an exact replica of your production system to test on, then you can test out gathering system statistics on a test environment and check out the values using this query:

FROM sys.aux_stats$;

If you can’t accurately gather system statistics anywhere but on your production environment, you will need to take a different approach.

Implementing System Statistics

One way is to gather noworkload statistics on each environment when you have no load going through the system. Oracle will gather statistics about your environment and use those values. You can test using workload statistics at a later point with thorough testing.

There is also a good way to test for differing system statistics. You can use the DBMS_STATS.SET_SYSTEM_STATS procedure to set  different values for the system statistics and then see what difference it has on your execution plans for SQL statements.

Note: When new system statistics are gathered, unlike table, index or column statistics, Oracle does not invalidate existing parsed SQL statements which are already in the shared pool. Only newly executed SQL  statements will use the new system statistics. You could run ALTER SYSTEM FLUSH shared_pool in order to make sure all new SQL statements are hard parsed and use the new system statistics, but this is probably not a good thing to do on a production system during times of high load.

You can also read more information about gathering statistics in Oracle in my other article which is about segment statistics, if that’s what you were interested in.

A Personal Approach to System Statistics

I, personally, would not recommend changing or gathering your system statistics without proper testing, even if Oracle do recommend it. Making a change to your statistics could change the explain plans of well performing queries overnight. I would advise you to test any change in a development and QA environment first before making any change to a production environment.

Then, once you have fully tested out the statistics with your application running and it’s been signed off, you can use the values and set them across all your environments. After the appropriate amount of time on your QA and UAT environments you can then roll the same stats to your production database. And remember, if it isn’t broken don’t try and fix it!

In summary, then, I would gather the statistics or set them on the testing environment where the testing and sign-off would happen. This same logic actually applies table and index statistics, too. Once you have a confirmed set of statistics which you know work and SQL queries execute efficiently using them, you should stick with those statistics.

Like it, share it...

Category: Statistics

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *