Thursday, May 31, 2012

Oracle 11g : Types of Statistics Collected

This article is the continuation of Oracle automated tasks discussed in the following post
BASH-DBA: Oracle 11g Automated Maintenance Tasks

To properly diagnose any issue with the database, stats should be available. Oracle gathers several types of statistics at the system, session or SQL level.Two main categories for statistic gathering are 

1. Database Statistics
2. OS Statistics

In this article we will cover both the categories briefly 

1.Database Statistics:

DB stats provide the type of load the database is experiencing currently or some time in past. The first and foremost of them would be wait events

1.1.Wait Events:

Wait Event records and presents all the bottlenecks that a process (user or background )encounters from start to finish. It keeps track of the number of times and the amount of time a process spent on each bottleneck.To get the “amount of time ” part, however, you must set the initialization parameter, TIMED_STATISTICS, to TRUE.

You can repeatedly querying some of the following wait event views in short successions when investigating performance bottlenecks.

This is where we can find out the total number of events that Oracle has defined in the database. Do the COUNT (*) against this view.Also, this view can be queried to find the exact spelling of an event name.

The V$SYSTEM_EVENT view is a good place to start if you want to perform a quick health check on the database instance. This quick health check may be helpful when you must diagnose a poorly performing database instance, especially if you are unfamiliar with it and the application. You will quickly discover the top n bottlenecks that plague the database instance since startup. However, don’t judge performance or make any tuning decisions based on these bottlenecks just yet. The information is cumulative since the instance start-up.You should always order the data from this view by the TIME_WAITED column

The V$SESSION_EVENT view is useful when SID of the session is known that is currently connected to the instance.Let’s say you get a call about a job that is running very slowly. You ask for  the USERNAME and find the SID from the V$SESSION view. You then query the V$SESSION_EVENT view for the particular SID and order the result by the TIME_WAITED column

A good time to query the V$SESSION_WAIT view is when you get a call about a slow running application and you want to investigate what the application is doing at that particular moment. Due to its real-time nature, you have to query this view repeatedly, most likely in quick successions

The V$SESSION view displays information about each current session and contains the same wait statistics as those found in the V$SESSION_WAIT view

The V$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for each session.

The V$SESSION_WAIT_HISTORY view displays information about the last ten wait events for each active session (such as event type and wait time).


The V$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis.


The V$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.


The V$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent in each class of wait events.


The V$TEMP_HISTOGRAM view displays a histogram of times waited during single block reads for each temporary file.
1.2.System and Session Statistics:

A large number of cumulative database statistics are available on a system and session level through the V$SYSSTAT and V$SESSTAT views for vital information about CPU usage

 1.3.Time Model Statistics:

When tuning an Oracle system, each component has its own set of statistics. To look at the system as a whole, it is necessary to have a common scale for comparisons. Because of this, most Oracle advisories and reports describe statistics in terms of time. In addition, the V$SESS_TIME_MODEL andV$SYS_TIME_MODEL views provide time model statistics. Using the common time instrumentation helps to identify quantitative effects on the database operations.

1.4.Active Session History (ASH)

The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.

For further information, see below the following articles on

BASH-DBA: Manually Getting Active Session Information
BASH-DBA: Getting ASH Information from the Data Dictionary Views

2. OS Statistics
Operating system statistics are only an indication of how the hardware and operating system are working.

2.1.CPU Statistics

The V$OSSTAT view captures machine level information in the database, making it easier for you to determine if there are hardware level resource issues. TheV$SYSMETRIC_HISTORY view shows a one-hour history of the Host CPU Utilization metric, a representation of percentage of CPU usage at each one-minute interval. The V$SYS_TIME_MODEL view supplies statistics on the CPU usage by the Oracle database. Using both sets of statistics enable you to determine whether the Oracle database or other system activity is the cause of the CPU problems.

2.2.Disk I/O Statistics

Because the database resides on a set of disks, the performance of the I/O subsystem is very important to the performance of the database
I/O statistics of database files that are or have been accessed are captured in the V$IOSTAT_FILE view.I/O statistics for database functions (such as the LGWR and DBWR) are captured in the V$IOSTAT_FUNCTION view
When Oracle Database Resource Manager is enabled, I/O statistics for all consumer groups that are part of the currently enabled resource plan are captured in the V$IOSTAT_CONSUMER_GROUP view

2.3.Network Statistics

reference : Oracle documentation

No comments:

Post a Comment