-------------------------------------------------------------------------
Oracle9i Server
Release 9.2
Production
-------------------------------------------------------------------------
Copyright (C) 1993, 2002, Oracle Corporation. All rights reserved.
Author: Connie Dialeris Green
Contributors: Cecilia Gervasio, Graham Wood, Russell Green, Patrick Tearle,
Harald Eri, Stefan Pommerenk, Vladimir Barriere
Please refer to the Oracle9i server README file in the rdbms doc directory,
for copyright, disclosure, restrictions, warrant, trademark, disclaimer,
and licensing information. On Unix systems, the file is README.doc,
and on Windows systems the file is README_RDBMS.HTM.
Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.
-------------------------------------------------------------------------
Statistics Package (STATSPACK) README (spdoc.txt)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE OF CONTENTS
-----------------
0. Introduction and Terminology
1. Oracle Enterprise Manager (EM) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Running the SQL report
4.4. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Sharing data via export
8.2. Purging/removing unnecessary data
8.3. Removing all data
9. New and Changed Features
9.1. Changes between 9.0 and 9.2
9.2. Changes between 8.1.7 and 9.0
9.3. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 9.2
10.1.2. Using Statspack shipped with 9.0
10.1.3. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 9.0 to 9.2
10.2.2. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.3. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.4. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.5. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.6. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Report
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications
0. Introduction and Terminology
--------------------------------
To effectively perform reactive tuning, it is vital to have an established
baseline for later comparison when the system is running poorly. Without
a baseline data point, it becomes very difficult to identify what a new
problem is attributable to: Has the volume of transactions on the system
increased? Has the transaction profile or application changed? Has the
number of users increased?
Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT
tuning scripts by collecting more information, and also by storing the
performance statistics 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.
Statspack improves on the existing UTLBSTAT/UTLESTAT performance scripts
in the following ways:
- Statspack collects more data, including high resource SQL
(and the optimizer execution plans for those statements)
- Statspack pre-calculates many ratios useful when performance
tuning, such as cache hit ratios, per transaction and per
second statistics (many of these ratios must be calculated
manually when using BSTAT/ESTAT)
- Permanent tables owned by PERFSTAT store performance statistics;
instead of creating/dropping tables each time, data is inserted
into the pre-existing tables. This makes historical data
comparisons easier
- Statspack separates the data collection from the report generation.
Data is collected when a 'snapshot' is taken; viewing the data
collected is in the hands of the performance engineer when he/she
runs the performance report
- Data collection is easy to automate using either dbms_job or an
OS utility
NOTE: The term 'snapshot' is used to denote a set of statistics gathered
at a single time, identified by a unique Id which includes the
snapshot number (or snap_id). This term should not be confused
with Oracle's Snapshot Replication technology.
How does Statspack work?
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the
collection, automation, storage and viewing of performance data. A user
is automatically created by the installation script - this user, PERFSTAT,
owns all objects needed by this package. This user is granted limited
query-only privileges on the V$views required for performance tuning.
Statspack users will become familiar with the concept of a 'snapshot'.
'snapshot' is the term used to identify 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 (using this
unique combination allows storage of multiple instances of a Clustered
database in the same tables).
Once snapshots are taken, it is possible to run the performance report.
The performance report will prompt for the two snapshot id's the report
will process. The report produced calculates the activity on the instance
between the two snapshot periods specified, in a similar way to the
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 it's nature only compare two static data points, the report
can compare any two snapshots specified.
1. Oracle Enterprise Manager (EM) and Statspack
------------------------------------------------
Statspack allows you to capture Oracle instance-related performance data,
and report on this data in a textual format.
Oracle Enterprise Manager Diagnostics Pack offers extended features including
capturing related operating system, middle-tier and application performance
data for end-to-end diagnostics.
The Oracle 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. In addition, you can be alerted
automatically via 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.
EM also allows you the ability to store the captured data in a separate
performance repository database, and to store the performance data for
multiple databases in the same repository.
For more information about Oracle Enterprise Manager or for a trial license,
visit the Oracle website () or the Oracle Store website
(oraclestore.oracle.com).
2. Statspack Configuration
---------------------------
2.1. Database Space Requirements
The amount of database space required by the package will vary considerably
based on the frequency of snapshots, the size of the database and instance,
and the amount of data collected (which is configurable).
It is therefore difficult to provide general storage clauses and space
utilization predictions which will be accurate at each site.
The default initial and next extent sizes are 100k, 1MB, 3MB or 5MB for all
Statspack tables and indexes. To install Statspack, the minimum
space requirement is approximately 100MB.
Locally Managed Tablespaces
---------------------------
If you install the package in a locally-managed tablespace, modifying
storage clauses is not required, as the storage characteristics are
automatically managed.
Dictionary Managed Tablespaces
------------------------------
If you install the package in a dictionary-managed tablespace, Oracle
suggests you monitor the space used by the objects created, and adjust
the storage clauses of the segments, if required.
2.2. Installing the Tool
Installation scripts create a user called PERFSTAT, which will own all
PL/SQL code and database objects created
(including the STATSPACK tables, constraints and the STATSPACK package).
During the installation you will be prompted for the PERFSTAT
user's password and default and temporary tablespaces.
The default tablespace will be used to create all Statspack
objects (such as tables and indexes). The temporary tablespace
will be used for sort-type activities (for more information on
temporary tablespaces, see the Oracle9i Concepts Manual).
NOTE:
o A password for PERFSTAT user is mandatory and there is no default
password; if a password is not specified, the installation will
abort with an error indicating this is the problem.
o For security reasons, keep PERFSTAT's password confidential.
o Do not specify the SYSTEM tablespace for the PERFSTAT users
DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the
installation will abort with an error indicating this is the
problem. This is enforced as Oracle do not recommend using
the SYSTEM tablespace to store statistics data, nor for workareas.
Use a TOOLS tablespace to store the data, and your instance's
TEMPORARY tablespace for workarea overflows.
o During the installation, the dbms_shared_pool and dbms_job
PL/SQL packages are created. dbms_shared_pool is used to
pin the Statspack package in the shared pool; dbms_job
is created on the assumption the DBA will want to schedule
periodic snapshots automatically using dbms_job.
To install the package, either change directory to the ORACLE_HOME
rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin
directory when calling the installation script, spcreate.
To run the installation script, you must use SQL*Plus and connect as
a user with SYSDBA privilege.
e.g. Start SQL*Plus, then:
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate
The spcreate install script runs 3 other scripts - you do not need to
run these - these scripts are called automatically:
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package
Check each of the three output files produced (spcusr.lis,
spctab.lis, spcpkg.lis) by the installation to ensure no
errors were encountered, before continuing on to the next step.
Note that there are two ways to install Statspack - interactively (as
shown above), or in 'batch' mode; batch mode is useful when you do
not wish to be prompted for the PERFSTAT user's default and
temporary tablespaces.
Batch mode installation
~~~~~~~~~~~~~~~~~~~~~~~
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the password and the default and temporary
tablespaces before running spcreate.
The variables are:
perfstat_password -> for the password
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g.
on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp'
SQL> define perfstat_password='erg8oiw'
SQL> @?/rdbms/admin/spcreate
SQL> undefine perfstat_password
spcreate will no longer prompt for the above information.
2.3. Errors during installation
A common error made during Statspack installation is running the install
script from Server Manager (svrmgrl) rather than from SQL*Plus. If you
use svrmgrl, the installation will fail.
Another possible error during installation is to specify the SYSTEM
tablespace for the PERFSTAT user's DEFAULT or TEMPORARY tablespace.
In such a situation, the installation will fail, stating the problem.
To correctly install Statspack after such errors, first run the
de-install script, then the install script. Both scripts must be
run from SQL*Plus.
e.g. Start SQL*Plus, connect as a user with SYSDBA privilege, then:
SQL> @spdrop
SQL> @spcreate
3. Gathering data - taking a snapshot
--------------------------------------
The simplest interactive way to take a snapshot is to login to SQL*Plus
as the PERFSTAT user, and execute the procedure Statspack.snap:
e.g.
SQL> connect perfstat/perfstat_password
SQL> execute statspack.snap;
Note: In a Clustered database environment, you must connect to the
instance you wish to collect data for.
This will store the current values for the performance statistics
in the Statspack tables, and can be used as a baseline snapshot
for comparison with another snapshot taken at a later time.
For better performance analysis, set the initialization parameter
timed_statistics to true; this way, Statspack data collected will include
important timing information. The timed_statistics parameter is also
dynamically changeable using the 'alter system' command. Timing data is
important and is usually required by Oracle support to diagnose performance
problems.
Typically, in the situation where you would like to automate the gathering
and reporting phases (such as during a benchmark), you may 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. Below is an example of calling
the snap function using an anonymous PL/SQL block in SQL*Plus:
e.g.
SQL> variable snap number;
SQL> begin :snap := statspack.snap; end;
2 /
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
12
3.1. Automating statistics gathering
To be able to make comparisons of performance from one day, week or
year to the next, there must be multiple snapshots taken over a period
of time.
The best method to gather snapshots is to automate the collection on
a regular time interval. It is possible to do this:
- within the database, using the Oracle dbms_job procedure to
schedule the snapshots
- using Operating System utilities (such as 'cron' on Unix or 'at' on
NT) to schedule the snapshot
3.2. Using dbms_job
To use an Oracle-automated method for collecting statistics, you can use
dbms_job. A sample script on how to do this is supplied in spauto.sql,
which schedules a snapshot every hour, on the hour.
You may wish to schedule snapshots at regular times each day to reflect your
system's OLTP and/or batch peak loads. For example take snapshots at 9am,
10am, 11am, 12 midday and 6pm for the OLTP load, then a snapshot at
12 midnight and another at 6am for the batch window.
In order to use dbms_job to schedule snapshots, the job_queue_processes
initialization parameter must be set to greater than 0 for the job to
run automatically.
Example of setting the job_queue_processes parameter in an init.ora file:
# Set to enable the job queue process to start. This allows dbms_job
# to schedule automatic statistics collection using STATSPACK
job_queue_processes=1
If using spauto.sql in a Clustered database environment, the spauto.sql
script must be run once on each instance in the cluster. Similarly, the
job_queue_processes parameter must also be set for each instance.
Changing the interval of statistics collection
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To change the interval of statistics collection use the dbms_job.interval
procedure
e.g.
execute dbms_job.interval(1,'SYSDATE+(1/48)');
Where 'SYSDATE+(1/48)' will result in the statistics being gathered each
1/48 hours (i.e. every half hour).
To force the job to run immediately,
execute dbms_job.run();
To remove the autocollect job,
execute dbms_job.remove();
For more information on dbms_job, see the Supplied Packages Reference
Manual.
4. Running the Performance reports
-----------------------------------
Once snapshots are taken, it is possible to generate a performance report.
There are two reports:
- spreport.sql (and sprepins.sql)
is a general instance health report, covering all aspects of instance
performance. The instance report calculate and print ratios,
increases etc. for all statistics between the two snapshot periods,
in a similar way to the BSTAT/ESTAT report.
Note: spreport.sql calls sprepins.sql, first defaulting the dbid and
instance number of the instance you are connected to. For more
information on the difference between sprepins and spreport,
see section 4.2 Running the instance report when there are
multiple instances.
- sprepsql.sql
is a report for a specific SQL statement. The SQL report is usually
run after examining the high-load SQL sections of the instance health
report. The SQL report provides detailed statistics and data for a
single SQL statement (as identified by the Hash Value).
Both reports prompt for the beginning snapshot id, the ending snapshot id,
and the report name. The SQL report additionally requests the Hash Value
for the SQL statement to be reported on.
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.
The reason for this requirement is the database's dynamic
performance tables which Statspack queries to gather the data
are memory resident, hence shutting down the database will
reset the values in the performance tables to 0. As Statspack
subtracts the begin-snapshot statistics from the end-snapshot
statistics, the resulting output will be invalid.
If begin and end snapshots which were taken between shutdowns
are specified in the report, the report shows an appropriate error
to indicate this.
Separating the phase of data gathering from producing a report, allows the
flexibility of basing a report on any data points selected. For example
it may be reasonable for the DBA to use the supplied automation script to
automate data collection every hour on the hour; If at some later point
a performance issue arose which may be better investigated by looking
at a three hour data window rather than an hour's worth of data, the
only thing the DBA need do, is specify the required start point and end
point when running the report.
4.1. Running the instance report
To examine the change in instance-wide statistics between two time periods,
the spreport.sql file is executed while being connected to the PERFSTAT
user. The spreport.sql command file is located in the rdbms/admin
directory of the Oracle Home.
This report assumes you are connected to the database you wish to report
on. In a clustered database environment, you must connect to the
instance you wish to report on when running spreport.sql. To avoid
this, see section 4.2. Running the instance report when there are
multiple instances.
When running spreport, you will be prompted for:
1. The beginning snapshot Id
2. The ending snapshot Id
3. The name of the report text file to be created
Note: Blank lines between lines of snapshot Id's means the instance
has been restarted (shutdown/startup) between those times -
this helps identify which begin and end snapshots can be used
together when running a statspack report (ones separated by
a blank line can not).
e.g. on Unix
SQL> connect perfstat/perfstat_password
SQL> @?/rdbms/admin/spreport
e.g. on NT
SQL> connect perfstat/perfstat_password
SQL> @%ORACLE_HOME%\rdbms\admin\spreport
Example output:
SQL> connect perfstat/perfstat_password
Connected.
SQL> @spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2618106428 PRD1 1 prd1
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
2618106428 1 PRD10 prd1 dlsun525
Using 261810642 for database Id
Using 1 for instance number
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
3 12 May 2000 07:07 5
4 12 May 2000 08: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:
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2 To use this name,
press
to continue, otherwise enter an alternative.
Enter value for report_name: Using the report name sp_1_2
The report will now scroll past, and also be written to the file
specified (e.g. sp_1_2.lis).
Batch mode report generation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To run a report without being prompted, assign values to the
SQL*Plus variables which 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
e.g.
on Unix:
SQL> connect perfstat/perfstat_password
SQL> define begin_snap=1
SQL> define end_snap=2
SQL> define report_name=batch_run
SQL> @?/rdbms/admin/spreport
spreport will no longer prompt for the above information.
