How do you gather statistics in your database?

Do you gather statistics randomly when you remember, or do you gather statistics when there is a problem?
Or
Do you gather statistics on a regular basis?

Do you use the default task that gathers statistics, or do you use your own script?

In the last 2 posts, I introduced you to the optimizer statistics concepts, and how statistics are gathered.

Today, I’ll show you what type of stats should be gathered in the database and when, and how to gather them. In the end, I will share with you the script I am using to gather stats in my databases.

1. What Are System Statistics?
2. What Are Data Dictionary Statistics?
3. What Are Fixed Objects Statistics?
4. How Do I Gather Statistics?

1. What Are System Statistics?

System stats where introduced with Oracle 9i, and contain information about the system hardware, such as CPU speed and IO performance. The following statistics are captured (some of them are not captured in older releases):

• sreadtim – single block read time
• mreadtim – multiblock read time
• mbrc – multi-block read count
• cpuspeed – CPU speed
• maxthr – maximum I/O throughput
• slavethr -average slave throughput
• cpuspeedNW – Represents noworkload CPU speed
• ioseektim – I/O seek time equals seek time + latency time + operating system overhead time.
• iotfrspeed – I/O transfer speed is the rate at which an Oracle database can read data in a single read request

(There is a very good document on Oracle’s support site, for more details see How to Collect and Display System Statistics (CPU and IO) for CBO use (Doc ID 149560.1))

System stats are not automatically collected as part of the default gather stats task. These stats don’t need to be collected regularly.

You should collect them once, and then only if any hardware component changes (faster CPU, faster disk)
Oracle recommends you collect these stats during peak workload times.

In order to gather system stats you need to have GATHER_SYSTEM_STATISTICS or DBA role assigned. Use dbms_stats.gather_system_stats procedure to do so.

If you are curious to see what system stats are collected, and what their values are, check out sys.aux_stats$ view.

SQL> select * from sys.aux_stats$;

SNAME			       PNAME				   PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO		       STATUS					 COMPLETED
SYSSTATS_INFO		       DSTART					 11-13-2017 10:48
SYSSTATS_INFO		       DSTOP					 11-13-2017 10:48
SYSSTATS_INFO		       FLAGS				       0
SYSSTATS_MAIN		       CPUSPEEDNW			    2174
SYSSTATS_MAIN		       IOSEEKTIM			       9
SYSSTATS_MAIN		       IOTFRSPEED			    4096
SYSSTATS_MAIN		       SREADTIM 			   6.774
SYSSTATS_MAIN		       MREADTIM
SYSSTATS_MAIN		       CPUSPEED 			    2176
SYSSTATS_MAIN		       MBRC
SYSSTATS_MAIN		       MAXTHR
SYSSTATS_MAIN		       SLAVETHR

13 rows selected.

2. What Are Data Dictionary Statistics?

Data Dictionary Stats are statistics on the data dictionary tables. Because these tables are used in queries, just like any other schema table, it is recommended to gather stats regularly on these tables.

The procedure will gather statistics on the SYS, SYSTEM, and schemas of RDBMS components.

The default gather stats task will gather statistics on the data dictionary as well. In order to gather the stats, you need the following permissions:
You must have both the ANALYZE ANY DICTIONARY, and ANALYZE ANY system privilege, or the DBA role.

To gather stats:

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

You can check if the statistics are up to date for data dictionary stats, by querying DBA_TABLES view.

select table_name, last_analyzed from dba_tables where owner='SYS' order by last_analyzed, table_name;

3. What Are Fixed Objects Statistics?

Fixed Object Statistics are statistics on dynamic performance tables and their indexes, these are called fixed objects.

Fixed objects are the X$ tables (in memory structures). The V$ views that most of us query, such as V$SESSION, V$DATABASE and many more, are based on the X$ tables.

The optimizer is not using dynamic sampling for the X$ tables, if the stats are missing. Instead it is using some predefined values.

This type of stats is not maintained by the gather stats task. It is recommeded to gather fixed object stats during a significant workload on the system.

There is no need to gather these type of stats often, usually you gather new fixed object stats after a major database or application upgrade.

​​​​​​​

You need ANALYZE ANY DICTIONARY system privilege or the DBA role in order to gather fixed object stats.

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

SELECT TABLE_NAME, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER = 'SYS' AND TABLE_NAME LIKE 'X$%' ORDER BY LAST_ANALYZED ASC;

4. How Do I Gather Statistics?

What steps should be included in the gather stats job, what type of stats should be collected?

Is this the rule to be followed?

Of course not! This is what I do!

Is this the best way to do it?
Of course not! I am convinced there are many other ways to do it.

Is this working for me?
Of course it is! Otherwise I wouldn’t do it!

Is this working for you? Should you do it this way?
Give it a try, and find out for yourself! Only you can answer that question.

So what does Diana do in the gather stats script?

First, I disable the default gather stats task.
The reason for it: I want to make sure that stats are gathered for all the objects that need new statistics, every time I run the stats job, that is why I am not using the default task. With the default job, if the maintenance window ends, not all tables that need statistics will get statistics collected.
I incorporated the step to disable the default gather stats task, right into my script, so I don’t need to remember and check if the default stats task is enabled or not.

Second, I gather statistics on the data dictionary. I do not gather system statistics or fixed object statistics in my script, as this is not recommended by Oracle.

Third, I do a listing of the objects with STALE stats, gather statistics on these objects for tables, table partitions, indexes.

Fourth, I list the objects with EMPTY stats, gather statistics on these objects for tables, table partitions, indexes.

I capture the time it takes to gather stats for each object, which is great for trending purposes, or for troubleshooting long job runs.

I also capture the statements to gather stats, this way I can confirm what was gathered in each run.

I use a shell script, which is scheduled as a database job, and runs once a week.

Please find the script here gather_statistics.sh.

​​​​​​​

Disclaimer: You need to update the script to run in your own environment. Run the script at your own risk, and always run it in test environment first.