21
Using Statspack

This chapter explains how to install, configure, and use Statspack.

The chapter contains the following sections:

• Introduction to Statspack
• Statspack Compared with BSTAT/ESTAT
• How Statspack Works
• Configuring Database Space Requirements for Statspack
• Installing Statspack
• Using Statspack
• Removing Statspack
• Statspack Supplied Scripts and Documentation

  Note: Statspack is not supported with releases earlier than 8.1.6. Storing data from multiple databases in one PERFSTAT user account is currently not supported.

  See Also: "Statspack Documentation" for information about the SPDOC.TXT file, installed with the Oracle database

Introduction to Statspack

When tuning a database, it is important to have an established baseline for later comparison when the system is running poorly. A baseline data point helps identify the factors to check when diagnosing new performance problems. Some factors to check are:

• Has the volume of transactions on the system increased?
• Has the transaction profile or application changed?
• Has the number of users increased?

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

Statspack Compared with BSTAT/ESTAT

Statspack differs from the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

• Statspack collects more data, including high-resource SQL.
• Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. Many of these ratios must be calculated manually when using BSTAT/ESTAT.
• Statspack uses permanent tables owned by the PERFSTAT user to store performance statistics. Rather than creating and dropping tables each time, data is inserted into the existing tables, making it easier to compare historical data.
• Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.

  Note: The term snapshot as used here denotes a set of performance statistics gathered at a single time, identified by a unique ID that includes the snapshot number (or SNAP_ID). This kind of snapshot has nothing to do with Oracle's snapshot replication technology.

• Statspack makes data collection easy to automate using either DBMS_JOB or an operating system utility to schedule collection tasks.
• Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as user commits + user rollbacks. BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = user commits. For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.

  Caution: If you choose to run BSTAT/ESTAT in conjunction with Statspack, do not run both as the same user. There is a name conflict with the STATS$WAITSTAT table.

How Statspack Works

When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package and is granted limited query-only privileges on the V$ views required for performance tuning.

Statspack users become familiar with the concept of a snapshot, a single collection of performance data. Each snapshot taken is identified by a snapshot ID, which is a unique number generated at the time the snapshot is taken. Each time a new collection is taken, a new SNAP_ID is generated.

The SNAP_ID, along with the database identifier (DBID) and instance number (INSTANCE_NUMBER), comprise the unique key for a snapshot. Use of this unique combination allows storage of multiple instances of an Oracle Real Application Clusters database in the same tables.

After snapshots are taken, you can run the performance report. The report prompts you for start and end snapshot IDs and then calculates activity on the instance between the two snapshots, much like a BSTAT/ESTAT report. To compare, the first SNAP_ID supplied can be considered the equivalent of running BSTAT; the second SNAP_ID specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT, which can by its nature only compare two static data points, the report can compare any two snapshots specified.

Configuring Database Space Requirements for Statspack

The default initial and next extent sizes are 100K, 1MB, or 5MB for all Statspack tables and indexes. Approximately 64MB is required to install Statspack.

The amount of database space required by the Statspack package depends on the frequency of snapshots, the size of the database and instance, and the amount of data collected, which can be configured. It is therefore difficult to provide general storage clauses and space utilization predictions that are accurate at each site.

• If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
• If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.

Installing Statspack

There are two ways to install Statspack:

• Interactive Statspack Installation
• Batch Mode Statspack Installation

Batch mode is useful when you do not want to be prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace.

Interactive Statspack Installation

The first step in the installation is the creation of the PERFSTAT user, which owns all PL/SQL code and database objects created, including the Statspack tables, constraints, and the Statspack package. During installation, you are prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace. The default tablespace is used to create all Statspack objects, such as tables and indexes. The temporary tablespace is used for sort-type activities.

When installing the Statspack package, you can either change to the ORACLE_HOME/rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, SPCREATE.SQL.

To install Statspack, perform the following:

• Start SQL*Plus.
• Connect as a user with SYSDBA privilege. For example:

  SQL>  CONNECT / AS SYSDBA
  
  

• Run the SPCREATE.SQL script.
  • On UNIX platforms, enter the following:

    SQL>  @?/rdbms/admin/spcreate
    
    

  • On Windows platforms, enter the following:

    SQL>  @%ORACLE_HOME%\rdbms\admin\spcreate
    
    

• Enter appropriate information when prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace.

The SPCREATE.SQL install script runs the following scripts automatically:

• SPCUSR.SQL: Creates the user and grants privileges
• SPCTAB.SQL: Creates the tables
• SPCPKG.SQL: Creates the package

To ensure that no errors were encountered during the installation, check the SPCUSR.LIS, SPCTAB.LIS, and SPCPKG.LIS output files. For example:

ORACLE_HOME/bin/spcusr.lis
ORACLE_HOME/bin/spctab.lis
ORACLE_HOME/bin/spcpkg.lis

Batch Mode Statspack Installation

To install Statspack in batch mode, you must assign values to the SQL*Plus variables that specify the default and temporary tablespaces before running SPCREATE.SQL. The variables are:

• DEFAULT_TABLESPACE: For the default tablespace
• TEMPORARY_TABLESPACE: For the temporary tablespace
• PERFSTAT_PASSWORD: For the PERFSTAT user

For example, on UNIX:

SQL>  CONNECT / AS SYSDBA
SQL>  define default_tablespace='TOOLS'
SQL>  define temporary_tablespace='TEMP'
SQL>  define perfstat_password='my_perfstat_password'
SQL>  @?/rdbms/admin/spcreate

When SPCREATE.SQL is run, it does not prompt for the information provided by the variables.

Using Statspack

The following topics are discussed in this section:

• Taking a Statspack Snapshot
• Automating Statistics Gathering
• Running a Statspack Performance Report
• Configuring the Amount of Data Captured in Statspack
• Time Units Used for Wait Events
• Event TimingsManaging and Sharing Statspack Performance Data
• Oracle Real Application Clusters Considerations with Statspack

Taking a Statspack Snapshot

The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user and run the procedure STATSPACK.SNAP. For example:

SQL>  CONNECT perfstat/my_perfstat_password
SQL>  EXECUTE statspack.snap;

Taking such a snapshot stores the current values for the performance statistics in the Statspack tables. This snapshot can be used as a baseline for comparison with another snapshot taken at a later time.

For better performance analysis, set the initialization parameter TIMED_STATISTICS to TRUE. Statspack will then include important timing information in the data it collects. You can change the TIMED_STATISTICS parameter dynamically by using the ALTER SYSTEM statement. Timing data is important and is usually required by Oracle support to diagnose performance problems.

Typically, to automate the gathering and reporting phases (during a benchmark, for example), you might need to know the snap_id of the snapshot just taken. To take a snapshot and display the snap_id, call the STATSPACK.SNAP function.

Example 21-1 Calling the snap Function in SQL*Plus

Using an anonymous PL/SQL block,

SQL> variable snap number;
SQL> begin   :snap := statspack.snap;   end;
  2  /
PL/SQL procedure successfully completed.
SQL> print snap
      SNAP
----------
        12

Automating Statistics Gathering

To make performance comparisons from one day, week, or year to the next, you need multiple snapshots taken over a period of time. The best method to gather snapshots is to automate the collection at regular intervals. You have the following options:

• Within the database, use the Oracle DBMS_JOB procedure to schedule snapshots.
• Use an operating system utility, such as cron on UNIX, to schedule snapshots. On Windows platforms, you can use the at utility on Windows NT or System Tools > Scheduled Tasks on Windows 2000.

Using DBMS_JOB to Collect Statistics

The DBMS_JOB package is an Oracle-automated method for scheduling and running different tasks, such as collecting statistics. A sample script on how to do this is supplied in SPAUTO.SQL, which schedules a snapshot every hour, on the hour.

You might want to schedule snapshots at regular times each day to reflect your system's OLTP or batch peak loads. For example, you could take snapshots at 9 a.m, 10 a.m, 11 a.m, 12 noon, and 6 p.m. for the OLTP load, and then take a snapshot at 12 midnight and 6 a.m for the batch window.

In order to use DBMS_JOB to schedule snapshots, you must set the JOB_QUEUE_PROCESSES initialization parameter to greater than 0 in the initialization file, so that the job can be run automatically.

Changing the Interval of Statistics Collection

Use the DBMS_JOB.INTERVAL procedure to change the interval of statistics collection. For example:

EXECUTE DBMS_JOB.INTERVAL(job_number,'SYSDATE+(1/48)');

Where 'SYSDATE+(1/48)' results in the statistics being gathered each 1/48 hours, or every half hour, and job_number refers to the specific job that you want to run.

To force a job to run immediately:

EXECUTE DBMS_JOB.RUN(job_number);

To remove a specified job:

EXECUTE DBMS_JOB.REMOVE(job_number);

Running a Statspack Performance Report

After snapshots are taken, you can generate performance reports. The SQL scripts that generate the reports prompts you for a beginning snapshot ID, an ending snapshot ID, and a report name. The Statspack package includes two reports.

• First, run a Statspack report, SPREPORT.SQL, which is a general instance health report that covers all aspects of instance performance. This reports calculates and prints ratios and differences for all statistics between the two snapshots, similar to the BSTAT/ESTAT report.
• After examining the instance report, run a SQL report, SPREPSQL.SQL, on a single SQL statement (identified by its hash value). The SQL report only reports on data relating to the single SQL statement.

  Note: It is not correct to specify begin and end snapshots where the begin snapshot and end snapshot were taken from different instance startups. In other words, the instance must not have been shutdown between the times that the begin and end snapshots were taken. This is necessary because the database's dynamic performance tables, which Statspack queries to gather the data, reside in memory. Hence, shutting down the database resets the values in the performance tables to 0. Because Statspack subtracts the begin-snapshot statistics from the end-snapshot statistics, the resulting output is invalid. If begin and end snapshots taken between shutdowns are specified in the report, then the report shows an appropriate error to indicate this.

Because data gathering is separate from report production, you have flexibility to base a report on any data points you select. For example, as DBA you might want to use the supplied automation script to automate data collection every hour, on the hour. If, at some later point, a performance issue arose that might be better investigated by looking at a three-hour data window, all you have to do is specify the required start point and end point when running the report.

Running the Statspack Report

To examine the change in instance-wide statistics between two time periods, the SPREPORT.SQL script is run while connected to the PERFSTAT user. The SPREPORT.SQL script is located in the rdbms/admin directory of the Oracle home.

When the report is run, you are prompted for the following:

• The beginning snapshot ID
• The ending snapshot ID
• The name of the report text file to be created

  Note: Both the serial number and the session_id (SID) must be the same for the begin and end snapshots. Blank lines between lines of snapshot IDs means that the instance has been restarted (shutdown/startup) between those times, changing the serial number. The blank lines thus identify begin and end snapshots that cannot be used together when running a Statspack report.

Example 21-2 shows the SQL commands to run the report and an example of the partial report output.

Example 21-2 Creating a Statspack Report with Prompts

SQL>  connect perfstat/my_perfstat_password
SQL>  @?/rdbms/admin/spreport

On Windows platforms, the command to run the report is:

SQL>  @%ORACLE_HOME%\rdbms\admin\spreport

Sample output:

SQL>  connect perfstat/my_perfstat_password
Connected.
SQL>  @?/rdbms/admin/spreport

DB Id       DB Name      Inst Num Instance
----------- ------------ -------- ------------
2618106428  PRD1                1 prd1
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------
prd1         PRD1             1 11 May 2000 12:07     5
                              2 11 May 2000 12:08     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2  To use this name, press <return> to 
continue, otherwise enter an alternative. Enter value for report_name: <press 
return or enter a new name>

Using the report name sp_1_2

The report now scrolls past and is also written to the file specified. For example:

ORACLE_HOME/bin/sp_1_2.lis

To run a report without being prompted, assign values to the SQL*Plus variables that specify the begin snap ID, the end snap ID, and the report name before running SPREPORT.

The variables are:

• BEGIN_SNAP: Specifies the begin snapshot ID
• END_SNAP: Specifies the end snapshot ID
• REPORT_NAME: Specifies the report output name

Example 21-3 Creating a Statspack Report Without Prompts (UNIX)

SQL>  connect perfstat/my_perfstat_password
SQL>  define begin_snap=1
SQL>  define end_snap=2
SQL>  define report_name=batch_run
SQL>  @?/rdbms/admin/spreport

When SPREPORT.SQL is run, it does not prompt for the information provided by the variables.

Running the SQL Report

When you examine the instance report, you often find high-load SQL statements that you want to examine more closely. The SQL report, SPREPSQL.SQL, displays statistics, the complete SQL text, and (if a level six snapshot has been taken), information on any SQL plan(s) associated with that statement.

The SQL statement to be reported on is identified by a hash value, which is a numerical representation of the statement's SQL text. The hash value for each statement is displayed for each statement in the SQL sections of the instance report.

You run the SPREPSQL.SQL script while connected as the PERFSTAT user. The report is located in the rdbms/admin directory of the Oracle home.

The SPREPSQL.SQL report prompts you for the following:

• Beginning snapshot ID
• Ending snapshot ID
• Hash value for the SQL statement
• Name of the report text file to be created

Example 21-4 Sample Output of SPREPORT.SQL

SQL>  connect perfstat/my_perfstat_password
Connected.
SQL>  @?/rdbms/admin/sprepsql

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ---------
 2618106428 PRD1                1 prd1

Completed Snapshots

                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- -------
prd1         PRD1            37 02 Mar 2001 11:01     6
                             38 02 Mar 2001 12:01     6

                             39 08 Mar 2001 09:01     5
                             40 08 Mar 2001 10:02     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 39
Begin Snapshot Id specified: 39

Enter value for end_snap: 40
End   Snapshot Id specified: 40

Specify the Hash Value
~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 1988538571
Hash Value specified is: 1988538571

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_39_40_1988538571.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: 

Using the report name sp_39_40_1988538571

The report scrolls past and is also written to the file you specified. For example:

sp_39_40_1988538571.lis

The SPREPSQL.SQL script can run the SQL report in batch mode. To run a report without being prompted, assign values to the SQL*Plus variables that specify the begin snap ID, the end snap ID, the hash value, and the report name before running the SPREPSQL.SQL script. The variables are:

• BEGIN_SNAP: specifies the begin snapshot ID
• END_SNAP: specifies the end snapshot ID
• HASH_VALUE: specifies the hash value
• REPORT_NAME: specifies the report output name

Example 21-5 Running SPREPORT.SQL in Batch Mode

SQL>  connect perfstat/my_perfstat_password
SQL>  define begin_snap=39
SQL>  define end_snap=40
SQL   define hash_value=1988538571
SQL>  define report_name=batch_sql_run
SQL>  @?/rdbms/admin/sprepsql

When SPREPSQL.SQL is run, it does not prompt for the information provided by the variables.

Gathering Optimizer Statistics on the PERFSTAT Schema

For best performance when running the performance reports, collect optimizer statistics for tables and indexes owned by PERFSTAT. You should do this whenever there is significant change in data volumes in PERFSTAT's tables.

To collect optimizer statistics on the PERFSTAT schema, use DBMS_STATS or DBMS_UTILITY, and specify the PERFSTAT user. For example:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);

or

EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT','COMPUTE');

Configuring the Amount of Data Captured in Statspack

Both the snapshot level and the thresholds specified affect the amount of data Statspack captures. You can change the amount of information gathered by specifying a different snapshot level. The higher the snapshot level, the more data is gathered. The default level set at installation is level 5.

For typical usage, level 5 snapshot is effective on most sites. There are certain situations when using a level 6 snapshot is beneficial. These include the following:

• When taking your first baseline
• When a new application or an application change is installed to take a new baseline
• After gathering optimizer statistics

  See Also: "Snapshot Levels"

Snapshot SQL Thresholds

There are other parameters you can configure, in addition to the snapshot level. These parameters are used as thresholds when collecting data on SQL statements; data is captured on any SQL statements that breach the specified thresholds. Snapshot level and threshold information used by the package is stored in the STATS$STATSPACK_PARAMETER table.

Changing the Default Values for Snapshot Levels and SQL Thresholds

You can change the default parameters used for taking snapshots so that they are tailored to the instance's workload. Simply use the appropriate parameter and the new value with the Statspack MODIFY_STATSPACK_PARAMETER or SNAP procedure. For example:

SQL>  EXECUTE STATSPACK.SNAP(i_ucomment=>'this is a temporary commment');
SQL>  EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(i_ucomment=>'this is a 
commment that is saved');

The parameters that can be passed into the MODIFY_STATSPACK_PARAMETER and SNAP procedures are listed in Table 21-1.

Temporarily Using New Values

To temporarily use a snapshot level or threshold that is different from the instance's default snapshot values, you specify the required threshold or snapshot level when taking the snapshot. This value is used only for the immediate snapshot taken; the new value is not saved as the default.

For example, to take a single level 6 snapshot:

SQL>  EXECUTE STATSPACK.SNAP(i_snap_level=>6);

Saving New Defaults

You can save the new value as the instance's default in either of two ways:

• Take a snapshot and specify the new defaults to be saved to the database, using STATSPACK.SNAP the I_MODIFY_PARAMETER input variable.

  SQL>  EXECUTE STATSPACK.SNAP(i_snap_level=>10, i_modify_parameter=>'true');
  
  

  Setting the I_MODIFY_PARAMETER value to TRUE saves the new thresholds in the STATS$STATSPACK_PARAMETER table. These thresholds are used for all subsequent snapshots.

  If the I_MODIFY_PARAMETER was set to FALSE or omitted, then the new parameter values are not saved. Only the snapshot taken at that point uses the specified values. Any subsequent snapshots use the preexisting values in the STATS$STATSPACK_PARAMETER table.

• Change the defaults immediately without taking a snapshot, using the STATSPACK.MODIFY_STATSPACK_PARAMETER procedure. For example, the following statement changes the snapshot level to 10 and modifies the SQL thresholds for BUFFER_GETS and DISK_READS:

  SQL>  EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER
        (i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);
  
  

  This procedure changes the values permanently, but does not take a snapshot.

Snapshot Levels

This section discusses the snapshot levels.

Levels >= 0 General Performance Statistics

Any level greater than 0 collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics.

Levels >= 5 Additional Data: SQL Statements

This level includes all statistics gathered in the lower levels, as well as performance data on SQL statements with high resource usage. In a level 5 snapshot (or higher), the time required for the snapshot to complete depends on the SHARED_POOL_SIZE and on the number of SQL statements in the shared pool at the time of the snapshot. The larger the shared pool, the longer it takes to complete the snapshot.

SQL statements gathered by Statspack are those that exceed one of six predefined threshold parameters:

• Number of executions of the SQL statement. The default 100.
• Number of disk reads performed by the SQL statement. The default 1,000.
• Number of parse calls performed by the SQL statement. The default 1,000.
• Number of buffer gets performed by the SQL statement. The default 10,000.
• Size of sharable memory used by the SQL statement. The default 1 Mb.
• Version count for the SQL statement. The default 20.

The values of each of these threshold parameters are used when deciding which SQL statements to collect. If a SQL statement's resource usage exceeds any one of these threshold values, then it is captured during the snapshot.

The SQL threshold levels used are either those stored in the table STATS$STATSPACK_PARAMETER or by the thresholds specified when the snapshot is taken.

Levels >= 6 Additional Data: SQL Plans and SQL Plan Usage

This level includes all statistics gathered in the lower levels, as well a SQL plans and plan usage data for each of the high-resource SQL statements captured.

A level 6 snapshot gathers valuable information for determining whether the execution plan used for a SQL statement has changed. Therefore, level 6 snapshots should be used whenever a plan might have changed.

To gather the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and it must exceed one of the SQL thresholds. To gather plans for all statements in the shared pool, specify the executions threshold to be zero (0) for those snapshots.

Levels >= 7 Additional data: Segment Level Statistics

This level includes all statistics gathered in the lower levels, and additionally gathers the performance data on highly used segments. RAC specific segment level statistics are also captured with level 7.

A level 7 snapshot gathers information which determines what segments are more heavily accessed and contended. With this information, you can decide to modify the physical layout of some segments or of the tablespaces they reside in. For example, to better spread the segment IO load, you can add files residing on different disks to a tablespace storing a heavily accessed segment or you can partition a segment. This information can also help decide on changing segment attributes values such as PCTFREE or INITRANS. On a RAC environment, this information allows us to easily spot the segments responsible for much of the cross-instance traffic.

Level 7 includes the following segment statistics:

• Logical reads
• Db block changes
• Physical reads
• Physical writes
• Physical reads direct
• Physical writes direct
• Global cache consistent read blocks served (RAC specific)
• Global cache current blocks served (RAC specific)
• Buffer busy waits
• ITL waits
• Row lock waits

Although Statspack captures all segment statistics, it reports only the following statistics that exceed one of the predefined threshold parameters:

• Number of logical reads on the segment. The default is 10,000.
• Number of physical reads on the segment. The default is 1,000.
• Number of buffer busy waits on the segment. The default is 100.
• Number of row lock waits on the segment. The default is 100.
• Number of ITL waits on the segment. The default is 100.
• Number of global cache consistent read blocks served (RAC only). The default is 1,000.
• Number of global cache current blocks served (RAC only). The default is 1,000.

The values of the threshold parameters are used when deciding which segment statistics to collect. If a segment's statistic exceeds a threshold value, all statistics regarding this segment are captured during the snapshot.The threshold levels used are either those stored in the table stats$statspack_parameter, or by the thresholds specified when the snapshot is taken.

Levels >= 10 Additional Statistics: Parent and Child Latches

This level includes all statistics gathered in the lower levels, as well as parent and child latch information. Sometimes data gathered at this level can cause the snapshot to take longer to complete. This level can be resource-intensive, and it should only be used when advised by Oracle personnel.

Specifying a Session ID

If you want to gather session statistics and wait events for a particular session (in addition to the instance statistics and wait events), specify the session ID in the call to Statspack. The statistics gathered for the session include session statistics, session events, and lock activity. The default behavior is to not gather session level statistics.

For example:

SQL>  EXECUTE STATSPACK.SNAP(i_session_id=>3);

Parameters for SNAP and MODIFY_STATSPACK_PARAMETER Procedures

Parameters that can be passed to the STATSPACK.SNAP and STATSPACK.MODIFY_STATSPACK_PARAMETER procedures are as follows:

Table 21-1 Parameters for SNAP and MODIFY_STATSPACK_PARAMETER Procedures

Parameter Name	Range of Valid Values	Default Value	Meaning
i_snap_level	0, 5, 6, 7, 10	5	Snapshot level
i_ucomment	Text	Blank	Comment to be stored with snapshot
i_executions_th	Integer >=0	100	SQL threshold: number of times statement was executed
i_disk_reads_th	Integer >=0	1000	SQL threshold: number of disk reads the statement made
i_parse_calls_th	Integer >=0	1000	SQL threshold: number of parse calls the statement made
i_buffer_gets_th	Integer >=0	10000	SQL threshold: number of buffer gets the statement made
i_sharable_mem_th	Integer >=0	1048576	SQL threshold: amount of sharable memory
i_version_count_th	Integer >=0	20	SQL threshold: number of versions of a SQL statement
i_seg_phy_reads_th	Integer >=0	1000	Segment statistic threshold: number of physical reads on a segment
i_seg_log_reads_th	Integer >=0	10000	Segment statistic threshold: number of logical reads on a segment
i_seg_buff_busy_th	Integer >=0	100	Segment statistic threshold: number of buffer busy waits for a segment
i_seg_rowlock_w_th	Integer >=0	100	Segment statistic threshold: number of row lock waits for a segment
i_seg_itl_waits_th	Integer >=0	100	Segment statistic threshold: number of ITL waits for a segment
i_seg_cr_bks_sd_th	Integer >=0	1000	Segment statistic threshold: number of consistent reads blocks served by the instance for the segment (RAC)
i_seg_cu_bks_sd_th	Integer >=0	1000	Segment statistic threshold: number of current blocks served by the instance for the segment (RAC)
i_session_id	Valid SID from V$SESSION	0 (no session)	Session ID of the Oracle session for which to capture session granular statistics
i_modify_parameter	TRUE, FALSE	FALSE	Determines whether the parameters specified are used for future snapshots

Time Units Used for Wait Events

Oracle supports capturing certain performance data with microsecond granularity. Views that include microsecond timing include the following:

• V$SYSTEM_EVENT, V$SESSION_EVENT (TIME_WAITED_MICRO column)
• V$SQL (CPU_TIME, ELAPSED_TIME columns)
• V$LATCH (WAIT_TIME column)
• V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE (ACTIVE_TIME column)

  Note: Existing columns in other views continue to use centisecond times.

Because microsecond timing might not be appropriate for rolled-up data, Statspack displays most times in seconds. It displays average times in milliseconds for easier comparison with operating system monitoring utilities, which often report timing in milliseconds.

For clarity, the time units used are specified in the column headings of each timed column in the Statspack report. The following convention are used:

• second (s)
• centisecond (cs) - 100th of a second
• millisecond (ms) - 1,000th of a second
• microsecond (us) - 1,000,000th of a second

Event Timings

If timings are available, the Statspack report orders wait events by time.These are listed in the Top-5 and background and foreground wait events sections.

If TIMED_STATISTICS is FALSE for the instance, but a subset of users or programs set TIMED_STATISTICS to TRUE dynamically, then the Statspack report output can look inconsistent, where some events have timings (those which the individual programs/users waited for) and some do not. The Top-5 section also looks unusual in this situation.

Optimally, TIMED_STATISTICS should be set to true at the instance level, for ease of diagnosing performance problems.

Managing and Sharing Statspack Performance Data

This section discusses the following topics:

• Sharing Data Through Export
• Removing Unnecessary Data
• Truncating All Statspack Data

Sharing Data Through Export

If you want to share data with other sites (for example, if Oracle Support requires the raw statistics), then you can export the PERFSTAT user. An export parameter file (SPUEXP.PAR) is supplied for this purpose. To use this file, supply the export command with the userid parameter, along with the export parameter file name. For example:

exp userid=perfstat/my_perfstat_password parfile=spuexp.par

This creates a file called SPUEXP.DMP and the log file SPUEXP.LOG. If you want to load the data into another database, use the import command.

Removing Unnecessary Data

Purge unnecessary data from the PERFSTAT schema using the SPPURGE.SQL script. This deletes snapshots that fall between the begin and end snapshot IDs you specify.

Purging can require the use of a large rollback segment, because all data relating to each snapshot ID to be purged is deleted. You can avoid rollback segment extension errors in one of two ways:

• Specify a smaller range of snapshot IDs to purge.
• Explicitly use a large rollback segment, by executing the SET TRANSACTION USE ROLLBACK SEGMENT statement before running the SPPURGE.SQL script.

  See Also: Oracle9i SQL Reference

When you run SPPURGE.SQL, it displays the instance to which you are connected and the available snapshots. It then prompts you for the low snap ID and high snap ID. All snapshots that fall within this range are purged.

Example 21-6 Sample Run of SPPURGE.SQL

SQL>  CONNECT perfstat/my_perfstat_password
SQL>  SET TRANSACTION USE ROLLBACK SEGMENT rbig;
SQL>  @?/rdbms/admin/sppurge

   Database Instance currently connected to
   ========================================
                                   Instance
      DB Id    DB Name    Inst Num Name
   ----------- ---------- -------- ----------
     720559826 PERF              1 perf

   Snapshots for this database instance
   ====================================
               Snap
      Snap Id Level Snapshot Started      Host            Comment
   ---------- ----- --------------------- --------------- -------------------
            1     5  30 Feb 2000 10:00:01 perfhost
            2     5  30 Feb 2000 12:00:06 perfhost
            3     5  01 Mar 2000 02:00:01 perfhost
            4     5  01 Mar 2000 06:00:01 perfhost

Specify the Low Snap ID and High Snap ID range to purge
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   Enter value for losnapid: 1
   Using 1 for lower bound.

   Enter value for hisnapid: 2
   Using 2 for upper bound.

   Deleting snapshots 1 - 2

Purge of specified snapshot range complete. If you want to rollback the purge, 
it is still possible to do so. Exiting from SQL*Plus automatically commits the 
purge.

SQL> -- end of example output

To purge in batch mode, you must assign values to the SQL*Plus variables that specify the low and high snapshot IDs to purge. The variables are:

• LOSNAPID: Begin snapshot ID
• HISNAPID: End snapshot ID

Example 21-7 Running SPPURGE.SQL in Batch Mode

SQL>  CONNECT perfstat/my_perfstat_password
SQL>  DEFINE losnapid=1
SQL>  DEFINE hisnapid=2
SQL>  @?/rdbms/admin/sppurge

When SPPURGE.SQL is run, it does not prompt for the information provided by the variables.

Truncating All Statspack Data

To truncate all performance data indiscriminately, use SPTRUNC.SQL. This script truncates all statistics data gathered.

Example 21-8 Sample Run of SPTRUNC.SQL

SQL>  CONNECT perfstat/my_perfstat_password
SQL>  @?/rdbms/admin/sptrunc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you would like to continue, enter any string, followed by <return>.
Enter value for anystring: 
entered - starting truncate operation
Table truncated.
<etc>
Truncate operation complete.

Oracle Real Application Clusters Considerations with Statspack

The unique identifiers for a database instance used by Statspack are the DBID and the INSTANCE_NUMBER. When you use Oracle Real Application Clusters, the INSTANCE_NUMBER could change between startups (either because the INSTANCE_NUMBER parameter is set in the initialization file or because the instances are started in a different order).

Statspack uses the INSTANCE_NUMBER and the DBID to identify the instance's snapshot preferences, so you could have a different set of levels or thresholds being used when taking snapshots of an instance. This happens only under the following set of conditions:

• The instance numbers have switched between startups.
• The DBA has modified the default Statspack parameters used for at least one of the instances.
• The parameters used (for example, thresholds and snapshot level) are not the same on all instances.

The parameters differ only if the DBA explicitly modified them after installation, either by saving the specified values or by using the MODIFY_STATSPACK_PARAMETER procedure. To check whether any of the Statspack snapshot parameters are different for the instances, query the STATS$STATSPACK_PARAMETER table.

Removing Statspack

To deinstall Statspack, connect as a user with SYSDBA privilege and run the following SPDROP script from SQL*Plus. For example:

SQL>  CONNECT / AS SYSDBA
SQL>  @?/rdbms/admin/spdrop

The SPDROP.SQL script calls the following scripts:

• SPDTAB.SQL - drops tables and public synonyms
• SPDUSR.SQL - drops the user

Check each of two output files produced (SPDTAB.LIS, SPDUSR.LIS) to ensure that the package was completely deinstalled.

Statspack Supplied Scripts and Documentation

This section discusses the following:

• Scripts for Statspack Installation and Removal
• Scripts for Statspack Reporting and Automation
• Scripts for Upgrading Statspack
• Scripts for Statspack Performance Data Maintenance
• Statspack Documentation

Scripts for Statspack Installation and Removal

The Statspack installation and removal scripts must be run as a user with the SYSDBA privilege.

• SPCREATE.SQL: Creates entire Statspack environment, calling the following scripts:
  • SPCUSR.SQL: Creates the Statspack user (PERFSTAT)
  • SPCTAB.SQL: Creates Statspack tables
  • SPCPKG.SQL: Creates the Statspack package
• SPDROP.SQL: Drops entire Statspack environment, calling the following scripts:
  • SPDTAB.SQL: Drops Statspack tables
  • SPDUSR.SQL: Drops the Statspack user (PERFSTAT)

Scripts for Statspack Reporting and Automation

The Statspack reporting and automation scripts must be run as the PERFSTAT user.

• SPREPORT.SQL: Generates a Statspack report
• SPREPSQL.SQL: Generates a Statspack SQL report for the specific SQL hash value specified
• SPREPINS.SQL: Generates a Statspack report for the database and instance specified
• SPAUTO.SQL: Automates Statspack statistics collection (using DBMS_JOB)

Scripts for Upgrading Statspack

The Statspack upgrade scripts must be run as a user with the SYSDBA privilege.

• SPUP90.SQL: Converts data from the 9.0 schema to the 9.2 schema. Back up the existing schema before running the upgrade.
• SPUP817.SQL: If upgrading from Statspack 8.1.7, this script must be run first.
• SPUP816.SQL: If upgrading from Statspack 8.1.6, this script must be run first, followed by SPUP817.SQL.

Scripts for Statspack Performance Data Maintenance

The Statspack data maintenance scripts must be run as the PERFSTAT user.

• SPPURGE.SQL: Purges a limited range of Snapshot IDs for a given database instance.
• SPTRUNC.SQL: Truncates all performance data in Statspack tables

  Caution: Do not use this script unless you wan to remove all data in the schema you are using. You can choose to export the data as a backup before using this script.

• SPUEXP.PAR: An export parameter file supplied for exporting the whole PERFSTAT user.

Statspack Documentation

The SPDOC.TXT file in the ORACLE_HOME/rdbms/admin directory contains instructions and documentation on the Statspack package.