Oracle Performance – Statspack

I mention before in my blog how to Generate AWR Reports But now How to Generate Statpack and how to use it !!!!


enable Statpack :

By default Statpack is disable to enable it do the following

1- Create tablespace with size at least 200MB
2-@?/rdbms/admin/spcreate

Disable it 

@?/rdbms/admin/spdrop
 
Statpack works on snapshot you can do the following with oracle snap shot :
 
Generate Snapshot : 

exec statspack.snap;
 
Or
 
exec statspack.snap(i_snap_level => 10, i_modify_parameter => 'true'); 

Levels Description :


Level 0 - This level captures general statistics, 
including rollback segment, row cache, SGA, system events, background
events, session events, system statistics, wait statistics, lock
statistics, and Latch information.

Level 5 - This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels.

Level 6 - This level includes capturing SQL plan and SQL plan usage
information for high resource usage SQL Statements, along with all data
captured by lower levels.

Level 7 - This level captures segment level statistics, including
logical and physical reads, row lock, itl and buffer busy waits, along
with all data captured by lower levels.
 
Level 10 - This level includes capturing Child Latch statistics, along with all data captured by lower levels. 
 
Delete SnapShot 


@?/rdbms/admin/sppurge;
 
Check SnapShot on Database Level : 

col "Date/Time" format a30
select snap_id
, snap_level
, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
from stats$snapshot
, v$database
order by snap_id
/

 To Scheducle One Hour Generate Snapshot :

@?/rdbms/admin/spauto.sql
 
This script use DBMS_JOB to create new job . you can check them from view dba_jobs
 and to delete job "exec dbms_job.remove();"
 
 

Finally Generate statpack Reports :

 
 @?/rdbms/admin/spreport.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.