Performance Tuning
Statspack at instance level
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 21:50
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 18 March 2010 21:50
-
Written by Guy Lambregts
-
Hits: 4106
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
Snap
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.