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.in

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_1109

Staspack output

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