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
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.
1. Configuration
The default initial and next extent size is 1MB or 5MB for all tables
and indexes which contain changeable data. The minimum default tablespace
requirement is approximately 45MB.
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.
Locally Managed Tablespaces
If you install the package in a locally-managed tablespace,
storage clauses are not required, as the storage characteristics are automatically
managed.
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. Do not use Server Manager (svrmgrl)
to install Statspack, as the installation will fail.
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
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.
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. 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
2. 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
SQL> execute statspack.snap;
Note: In an OPS 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 init.ora parameter timed_statistics to true; this way, Statspack data collected will include important timing information. The timed_statistics parameter is also dynamically changable using the 'alter system' command. Timing data is important and is usually required by Oracle support to diagnose performance problems.
2.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 utlities (such as 'cron'
on Unix or 'at' on NT) to schedule the snapshot
2.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 in the init.ora
file for the job to be run automatically.
Example of an init.ora entry:
# 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 OPS enviroment, 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 each1/48 hours (i.e. every half hour).
To force the job to run immediately,
execute dbms_job.run(<job number>);
To remove the autocollect job,
execute dbms_job.remove(<job number>);
3. Running a Performance report
Once snapshots are taken, it is possible to generate a performance
report. The SQL script which generates the report prompts for the two snapshot
id's to be processed.
The first will be the beginning snapshot id, the second will be the
ending snapshot id. The report will then calculate and print ratios,
increases etc. for all statistics between the two snapshot periods, in
a similar way to the BSTAT/ESTAT report.
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.
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
e.g. on Unix
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport
e.g. on NT
SQL> connect perfstat/perfstat
SQL> @%ORACLE_HOME%\rdbms\admin\spreport
Gathering Optimizer statistics on the PERFSTAT schema
For best performance when running spreport, collect optimizer statistics
for tables and indexes owned by the PERFSTAT. This should be performed
whenever significant change in data volumes in PERFSTAT's tables. The easiest
way to do this, is either to use dbms_utility, or dbms_stats, and specify
the PERFSTAT user:
execute dbms_utility.analyze_schema('PERFSTAT','COMPUTE');
or
execute dbms_stats.gather_schema_stats('PERFSTAT');
4. Configuring the amount of data captured
Snapshot Level
It is possible to change the amount of information gathered by the
package, by specifying a different snapshot 'level'. In other words,
the level chosen (or defaulted) will decide the amount of data collected.
The higher the snapshot level, the more data is gathered. The default
level set by the installation is level 5.
Snapshot Levels - details
Levels >= 0 General performance statistics
Statistics gathered:
This level and 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, parent latch statistics.
Levels >= 5 Additional data: SQL Statements
This level includes all statistics gathered in the
lower level(s),
and additionally gathers the performance data on
high resource
usage SQL statements.
In a level 5 snapshot, note that the time required
for the snapshot
to complete is dependant on the shared_pool_size
and on the number of
SQL statements in the shared pool at the time the
snapshot is taken:
the larger the shared pool, the longer the time
taken to complete
the snapshot.
SQL 'Thresholds'
The SQL statements gathered by
Statspack are those which exceed one of
four predefined threshold parameters:
- number of executions of
the SQL statement
(default 100)
- number of disk reads performed
by the SQL statement (default 1,000)
- number of parse calls
performed by the SQL statement (default 1,000)
- number of buffer gets
performed by the SQL statement (default 10,000)
- size of sharable memory
used by the SQL statement (default 1m)
- version count for the
SQL statement
(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 the above threshold values, 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 >= 10 Additional statistics: Parent and Child
latches
This level includes all statistics gathered in the
lower levels, and
additionally gathers Parent and Child Latch information.
Data gathered
at this level can sometimes cause the snapshot to
take longer to complete
i.e. this level can be resource intensive, and should
only be used
when advised by Oracle personnel.
Purging/removing unnecessary data
It is possible to purge unnecessary data from the PERFSTAT schema using
sppurge.sql. This script deletes snapshots which fall between
the begin and end range of Snapshot Id's specified.
Purging may require the use of a large rollback segment, as all data
relating each Snapshot Id to be purged will be deleted. To avoid rollback
segment extension errors, explicitly use a large rollback segment.
This can be done by executing the 'set transaction use rollback segment..'
command before running the sppurge.sql script
Truncating all data
If you wish to truncate all performance data indiscriminantly, it is
possible to do this using sptrunc.sql This script truncates all statistics
data gathered.
Removing the package
To deinstall the package, connect as a user with SYSDBA privilege and
run the following script from SQL*Plus: spdrop
e.g.
SQL> connect / as sysdba
SQL> @spdrop
Check each of two output files produced (spdtab.lis, spdusr.lis) to ensure the package was completely deinstalled.