Tuesday, August 23, 2011

The Statspack Report


The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

How Statspack Works?

When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package.Statspack users become familiar with the concept of a snapshot, a single collection of performance data.Each time a new collection is taken, a new SNAP_ID is generated.

Installing Statspack..

1.) Creating tablespace s_perfstat:

SQL>create tablespace S_PERFSTAT datafile ‘D:\oracle\product\10.2.0\oradata\s_PERFSTAT.dbf’ size 200M autoextend on next 20M maxsize 1024M;

2.) Now run spcreate.sql script placed at :   D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\

3.) Take the snapshots now.

Snapshots must be collected to evaluate database performance. Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Once two snapshots are collected, they can be compared to identify the activity that occurred during the interval between the two snapshots.

Snapshots can be collected a various levels, each increasing level collecting a greater amount of information about the database. As the levels go higher, each level is inclusive of the information collected at the levels below it.











c:\> sqlplus perfstat/perfstat

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


Generating Reports:

Oracle Statspack comes with a comprehensive reporting script called spreport.sql. When this script is run, it outputs a list of available snapshots, asks the user for two snapshot IDs and a name for the report, and then outputs a text report of the results.

1.) Goto D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\ and run spreport.sql

2.) Enter a beginning snapshot ID.
3.) Enter an ending snapshot ID.
4.) Enter a name for the report or accept the default.

Enjoy:-)

2 comments:

Zheng junxai5 said...

zhengjx20160428
kd 8
jordan retro 8
kate spade outlet
coach factory outlet online
coach outlet store online clearances
toms outlet
nfl jerseys
hollister clothing store
michael kors outlet clearance
true religion outlet
ray bans
air jordan homme
nike outlet store
ray ban outlet
burberry handbags
hollister clothing store
asics running shoes
montblanc pen
oakley canada
cheap nfl jerseys
coach outlet
michael kors outlet online
nike store outlet
coach factory outlet
nike air max 90
coach factory outlet
adidas stan smith
cheap jordan shoes
abercrombie and fitch
polo ralph lauren outlet
louis vuitton outlet stores
louis vuitton bags
louis vuitton outlet
polo ralph lauren outlet
oakley vault
cheap jerseys
gucci outlet online
coach outlet
lebron james shoes 13

Zheng junxai5 said...

zhengjx20160428
kd 8
jordan retro 8
kate spade outlet
coach factory outlet online
coach outlet store online clearances
toms outlet
nfl jerseys
hollister clothing store
michael kors outlet clearance
true religion outlet
ray bans
air jordan homme
nike outlet store
ray ban outlet
burberry handbags
hollister clothing store
asics running shoes
montblanc pen
oakley canada
cheap nfl jerseys
coach outlet
michael kors outlet online
nike store outlet
coach factory outlet
nike air max 90
coach factory outlet
adidas stan smith
cheap jordan shoes
abercrombie and fitch
polo ralph lauren outlet
louis vuitton outlet stores
louis vuitton bags
louis vuitton outlet
polo ralph lauren outlet
oakley vault
cheap jerseys
gucci outlet online
coach outlet
lebron james shoes 13