Statspack install and overview


PERFSTAT offers a good possibility of a performance evaluation and, more importantly, also on a Oracle Standard Edtion.


The following parameters must be set or checked on the database:

alter system set statistics_level=typical;
alter system set timed_statistics=TRUE;


SQL> @?/rdbms/admin/spcreate.sql

For the installation of perfstat we need the following user input

Choose the PERFSTAT user's password
Enter value for perfstat_password: oracle

Choose the Default tablespace for the PERFSTAT user
Enter value for default_tablespace: SYSAUX

Choose the Temporary tablespace for the PERFSTAT user
Enter value for temporary_tablespace: TEMP

Automate Statistics Collection

The SQL script spauto.sql creates a job which makes every hour a Statspack snapshot. The use of DBMS_SCHEDULER is also possible.

SQL> conn perfstat/oracle
SQL> @?/rdbms/admin/spauto.sql

Statistics Manual creation and parameter changes

Create a manual snapshot which is performed with statspack.snap command. The default SNAP level is 5.


SQL> execute statspack.snap;
SQL> execute statspack.snap(i_snap_level=>7);
Changing the SNAP level for future executions
SQL> execute statspack.snap(i_snap_level=>7,I_MODIFY_PARAMETER=>'TRUE');

Overview of the SNAP level

4 General Performance Statistics
5 Same as Level 4 but including performance-intensive SQL statements
6 Same as Level 5 but with SQL plans
7 Same as Level 6 but with Segment Statistik
10 Same as Level 7 but with Parent and Child Latches

Statistik Baseline

It is also possible to create a baseline, which can used to compare them with later snapshots that have been created at high load and poor performance simplified. Baselines can be created with a time frame.


Create a baseline using a SNAP ID
SQL> execute statspack.make_baseline(i_begin_snap=>2,i_end_snap=>3);
Deleting a Baseline
SQL> execute statspack.make_baseline(2,3,FALSE);

Delete snapshots


Deleting certain SNAP IDs
SQL> @?/rdbms/admin/sppurge.sql
Delete all snapshots
SQL> @?/rdbms/admin/sptrunc.sql
Delete the perfstat schema
SQL> @?/rdbms/admin/spdrop.sql
Delete a timerange
SQL> exec statspack.purge (i_begin_date=>to_date('01-JAN-2012', 'DD-MON-YYYY'),i_end_date =>to_date('02-JAN-2012', 'DD-MON-YYYY'),i_extended_purge=>TRUE);

Export snapshots

exp system@dbm parfile=$ORACLE_HOME/spuexp.par

Statspack Report

SQL> @?/rdbms/admin/spreport.sql

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s