||Oracle Tips by Burleson
If STATSPACK is used to tune a performance
problem, the first step is the installation of the product.
STATSPACK is shipped with most versions of Oracle. If a user’s
version of Oracle is one of the few that does not have STATSPACK,
then a copy can be obtained from Oracle.
There is no point in duplicating
documentation that Oracle already provides, so this section will not
attempt to repeat the information available on Metalink in documents
like Note 149113.1. This Note provides instructions for installing
and configuring STATSPACK. This chapter will only highlight the
basic process and mention a few important things to keep in mind
when installing STATSPACK.
The install process is initiated by running
the spcreate.sql script while connected to SQL*Plus as sysdba.
Spcreate.sql is short for STATSPACK create. The STATSPACK scripts
are prefixed with “sp” which makes them easier to remember. The
default location for the creation script, as well as the additional
STATSPACK scripts, is the rdbms/admin subdirectory of the Oracle
Before running this script, it is helpful to
make a list of the tablespaces that are available for storing the
STATSPACK repository data. For many reasons, the STATSPACK
repository should not be installed into the system tablespace. The
installation script is designed to abort if this is attempted. When
prompted for a tablespace name, choose a tablespace designated for
tools. The installation script will list the available tablespaces
before prompting for a choice. If there are only a few tablespaces,
using the prompt will work; however, if there are many tablespaces,
it may be easier to query for a list of tablespaces with the name
tool as part of the name if that is the naming standard.
Also, if an older version of Oracle that
includes the svrmgrl program is being used, DO NOT use this program
to install STATSPACK. svrmgrl does not support some of the commands
that are part of the install script. In this instance, SQL*Plus
should be used instead of svrmgrl.
The install should be fairly simple and
painless. It will create the tables and packages that will be used
for monitoring database statistics. It will also create a user
called PERFSTAT with a default password of PERFSTAT. Oracle Note
160861.1 contains a recommendation for changing the default password
for security reasons.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis