STATS PACK

•Statspack collects more data, including high-resource SQL.
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
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 as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6

How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)

runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )

Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)

sql >oracle_home/ora90/rdbms/admin/spreport.sql;

this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)