Performance - Statspack

Take a snapshot

Delete one or more snapshots

Generate a report

List snapshots

Install statspack

Uninstall statspack

Schedule and hourly snapshot

Take a snapshot

exec statspack.snap;

Or to specify a level...

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

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 one or more snapshots

@?/rdbms/admin/sppurge;

Generate a report

@?/rdbms/admin/spreport.sql

List snapshots

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
/

Install statspack

1. Create a tablespace (minimum size 100MB)
2. Run...

@?/rdbms/admin/spcreate

Uninstall statspack

@?/rdbms/admin/spdrop

Schedule and hourly snapshot

@?/rdbms/admin/spauto.sql

Note. This uses dbms_job, so job_queue_processes needs to be set greater than 0.

To see the job:

select job
, what
from dba_jobs
/

To delete the job:

exec dbms_job.remove(<job number>);