PURPOSE
-------
This note is intended to provide configuring and installing information about
the StatsPack package.
SCOPE & APPLICATION
-------------------
This note is applicable for Oracle RDBMS releases 8.1.6 and greater, and is
related to the task of gathering database and session level performance
information.
Configuration and Installation of the StatsPack Package.
--------------------------------------------------------
- StatsPack Database Space Requirements
Oracle does not recommend installing the package in the SYSTEM tablespace. A
more appropriate tablespace (if it exists) would be a "TOOLS" tablespace. If
you install the package in a locally-managed tablespace, storage clauses are
not required, as the storage characteristics are automatically managed. 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.
The default initial and next extent size is 1MB for all tables and indexes
which contain changeable data. The minimum default space requirement is
approximately 35MB.
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.
- Installing the StatsPack package
Interactive Installation:
========================
During the installation process, the user PERFSTAT will be created, default
password PERFSTAT. This user will own all PL/SQL code and database objects
created (including the STATSPACK tables, constraints and STATSPACK package).
The installation SQL script will prompt for the PERFSTAT user's default and
temporary tablespaces and also for the tablespace in which the tables and
indexes will be created
NOTE:
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 sorting.
Use a TOOLS tablespace to store the data, and your instance's
TEMPORARY tablespace for sorting.
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.
o The installation grants SELECT privilege to PUBLIC for all
of the Statspack owned tables (prefixed by STATS$).
Even though SVRMGRL is available with Oracle8i releases 8.1.6 and 8.1.7,
installation of StatsPack can only be done with SQL*Plus do to special
formatting commands that are used in the scripts. Oracle9i releases do not
include SVRMGRL.
FYI:
====
1)Note:105692.1: "Installation of statspack fails from Server Manager",
discusses this matter further.
2)BUG 2673074 Abstract: SPCREATE.SQL HANGS BUSY SYSTEM
This may cause the database to hang while running the
SPCREATE.SQL script.
Workaround:
Make sure all DBMS_* packages are compiled
or manually comment out the creation of dbmsjob.sql from spcusr.sql
To install the package, using SQL*Plus and based on the correct platform and
RDBMS version, do the following:
Oracle 8.1.6:
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/statscre
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\statscre
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:statscre
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spcreate
Oracle10g and 11g
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spcreate
Although new tools are available for 10g and 11g such as AWR and ADDM, Statspack can also be used for 10g and 11g.
Batch mode installation
=======================
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces before
running spcreate.
The variables are:
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> @?/rdbms/admin/spcreate
spcreate will no longer prompt for the above information.
- Log files created during installation
The StatsPack installation script runs 3 other scripts - you do not need
to run these - the scripts are called automatically:
Oracle8i 8.1.6
1. statscusr -> creates the user and grants privileges
2. statsctab -> creates the tables
3. statspack -> creates the package
Oracle8i 8.1.7 or Oracle9i 9.x
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package
The installation script will generate spooled output file based on the name of
the script being run and end with a 'lis' extension. Check each of the three
output files produced by the installation to ensure no errors were encountered,
before continuing on to the next step. For example, on Oracle8i 8.1.6, a
output file called statcusr.lis will be created. Under Oracle8i 8.1.7 or
Oracle9i 9.x, a output file spcusr.lis will be created.
- Errors found during installation process
If the scripts were incorrectly run while in SVRMGRL, an ORA-1012 error
indicating that the session is not logged in or a PLS-00201 error indicating
that stats$statspack_parameter must be declared may be generated. To correct
this problem, first use SQL*Plus to remove StatsPack, and attempt installation
using the above steps:
Oracle 8.1.6:
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/statsdrp
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\statsdrp
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:statsdrp
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spdrop
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spdrop
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spdrop
Oracle 10g and 11g
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spdrop
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spdrop
on VMS
SQL> connect / as sysdba
SQL> @ora_rdbms_admin:spdrop
- Batch mode installation
There are two ways to install Statspack - interactively (as shown above),
or in 'batch' mode (as shown below). Batch mode is useful when you do not
wish to be prompted for the PERFSTAT user's default and temporary tablespaces.
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces before running
the StatsPack installation script.
The variables are:
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'
Oracle8i 8.1.6
SQL> @?/rdbms/admin/statscre
Oracle8i 8.1.7 or Oracle9i 9.x
SQL> @?/rdbms/admin/spcreate
The StatsPack installation script will no longer prompt for the above
information.
Installing STATSPACK invalidates dbms_utility:
==============================================
If dbms_job is already installed, then comment out the @@dbmsjob line in spcusr.sql.
RELATED DOCUMENTS
-----------------
Note:94224.1 - FAQ - STATSPACK COMPLETE REFERENCE
Installing and Configuring StatsPack Package (文档 ID 149113.1)
最新推荐文章于 2018-01-15 11:08:22 发布
扫一扫
1654
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
