Oracle 9i statspack

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();