Statspack Scripts

Posted By Sagar Patil

Installation
The following scripts must be run as a user with SYSDBA privilege:
* SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL) * SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)

The following scripts are run as a user with SYSDBA privilege by the preceding calling scripts:
* SPDTAB.SQL: Drops Statspack tables
* SPDUSR.SQL: Drops the Statspack user (PERFSTAT)

The following scripts are run as PERFSTAT by the preceding calling scripts:
* SPCUSR.SQL: Creates the Statspack user (PERFSTAT)
* SPCTAB.SQL: Creates Statspack tables
* SPCPKG.SQL: Creates the Statspack package

Scripts for Statspack Reporting and Automation

The following scripts must be run as PERFSTAT:
* SPREPORT.SQL: Generates a Statspack report
* SPREPSQL.SQL: Generates a Statspack SQL report for the specific SQL hash value specified
* SPREPINS.SQL: Generates a Statspack report for the database and instance specified
* SPAUTO.SQL: Automates Statspack statistics collection (using DBMS_JOB)

Scripts for Statspack Performance Data Maintenance
The following scripts must be run as PERFSTAT:
* SPPURGE.SQL: Purges a limited range of Snapshot IDs for a given database instance.
* SPTRUNC.SQL: Truncates all performance data in Statspack tables

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu