Überwachung, Analysen und Tuning

Überwachung, Analyse und Tuning
Reimar Lehmann
PC-Ware Business Solutions
Tel: 0341/2568000 mail: [email protected]
Themen






Tuning-Möglichkeiten
Oracle Diagnose- und Tuningwerkzeuge (bis 9i)
SGA
Oracle Optimizer
SQL-Tracing
I/O Contention
Tuning-Möglichkeiten
Komponente
verantwortlich
Perf. Steigerung
Design (Datenmodell +
SQL)
System/App.entwickler
>> 100%
Applikation
App.entw. /
App.Admin
>>100%
DB – Speicher(SGA)
DBA
< 60%
I/O
DBA (Netw.Admin)
< 30%
Zugriffskonflikte
DBA
<10%
OS-Tuning
Diagnose- und Tuningwerkzeuge

Alert.log:

Regelmässige Prüfung auf




Interne Fehler (ORA-600)
Überwachung Datenbankvorgänge
Nicht-Defaul-Initialisierungsparameter
Anfang und Ende von Checkpoints
(init.ora: LOG_CHECKPOINTS_TO_ALERT=TRUE)
 Deadlocks





Benutzer-Tracedateien
Dynamische PerformanceViews (V$-Views) und DataDict-Views
Skripte utlbstat.sql/utlestat.sql
Package (PL/SQL) STATSPACK (ab 8.1.6)
Oracle Enterprise Manager / DBA Studio
Dynamische PerformanceViews



Werden alle in V$fixed_table aufgelistet
Wichtigste Views:

V$DATABASE, V$INSTANCE:
Informationen über Instanz und DB

V$WAITSTAT:
Statistiken über Zugriffskonflikte

V$SYSTEM_EVENT: gesamten Waits für bestimmte Ereignisse (->
z.B.buffer_busy_waits

V$LIBRARYCACHE,V$SQLAREA: Verwendung von Statements im
Library-Cache

V$SYSSTAT: allg. Instanzstatistiken

V$FILESTAT: I/O-Vorgänge auf Dateien

V$WAITSTAT: Statistiken zu Block-Zugriffskonflikten

V$SESSTAT: Statistiken der Sessions einzelner Benutzer

V$SESSION_EVENT Waits für best. ereignisse auf Sessionebene
Überblick über Statistik-Views in V$STATNAME
Utilities UTLBSTAT und UTLESTAT (bis 8i)
 Sammlung von Performance-Daten für bestimmte Periode zwischen
Ausführung utlbstat und utlestat
 Generierung eines Berichtes
(report.txt)
 Anmeldung als SYSDBA
 utlestat.sql generiert Bericht (Default: report.txt im aktuellen Verzeichnis)
statspack (ab 8i)
 Installation: @%ORACLE_HOME%\rdbms\admin\spcreate.sql
 Statistiksammlung:
 EXECUTE statspack.snap;
Erstellung eines Snapshots
 @%ORACLE_HOME%\rdbms\admin\spauto.sql
Script zur Automatisierung von Snapshoterstellung (Default alle
Stunde)
 Als user perfstat/perfstat Skript ausführen zur Generierung eines
Reports (als Differenzmenge zweier Snapshots):
@%ORACLE_HOME%\rdbms\admin\spreport.sql
Statspack-Informationen










Vollständige Liste aller Wait-Ereignisse
Informationen zu derzeitig im Shared Pool befindlichen SQLAnweisungen
Statistiken zur Instance-Aktivität
Tablespace- und Datei-I/O
Buffer Pool Statistiken
Statistiken zu Rollbacksegmenten
Latch-aktivität
Dictionary Cache- und Library Cache-statistiken
SGA-Statistiken
Startup-Parameter-Werte aus init.ora
Themen
 Tuning-Möglichkeiten
 Oracle Diagnose- und Tuningwerkzeuge (bis 9i)
 SGA
 Oracle Optimizer
 SQL-Tracing
 I/O Contention
SGA - Shared Pool

Library-Cache (SQL und PL/SQL-Anweisungen):

Anweisungen sollten mehrfach genutzt werden

Anweisungen sollten nicht gleich überschrieben werden ->

Grosse und häufig benutzte PL/SQL-Objekte sollten im Cache
verbleiben;

Performanceoptimierung:




SHARED_POOL_RESERVED_SIZE setzen (nicht mehr als 50%)
SHARED_POOL_SIZE
Grosse PL/SQL-Objekte im Cache pinnen mit
DBMS_SHARED_POOL.KEEP
Init.ora-Parameter OPEN_CURSORS erhöhen;
CURSOR_SHARING=FORCE setzen
Dictionary Cache (V$ROWCACHE): Verhältnis der gets zu getsmisses
< 15%, ansonsten shared_pool_size erhöhen -> ABER abhängig von
Situation/Umgebung
SGA - Buffer Cache




Beeinflussung durch init.ora-parameter: DB_BLOCK_SIZE,
DB_BLOCK_BUFFERS, BUFFER_POOL_KEEP, BUFFER_POOL_ RECYCLE
Latch-Optimierung durch DB_BLOCK_LRU_LATCHES (8i)
(Default 1):

Misses in V$LATCH > 5%
Optimierung durch:

Buffer-Pool erhöhen

Mehrere Buffer-Pools verwenden

Kleine u. häufig verwendete Tabellen im Cache pinnen
Überwachung (Berichte von statspack o. utlb/estat; V$SYSSTAT): Cache-Hitratio (Tuning, wenn < 90% )
ABER situationsabhängig
Sortiervorgänge optimieren
 Sortiert wird bei:
 Index-Erstellung
 ORDER BY ; GROUP BY
 DISTINCT
 UNION
 SORT/MERGE-Joins
 ANALYZE
 SORT_AREA_SIZE entsprechend wählen (8i)
 Sortiervorgänge möglichst vermeiden
 Benutzer TEMPORARY TABLESPACE zuweisen
 Ab 9i PGA_AGGREGATE_TARGET und
WORKAREA_SIZE_POLICY=AUTO setzen
Themen
 Tuning-Möglichkeiten
 Oracle Diagnose- und Tuningwerkzeuge (bis 9i)
 SGA
 Oracle Optimizer
 SQL-Tracing
 I/O Contention
Optimizer
 Modi:
 Regelbasiert(Rule) RBO
 Verwendet feste Regeln; gesteuert über Syntax und Data-dictionary
 Ab 10g nicht mehr supported
 Kostenbasiert (Choose) CBO
 Ausführungspfad mit geringsten Kosten
 Statistikgesteuert -> Tabellen sollten Statistiken haben:

ANALYZE TABLE COMPUTE|ESTIMATE STATISTICS
 Kann auf Instanzebene
(optimizer_mode=choose|first_rows|all_rows|rule), auf Sessionebene
(Alter session set optimizer_mode=) und auf Statement-Ebene
angegeben werden (z.B. Hint /*+ CHOOSE */)
Themen
 Tuning-Möglichkeiten
 Oracle Diagnose- und Tuningwerkzeuge (bis 9i)
 SGA
 Oracle Optimizer
 SQL-Tracing
 I/O Contention
SQL-Trace






Einschalten auf Instance-Ebene: SQL_TRACE=TRUE
Session-Ebene: ALTER SESSION SET SQL_TRACE=TRUE
Execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id,
serial_id, true) -> session_id und serial_id aus V$SESSION
Trc-Datei im user_dump_dest
SQL*Plus-Befehl: SET AUTOTRACE [ON|OFF|TRACEONLY]
[EXPLAIN|STATISTICS]
Statistiken sammelbar mit package DBMS_STATS
Parameter TIMED_STATISTICS=TRUE setzen (8i/9i)
oder STATISTICS_LEVEL=TYPICAL|ALL (ab9i)
SQL-Trace und tkprof



„Formatierung“ der Trc-Datei im user_dump_dest
Kommandozeilen-Tool tkprof zum Ausgeben der Datei in lesbarem
Format:
tkprof tracefile.trc output.txt [options]
Beispiel:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a
INSERT=STOREA.SQL SYS=NO
Indizes
 Überwachung mit ANALYZE INDEX name VALIDATE STRUCTURE (9i)
 SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS
Nichtnutzbar From index_stats
 Wenn Wert > 20%:
ALTER INDEX name REBUILD
Themen
 Tuning-Möglichkeiten
 Oracle Diagnose- und Tuningwerkzeuge (bis 9i)
 SGA
 Oracle Optimizer
 SQL-Tracing
 I/O Contention
I/O Contention
 Asynchrone Schreiboperationen durch Unterprozesse des DBWR (nur 1
DBWR konf.) durch Parameter DBWR_IO_SLAVES (int)
oder
 Mehrere DBWn-Prozesse -> DB_WRITER_PROCESSES
z.B. auf Systemen mit mehr CPUs
(Beobachtung von V$SYSTEM_EVENT.TOTAL_WAITS von EVENT ‚free
buffer waits‘
 REDOLogs auf schnellste Platte
 Problematik: Checkpoint-Queue: geringer Wert für
FAST_START_MTTR_TARGET (unter 8i FAST_START_IO_TARGET)
mehr Schreibvorgänge durch DBWR
Links




http://www.oracle.com
http://technet.oracle.com
http://metalink.oracle.com
http://www.oraperf.com