Kenneth at 16:52, 15 April 2015

2015-04-15T16:52:07Z

New page

== Oracle Statspack ==

[http://www.orafaq.com/wiki/Statspack Statspack - Oracle FAQ]

'''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.

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

== Install statspack ==
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Drop and install statspack
sqlplus "/ as sysdba" @spcreate.sql -- Enter [[tablespace]] names when prompted

References:
* [http://www.dba-oracle.com/t_statspack_installation_steps.htm Oracle STATSPACK installation steps]

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

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

== Statspack reporting ==
-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

@spreport.sql -- Enter two snapshot id's for difference report

== Other statspack scripts ==
Some of the other statspack scripts are:
* sppurge.sql - Purge (delete) a range of Snapshot Id's between the specified begin and end Snap Id's
* spauto.sql - Schedule a [[DBMS JOB|dbms_job]] to automate the collection of STATPACK statistics
* spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
* spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
* spreport.sql - Report on differences between values recorded in two snapshots
* sptrunc.sql - Truncates all data in Statspack tables

==Potential problems==
Statpack reporting suffers from the following problems:

* 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.

== References ==

Source: [http://www.orafaq.com/wiki/Statspack Statspack - Oracle FAQ]

== Resources ==

* [http://www.oraperf.com/ OraPerf.com], upload your STATSPACK file to get performance recommendations.
* [http://www.spviewer.com www.spviewer.com], website with software tools for Oracle tuning based on statspack and AWR.
* [http://www.statspackanalyzer.com statspackanalyzer.com] - website that can analyze statspack reports.

== keywords ==

Oracle Statspack

[[Category:Oracle]]

Oracle/Database/Oracle Statspack - Revision history

Kenneth at 16:52, 15 April 2015