Installation of STATSPACK and Using (Oracle10g)

What is STATSPACK?

STATSPACK is a performance diagnosis tool available since Oracle8i version. The Oracle STATSPACK utility is one of the most exciting Oracle enhancements. The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. 

By interrogating these tables, we can gain tremendous insight into the relative performance of their databases.

Steps to install Statspack (ORACLE 10g):

1. Connect to database as SYS user and check is there any user named PERFSTAT.

It displays no rows selected. What does it means, Statspak not installed on your database. 

2. Now we have to execute a script called spcreate.sql. This script is under oracle home/rdbms/admin directory 

Run this script as SYSDBA

3. Now it will ask for password, give password of your own. I am giving password as oracle.

Next it will ask for default table space, Give SYSAUX  (Note: Don't Give system tablespace as default tablespace for statspack,  Actuvally it won't take even if we gave system.)

Next it will ask for Temporary tablespace, give TEMP.

After giveing above steps you are about completed installation STATSPACK on your database.

4. Now what you need to do is. Connect to database as PERFSTAT user and issue the command for checking table in PERFSTAT schema.

SQL  > select * from tab;

5.  Now select any table in that 68 tables and issue select * from table_name to see the content.

You can see above picture. There is no data in 68 tables in PERFSTAT schema. why because, We just completed installation of STATSPACK. We haven't start using it yet.

6.  Now two snap shots by using following command as shown in figure. Why two? We need atleast two snapshot to compare performance of our database on different time intervals right? That's why we need two.

SQL > exec statspack.snap;

 7. We have two snapshots available. Now how to compare these two. For this we need to execute another script called spreport.sql.

When you execute this scrip. You can see DBID, name of the Database, host name and  the total  (executed) snapshots.

You all know that I installed Statspack and created two snapshots. If you see below picture. You may find out no of snapshots and unique number of snapshot as well time.

8) Now we need two compare two snapshots so enter the snapshot begin snapshot unique numbers and end snapshot unique number as shown in figure.

After that Statspack will generate report. In order to view that we need to give a name.

9. Now statspack report is generated. Let's take a look at that report.

By Using above report we  can check the performance of the database. Where the system performance is effecting. you will get a clear idea, what cause effecting performance of database.

10. Now check the content on tables on perfstat schema. 

By using these table we can improve performance of the database

Thanks

Sagar