9. März 2008

Oracle11g und Bind Variablen: Intelligent Cursor Sharing

English title: Oracle11g and bind variables: intelligent cursor sharing)

So, nach der Pause von zwei Wochen geht es nun wieder weiter mit dem Thema Datenbank-Performance aus Sicht des Anwendungsentwicklers. In den letzten zwei Wochen hielten meine Kollegen Bernhard, Haitham, Rainer und ich den Workshop Geodaten LIVE erleben ab. Der war wirklich klasse - die Hands-On mit den Teilnehmen haben richtig Spaß gemacht - ich denke, ich muss doch mal was zum Thema Geodaten schreiben. Aber dies am Rande ...
Here is (after a two weeks' "outage") the next post about database performance from the developers' point of view. During the last two weeks my collegues Bernhard, Haitham, Rainer and I held a workshop Experience spatial data LIVE which was really great. The hands-on to get a map based on spatial data in the web were fun - so I think I'll post something about spatial data in the Oracle database here - but these are some thoughts beside - now we'll come back to the topic:
Nach meinen letzten Post zum Thema Bind Variable Peeking hat der Patrick Wolf mich freundlicherweise darauf hingewiesen, dass es in Oracle11g hier einige Neuerungen gibt. In der Tat: Das im Post beschriebene Verhalten ist so nur bis Oracle10g (einschließlich) gültig. Oracle11g hat das sog. Intelligent Cursor Sharing eingeführt und arbeitet ein wenig geschickter - Dazu nun mehr ...
After my last post about "Bind Variable Peeking I got a comment from Patrick Wolf who told me that there's something new in Oracle11g. And really: The behaviour described in my post is valid for Oracle9i and Oracle10g but not for Oracle11g. Now the database has intelligent cursor sharing and the optimizer deals in a more "smart" manner with bind variables. Instead of translating this whole post to english I'd like to refer to the blog of the Oracle optimizer group. The post Why are there more cursors in 11g for my query containing bind variables? contains the background information about intelligent cursor sharing.
From my point of view there is a clear trend towards the general usage of bind variables for every kind of SQL statement. The Oracle11g optimizer takes the first step to always choose a good execution plan even when bind variables are used and there is skew in the data. So the recommendation to avoid bind variables in those cases does not apply in Oracle11g. As the Q & A section in the blog post of the optimizer group states it is even planned to enable this together with CURSOR_SHARING=FORCE - with this setting the optimizer does not see literals any more.
So if an application is being developed to run on Oracle11g (or higher) developers should basically prefer bind variables. Let the optimizer (based on histograms) decide whether to use static or "dymamic" execution plans. (The database behaviour has to be tested for the particular queries of the developers' application - of course!). If the application is being developed for Oracle9i (development towards Oracle9i? hopefully not!) or Oracle10g you have still to think about using literals for columns with data skews.
Wir nehmen wieder den Setup vom letzten Mal mit der Tabelle CUSTOMERS. Übrigens kann man auch in SQL*Plus ganz einfach mit Bindevariablen arbeiten. Und zwar so:
SQL> var v varchar2(10);
SQL> exec :v := 'A';
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
     1091134
Der SQL-Kommentar dient dazu, die Abfrage später in den Views V$SQL bzw. V$SQL_PLAN wiederzufinden. Schauen wir uns die Abfrage anschließend mal in der View V$SQL an.
select /*sqlareaselect*/ 
  sql_id, is_bind_sensitive, is_bind_aware, child_number, loaded_versions, last_load_time 
from  v$sql 
where 
  lower(sql_text) like '%test0001%' and not lower(sql_text) like '%sqlareaselect%'
order by last_load_time desc

SQL_ID        I I CHILD_NUMBER LOADED_VERSIONS LAST_LOAD_TIME
------------- - - ------------ --------------- ------------------------------------
0bqb5a0dq4qt4 Y N            0               1 2008-03-09/09:25:05 
Wir sehen die SQL ID und die child number des Cursors. Interessant sind die Spalten IS_BIND_SENSITIVE und IS_BIND_AWARE. Während die erste angibt, ob dieser Cursor für das neue intelligende Cursor Sharing in Frage kommt (hier: Ja), besagt die zweite, ob es für diese Abfrage bereits passiert ist. Mit DBMS_XPLAN.DISPLAY_CURSOR können wir uns auch den tatsächlichen Ausführungsplan anschauen ...
select * 
from table(
  dbms_xplan.display_cursor(
    sql_id          => '0bqb5a0dq4qt4',
    cursor_child_no => 0
  )
) 

SQL_ID  0bqb5a0dq4qt4, child number 0
-------------------------------------
select /*test0001*/ sum(cust_id) from customers where status=:v

Plan hash value: 2427356980

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |            |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS  |    45 |   315 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_STATUS |    45 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - access("STATUS"=:V)
Gibt man bei cursor_child_no etwas anderes als 0 ein, so kommt nur die Meldung, dass es dieses Child nicht gibt - ist ja auch logisch. So weit so gut. Nun setzen wir die Bindevariable auf den Wert "C":
SQL> exec :v := 'C';
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
   119625495
Nun machen wir (wieder als SYS die gleichen Abfragen auf V$SQL und DBMS_XPLAN.DISPLAY_CURSOR nochmal - mit dem gleichen Ergebnissen wie vorhin. Also doch nix besonderes in Oracle11g? Schließlich hätte er für den Wert C einen full table scan machen sollen; das sind ja über 90% der Zeilen.
Aber nicht so voreilig. Setzen wir die gleiche SQL-Abfrage nochmals ab:
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
   119625495
Und nun noch einmal die Abfragen auf V$SQL und DBMS_XPLAN.DISPLAY_CURSOR ausführen ... und jetzt kommt's:
select /*sqlareaselect*/ 
  sql_id, is_bind_sensitive, is_bind_aware, child_number, loaded_versions, last_load_time 
from  v$sql 
where 
  lower(sql_text) like '%test0001%' and not lower(sql_text) like '%sqlareaselect%'
order by last_load_time desc

SQL_ID        I I CHILD_NUMBER LOADED_VERSIONS LAST_LOAD_TIME
------------- --- ------------ --------------- ------------------------------------
0bqb5a0dq4qt4 Y Y            1               1 2008-03-09/09:34:59
0bqb5a0dq4qt4 Y N            0               1 2008-03-09/09:25:05 
Für dieses SQL gibt es einen neuen Child Cursor - und dieser ist bind aware - es hat also kein "blindes" Cursor-Sharing wie in früheren Oracle-Versionen stattgefunden. Und wenn wir uns via DBMS_XPLAN mal den Ausführungsplan anschauen, dass kommt der Vorteil von Oracle11g heraus:
select * 
from table(
  dbms_xplan.display_cursor(
    sql_id          => '0bqb5a0dq4qt4',
    cursor_child_no => 1
  )
) 

SQL_ID  0bqb5a0dq4qt4, child number 1
-------------------------------------
select /*test0001*/ sum(cust_id) from customers where status=:i

Plan hash value: 296924608

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

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

   2 - filter("STATUS"=:I)
Die Datenbank hat erkannt, dass der vorhandene Ausführungsplan - wegen der ungleichen Datenverteilung in der Spalte STATUS - suboptimal ist. Also wurde für das SQL ein neuer Ausführungsplan erstellt (ein erneuter hard parse hat stattgefunden). Das Ergebnis ist, dass für dieses SQL im Library Cache nun zwei Ausführungspläne vorhanden sind - ausgewählt wird je nach Inhalt der Bindevariable. Auf diesem Weg können auch noch mehr Ausführungspläne für dieses SQL entstehen.
Zusammengefasst: Der Oracle11g-Optimizer kann wesentlich besser mit Bindevariablen umgehen als die Versionen davor. Die Frage ist nun, ob sich die im letzten Post gemachte Aussage, wann man Bindevariablen einsetzen sollte und wann nicht, ändert ...
Zunächst: Der Trend bzw. die Absicht der Entwickler des Optimizers ist klar erkennbar - Bind Variablen sollen die erste Wahl sein! Der Optimizer in Oracle11g macht den ersten Schritt dazu, auch ungleich verteilten Daten intelligent mit Bind Variablen umzugehen und den jeweils passenden Ausführungsplan zu wählen. Damit kann man Bindevariablen nun auch dort verwenden, wo bislang (siehe letzter Post) davon abzuraten war. Wie im Blog der Oracle Optimizer Gruppe unter Q & A nachzulesen ist, ist auch geplant, dieses Feature im Zusammenhang mit CURSOR_SHARING=FORCE bereitzustellen (in Oracle 11.1 allerdings noch nicht). Mit dieser Einstellung sieht der Optimizer überhaupt keine Literale mehr. Man kann also deutlich sehen: Die Entwickler des Optimizer wollen Bindevariablen im SQL sehen.
So möchte ich folgendes Fazit ziehen: Wenn Anwendungen für 11g und höher entwickelt werden, sollte man grundsätzlich eher mit Bindevariablen als mit Stringverkettung arbeiten - überlassen wir dem Optimizer die Entscheidung, ob wechselnde oder gleiche Ausführungspläne besser sind (Natürlich muss jeder das Verhalten der Datenbank für seine Queries testen - Fehler gibt es immer!). Wenn für 9i (hoffentlich nicht) oder für 10g entwickelt wird, muss der Entwickler ein wenig nachdenken: bei ungleichen Datenverteilungen ist Stringverkettung vorzuziehen.

Keine Kommentare:

Beliebte Postings