Oracle Database Statistics
When it comes to statistics within your Oracle database, information is power. That saying translates a lot of different ways but the overall idea is that the more you know about someone or something, the more effective you can be working with that someone or something.
For a database to perform at the top level, you have to give the database engine information about your data. Data about data is called metadata. Oracle statistics is metadata about your data. There are several kinds of statistics that Oracle uses:
Object statistics: These statistics contain information about your actual data. For example, how many rows does a table have? Or, what is the uniqueness of the data within a column?
System statistics: These statistics contain information about the operating resource consumption of the database such as how busy are the CPUs or what is the read/write time of the disks?
Fixed table statistics: These statistics contain information about the database itself with a primary focus on the internal V$ tables or data dictionary.
Entire chapters of books have been devoted to gathering statistics. However, recently (since Oracle 10g) Oracle has provided some methods internal to the database that help in gathering object statistics automatically. The fact is there are still some things database administrators can do to get more information into the hands of the Oracle database because system statistics and fixed table statistics are not yet automatically gathered.
First of all, let's talk about gathering system statistics. System statistics are not gathered by the database. The database comes with a set of generic catchall system statistics but they aren't really going to do us much good. You see, system statistics, sometimes also called workload statistics, should be aligned with your databases' busy times when your workload is heaviest. Those are the times when system statistics are most important because that's when the system may be taxed. The best practice is to capture statistics in the interval of time when the system has the most common workload. Gathering workload statistics does not generate additional overhead.
To gather system statistics, follow this procedure:
Let's say your company is busiest from 12 to 5 p.m.
At 12 p.m., start the system statistics gathering process. Log into your database as a user with database administrator (DBA) privileges and type the following:
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'START' );
At 5 p.m., log back into your database and issue:
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'STOP' );
The most common guideline is that you gather system statistics only when a physical change occurs in your environment, for example, the server gets faster CPUs, more memory, or different disk storage. However, you might also consider doing this if you feel your workload has significantly changed. For example, your company has doubled the number of employees.
One last note on system statistics — you can get more specific with system statistics. This was a most basic example. Some of you might have two distinct workloads which differ greatly in resource consumption throughout your day. For example, you may have a daytime workload which is mostly transaction based and a nighttime workload that is mostly read based. You can actually define more than one set of system statistics and then deploy each one at certain times of the day through a scheduled job so you have the best statistics for the workload. To get more information on using this approach, see the DBMS_STATS package reference with examples for varying workload at this Oracle documentation link.
Lastly, fixed table statistics. Fixed table statistics are statistics that contain metadata about your internal X$ and V$ views. These views are not only used by you, the DBA, but also internally by Oracle. Like normal object statistics, the more Oracle knows about what is contained within these views, the better the optimizer can perform against them. Like system statistics, Oracle recommends that these statistics are best gathered when there is a workload on the system.
You must have the system dabatase administrator (SYSDBA) or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
To gather fixed object statistics follow these steps:
Log into the database as a SYSDBA user.
Issue the command:
Be advised that during the period while these statistics are running, there is going to be a small impact on the system overhead. Since these statistics are similar to object statistics, it should be no more than you would normally experience when gathering object statistics. So, even though Oracle recommends that you do this during peak workload, be sure you can spare the resources to complete the job. It can sometimes take up to 15 minutes. Oracle also recommends fixed object statistics are refreshed if you make any major database or application changes. For example, if you increase the memory of the database then all of the internal tables that contain information about the buffer cache and shared pool may change.