Wednesday, October 12, 2016

Don't Forget System Level Statistics

If you do any work with performance tuning, you know the importance of Object Level statistics. Oracle uses this information to estimate how many rows will be returned by different steps in the plan. These estimates help the Optimizer form what it thinks is the best plan.

There are numerous subtopics that can have large impacts on SQL Statements. One statistics related item that is often overlooked is System Level Statistics.

When I talk about system level statistics I am referring to three different things:
1. Fixed object statistics
2. Data Dictionary statistics
3. System stats.

If these items are not kept up to date, then you can see some performance degradation. Oracle has several blog posts detailing the importance of these statistics. These statistics should be updated following upgrades to the database software. The system statistics should be updated following any hardware changes.

Fixed Object Statistics

This is different from user level object statistics. For example if you are querying the EMP and DEPT tables then you usually will want to have accurate statistics on the tables which reflect the data distribution of values in the columns of those tables. I say usually because there may be exceptions to this rule.

You can check the status of the fixed object statistics using the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  table_name IN
             (SELECT name
              FROM   v$fixed_table
              WHERE  type = 'TABLE'
             )
ORDER BY last_analyzed NULLS LAST;

Fixed Objects are the X$ objects defined in the database. These objects are not owned by regular users, so gathering stats on user level objects will not impact these objects. There is a different command, EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, that will gather stats on these objects. The statistics should be run while the system is performing a normal workload. If there are significant updates to the workload then the statistics should be regathered. Also, the statistics should be regathered following any upgrades.

For more information about the importance of fixed objects can be seen here:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

Data Dictionary Statistics

Similar to the fixed objects, data dictionary statistics are also used by the system to build plans for system queries. If these are not kept up to date you will see some performance issues related to queries against those objects.

You can check the status of data dictionary statistics with the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  owner='SYS'
ORDER BY last_analyzed NULLS LAST;

The command to update these statistics is DBMS_STATS.GATHER_DICTIONARY_STATS.

At a minimum this should be run after every database upgrade. To keep these statistics up to date, you may choose to run the gather command more frequently such as every quarter.

For additional information you can also look at the My Oracle Support Document, How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (Doc ID 457926.1)

System Statistics

The system statistics collect information about the hardware, in particular CPU performance and I/O performance. The Oracle optimizer will use this information to determine the cost of a statement. If this data is not accurate then it can result in suboptimal plans. This data is collected with the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS. As part of any hardware changes or database upgrades, these statistics should be recollected. Note that if you are running on Exadata, you should use the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’).

Warning! There is a case where a bug caused the command to collect inaccurate information. Oracle bug 9842771 impacted the system statistics collected in release 11.2.0.2. So it is advised to review the values before and after collecting.

To check the system statistics use the following query

select    *
from    sys.aux_stats$;

These fixed object and system statistics provide a foundation that is used by the optimizer to impact many different statements. If you are collecting user level objects but not keeping the other statistics up to date then you are putting yourself at risk for poor performance.