FUNKTIONSWEISE DES ADAPTIVEN CURSOR SHARINGS

04.
April
2019
Veröffentlicht von: Rupert Neiß

SQL-Statements in Applikationen, die BIND Variablen verwenden, weisen eine Menge Vorteile gegenüber den Applikationen auf, die lediglich Literale in ihren SQL-Statements verwenden. Zu diesen Vorteilen zählen u. a. ein reduziertes hard parsing und damit verbunden geringere Zugriffskonflikte auf Speicherbereiche im SHARED POOL als auch ein geringerer Speicherverbrauch insgesamt, da Cursoren gemeinsam benutzt werden können. In diesem Zusammenhang spielt auch das Feature Adaptive Cursor Sharing (ACS) seit seiner Einführung in der Version 11g R1 eine signifikante Rolle. Doch was verbirgt sich dahinter?
Dieser Monatstipp soll dem Leser einen Einblick in die Funktionsweise des Features ACS verschaffen.

ADAPTIVE CURSOR SHARING

Das Feature Adaptive Cursor Sharing (ACS) versetzt die Datenbank in die Lage, nicht immer ein und denselben Ausführungsplan für verschiedene Werte in den BIND Variablen zu benutzen. Insbesondere bei Datenschiefverteilungen kann der Einsatz von unterschiedlichen Ausführungsplänen sinnvoll sein und zu einer entsprechenden Performance-Steigerung führen.
ACS braucht keine separate Lizenz. Es ist standardmäßig ein Feature in der Personal Edition, Standard Editione One, Standard Edition, Standard Editiion 2 als auch in der Enterprise Edition (Öffnet externen Link in neuem FensterMOS Doc ID 1518681.1). Es braucht auch keine besondere Konfiguration, um ACS zu aktivieren. Jedoch gibt es für das Funktionieren des Features einige Voraussetzungen wie u. a. das sogenannte „bind peeking“. Ist dieses deaktiviert bzw. der versteckte Parameter _optim_peek_user_binds auf den Wert false gesetzt, kann kein ACS funktionieren. Nun würde sich die Modifikation dieses Parameters evtl. für Testzwecke anbieten, jedoch sei an dieser Stelle erwähnt, die sogenannten versteckten Parameter nur in Rücksprache mit dem Oracle Support zu setzen.

Wie aber funktioniert nun ACS?

FUNKTIONSWEISE

Zur Verdeutlichung der Funktionsweise des ACS wird die Tabelle rup.sales betrachtet, die eine signifikante Datenschiefverteilung in der Spalte COUNTRY_ID aufweist.

SQL> select country,country_id, count(*),round(ratio_to_report(count(*)) over(),8)*100 as percentage from rup.sales group by country_id, country order by country_id;

COUNTRY                             COUNTRY_ID   COUNT(*) PERCENTAGE
----------------------------------- ---------- ---------- ----------
England                                      1         96    .007494
Frankreich                                   2         96    .007494
Deutschland                                  3        800    .062452
USA                                          4    1280000   99.92256


Auf die Spalte COUNTRY_ID wird ein Index gelegt. Im Anschluss erfolgt eine Statistikgenerierung.

SQL> create index countryid_idx on rup.sales(country_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'RUP',tabname=>'SALES', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=> 'for columns country_id size skewonly',cascade=>TRUE);

PL/SQL procedure successfully completed.


Auf der Spalte COUNTRY_ID existiert nun ein Histogramm.

SQL> COL TABLE_NAME FORMAT a15
COL COLUMN_NAME FORMAT a20
COL HISTOGRAM FORMAT a9
SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM   DBA_TAB_COLS
WHERE  OWNER = 'RUP'
AND    TABLE_NAME = 'SALES';
TABLE_NAME      COLUMN_NAME          HISTOGRAM
--------------- -------------------- ---------
SALES           PROD_ID              NONE
SALES           CUST_ID              NONE
SALES           CHANNEL_ID           NONE
SALES           AMOUNT_SOLD          NONE
SALES           COUNTRY              NONE
SALES           COUNTRY_ID           FREQUENCY

6 rows selected.

SQL> alter system flush shared_pool;

System altered.


Im folgenden SQL Statement wird der BIND Variablen ein Wert mit einer äußerst kleinen Kardinalität übergeben. Wie erwartet entscheidet sich der Optimizer für einen Index Range Scan. Da das SQL-Statement zum ersten Mal aufgerufen wird, findet ein hard parsing statt. Aufgrund dessen, dass ein Histogramm auf einer Prädikatsspalte existiert, überwacht die Datenbank im Laufe der Zeit den Datenzugriff für verschiedene Werte in den BIND Variablen, um den für den jeweiligen Wert optimalsten Ausführungsplan bzw. Cursor zu finden. Die Überwachung wird eingeschaltet, indem die Datenbank beim ersten Aufruf des SQL Statements einen initialen Plan erzeugt und den Cursor als bind-sensitive kennzeichnet. Ein Blick in die View V$SQL verdeutlicht dies (IS_BIND_SENSITIVE = Y).

SQL> VARIABLE  land_id NUMBER
SQL> EXEC :land_id := 1

PL/SQL procedure successfully completed.

SQL> select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> set line 999
SQL> col plan_table_output for a130
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0uzb85mz10rzs, child number 0
-------------------------------------
select sum(amount_sold) from rup.sales where country_id = :land_id

Plan hash value: 2306986625

------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE                      |               |     1 |    16 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES         |    96 |  1536 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | COUNTRYID_IDX |    96 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   3 - access("COUNTRY_ID"=:LAND_ID)

20 rows selected.

SQL> set line 999
SQL> col BIND_SENS for a8
SQL> col BIND_AWARE for a8
SQL> col SHAREABLE for a8
SQL> SELECT SQL_ID, PLAN_HASH_VALUE, CHILD_NUMBER AS CHILD#,
     EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE
     AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS
     SHAREABLE FROM   V$SQL WHERE SQL_TEXT LIKE '%amount%'
     AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          1        111 Y        N        Y


Jetzt wird der BIND Variablen ein Wert mit einer extrem hohen Kardinalität übergeben. Dieser Wert steht für rund 99,92% aller Zeilen. Der Optimizer verwendet den Ausführungsplan aus dem ersten Cursor und setzt auch hier zunächst den Index Range Scan ein, obwohl ein Datenzugriff über einen Full Table Scan (FTS) wesentlich effizienter gewesen wäre, da die extrem hohe Anzahl der Zeilen in jedem Block der Tabelle zu finden sein dürfte. Dies liegt daran, dass der Child Cursor (Child# 0) als gemeinsam benutzbar gekennzeichnet ist (IS_SHAREABLE=Y). Es findet ein soft parsing statt und ein Blick in die View V$SQL zeigt, dass der Cursor ein zweites Mal verwendet wurde. Zu diesem Zeitpunkt ist der Cursor noch nicht als bind-aware gekennzeichnet. Gleichwohl vergleicht die Datenbank nach der Ausführung die Datenzugriffsmuster mit allen vorausgegangenen Ausführungen.

SQL> EXEC :land_id := 4

PL/SQL procedure successfully completed.

SQL> select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0uzb85mz10rzs, child number 0
-------------------------------------
select sum(amount_sold) from rup.sales where country_id = :land_id

Plan hash value: 2306986625

------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE                      |               |     1 |    16 |            |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES         |    96 |  1536 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | COUNTRYID_IDX |    96 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   3 - access("COUNTRY_ID"=:LAND_ID)

20 rows selected.

SQL>@curs_bind.sql

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        Y


Wir übergeben der BIND Variable wieder den Wert 4 und führen das Statement erneut aus. Was passiert? Der Optimizer generiert jetzt einen neuen Ausführungsplan und entscheidet sich für den FTS. Bei den ersten beiden Ausführungen beobachtete die Datenbank noch das SQL-Statement und stellte fest, dass es einen signifikanten Unterschied in der Kardinalität der beiden Abfragen gab. Aus diesem Sachverhalt lernte die Datenbank, dass derselbe Ausführungsplan nicht für jeden Wert in der BIND Variable optimal ist. In der View V$SQL taucht ein neuer Child Cursor (Child# 1) auf. Dieser Cursor ist jetzt bind-aware (IS_BIND_AWARE=Y). Er kann gemeinsam genutzt werden und kann verschiedene Pläne für verschiedene Werte in der BIND Variablen annehmen. Gleichzeitig wird der child cursor (child# 0) als nicht mehr gemeinsam benutzbar gekennzeichnet (IS_SHAREABLE=N). Er wird somit mit der Zeit aus dem Library Cache herausfallen.

EXEC :land_id := 4

SQL> select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0uzb85mz10rzs, child number 1
-------------------------------------
select sum(amount_sold) from rup.sales where country_id = :land_id

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |  1314 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    16 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SALES |  1280K|    19M|  1314   (2)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter("COUNTRY_ID"=:LAND_ID)

19 rows selected.

SQL> @curs_bind.sql

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        N
0uzb85mz10rzs      1047182207          1          1       4794 Y        Y        Y


Sobald ein Cursor als bind-ware gekennzeichnet wird, wird auch die View v$SQL_CS_SELECTIVITY gefüllt. Die Selektivität des Wertes „4“ liegt bei 0,9992. Der gültige Selektivitätsbereich für den Child Cursor (Child# 1) liegt zwischen 0,899303 und 1,099148 also ca. +/- 10% um die Selektivität des Wertes „4“ verteilt. Liegt in einer zukünftigen Ausführung die Selektivität einer BIND Variablen außerhalb dieses Wertebereiches, so wird ein neuer Child Cursor erzeugt.

SQL> select * from V$SQL_CS_SELECTIVITY;
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH           CON_ID
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- ----------
00000000BFDDFEF0 4262486008 0uzb85mz10rzs            1 =LAND_ID                                          0 0.899303   1.099148            0


Nun wird der BIND Variablen erneut ein Wert mit einer äußerst kleinen Kardinalität übergeben, die nur für 0,062% aller Zeilen steht. Da die Datenbank für diese Abfrage keinen passenden Child Cursor findet (Child# 0, SHARABLE=N) und die Selektivität für den Wert „3“ außerhalb des gültigen Selektivitätsbereiches von Child Cursor 1 liegt, erfolgt ein hard parse und die Datenbank erzeugt einen neuen Child Cursor (Child# 2). Wie erwartet entscheidet sich der Optimizer für einen Index Range Scan (PLAN_HASH_VALUE = 2306986625). Der gültige Selektivitätsbereich für den Child Cursor (Child# 2) liegt in einem Bereich zwischen 0.000562 und 0.000687, also in einem Wertebereich ca. zwischen 720 < 800 < 880.

SQL> EXEC :land_id := 3
SQL> select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> @curs_bind.sql

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        N
0uzb85mz10rzs      1047182207          1          1       4794 Y        Y        Y
0uzb85mz10rzs      2306986625          2          1         28 Y        Y        Y

SQL> select * from V$SQL_CS_SELECTIVITY;

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE         RANGE_ID LOW        HIGH           CON_ID
---------------- ---------- ------------- ------------ --------------- ---------- ---------- ---------- ----------
00000000AD9CB250 4262486008 0uzb85mz10rzs            2 =LAND_ID                 0 0.000562   0.000687            0
00000000AD9CB250 4262486008 0uzb85mz10rzs            1 =LAND_ID                 0 0.899303   1.099148            0


Wir spielen das Spiel weiter und üben erneut einen Datenzugriff mit der niedrigsten Kardinalität aus.
Was passiert? Erneut wird ein Indexzugriff vollzogen. Da die Selektivität für den Wert „2“ außerhalb des gültigen Selektivitätsbereiches von Child Cursor 2 liegt, erfolgt ein hard parse. Es wird ein neuer Child Cursor (Child# 3) angelegt und als bind aware deklariert. Der zuvor angelegte Child Cursor (Child# 2) wird als unbrauchbar gekennzeichnet.

SQL> EXEC :land_id := 2
SQL> select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> @curs_bind.sql

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        N
0uzb85mz10rzs      1047182207          1          1       4794 Y        Y        Y
0uzb85mz10rzs      2306986625          2          1         28 Y        Y        N
0uzb85mz10rzs      2306986625          3          1         26 Y        Y        Y

SQL> select * from V$SQL_CS_SELECTIVITY;

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE         RANGE_ID LOW        HIGH           CON_ID
---------------- ---------- ------------- ------------ --------------- ---------- ---------- ---------- ----------
00000000AD9CB250 4262486008 0uzb85mz10rzs            3 =LAND_ID                 0 0.000067   0.000687            0
00000000AD9CB250 4262486008 0uzb85mz10rzs            2 =LAND_ID                 0 0.000562   0.000687            0
00000000AD9CB250 4262486008 0uzb85mz10rzs            1 =LAND_ID                 0 0.899303   1.099148            0


Wird nun erneut der Wert „3“ an die BIND Variable übergeben, so kann der zuvor angelegte Cursor (Child# 3) verwendet (Executions = 2) werden, da die Selektivität für den Wert „3“ ebenso wie die Selektivität für den Wert „2“ innerhalb des gültigen Selektivitätsbereiches  (Low=0,000067 und High=0,000687) von Cursor (Child# 3) liegt (siehe V$SQL_CS_SELECTIVITY). Die beiden Selektivitätswerte bzw. –bereiche wurden quasi in einem einzigen Child Cursor zusammengeführt und ein hard parsing konnte somit vermieden werden.

EXEC :land_id := 3
select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL> @curs_bind.sql

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        N
0uzb85mz10rzs      1047182207          1          1       4794 Y        Y        Y
0uzb85mz10rzs      2306986625          2          1         28 Y        Y        N
0uzb85mz10rzs      2306986625          3          2         54 Y        Y        Y


Die beiden in der View V$SQL als gemeinsam benutzbar gekennzeichneten Child Cursor (Child# 1 und Child# 3) stehen für zukünftige Ausführungen des SQL-Statements zur Verfügung, damit für verschiedene Werte in den BIND Variablen unterschiedliche Ausführungspläne benutzt werden können. So kommt für die Werte mit geringer Kardinalität (1, 2 und 3) der Indexzugriff (PLAN_HASH_VALUE=2306986625) und für den Wert „4“ mit hoher Kardinalität (PLAN_HASH_VALUE=1047182207) der FTS zum Einsatz. Übergeben wir zum Abschluss jeweils noch einmal die unterschiedlichen Werte an die BIND Variable und führen das SQL-Statement aus. Somit erhöht sich in der Spalte Executions der View V$SQL entsprechend die Anzahl im Cursor Child# 1 um eins auf zwei und im Cursor Child# 3 von zwei auf fünf.

EXEC :land_id := 1
select sum(amount_sold) from rup.sales where country_id = :land_id;

EXEC :land_id := 2
select sum(amount_sold) from rup.sales where country_id = :land_id;

EXEC :land_id := 3
select sum(amount_sold) from rup.sales where country_id = :land_id;

EXEC :land_id := 4
select sum(amount_sold) from rup.sales where country_id = :land_id;

SQL_ID        PLAN_HASH_VALUE     CHILD#       EXEC  BUFF_GETS BIND_SEN BIND_AWA SHAREABL
------------- --------------- ---------- ---------- ---------- -------- -------- --------
0uzb85mz10rzs      2306986625          0          2       7377 Y        N        N
0uzb85mz10rzs      1047182207          1          2       9582 Y        Y        Y
0uzb85mz10rzs      2306986625          2          1         28 Y        Y        N
0uzb85mz10rzs      2306986625          3          5        135 Y        Y        Y


FAZIT

Oracle bietet mit dem Feature ACS eine sehr einfache Möglichkeit zur Steigerung der Performance. Voraussetzung dafür ist jedoch eine sorgfältige Planung der zu entwickelnden Applikation unter dem gezielten Einsatz von BIND Variablen als auch der regelmäßigen Generierung von Histogrammen.
Letztendlich ist aber auch ein tieferes Verständnis über die Funktionsweise des ACS selbst unerlässlich, um dieses Instrument gezielt – und evtl. auch in Zusammenwirken mit anderen Performance-Features – einzusetzen. Fragen Sie einen unserer Öffnet internen Link im aktuellen Fenstererfahrenen Oracle Consultants, wenn Sie noch mehr Informationen zu diesem oder anderen Performance-Themen benötigen.

Cursor DBA Performance Tuning

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.