BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Overview of the STATSPACK Scripts

Oracle Tips by Burleson Consulting

The STATSPACK scripts have completely changed.  All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory.

Oracle 8.1.7 and Oracle9i Script Name

Pre Oracle 8.1.7 Script Name

Script Function

     spdoc.txt

statspack.doc

Installation documentation

     spcreate.sql

statscre.sql

Create user, tables & install packages

     spreport.sql

statsrep.sql

Standard STATSPACK report

     spauto.sql

statsauto.sql

Schedule automatic data collection

     spuexp.par

statsuexp.par

Parameter file for full STATSPACK export

     sppurge.sql

- new file -

Purge SQL for removing old snapshots

     sptrunc.sql

- new file -

Script to truncate all STATSPACK tables

     spup816.sql

- new file -

Upgrade script to moving to 8.1.6

     spup817.sql

- new file -

Upgrade script to moving to 8.1.7

     spdrop.sql

statsdrp.sql

Script to drop all STATSPACK tables

     spcpkg.sql

statspack.sql

Script to create statspack package

     spctab.sql

statsctab.sql

Creates STATSPACK tables

     spcusr.sql

statscusr.sql

Creates STATSPACK user & assigns grants

     spdtab.sql

statsdtab.sql

Drops all STATSPACK tables

     spdusr.sql

statsdusr.sql

Drops the statspack user

Next, let’s take a closer look at these scripts and see details on how to install STATSPACK. Because of the differences between versions, we will have two sections: one for pre-8.1.7 and another for Oracle 8.1.7 and Oracle9i STATSPACK.

STATSPACK scripts for Oracle8 and Oracle8i

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “stat”:

L 3-1

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin
>ls -al stat*
-rw-r--r--   1 oracle   oinstall    1739 Dec  6  1999 statsauto.sql
-rw-r--r--   1 oracle   oinstall     843 Dec  6  1999 statscre.sql
-rw-r--r--   1 oracle   oinstall   27183 Nov 10  1999 statsctab.sql
-rw-r--r--   1 oracle   oinstall    4686 Nov 10  1999 statscusr.sql
-rw-r--r--   1 oracle   oinstall     792 Aug 27  1999 statsdrp.sql
-rw-r--r--   1 oracle   oinstall    3236 Nov 10  1999 statsdtab.sql
-rw-r--r--   1 oracle   oinstall    1081 Nov 10  1999 statsdusr.sql
-rw-r--r--   1 oracle   oinstall   26667 Dec  6  1999 statspack.doc
-rw-r--r--   1 oracle   oinstall   49821 Nov 10  1999 statspack.sql
-rw-r--r--   1 oracle   oinstall   46873 Nov 10  1999 statsrep.sql
-rw-r--r--   1 oracle   oinstall     559 Aug 27  1999 statsuexp.par

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files in a hierarchy:

  • statscre.sql This is the first install script run after you create the tablespace. It calls several subscripts:

  • statscusr.sql This script creates a user called PERFSTAT with the required permissions.

  • statsctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

  • statspack.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

  • statsauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

  • statsdrp.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:

  • statsdtab.sql This drops all STATSPACK tables and indexes.

  • statsdusr.sql This script drops the PERFSTAT user.

  • statsuexp.par This is an export parameter file for exporting the STATSPACK objects. This can be useful if you want to consolidate STATSPACK reports for several databases into a single STATSPACK structure.

  • statspack.doc This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

  • statsrep.sql This is the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

  • statsrep80.sql This is a version of the STATSPACK report for Oracle 8.0.

Now that we understand the functions of each of the files, we are ready to install STATSPACK. Our first step is to review the installation files for the STATSPACK install.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter