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
Technology Geek 