Statspack

Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK, users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.

sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
-- or instruct statspack to do gather more details in the snapshot
-- (look up which oracle version supports which level).
exec perfstat.statspack.snap(i_snap_level=>10);

The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.

Some statistics may only be reported on COMPLETION of a query. For example, if a query runs for 12 hours, its processing won't be reported during any of the snapshots taken while the query was busy executing.

If queries are aged out of the shared pool, the stats from V$SQL are reset. This can throw off the delta calculations and even make it negative. For example, query A has 10,000 buffer_gets at snapshot 1, but at snapshot #2, it has been aged out of the pool and reloaded and now shows only 1,000 buffer_gets. So, when you run spreport.sql from snapshot 1 to 2, you'll get 1,000-10,000 = -9,000 for this query.