Install Statspack

use spcreate.sql at /rdbms/admin PATH

SQL> define default_tablespace='SYSAUX'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='password'
SQL> @?/rdbms/admin/spcreate

The spcreate.sql script creates the perfstat user to own ths statpack package.

- Capture a snapshot

SQL> connect perfstat/password
SQL> execute statspack.snap;

- Automate snapshot

SQL> connect perfstat/password
SQL> @spauto

Statspack Snapshot Level:
0 = > General Performance
5 = > SQL Statement
6 = > SQL plans and SQL plan usage
7 = > Segment-level statistics
10 = > Parent an child latches

Temporary change:
SQL> execute statspack.snap(i_snap_level =>6 );

Change defaults and take a snapshot:
SQL> execute statspack.snap(i_snap_level => 10, i_modify_parameter=>'true');

Modify defaults without taking a snapshot:
SQL> execute statspack.modify_statspack_parameter(i_snap_level => 10);

Reporting with Statspack
SQL> @?/rdbms/admin/spreport


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

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2448266380 DB 1 db



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

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
2448266380 1 DB db hosttest

Using 2448266380 for database Id
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Listing all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
db DB 1 13 Oct 2008 00:42 5
3 13 Oct 2008 00:59 6
4 13 Oct 2008 01:00 5
5 13 Oct 2008 01:07 6
6 13 Oct 2008 01:07 10

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

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



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_5_6. To use this name,
press to continue, otherwise enter an alternative.
.
.
.


Example statspack file:

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2448266380 db 1 12-Sep-08 14:53 11.1.0.6.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
hosttest Linux 64-bit for AMD 4 2 2 7.8

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 5 13-Oct-08 01:07:27 30 1.4
End Snap: 6 13-Oct-08 01:07:45 30 1.4
Elapsed: 0.30 (mins)
DB time: 0.01 (mins) DB CPU: 0.01 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 800M Std Block Size: 8K
Shared Pool: 448M Log Buffer: 6,089K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 0.7 0.01 0.35
DB CPU(s): 0.0 0.5 0.01 0.25
Redo size: 90,167.1 1,623,008.0
Logical reads: 1,513.2 27,237.0
Block changes: 196.2 3,532.0
Physical reads: 0.0 0.0
Physical writes: 0.0 0.0
User calls: 0.1 2.0
Parses: 3.7 66.0
Hard parses: 0.1 1.0
W/A MB processed: 0.9 16.2
Logons: 0.0 0.0
Executes: 4.0 72.0
Rollbacks: 0.0 0.0
Transactions: 0.1

Instance Efficiency Indicators
.
.
.