19. Februar 2008

Bind Variablen: Wirklich immer benutzen? (Was ist "Bind Variable Peeking")?

English title: Always use bind variables: Really? (about "Bind Variable Peeking")

Heute geht mit dem Thema effizientes SQL - aus der Sicht des Entwicklers - weiter:
Today we'll continue with efficient SQL - from the developers' point of view:
Im letzten Post habe ich den Performance-Effekt der Verwendung von Bindevariablen vorgestellt. Wie das Testprogramm zeigte, ist dieser Effekt ganz erheblich - wenn eine Applikation viele "gleichartige" SQL-Statements absetzt, hat die Verwendung (oder Nicht-Verwendung) von Bindevariablen signifikante Auswirkungen auf die Performamce.
The last post illustrated the performance impact of using bind variables. As the results of little test program demonstrated this impact can be quite significant. If an application frequently issues similar SQL statements the developer can improve the performance just by using bind variables.
Heute möchte ich auf die Frage eingehen, ob man wirklich immer Bindevariablen nutzen sollte (Ihr könnt es euch denken: Natürlich nicht). Das Thema Bind Variable Peeking wird dabei ein wichtiger Schwerpunkt sein. Zunächst jedoch nochmal ein paar Worte zur Abarbeitung von SQL-Kommandos in der Datenbank:
  1. Parse: Das Statement wird syntaktisch und semantisch geprüft und es wird ein Ausführungsplan erstellt
  2. Bind: Die Platzhalter der Bindevariablen werden durch die konkreten Werte ersetzt
  3. Execute: Das Statement wird ausgeführt.
Today I'd like to elaborate a bit on the question whether bind variables are the best choice in every case (and as you can imagine: of course not). Bind Variable Peeking will play an important role here. But beforehand some words on the phases of SQL execution in the database:
  1. Parse: The statement syntax is being checked as well as its semantics (tables and columns are present?); furthermore the execution plan is being generated
  2. Bind: The bind variables are replaced with the "real" values
  3. Execute: questions?
Der Ausführungsplan wird also während des "Parse" im ersten Schritt erstellt. Der Optimizer schaut dazu nach ...
  • ... ob Indizes da sind ...
  • ... wieviele Zeilen da sind ...
  • ... ob die Query selektiv ist oder nicht ...
... und anderes mehr. Insbesondere die Selektivität ist ganz entscheidend für die Frage, ob und wie Indizes genutzt werden - je weniger selektiv eine Query (oder Subquery) ist, desto wahrscheinlicher ist ein Full Table Scan.
The execution plan is therefore generated during the "parse" step. The optimizer looks ...
  • ... for existing indexes ...
  • ... how many rows the involved tables contain ...
  • ... whether the query is selective is or not ...
... and many other things. The selectivity is very important for the decision whether to use an index or not. The more selective a query or subquery is the more probably is index usage.
Doch wie kann der Optimizer die "Selektivität" bestimmen, wenn er die Werte der Bindevariablen gar nicht kennt ... ?
But how can the optimizer determine the "selectivity" when it does not know the values of the bind variables...?
Naja, zunächst ist diese Frage nur dann relevant, wenn es ein extremes Ungleichgewicht in der Verteilung der Daten der jeweiligen Spalte gibt (wie im folgenden Beispiel). Bei Gleichverteilung ist die optimale Strategie vom konkreten Wert der Bindevariable unabhängig. Zunächst erstellen wir mit folgendem Skript eine Tabelle basierend auf der Tabelle CUSTOMERS im Schema SH. Dieser Tabelle wird eine neue Spalte STATUS hinzugefügt. 1% der Kunden werden den Status "A" und alle anderen die Status "B" oder "C" erhalten.
Now, firstly this question is only relevant when there is a skew in the data distribution of that particular column. If the data was distributed equally the execution plan would not depend on bind variable values - each possible value would lead to the same plan. But a skew in the data distribution changes the picture. In this example we'll first create a table based on the CUSTOMERS table in the sample schema SH. We'll then add a column named STATUS to that table and finally about 1% of the table rows will get the value "A" - the other 99% will get the values "B" or "C".
drop table customers
/

create table customers as select * from sh.customers
where rownum < 5000
/

alter table customers add (status char(1))
/

declare
  type number_array is table of number index by binary_integer;
  type char_array is table of varchar2(1) index by binary_integer;
  v_id_array  number_array;
  v_status_array char_array;

  v_random_val number;
begin
  select cust_id bulk collect into v_id_array from customers;
  for i in v_id_array.first..v_id_array.last loop
    v_random_val := dbms_random.value(0,100);
    if v_random_val < 1 then 
      v_status_array(i) := 'A';
    elsif v_random_val > 1 and v_random_val < 10 then
      v_status_array(i) := 'B';
    else 
      v_status_array(i) := 'C';
    end if;
  end loop;
  forall i in v_id_array.first..v_id_array.last 
    update customers set status = v_status_array(i)
    where cust_id = v_id_array(i);
end;
/
sho err 
Nun wird die neue Spalte STATUS indiziert (IDX_STATUS). Schließlich werden die Statistiken erneuert (DBMS_STATS.GATHER_SCHEMA_STATS). Dabei stellt der Parameter method_opt sicher, dass ein Histogramm für die Spalte STATUS gebildet wird - mit einem solchen Histogramm "weiss" der Optimizer um die (ungleiche) Verteilung der Daten.
Now the new column STATUS gets indexed (IDX_STATUS). The statistics get refreshed using DBMS_STATS.GATHER_SCHEMA_STATS. Since we know about the distribution skew in the STATUS column we'll force the database to create histograms for it (parameter method_opt). Now the optimizer "knows" about the data distribution and it will consider it when deciding about the execution strategy.
create index idx_status on customers (status)
/

exec dbms_stats.gather_schema_stats(ownname => user, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY'); 
Wie geht die Datenbank nun vor, wenn eine SQL Abfrage mit der Spalte STATUS in der WHERE-Klausel hereinkommt...?
What will the database do if a SQL using the STATUS column in its WHERE clause arrives ...?
select * from customers where status = 'A' 
Wenn keine Bindevariablen verwendet werden, "sieht" die Datenbank den Wert "A" sofort und sie kann anhand der Histogramme nachschauen, wieviele Zeilen die Abfrage zurückliefern wird. Die Entscheidung, ob Index oder Full Table Scan nun besser sind, ist dann ein Leichtes. Im letzten Beispiel haben wir jedoch gesehen, dass man besser Bindevariablen verwenden sollte - was passiert dann ...?
If bind variables were not used the database would "see" the value (here: "A") immediately when parsing the SQL statement - the optimizer can use the histograms to determine the amount of rows the query will return. The decision whether to do a full table scan or an index lookup is now very easy. But in the last post we have seen that bind variables lead to better performance - so the developer uses bind variables. What now ...?
select * from customers where status = :1 
Die Datenbank "sieht" den Wert nun zur Parse-Zeit noch nicht. Allerdings muss der Optimizer einen Ausführungsplan erstellen - zur Lösung des Problems zieht er das Binding gewissermaßen vor (Bind Variable Peeking). Dieses Bind Variable Peeking wird pro SQL-Kommando aber nur einmal durchgeführt - wenn das gleiche SQL-Kommando erneut hereinkommt, wird der beim ersten Mal erstellte Ausführungsplan wiederverwendet. Und dies kann zu "seltsamen" Ergebnissen führen ... dazu zurück zum Beispiel:
Now the database cannot "see" the bind variable's value when parsing the SQL - it only sees ":1". To solve the problem it "peeks" into to bind variable (bind variable peeking) - but this is done only once; when the SQL is "new" to the database. The next time this SQL text arrives at the database it will pick up the already generated execution plan. And this can lead to unexpected results ...
Also: Die Tabelle wurde wie oben beschrieben erstellt und die Applikation nutzt Bindevariablen. Nach dem Hochfahren der Datenbank kommt also das SQL herein ...
Back to the example: The table was created as described above - the application does use bind variables. So the arriving SQL looks like this ...
select * from customers where status = :1 
Wie gerade beschrieben, führt die Datenbank das Bind Variable Peeking durch; wir gehen davon aus, dass in diesem Fall nach dem Status "A" gefragt wird. Anhand des Histogramms stellt der Optimizer fest, dass nur 1% der Zeilen selektiert werden und er entscheidet sich für ...
The database "peeks" into the bind variable as explained and in this case it finds the value "A". The table statistics (histogram) tells the optimizer that this value matches 1% of the rows and therefore it choses as execution strategy ...
SQL_ID  cfrfax3f40swx, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 2427356980

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE              |            |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS  |    38 |   228 |    40   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_STATUS |    38 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("STATUS"=:1) 
... den Indexzugriff - und das ist gut so. Nun geht es weiter. Über die Applikation sucht der nächste Endanwender nach allen Kunden mit Status "C". Als SQL kommt wieder herein ...
... index lookup - which is good. Now the application issues the next SQL - the end user now queries all customers with status "C", but the database does just see ...
select * from customers where status = :1 
Nun kennt die Datenbank das SQL-Statement aber bereits - es wurde ja schonmal ausgeführt. Also findet kein Bind Variable Peeking statt - der beim erstem Mal erstellte Ausführungsplan wird wiederverwendet - die Datenbank macht für dieses SQL-Statement nun immer den Indexzugriff. Aber eigentlich wäre der Full Table Scan besser ...
Now the database knows this SQL already - the SQL text is stored in the SQL cache. So it does not peek the bind variable's value and picks up the already existing execution plan. The database uses again the index - as long this SQL text stays in the SQL area it will always use the existing execution plan. But a full table scan would (of course) be better ...
Anderes Szenario: Die Datenbank wird durchgestartet und der erste Endanwender, der über die Anwendung auf die Datenbank kommt, sucht diesmal nach Kunden mit Status "C". Der Optimizer kennt das Statement noch nicht, also macht er ein Bind Variable Peeking - und kommt zu folgendem Ausführungsplan:
Another scenario: We'll bounce the database (or issue ALTER SYSTEM FLUSH SHARED_POOL) and now the first query is for customers with status "C". The optimizer again peeks into the bind variable and based on the histogram information it decides to this execution plan:
SQL_ID  bz2rfvv5145f5, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  4494 | 26964 |    40   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=:1) 
"Full Table Scan" (logisch - schließlich haben 90% der Kunden den Status "C"). Nun kommt der nächste Anwender und sucht nach den Kunden mit Status "A" ... und Ihr könnt euch den Rest denken: Das SQL ist ja nun bekannt, die Datenbank verwendet den bereits erstellten Ausführungsplan wieder und wird nun alle diese SQL-Abfragen mit Full Table Scans ausführen ... nicht so gut ...
Full table scan - this is obvious since the query targets 90% of the table rows. Now an execution plan with a full table scan is assigned to that SQL text - and the next user who queries the "A" customers will get his query executed with a full table scan.
Das Problem ist hier, dass wir eigentlich gerne je nach abgefragtem Status unterschiedliche Ausführungspläne hätten. Durch die Verwendung der Bindevariable ist das SQL-Statement textlich jedoch stets dasselbe. Mit Bind Variable Peeking wird für die erste Abfrage ein Ausführungsplan berechnet und dann beibehalten. Für ein SQL-Statement (der SQL-Text ist entscheidend) gibt es im SQL-Cache der Datenbank nur einen aktiven Ausführungsplan.
The problem is that it would be best if the execution plan changed with the bind variable's value. But due to the bind variable the SQL text is always the same. Bind variable peeking allows the database to optimize the execution based on the value of the first bind. But it will then keep this execution plan for all other bind variable values. The SQL cache will contain only one active execution plan for one SQL text.
Dies ist also ein Fall, in dem es besser ist, das SQL per Stringverkettung zusammenzusetzen und keine(!) Bindevariablen zu verwenden. Denn dann liegen in der Datenbank für die verschiedenen Status textlich verschiedene SQL-Abfragen (mit unterschiedlichen Ausführungsplänen) vor. Bindevariablen sind zwar in den meisten Fällen, aber eben nicht "per-se" die beste Lösung:
  • Bindevariablen sollten nicht genutzt werden, wenn je nach Inhalt der Bindevariablen unterschiedliche Ausführungspläne erwartet werden
  • Bindevariablen sollten genutzt werden, wenn der Ausführungsplan von den Inhalten der Bindevariablen unabhängig ist und ohnehin stets gleich berechnet würde.
Oder anders gesagt: Als Entwickler sollte man sicherstellen, dass die Datenbank zur Parse-Zeit alle Informationen hat, die sie zur Erstellung eines guten Ausführungsplans braucht. Wenn Bindevariablen solche Informationen verstecken würden, sollte man sie weglassen.
In such cases it is better to construct the SQL via string concatenation instead of using bind variables: Because then each queries status value leads to "its own" SQL text in the databases SQL cache. And each SQL text is assigned its own execution plan. Bind variables are in most cases but not in all cases the better choice:
  • Bind variables should not be used if the optimal execution plan changes with the variable's value
  • Bind variables should be used if the optimal execution plan is independent of the variable's value
. Expressed otherwise: The developer should make sure that database has all information it needs to generate the optimal plan at parse time. If bind variables hide this information the developer is better off with string concatenation.
Und wer Oracle11g nutzt, liest am besten gleich im nächsten Posting weiter, denn 11g bringt Lösungen für genau dieses Problem mit.
Oracle11g users are recommended to instantly go to the next posting. Oracle11g has some new features exactly for dealing with those problems ...

Kommentare:

Anonym hat gesagt…

Interessanter Artikel. Ich hab mich nur gerade gefragt, warum ein Full Table Scan bei einem Select auf 90% der Wertemenge besser sein sollte als ein Indexzugriff? Ich hatte bislang immer die Regel im Hinterkopf, dass ein Index nur Sinn macht wenn weniger als 25% aller Zeilen gelesen werden soll. Das umgekehrt aber ein Full Table Scan bei grossen Datenmengen einem Index-Scan vorzuziehen ist, war mir neu. Werde das mal recherchieren :)

Grüsse, Stefan

Patrick Wolf hat gesagt…

Hi Carsten,

gefällt mir Deine Serie, bin schon auf die anderen Postings gespannt.

Zu Bind Variable Peeking ist noch zu sagen, dass es meines Wissens in Oracle 11g eine Verbesserung gegeben hat. Wenn ein Execution Plan wiederverwendet wird, aber die Ausführzeit wesentlich länger ist als beim letzten mal, dann invalidiert er meines Wissens den Plan wieder. Dadurch hat man die Chance beim nächsten Mal wieder einen optimaleren Plan zu bekommen.

Ist glaube ich in einem Posting vom Tom Kyte besprochen worden.

Lg
Patrick

Carsten Czarski hat gesagt…

Naja, wenn "normale" Indizes (und keine Cluster oder IOT) verwendet werden, ist das schon naheliegend. Schließlich muss er bei 90% der Tabellenzeilen für jede Zeile erstmal im Index nachsehen (einmal IO) und dann nochmal in der Tabelle (nochmal IO). Wenn ohnehin 90% der Zeilen zurückgeliefert werden, kann man auch gleich die ganze Tabelle lesen ... So eindeutig ist das aber nur bei extremen "Skews" wie in diesem Beispiel - nimmt man dadagen mal nspw. 70% der Zeilen an, sieht das schon anders aus ...

Wichtig ist auf jeden Fall, dass der Optimizer durch das Bind Variable Peeking den ersten Ausführungsplan für alle diese SQL-Statements nimmt - auch wenn (abhängig von der tatsächlichen QueryxQuery) ein anderer besser wäre ...

Viele Grüße

-Carsten

Carsten Czarski hat gesagt…

Hi Patrick,

in der Tat - in Oracle11g hat sich das Verhalten geändert. Es ist selbst für einen Oracler gar nicht so einfach, den Überblick über alle neuen Features zu behalten ...;-)
Also: Im nächsten Post kommen die 11g-Neuerungen

-Carsten

mno hat gesagt…

Hi Carsten,
danke für den Artikel.
Ich hätte dazu noch eine Frage, vielleicht hättest du etwas dazu zu sagen.
Wann wird denn wohl die Typdefinition festgelegt. Als Bspl.:
Wenn ich ein varchar2 Feld mit z.b. dem Wert 10078 suche, wird über das entsprechende Feld ein to_number gelegt. Wobei hier aber ein to_char(100078) womöglich schneller wäre.
Also aus
select * from tabelle where feld = 100078
wird
select * from tabelle where to_number(feld) = 100078
Wenn ich aber 100078 in meiner Binding Variablen habe, was passiert dann? Weiss oracle das dies ein String ist und macht eine to_char Konvertierung? Die Variable ist als tabelle.feld%type vorher definiert worden.
Oder muss ich explizit schreiben
select * from tabelle where feld = to_char(:1)


Gruß
Marc

Carsten Czarski hat gesagt…

Hi Marc,

bei einer Bindevariable ist es einfacher. In dem Moment, in dem Du der Variable den Wert 100078 zuweist, findet bereits das to_char statt - denn durch den Deklaration (tabelle.feld%TYPE) ist die Variable ja auf VARCHAR2 festgelegt. Wenn Du nun also der Variable in Deinem Code den Wert 100078 zuweist, ist das zwar kein schöner Code, PL/SQL macht aber quasi automatisch das to_char daraus.

In der SQL-Abfrage findet dann keine Konvertierung mehr statt - aus diesem Grund ist die Definition mit tabelle.feld%type ja auch gängige, gute Praxis.

Hilft das weiter ...?

-Carsten

Anonym hat gesagt…

Hmm, ich habe gerade den Fall, dass ein SQL mit BIND-Variable 4,5 Mal so lange läuft wie ohne.
Die Aussage : "Wenn eine Bind-Variable negative Auswirkungen auf den Execution-Plan hat, benutz Sie nicht." ist leider nicht wirklich hilfreich. Bei komplexen SQL scheidet diese Option leider völlig aus.

Carsten Czarski hat gesagt…

Hallo "anonym",

das könnte (speziell vor Oracle11g) ein Fall des beschriebenen Bind Variable Peekings sein. Wichtig ist die Klärung folgender Fragen:

* Sind die Statistiken alle auf aktuellem Stand?
* Was ist der wesentliche Unterschied im Ausführungsplan?

Vielleicht hilft auch dieses Posting hier weiter - damit kann man (auch in Oracle10g) einen in der SGA gecachten Cursor gezielt entfernen ...

Grüße

-Carsten

Beliebte Postings