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 (MOS 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?
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
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 erfahrenen Oracle Consultants, wenn Sie noch mehr Informationen zu diesem oder anderen Performance-Themen benötigen.
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.