04 | 03 | 2021
Latest Articles
Popular Articles

Performance Tuning

Statspack at instance level

User Rating:  / 0

Statspack at the instance level


I still use statspack since it is free.
Whereas interpretation of wait events, hit ratio' s, troubleshooting expensive sql, sql tuning can become complicated and require experience, taking some statspack snapshots and some statspack reports is as easy as the installation.
I' ll show it here.



SQL > exec statspack.snap( i_snap_level => 7 );

after some time - the dba is sometimes allowed to drink a coffee - we take another snapshot of the performance statistics

SQL > exec statspack.snap( i_snap_level => 7 );

With the $ORACLE_HOME/rdbms/admin/spreport.sql we make a statspack report

SQL> start spreport.sql

Current Instance

   DB Id    DB Name      Inst Num Instance

 4255633833 MY_DB            1 MYDB1

Instances in this Statspack schema

   DB Id    Inst Num DB Name      Instance     Host

 4255633833        1 MY_DB        MYDB1        myhost1

Using 4255633833 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
Instance     DB Name        Snap Id   Snap Started    Level Comment

MYDB1        MY_DB             1 03 Mar 2008 15:54     5
                                         2 03 Mar 2008 15:55     7
                                         3 03 Mar 2008 15:58     7
                                         4 04 Mar 2008 10:26     7
                                         5 04 Mar 2008 10:38     7

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 4
Begin Snapshot Id specified: 4

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

Specify the Report Name

The default report file name is sp_4_5.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/sp_04032008_1026.txt

That' s it. One can start with the analysis of the statspack report. A general rule is you start with the first page(s) which will list you at which level the performance bottelenecks are - IO, CPU, sorting, parsing, wait events, load profile, ... - the next pages list more detailled info per section.
Here is an example of a statspack report.