Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
27 novembre 2009 5 27 /11 /novembre /2009 10:14

Heute handelt es sich um die Bind-Variablen.
Ob PL/SQL, Java, oder irgend welche Programmierungsprache gilt das selbe Motto : Bind-Variablen nutzen, oder den Zorn des AmokDBAs erleiden...
Aber wieso ? Und was zum Teufel sind dann diese "bind variable peeking" und "shared cursor" ?


Dieser Artikel bietet eine illustrierte Zusammefassung :
1) Frühere Ausführungspläne verwenden
2) Erstellung des Ausführungsplans für Unregelmässige Aufteilungen
3) Wie man die (wieder)Erstellung des Ausführungsplan zwingt

1) Frühere Ausführungspläne verwenden

Bevor die Ausgeführung eines SQL-Kommandos, wird es von Oracle geparst.
Da wird unter anderen der Ausführungsplan erstellt : Oracle entscheidet welches Index, welches JOIN Verfahren gebraucht wird.
Das ganze kostet selbstverständlich Zeit. Zwar gering wenn es sich um eine lange Anfrag handelt, aber dagegen schon ziemlich bedeutend für kurze Anfrage (zum Beispiel in OLTP Anwendungen).
In solche Anwendungen werden meistens die selben Queries ausgeführt : die Daten des Kunden X, Y, Z, der heutige, gestrige Umsatz, ... den Ausführungsplan jedes Mal zu erstellen wäre reine verschwendung !

Deshalb werden die Abfragen und die entsprechenden Ausführungspläne von Oracle im Library Cache gespeichert.
Diese Pläne können wiedergebraucht werden, NUR wenn die SQLString ganz genau einer in der Library Cache gespeicherten Anfrage gleicht. (Ausser mit den cursor_sharing optionen, die wir im dritten Teil besprechen werden)

Deshalb sollen Bindvariablen benutzt werden : die SQL Strings im Library Cache sollen unabhängig von der Literalen Werten sein.

Falls ihr glaubt, es geht nur um geringe Steigerungen, messen wir jetzt den Unterschied mit einem kleinen Beispiel :

Die Tabelle der 1000000 erste Zahlen, ein Index, und die Statistiken :

CREATE TABLE TEST_LIB_CACHE AS
SELECT level AS n FROM DUAL
CONNECT BY LEVEL <= 1000000
/

CREATE INDEX TEST_LIB_CACHE_I1 ON TEST_LIB_CACHE(n)
/

exec dbms_stats.gather_table_stats('PACMAN', 'TEST_LIB_CACHE', cascade => true)



Der Test besteht aus einem einfachen Durchlauf der Tabellezeilen (ohne Cursor), mit oder ohne Bindvariablen.


Erste Variante (MIT) :


SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
  i number;
  j number;
BEGIN
  FOR i IN 1..1000000
  LOOP
    SELECT n INTO j FROM TEST_LIB_CACHE WHERE n = i;
  END LOOP;
  dbms_output.put_line('Fertig !');
END;
/

Fertig !

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.90
 

Zweite Variante (MIT) :


ALTER SYSTEM FLUSH BUFFER_CACHE;
DECLARE
  i number;
  j number;
BEGIN
  FOR i IN 1..1000000
  LOOP
    EXECUTE IMMEDIATE 'SELECT n INTO :j FROM TEST_LIB_CACHE WHERE n =' || to_char(i);
  END LOOP;
  dbms_output.put_line('Fertig !' || j);
END;
/

Fertig !

PL/SQL procedure successfully completed.

Elapsed: 00:07:39.41


Überzeugt ?


2) Wir haben jetzt ein Paar Ausführungspläne, die für die ganze Anwendungen reichen.


Aber ist der Bestmögliche Ausführungsplan wirklich unabhängisch der Literalen ?
Die antwort lautet : selbstverständlich nein !


Die Rechnungen des CBO (Cost Based Optimizer) sind extrem Kompliziert. Trotzdem werden wir hier versuchen zu verdeutlichen, warum die Selectivität eines Prädikates so wichtig ist.


Ihr Aufgabe lautet : "Alle Sätze abschreiben, die ein Stichwort enthält, das mit M beginnt."
Dafür gibt es den Index !
- Erstens die Buchstabe M im Index suchen
- Für jedes Wort, die Liste der Seiten durchgehen
- Jede Seite öffnen, die Sätze finden und abschreiben


Wieviele Seiten werdet ihr öffnen müssen ?
Falls es zu Viele davon gibt, ist es nünftiger das Buch direkt durchzublättern !


Die Selectivität einer Spalte wäre hier der durchschnittliche Zahl der Erscheinungen der Stichwörter.
Aber diese statistische Eingabe konnte leider nicht ausreichen, weil die Aufteilung unregelmässig ist : manche Stichwörter werden viel mehr gebraucht als andere...
Deswegen wurden die Histograms eingeführt.


Prüfen wir mal nach ob's klappt !


Eine tabelle mit verzerrten Einteilung (die Gruppe 1 besteht aus einem Mitglied, während die Anderen der 0 Gruppe gehören) :


CREATE TABLE test_histo AS
SELECT level AS id, CASE level WHEN 1 THEN 1 ELSE 0 END as grp
FROM DUAL
CONNECT BY level <= 1000000
/
CREATE INDEX test_histo_i ON test_histo(grp)
/


exec dbms_stats.gather_table_stats('PACMAN', 'TEST_HISTO', cascade => true, method_opt => 'FOR COLUMNS GRP 2 SKEWONLY')


Und jetzt die Ausführungspläne für beide Gruppen :


SET AUTOT TRACEONLY EXPLAIN
 SELECT *
 FROM test_histo
 WHERE grp = 0
 /


--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1003K|  5880K|   512  (10)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| TEST_HISTO |  1003K|  5880K|   512  (10)| 00:00:07 |
--------------------------------------------------------------------------------


SELECT *
FROM test_histo
WHERE grp = 1
/

--------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     6 |     4|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HISTO   |     1 |     6 |     4|
|*  2 |   INDEX RANGE SCAN          | TEST_HISTO_I |     1 |       |     3|
---------------------------------------------------------------------------



Perfekter kann es nicht sein.


Aber kommen wir zum Hauptthema : wenn wir die Gruppenummer hinter der Bindevariable verstecken, wie soll der CBO die Gruppe erkennen (beziehungsweise den richtigen Ausführungsplan) ?


3) Eine Menge von Schlechten Lösungen...


a. Bind Variable Peeking
Seit Oracle 9i gibt es die "Bind variable peeking".
Das heisst, dass Oracle die Literalen prüft, bevor er den Plan erstellt, egal ob Bindevariablen oder nicht.
Hört sich gut an... aber dass gilt nur wenn die Anfrage zum ersten Mal geparst wird !


Und deswegen hassen viele DBA / Entwickler die Histograms : der erstgeparste plan wird dann für alle Anfragen benutzt.


b. Cursor sharing
Eigentlich ist es die Gegenproblematik.
Falls die Literalen "hardcoded" sind, kann der in der 8i version eingeführte cursor_sharing Parameter den CBO dazu bringen, diese Literalen mit Bindevariablen ersetzen. Genial ? Nein !


So einfach ist aber nicht...


Drei verschiedene Werten für diesen Parameter :
- EXACT : kein Ersetzen
Das vorgegebene Wert.


- FORCE : systematisches Ersetzen
Nein, um Gottes Willen ! Wir haben doch gerade erklärt, warum wir uns nicht leisten können, den gleichen Ausführungsplan zu nutzen...


- SIMILAR : Ersetzen, ausser wenn der Ausführungsplan sich ändern könnte
Schon besser. Aber falls es Histogramme gibt, wird jedes Mal einen neuen Ausführungsplan gebaut (und beziehungsweise die Anfrage hardparsed).


Dieser Parameter ist desshalb hauptsächlich hilfreich wenn man kein Zugriff zu den Abfragen hat (zum Beispiel in einem Progiciel).
Aber ansonsten entscheidet ihr am besten allein : Bindvariablen im Allgemeinen, hardcodierte literalen wenn unregelmässig aufgeteilte spalten benutzt werden.


c. Yes we can !
Aber bitte nicht verzweifeln : mit Oracle 11g kommt der Adaptative Cursor !
Die sensiblen (das heisst mit Bindvariablen auf die histograms-Spalten) Abfragen werden markiert, duppliziert für jedes Bucket des Histograms.


Oracle bringt Hoffnung für die Menscheit, aber bringt die 11g Migration zu Ende bevor 2012 ;)

 

 

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires