Tuesday, October 23, 2012

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

No comments:

Post a Comment