STATSPACK CREATION IN WINDOWS :

STEP 1 :

RUN THE SCRIPT spcreate.sql at the sql prompt.

SQL>@D:\oracle\rdbms\admin\spcreate.sql

->Once u press enter the oracle server will automatically start installing packages.

->Then create a default user perfstat.In windows we need not select password for

this user ,it will implicitly take password as perfstat only.But in linux u have to

specify password when it is prompted.

->It will automatically execute the following command:

Conn perfstat/perfstat.Hence u need not enter specifically.

STEP 2 :

IT WILL ASK TO SPECIFY DEFAULT TABLESPACE & TEMPORARY TABLESPACE.

-> If u didn’t create separate default and temporary tablespace then u can use

default tablespace as users & temporary tablespace as temp.

Example :

Choose the PERFSTAT user’s temporary tablespace:user

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Specify PERFSTAT user’s temporary tablespace.

Enter value for temporary_tablespace: temp

-> After specifying tablespaces it’ll create corresponding rows,tables,synonyms

and also grant priviledges.

-> Automatically connect as perfstat/perfstat and install the statspack package.

STEP 3 :

EXECUTE THE COMMAND TO CREATE SNAP SHOTS

SQL> execute statspack.snap

Execute the above command some 4 times so that u can select the begin snap id as 1

& the end snap id as 4.Its not compulsory to execute 4 times u can execute it 2 times.

STEP 4 :

NOW EXECUTE SCRIPT (spreport.sql) TO GENERATE THE REPORT

SQL>@D:\oracle\rdbms\admin\spreport.sql

->After creating report , it ‘ll ask to specify the report name.(sp_1.lst or sp_1.txt)

-> U can also specify the location where u want to store the report.That means u have

to specify the path along with the report name as follows :

D:\oracle\PT_REPORT\sp_1.lst

Where PT_REPORT = folder in which all the reports can be stored.

Sp_1.lst = report name

-> If u did not specify the location then the report will be stored in the default location i.e..c:\Documents\settings\anupama\sp_1.lst

STEP 5 : FINALLY VIEW THE REPORT

STATSPACK CREATION IN LINUX

STEP 1 :

RUN THE SCRIPT spcreate.sql at the sql prompt.

SQL>@/oracle/ora92/rdbms/admin/spcreate.sql

STEP 2 :

It will ask password for perfstat user

Specify as perstat (u can specify diff p/w also)

STEP 3 :

Enter the default tablespace

Users(u can specify user create default tablespace also)

STEP 4 :

Enter the temporary tablespace

Temp(u can specify user create temporary tablespace also)

STEP 5 :

Connect as perfstat user

SQL>conn perfstat/perfstat

Note :without connecting to perfstat user also ,u can execute the below step but it is adviced by oracle to connect and then execute the script.

STEP 6 :

Execute the command to create snap shots

SQL>execute statspack.snap

Execute it some 2 or more times

Note:Instead of manually entering the above command u can run the script spauto.sql , so that it will automatically execute that command based on the time interval u mentioned in the script & then u can take the report.(i.e, directly u can proceed with the below step)

STEP 7 :

Execute the script to generate report.

SQL>@/oracle/ora92/ rdbms/admin/spreport.sql

STEP 8 :

Enter the begin & end snap shot id.If u executed the above script 2 times then the

Begin snap shot id will 1 and the end snap shot id will be 2.

STEP 9 :

It’ll ask for report name.Here u can directly specify the report name but it ll get stored in a default location(i.e from the place where u executed the script) ,where in if u specify the location along with report name then it will store the report in a location u want.

/oracle/pt_reports/sp_report_1.lst (sp_report_1 is the report name)

STEP 10 :

Then exit and see the report in a location where u stored ,if location is not specified check in the default location i.e.from where u executed the script.

Vi sp_report_1.lst

SUMMARY OF STATSPACK OUTPUT :

In output you will get details about the following things

Database and instance name

Time at which the snapshots were taken

Current sizes of the caches

Load profile

Efficiency percentages of the instance

Top five wait events & Complete list of wait events

Information on sql statements currently in the pool

Instance activity statistics & Latch activity

Tablespace & file i/o

Buffer pool statistics & SGA statistics

Rollback or undo segment statistics

Dictionary&library cach statistics

Advertisement