- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
Performance - 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(<job number>);
|
|
