Database Reference
In-Depth Information
$ ls sp*.sql
spauto.sql spcusr.sql sppurge.sql sprepsql.sql spup816.sql
spcpkg.sql spdrop.sql sprepcon.sql sprsqins.sql spup817.sql
spcreate.sql spdtab.sql sprepins.sql sptrunc.sql spup90.sql
spctab.sql spdusr.sql spreport.sql spup10.sql spup92.sql
Note that setting ORACLE_HOME as part of the SQL script directory search path with environ-
ment variable SQLPATH removes the necessity to supply the full path name of Statspack SQL
scripts and any other scripts in the directories thereby specified. On UNIX use a colon ( : ) as a
separator between multiple directories.
$ export SQLPATH=$ORACLE_HOME/rdbms/admin:$HOME/it/sql
On Windows, use a semicolon ( ; ).
C:> set SQLPATH=%ORACLE_HOME%\rdbms\admin;C:\home\ndebes\it\sql 23 3
Table 25-1. Statspack Quick Reference
Action
Command to Enter in SQL*Plus
Run as User
Installation
SQL> @spcreate
SYS
SQL> EXEC statspack.snap
(i_snap_level=> snapshot_level [,
i_session_id=> sid_from_v$session ][,
i_ucomment=>' comment ' ]) 2
DBA
Manual snapshot of performance
data with optional session-level
snapshot and comment
SQL> @spauto
DBA
Automatic snapshots every hour
on the hour taken by job queue
processes ( DBMS_JOB )
Reporting
SQL> @spreport
DBA
SQL> @sppurge
DBA
Purge obsolete Statspack data
by snapshot ID range to prevent
the default tablespace of user
PERFSTAT from overflowing 3
SQL> @sptrunc
PERFSTAT
Truncate tables containing snap-
shot data
SQL> @spdrop
DBA
Deinstallation
2. Snapshot_level is an integer in the range 1..10; sid_from_v$session is V$SESSION.SID of a session for
which CPU consumption, wait events, and session statistics are captured; comment is a comment,
which will be reproduced along with the snapshot ID, time, and level when spreport.sql is run. Statspack
releases including Oracle10g Release 2 have a software defect concerning the generation of the session-
specific report. Wait events that occurred solely in the end snapshot, but not in the begin snapshot, are
omitted from the report due to a missing outer join. I reported this issue, which is tracked by bug 5145816.
The bug is fixed in Oracle11g. There is no backport of the fix to earlier releases, but you can use my fixes
in the source code depot.
3. In Oracle10g, the purge functionality is also available as part of the Statspack package ( STATSPACK.PURGE ). The
source code depot contains a backport of the procedure to Oracle9i (see Table 25-4).
 
Search WWH ::




Custom Search