PERF--STATSPACK Scripts小記

By Alexander Geldutes

STATSPACK is a set of SQL scripts, PL/SQL stored procedures and packages for collecting performance statistics. It's available starting from Oracle 8.1.6. It provides more information than UTLBSTAT/UTLESTAT utilities, plus it automates some operations.

Installation

1. Check if you have TOOLS tablespace on your database, otherwise create it (minimum size is 35M).
   
   
   
2. Run SQL*Plus and connect as SYSDBA:
   
   connect / as sysdba
   
   
   
3. To install STATSPACK run the following script:
   
   On 8.1.6
   on Unix:
   
   @?/rdbms/admin/statscre
   
   On 8.1.6
   on NT:
   
   @%ORACLE_HOME%rdbmsadminstatscre
   
   On 8.1.7 and 9i
   on Unix:
   
   @?/rdbms/admin/spcreate
   
   On 8.1.7 and 9i
   on NT:
   
   @%ORACLE_HOME%rdbmsadminspcreate

Collect statistics

1. Run SQL*Plus and connect as perfstat (default password is perfstat):
   
   connect perfstat/perfstat
   
   
   
2. To collect statistics run the following command:
   
   execute statspack.snap;
   
   
   

Generate Report

1. Run SQL*Plus and connect as perfstat (default password is perfstat):
   
   connect / as perfstat
   
   
   
2. To generate a report, run the following script:
   
   On 8.1.6
   on Unix:
   
   @?/rdbms/admin/statsrep
   
   On 8.1.6
   on NT:
   
   @%ORACLE_HOME%rdbmsadminstatsrep
   
   On 8.1.7 and 9i
   on Unix:
   
   @?/rdbms/admin/spreport
   
   On 8.1.7 and 9i
   on NT:
   
   @%ORACLE_HOME%rdbmsadminspreport
   
   
   

Uninstall

1. Run SQL*Plus and connect as SYSDBA:
   
   connect / as sysdba
   
   
   
2. To uninstall STATSPACK run the following script:
   
   On 8.1.6
   on Unix:
   
   @?/rdbms/admin/statsdrp
   
   On 8.1.6
   on NT:
   
   @%ORACLE_HOME%rdbmsadminstatsdrp
   
   On 8.1.7 and 9i
   on Unix:
   
   @?/rdbms/admin/spdrop
   
   On 8.1.7 and 9i
   on NT:
   
   @%ORACLE_HOME%rdbmsadminspdrop

Clean old statistics

1. Run SQL*Plus and connect as perfstat (default password is perfstat):
   
   connect perfstat/perfstat
   
   
   
2. It will work only on 8.1.7 and 9i. To clean old statistics, run the following command:
   
   On 8.1.7 and 9i
   on Unix:
   
   @?/rdbms/admin/sppurge
   
   On 8.1.7 and 9i
   on NT:
   
   @%ORACLE_HOME%rdbmsadminsppurge
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51822/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987/viewspace-51822/