Database Reference
In-Depth Information
Performing Installation
To install Statspack, you have to connect as user
sys
and run the
spcreate.sql
script, which is available in the
$ORACLE_HOME/rdbms/admin
directory. The script creates a user named
perfstat
that contains most of the objects
required to run Statspack. In addition, it creates a number of public synonyms and some views in the
sys
schema.
During execution, the script asks for the password of the
perfstat
user, the temporary tablespace that has to be
used for it, and the tablespace that the tables and indexes have to be stored in. Note that both tablespaces have to
already exist before executing the script. If you don't want to create new tablespaces, just select the default temporary
tablespace and the
sysaux
tablespace.
Configuring the Repository
The Statspack configuration, which is stored in the
stats$statspack_parameter
table of the
perfstat
schema, is
based on three types of parameters:
•
Snapshot level
: Defines the data that is stored when a snapshot is taken. Table
5-2
briefly
describes the available snapshot levels. Also, the
stats$level_description
table contains a
short description of each level.
Table 5-2.
Statspack's Snapshot Levels
Level
Description
0
Captures general performance statistics.
5
Captures general performance statistics (as level 0), as well as statistics about SQL statements that cross a
threshold. This is the default level.
6
Captures all statistics gathered at lower levels, as well as execution plans (including usage statistics).
7
Captures all statistics gathered at lower levels, as well as segment-level statistics (for example, the number
of logical and physical reads) for the segments that cross a threshold.
10
Captures all statistics gathered at lower levels, as well as statistics about latches.
•
SQL statement thresholds
: Six thresholds (number of executions, number of parse calls,
number of physical reads, number of logical reads, amount of sharable memory, and number
of child cursors) that are used to determine whether a SQL statement is captured. The capture
of a SQL statement takes place only when at least one of them is crossed.
•
Segment statistics thresholds
: Seven thresholds (number of logical reads, number of physical
reads, number of buffer busy waits, number of row lock waits, number of ITL waits, number
of global cache-consistent read blocks, and number of global cache current blocks) that are
used to determine the segments for which statistics are captured. The capture of statistics for a
given segment takes place only when at least one of them is crossed.
