Statspack install and overview


Generally

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

Preliminary

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

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

Installation

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

Samples:

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.

Sample:

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

Sample:

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
Advertisements

Kommentar verfassen

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

WordPress.com-Logo

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

Twitter-Bild

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

Facebook-Foto

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

Google+ Foto

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

Verbinde mit %s