enable Statpack :
By default Statpack is disable to enable it do the following
1- Create tablespace with size at least 200MB
Statpack works on snapshot you can do the following with oracle snap shot :
Generate Snapshot :
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.
Check SnapShot on Database Level :
col "Date/Time" format a30
, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
order by snap_id
To Scheducle One Hour Generate Snapshot :
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 :