Create StatsPack and How does one use it

SQL> connect sys as sysdba

CREATE TABLESPACE


CREATE TABLESPACE perfstat
DATAFILE ‘/oracle/app/oracle/visdata/perfstat01.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k;


Drop Existing statspack

sqlplus "/ as sysdba" @spdrop.sql

Create New Statspack

SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin

SQL> @spcreate.sql



TABLESPACE_NAME CONTENTS
------------------------------ ---------
ODM PERMANENT
OLAP PERMANENT
OWAPUB PERMANENT
PERFSTAT PERMANENT
PORTAL PERMANENT
SYNCSERVER PERMANENT
TEMP TEMPORARY
TEST PERMANENT
XYZ PERMANENT

20 rows selected.


Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT
Using PERFSTAT for the default tablespace

Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: TEMP


Use Statspack:

[oracle@sys4 admin]$ sqlplus perfstat/perfstat

Take a performance snapshots

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

SNAP_ID SNAP_TIME
---------- ---------
1 04-JAN-08
2 04-JAN-08


Enter two snapshot id's for difference report

SQL> @spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
190608494 VIS 1 VIS



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
190608494 1 VIS VIS sys4.doyen.i
n

Using 190608494 for database Id
Using 1 for instance number

Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
VIS VIS 1 04 Jan 2008 12:31 5
2 04 Jan 2008 12:35 5

Enter Snapshot ID

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: report_0408



Staspack output


-rw-r--r-- 1 oracle dba 77294 Jan 4 12:37 report_0408.lst

SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin