Skip Headers

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)

Part Number A96533-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Oracle Tools to Gather Database Statistics

This chapter explains why performance data gathering is important, and it describes how to use available Oracle tools.

This chapter contains the following sections:

Overview of Tools

Effective data collection and analysis is essential for identifying and correcting system performance problems. Oracle provides a number of tools that allow a performance engineer to gather information regarding instance and database performance.

Principles of Data Gathering

To effectively diagnose a performance problem, it is vital to have an established performance baseline for later comparison when the system is running poorly. Without a baseline data point, it can be very difficult to identify new problems. For example, perhaps the volume of transactions on the system has increased, or the transaction profile or application has changed, or the number of users has increased.

Although Oracle Enterprise Manager, Statspack and BSTAT/ESTAT, and the V$ views have different interfaces (GUI, command line, SQL), the majority of data they collect and report on is extracted from the V$ views. Because the V$ views are based on memory-resident data, when an instance is shut down, the data related to the instance is lost.

In order to perform analysis of data from one day to the next, data visible through the V$ views must be saved. On each instance startup, the memory resident V$ views are reinitialized; hence, to determine what has changed within any particular period, you must calculate the difference in the performance data. This is done by subtracting the statistic values at the beginning of the period from the statistic values at the end of the period. This gives you the activity of the instance during that period, or the delta.The delta of each statistic can then be normalized (for example, over seconds or over transactions).

The delta of all statistics for a period of time can be considered a baseline, assuming the response time and operations performed on the instance were representative of some typical load on your system (that is, batch, online, or both).

Each of the tools Oracle provides (except for the V$ views themselves) has a mechanism for saving this data and for determining the delta.

It is also important to gather operating system and network statistics. These can then be correlated with the Oracle performance data. If you are using Statspack, BSTAT/ESTAT, or your own tool, you should devise a mechanism for collecting operating system statistics. With Oracle Enterprise Manager, this capability is built-in.

Interpreting Statistics

When initially examining performance data, you can formulate potential theories by examining your statistics. One way to ensure that your interpretation of the statistics is correct is to perform cross-checks with other data. This establishes whether a statistic or event is really of interest.

Some pitfalls are discussed in the following sections:

Oracle Enterprise Manager Diagnostics Pack

Oracle Enterprise Manager (EM) Diagnostics Pack captures related operating system, middle-tier, and application performance data, in addition to instance performance data, allowing end-to-end diagnostics.

The Diagnostics Pack can automatically analyze this performance data, display it in a graphical interface, and use alerts to immediately direct you to any performance problems. You can be alerted automatically through email or page when a problem is detected. Oracle Enterprise Manager also includes an integrated diagnostics methodology that uses guided drilldowns and expert advice to help you quickly resolve performance issues. Figure 20-1 is a screen of the Oracle Performance Manager.

Figure 20-1 Oracle Performance Manager

Text description of emperfmgr1.gif follows.

Text description of the illustration emperfmgr1.gif

EM also lets you store the captured data in a separate performance repository database. You can store the performance data for multiple databases in the same repository.

The EM Intelligent Agent data gathering service collects performance data on a scheduled basis. A single agent can manage the data collections for all Oracle databases and the operating system of the target node. The data is automatically stored in an historical data repository for performance reporting. Data stored in the repository can be used to analyze many facets of database performance, such as database load, cache allocations and efficiency, resource contention, and high-impact SQL.

Performance data collections can be initiated directly from the EM Console or through the Capacity Planner application of the Oracle Enterprise Manager Diagnostics Pack. HTML reports of historical performance data can be generated from the EM Console. These reports provide a comprehensive analysis of database system usage and performance, which can be easily accessed and navigated from a browser. EM also provides a graphical real-time Performance Overview for monitoring a subset of these performance metrics using line charts, bar graphs, and so forth.

The Performance Overview charts let you troubleshoot existing performance problems by drilling into performance data to track down the source of a performance bottleneck. For example, a decline in the memory sort percentage can be immediately investigated by drilling down to the sessions and corresponding SQL responsible for high volume sort activity. High-impact SQL statements discovered through this process can be further investigated by launching SQL diagnostic tools in the context of the problem.

See Also:


Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tuning scripts by collecting more information, and by storing the performance statistics data permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an instance health and load summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.

See Also:

Chapter 21, "Using Statspack"

V$ Performance Views

The V$ views are the performance information sources used by all Oracle performance tuning tools. The V$ views are based on memory structures initialized at instance startup. The memory structures, and the views that represent them, are automatically maintained by Oracle throughout the life of the instance.

See Also:

Chapter 24, "Dynamic Performance Views for Tuning"

If you choose not to use an Oracle tool to gather your performance data, then you need to develop your own. You need to save data from the required performance views to a structure on disk, so that the data can be analyzed and compared with other data collected. Because there are multiple collections, you need a key to identify each collection. This method is very similar to the method used by Statspack.

The following is an example of how to save performance data from a single V$ view to an Oracle table. To implement your own collection tool, you must perform a similar collection mechanism for all essential V$ views.


Oracle recommends using Oracle Enterprise Manager Diagnostics Pack or Statspack to gather performance data. These tools have been designed to capture all of the data needed for performance analysis.

Example - Saving File I/O Data

The following example creates a table that stores the collection data. The table has all V$FILESTAT columns and also includes the collection ID column and the collection date column. A sample SQL statement that reports the delta in this table for the first two collections has also been included.

Create the table, and insert the first data collection:

CREATE TABLE coll_filestat AS
  SELECT 1       coll_id       -- collection number
       , sysdate coll_date     -- collection date
       , fs.* 

ALTER TABLE coll_filestat add
   (CONSTRAINT coll_filestat PRIMARY KEY (coll_id, file#));

At the end of the interval, insert second collection:

 INSERT INTO coll_filestat
 SELECT 2                      -- collection number
       , sysdate               -- collection date
       , fs.*


In order to insert any other collections, you need to keep the collection ID key unique. This could be done using a sequence number. (The sequence number should have value for all V$ views captured in one collection.)

To query for high I/O tablespaces:

SELECT t.tablespace_name
      ,   SUM(fs2.phyrds-fs1.phyrds)
        / MAX(86400*(fs2.coll_date-fs1.coll_date)) "Rd/sec"
      ,   SUM(fs2.phyblkrd-fs1.phyblkrd)
        / MAX(86400*(fs2.coll_date-fs1.coll_date)) "Blk/sec"
      ,   SUM(fs2.phywrts-fs1.phywrts) 
        / MAX(86400*(fs2.coll_date-fs1.coll_date)) "Wr/sec" 
      ,   SUM(fs2.phyblkwrt-fs1.phyblkwrt)
        / MAX(86400*(fs2.coll_date-fs1.coll_date)) "Blk/sec"
   FROM coll_filestat fs1, coll_filestat fs2, dba_data_files t
  WHERE fs2.file#   = fs1.file#
    AND fs2.coll_id = fs1.coll_id + 1
    AND t.file_id   = fs2.file#
  GROUP BY t.tablespace_name
  ORDER BY sum(fs2.phyblkrd+fs2.phyblkwrt-fs1.phyblkrd-fs1.phyblkwrt) DESC;

The following is a sample output from the preceding select statement:

TABLESPACE_N Rd/sec Blk/sec Wr/sec Blk/sec
------------ ------ ------- ------ -------
AP_T_02       287.1  2245.7     .0      .0
PO_T_01       313.5   650.6     .2      .2
RECEIVABLE_T  401.0   613.8    2.4     2.4
INV_T_01      154.3   155.3     .0      .0
APPLSYS_T_01   63.3   139.6     .4      .4
PA_T_01       102.3   102.3     .0      .0
SO_I_01        63.4    63.4   34.5    34.5
TEMP            2.3    45.0    1.9    47.0
RECEIVABLE_I   73.0    73.0     .1      .1
AP_T_03        69.3    69.3     .0      .0
RECEIVABLE_I   65.1    65.1    1.9     1.9
SO_T_01        54.0    57.8    2.9     2.9
SYSTEM         45.2    59.0     .3      .3
PER_T_01       48.0    58.7     .0      .0
AP_T_01        12.9    51.0     .2      .2
SO_T_03        43.0    43.0    1.2     1.2
PER_I_01       30.8    30.8     .0      .0
FA_T_01        22.3    22.3     .0      .0
INV_I_01       20.7    20.7     .7      .7
PO_I_01        19.5    19.5     .7      .7
GSR_T_01       19.2    19.2     .4      .4
INV_I_03       18.3    18.3     .0      .0
ROLL_01         1.4     1.4   14.7    14.7
PA_I_01        14.3    14.3     .2      .2