Einige Worte zu Statspack
„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)