• Doku
• HowTo
  • Oracle
   • Admin
   • Install
   • Encrypt
   • Defrag
   • Tuning
   • External Calls
  • MaxDB
  • HowTo CVS
  • Linux (Unix)
  • IBM(AIX)
  • Windows
  • OpenSSL
  • Setup Server
• Australien
• Links
• Impressum

Wie man Oracle SQL's optimiert

Claus Ebert

Version 0.01

Kurze sammlung von Methoden zum optimieren von Oracle (SQL's)

1.Zugriffsoptimierung

• 1.1 SQL*Plus (autotrace, timing)
• 1.2 EXPLAIN PLAN und die PLAN_TABLE
• 1.3 TKPROF
• 1.4 Full Table Scans finden

2.Analyse

• 2.1 Dynamisches Analysieren

3.Rule-Based-Optimizer

• 3.1 Funktionsweise des Rule-Based-Optimizers
• 3.2 Rangfolge

4.Cost-Base-Optimizer

• 4.1

5.Einsatz von DBMS_STATS

• 5.1 Kopieren von Statistiken
• 5.2 Rückkehr zu vorherigen Statistiken

1. Zugriffsoptimierung

1.1 SQL*Plus (autotrace, timing)

Wie verwende ich SQL*Plus zur Zugriffsoptimierung

Eine schnelle möglichkeit Ausführungspläne zu einem SQL zu ermitteln ist der autotrace Parameter.
Dazu ist es notwendig, das man Zugriff auf die Tabelle EXPLAIN_PLAN hat.
Falls die Tabelle noch nicht existiert, hier steht wie man das macht.
Desweiteren muß die Rolle plustrace erzeugt worden sein (Script plustrace.sql im rdbms-Verzeichnis der Datenabank), und dem Benutzer zugewiesen sein.
Durch die Eingabe von :
SET AUTOTRACE ON
in SQL*Plus am Eingabeprompt wird die automatische Trace-Funktion aktiviert. Jeder jetzt eingegeben SQL-Befehl produziert nicht nur das Ergebnis der Abfrage, sondern auch den Zugriffsplan:

SQL> select PSN_ID,  PSN_NAME_KURZ from krn_person where psn_id=111111;

    PSN_ID PSN_NAME_KURZ
---------- ----------------------------------------
    111111 Ebert

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=48)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'KRN_PERSON' (Cost=2 Card=1 Bytes=48)
   2    1     INDEX (UNIQUE SCAN) OF 'PSN_PK' (UNIQUE) (Cost=1 Card=1)

Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        241  bytes sent via SQL*Net to client
        309  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der oben Dargestellte Plan zeigt, daß über den Primary Key PSN_PK auf die Tabelle KRN_PERSON im CHOOSE-Modus zugegriffen wird.
Es sind 3 Oracle-Blöcke im konsistenten Modus gelesen worden, und der Client hat 241 Bytes gesendet und 309 Bytes empfangen.

Durch eine genauere Parametisierung kann die Ausgabe eingeschränkt werden.
SET AUTOTRACE TRACE EXPLAIN
zeigt nur den Ausführungsplan. Alles andere wird unterdrückt.
SET AUTOTRACE TRACE STATISTICS
gibt nur die Zugriffsstatistiken aus.

Falls auch noch die Antwortzeiten gemessen werden sollen, kann das mit
SET TIMING ON
erreicht werden. (Das Ausgabeformat ist nicht auf allen Plattformen einheitlich)

1.2 EXPLAIN PLAN und die PLAN_TABLE

Eine weitere Möglichkeit den Ausführungsplan eines SQL-Statements zu ermitteln ist das EXPLAIN PLAN Kommando.

Die Tabelle PLAN_TABLE hat folgende Spalten:

Durch den Befehl:

EXPLAIN PLAN SET STATEMENT_ID = 'Q1' FOR
  SELECT PSN_ID,  PSN_NAME_KURZ FROM KRN_PERSON WHERE PSN_ID=111111;

Wird der Zufriffsplan der Datenbankabfrage in der PLAN_TABLE unter den Name 'Q1' protokilliert.
Es ist sehr sinnvoll den Ausführungsplänen eigene Namen zu geben, da so verschieden Pläne in der Tablelle gespeichert werden können.
Mit

SELECT LPAD(' ',2*(LEVEL-1))||OPERATION||' '||OPTIONS||' '||OBJECT_NAME||' '
       ||DECODE(OBJECT_NODE,'','','['||OBJECT_NODE||'] ')
       ||DECODE(OPTIMIZER,'','','['||OPTIMIZER||'] ')
       ||DECODE(ID,0,'Cost='||POSITION,
          DECODE(COST,'','',' Cost='||COST||' '||
            DECODE(ID,0,'','Card='||CARDINALITY||' ')
            ||DECODE(ID,0,'','Bytes='||BYTES)
            )
          ) QUERY
 FROM PLAN_TABLE
START WITH ID = 0 AND STATEMENT_ID = 'Q1'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'Q1'
ORDER BY ID;

wird der Plan in einer hirarchischen Struktur ausgegben. Man kann natürlich auch das Script utlxpls.sql in Oracle/rdbms/admin Verzeichnis verwenden.
Wichtig bei diesen Statement sind die STATEMENT_ID='Q1' Klauseln, mit denen der auszugebende Plan benannt wird. Sie sollten angepasst werden.

1.3 TKPROF

Wem die vom Optimizer ausgegebenen Zufriffsoperationen zu wenig ist, der kann sich mit SQL_TRACE und TKPROF die tatsächlichen Werte anzeigen lassen.

Die Arbeit mit tkprof läßt sich in fünf unterschiedliche Phasen einteilen:

1. Vorbereitung der Instance.
2. Aktivieren von SQL_TRACE.
3. Starten der zu untersuchenden Anwendung.
4. Deaktivieren von SQL_TRACE.
5. Analysieren der Ergebnisse.

1.) Zur Vorbereitung der Instance sind die folgenden Parameter zu setzen (bzw. zu Prüfen):

  SELECT NAME, VALUE FROM V$PARAMETER;

zeigt alle Parameter, und deren momentane Werte an.

timed_statistics=true
Dieser Parameter schaltet die interne Zeitmessung ein. Es geht auch ohne, aber dann werden auch keine Ausführungszeiten angezeigt.
Da dieser Parameter das Laufzeitverhalten der Datenbank beeinflußt sollte er nur bei Bedarf eingeschaltet werden.
Ab Version 8 kann die Zeitmessung auch in der Session aktiviert werden.
  ALTER SESSION SET TIMED_STATISTICS=TRUE;

Der Parameter max_dump_file_size legt die maximale Größe der vom Trace produzierten Files fest (in Blöcken des Betriebssystems). Ist der maximale Wert erreicht, wird einfach nicht mehr weiter geschrieben und keine Warnungen oder Fehlermeldungen ausgegeben.
Die Ergebnisse werden in so einem Fall NICHT KORREKT sein.

Im Parameter user_dump_path befindet sich der Path, wo (auf dem Oracle-Server) die Trace-Files abgelegt werden.

2.) Das SQL_TRACE aktivieren ( innerhalb einer Session oder außerhalb einer Session ).
evtl die Zeitmessung einschalten
  ALTER SESSION SET TIMED_STATISTICS=TRUE;

3.) Ausführen der Anwendung. z.B: Ausführen eines SQL's;
  select * from krn_person where psn_id = 111111;

4.) Zu Deaktivieren von SQL_TRACE gehen wir genauso vor, wie beim Aktivieren, nur das das wir SQL_TRACE FALSE zuweise. z.B:
   ALTER SESSION SET SQL_TRACE=FALSE;

5.) Die erzeugte Datei muß zuerst im user_dump_path Verzeichnis gesucht werden (siehe oben). Sie hat normalerwise das Format "ora_nnnn.trc", wobei nnnn die Prozessnummer des Serverprozesses ist, zu dem der User Connected ist.
Jetzt kann die Trace-Datei mit dem Programm tkprof(windows: tkprof80) formatiert, der zugriffsplan generiert und in eine Ausgabedatei geschrieben werde.
  tkprof tracedatei ausgabedatei explain=user/passwort
Die Ausgabedate kann danach mit jedem Texteditor betrachtet werden.

z.B: unser obiger SQL

TKPROF: Release 8.1.5.0.0 - Production on Thu Oct 23 18:34:23 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Trace file: ora_27286_ch.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

select * 
from
 krn_person where psn_id = 111111

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.03          0          6          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 60  (CHOC)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID KRN_PERSON 
      1   INDEX UNIQUE SCAN (object id 32071)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'KRN_PERSON'
      1    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PSN_PK' (UNIQUE)

********************************************************************************

1.4 Full Table Scans finden

seit Version 9i könne wir auf einfache Weise alle Full Table Scans auslesen

Full Table Scans sind in normalen OLTP Datenbanken unerwünscht,
und mit Hilfe des folgenden Scripts können wir diesen SQL's auf die schliche kommen.
set pages 1000 
set feedback off
set lines 150
Column sql_text Format A50 Heading 'Full Table Scans' Wrap
break on sql_text skip 1
Select Executions, Sorts, Disk_Reads, Buffer_Gets, CPU_Time,
       Elapsed_Time, sql_text
  from v$sqlarea
 where (address, hash_value) in (Select address, hash_value from
v$sql_plan   where options   like '%FULL%' and operation like '%TABLE%') 
Order by Elapsed_Time
/
set feedback on 

2. Analyse

Scripts zum Analysieren der Datenbank

2.1 Dynamisches Analysieren

Dynamisches Analysieren aller Tabellen eines DB-Owners

Ich benutze diese Script, indem ich den ich das Statement
DEFINE OWN='PTS'
meinen Bedürfnissen anpasse:

CLEAR BUFFER
SET ECHO ON
SET FEED ON
SET TIMING ON
SET VERIFY OFF
SET SERVEROUTPUT ON  SIZE 1000000

DEFINE OWN='PTS'

DECLARE
  SQL_STMT    VARCHAR2(2000);
BEGIN
  FOR TAB_REC IN (SELECT OWNER,TABLE_NAME
      FROM ALL_TABLES WHERE OWNER = UPPER('&OWN') 
    )  LOOP
    SQL_STMT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS  (' ||
                'OWNNAME  => :1 , TABNAME => :2 , PARTNAME=> NULL, ' ||
                'ESTIMATE_PERCENT => 20, DEGREE => 5 , CASCADE => TRUE); END;'  ;
    DBMS_OUTPUT.PUT_LINE (SQL_STMT);
    EXECUTE IMMEDIATE SQL_STMT USING TAB_REC.OWNER, TAB_REC.TABLE_NAME ;
  END LOOP;
END;
/

3. Rule-Based-Optimizer

3.1 Funktionsweise des Rule-Based-Optimizers

Der regelbasierte Optimizer verwendet einen vordefiniterten Satz an Regeln um den Ausführungsplan zu ermitteln.

Unter folgenden Bedingungen wird der Rule-Based-Optimizer von der Datenbank verwendet:

• In der INIT.ORA wird OPTIMIZER_MODE=RULE angegeben.
• In der INIT.ORA wird OPTIMIZER_MODE=CHOOSE angegeben, aber es existieren keine Statistiken.
• Die Session wird mit
  ALTER SESSION SET OPTIMIZER_MODE=RULE;
  umgestellt
  
• Die Session wird mit
  ALTER SESSION SET OPTIMIZER_MODE=RULE;
  umgestellt, aber es sind keine Statistigen vorhanden.
  
• Der Hint /*+ RULE */ wird in dem SQL verwendet.

3.2 Rangfolge

4. Cost-Base-Optimizer

4.1

Kommt noch

5. Einsatz von DBMS_STATS

DBMS_STATS wurde erst mit Oracle 8i eingeführt und ermöglicht (unter anderem) das kopieren von Statistiken von einer Datenbank (Produktion) in eine andere (Test)

5.1 Kopieren von Statistiken

DBMS_STATS ermöglicht das Kopieren von Statistiken von einem Schema in ein anderes

Um die Statistiken eines Schemas zu speichern muss zuerst (falls noch nicht vorhanden) eine Tabelle angelegt werden:

EXEC SYS.DBMS_STATS.CREATE_STATS_TABLE('CEDB','CEDB_STATS');

wobei CEDB das Datenbank-Schema (oder DB-Owner) ist und CEDB_STATS Den Tabellennamen darstellt.

Als nächstes wird die neu angelegte Tabelle mit den Statistiken gefüllt:

EXEC SYS.DBMA_STATS.EXPORT_SCHEMA_STATS('CEDB','CEDB_STATS','UNIQUE_ID');

Der Wert UNIQUE_ID ist ein Textfeld, das mit einer eindeutigen Bezeichnung gefüllt sein sollte (Datum und Zeit bieten sich hier an)
Da in der Statistik-Tabelle mehrere Statistiken gespeichert werden können ist diese Bezeichnung notwendig um auf eine bestimmte Statistik zugreifen zu können (siehe auch Rückkehr zu vorherigen Statistiken)
Jetzt befinden sich die aktuellen Statistiken in der Tabelle CEDB_STATS
Falls man diese Statistiken in eine andere Datenbank, etwa aus der Produktion in die Entwicklungsdatenbank, kopieren möchte, exportiert und importiert man die Statistk-Tabelle:

exp cedb/geheim@prod file=stats.dmp tables=cedb_stats

imp cedb/geheim@test file=stats.dmp tables=cedb_stats

Nach dem Import (evtl muss vor dem Import die CEDB_STATS tabelle mit einem TRUNCATE geleert werden um Import-Fehler zu vermeiden) kann man die Statistiken mit einem

EXEC SYS.DBMS_STATS.IMPORT_SCHEMA_STATS('CEDB','CEDB_STATS','UNIQUE_ID');

in die Zieldatenbank eingefügen.

Danach sollte sich der Cost-Base-Optimizer in der Testdatenbank genauso wie in Produktion verhalten

5.2 Rückkehr zu vorherigen Statistiken

Viele DBA's analysieren ihre Datenbank in regelmäßigen Abständen.
Dagegen in prinzipiell nichts einzuwenden, da eine erneute Analyse bei veränderten Datenmengen normalerweise eine Verbesserung der Performance bewirkt.

Die Betonung liegt hier auf normalerweise, da in einigen Fällen der Cost-Base-Optimizer den völlig falsche Weg einschlägt.

Abhilfe schafft hier das DBMS_STATS Package, mit dem es möglich ist eine Historie der Statistik vorzuhalten.

Dazu erstellt man sich für das Schema die Statistik-Tabelle (falls noch nicht vorhanden), in der die Historie gespeichert wird.
EXEC SYS.DBMS_STATS.CREATE_STATS_TABLE('CEDB','CEDB_STATS');

Jetzt überträgt man die aktuellen Statistiken in die Statistik-Tabelle:
EXEC SYS.DBMS_STATS.EXPORT_SCHEMA_STATS('CEDB','CEDB_STATS','PROD_20051129');

Danach kann man die neuen Statistiken erstellen:
EXEC SYS.DBMS_STATS.GATHER_SCHEMA_STATS('CEDB',10,4,true));

Falls sich nun herausstellt, das die neuen Statistiken zu problemem führen kann man die vorher gesicherten Statstiken mit
EXEC SYS.DBMS_STATS.IMPORT_SCHEMA_STATS('CEDB','CEDB_STATS','PROD_20051129');

wiederzurückholen.