Einige Worte zu Statspack

28. Mai 2013 Aus Von Markus Flechtner

„Statspack“ ist immer mal wieder ein Thema in meinen Kursen. Daher sollen hier im Blog ein paar Worte zu diesem wichtigen Teil der „DBA-Allgemeinbildung“ sollen hier nicht fehlen. Zudem ist der Nachfolger von Statspack, das sog. „Automatic Workload Repository“, kurz AWR, lizenzpflichtig und nur in der Enterprise Edition verfügbar. Statspack ist somit die kostenlose Alternative für Nutzer der Standard Edition oder für Nutzer der Enterprise Edition, die das „Diagnostic Pack“ nicht lizensiert haben.

Funktionsweise
Bekannterweise sind die V$-Views kumulativ, d.h. die ganzen Statistik-Informationen werden vom Start der Instance hochgezählt. Wenn man nun wissen will, was in einem gewissen Zeitraum in der Datenbank passiert ist, dann muss man zwei Zustände der V$-Views vergleichen. Und genau dabei hilft Statspack. Es macht regelmäßig „Snapshots“ von den V$-Views und speichert die Daten in eigenen Tabellen. In einem zweiten Schritt können dann Auswertungen gemacht werden, in dem man mittels Skript die „Differenz“ von zwei Snapshots ermittelt und sich damit anzeigen lassen kann, was in dem Zeitraum zwischen den Snapshots passiert ist.

Installation
Die Tabellen für Statspack werden mit Hilfe des Skriptes spcreate.sql im Schema PERFSTAT angelegt (default-mäßig werden diese im Tablespace SYSAUX angelegt, aber es ist empfehlenswert, dafür einen eigenen Tablespace zu erstellen):

oracle@nf11gdba:/tmp/ [NF11GSE] sqlplus „/ as sysdba“
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 19:28:24 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> start $ORACLE_HOME/rdbms/admin/spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: PERFSTAT
PERFSTAT

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME
-----------------------------------------------------------------
CONTENTS STATSPACK DEFAULT TABLESPACE
--------------------------- ----------------------------
SYSAUX
PERMANENT *

PERFSTAT_DATA
PERMANENT

USERS
PERMANENT

Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT_DATA

Using tablespace PERFSTAT_DATA as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME
------------------------------------------------------------------
CONTENTS DB DEFAULT TEMP TABLESPACE
--------------------------- --------------------------
TEMP
TEMPORARY *

Pressing will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL> --
SQL> -- Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
SQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/1 2010/08/13 10:06:01 kchou Exp $
SQL> Rem spctab.sql
SQL> Rem Copyright (c) 1999, 2010, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem NAME
SQL> Rem spctab.sql
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create tables to hold
SQL> Rem start and end "snapshot" statistical information
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT
[..]
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using SYSAUX tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

Synonym created.

... Creating STATS$... tables

Table created.

Synonym created.

[..]

Synonym created.

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> -- Create the statistics Package
SQL> @@spcpkg
SQL> Rem $Header: spcpkg.sql 28-jan-2008.21:06:26 arogers Exp $
SQL> Rem spcpkg.sql
SQL> Rem Copyright (c) 1999, 2008, Oracle. All rights reserved.
SQL> Rem NAME
SQL> Rem spcpkg.sql
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create statistics package
SQL> Rem NOTES
SQL> Rem Must be run as the STATSPACK owner, PERFSTAT
[..]
SQL> set echo off;
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Im nächsten Schritt muss man dann das Erstellen der Statspack-Snapshots automatisieren. Dazu bietet Oracle das Skript spauto.sql ein, das einen Datenbank-Job anlegt, der stündlich einen Snapshot erstellt. Historisch bedingt nutzt Oracle hier das Package DBMS_JOB. Wer den Scheduler nutzen will, kann z.B. mittels

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"PERFSTAT"."STATSPACK_SNAPSHOT"',
job_type => 'STORED_PROCEDURE',
job_action => '"PERFSTAT"."STATSPACK"."SNAP"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
start_date => to_timestamp_tz('2013-05-27 21:30:00 Europe/Vienna', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'run Statspack snapshots every 30 minutes',
auto_drop => FALSE,
enabled => TRUE);
END;

Einen Job erzeugen, der halbstündlich Snapshots erzeugt.

Auswertungen
Snapshots sammeln ist nur die halbe Miete. Die Auswertung der Snapshots ist der wichtigere Teil.

SQL> start $ORACLE_HOME/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1723222486 NF11GSE 1 NF11GSE
[..]

Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- -----
NF11GSE NF11GSE 11 27 May 2013 21:30 5
12 27 May 2013 22:00 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11

Enter value for end_snap: 12
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_12. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:
[..]

End of Report ( sp_11_12.lst )
SQL>

Die Statspack-Reports enthalten u.a. Informationen zu

  • Wait-Events
  • Memory-Statistiken
  • System-Statistiken
  • Top-SQL-Statements
  • Instance-Aktivität
  • IO-Statistiken
  • Buffer-Cache-Advice
  • PGA-Statistiken
  • Undo-Statistiken
  • Latch-Statistiken
  • Shared-Pool-Advice
  • SGA-Aufteilung
  • Non-Default-Instanz-Parameter
  • ..

Dann folgt die Interpretation dieser Ergebnisse und die Suche nach Tuning-Möglichkeiten.

Tipps & Tricks
1. Im Laufe der Zeit sammeln sich viele Snapshots an. Von Zeit zu Zeit sollte man daher alte Snapshot löschen. Dabei hilft das Skript sppurge.sql oder die Prozedur STATSPACK.PURGE.

Mit diesem Beitrag möchte ich nicht das „Statspack-Rad“ neu erfinden und Statspack komplett dokumentieren, sondern nur kurz die „Basics“ anreißen. Weitere Informationen zu Statspack gibt es im Web:
My-Oracle-Support-Notes:
• Statistics Package (STATSPACK) Guide [ID 394937.1]
• Installing and Configuring StatsPack Package [ID 149113.1]
• FAQ- Statspack Complete Reference [ID 94224.1]
Websites
Muniqsoft: Tips + Tricks zu Statspack
Oracle-FAQ: Statspack
Statspack Survival Guide
Oracle-Base: Statspack
Simon Krenger: Statspack Quick Reference
Jonathan Lewis: Statspack on RAC
Whitepaper „Diagnosing Performance with Statspack“ – (schon ein paar Tage älter, aber immer noch hilfreich)
Whitepaper „Tuning with Statspack“ –  (schon ein paar Tage älter, aber immer noch hilfreich)