Recentemente ho parlato di ACS (Adaptive Cursor Sharing)  all’Oracle Tech Day organizzato da ITOUG (Italian Oracle User Group) di cui sono membro ed il cui stream database è stato sponsorizzato da ICTeam, le slide possono essere scaricate al seguente link: http://www.itoug.it/techday-2017/
 
Cos’è e da cosa è controllato

ACS è una feature Oracle che nasce con l’ambizione di rendere un piano flessibile in presenza di SQL statement che utilizzano bind variable, in sostanza quindi è in grado di rendere “Adaptive” il piano di esecuzione di un cursore scegliendo tra differenti piani ottimali . Uno dei requisiti fondamentali di ACS è l’esistenza di un istogramma calcolato sulla colonna che contiene il bind value. Tutti i requisiti e check per poter utilizzare ACS sono comunque elencati nella nota MOS Doc ID 740052.1
La caratteristica principale di ACS è quella di riconoscere se un cursore è bind sensitive (valore ‘Y’ nella colonna IS_BIND_SENSITIVE in V$SQL o V$SQLAREA). Un cursore viene etichettato “bind sensitive” se l’optimizer Oracle ritiene che il piano ottimale possa dipendere da differenti valori passati alla bind variable.
L’utilizzo di ACS è possibile se ECS (Extended Cursor Sharing) è abilitato (lo è per default). Lo scopo di ECS è quello di etichettare un cursore come bind aware se dopo la prima esecuzione del cursore viene rilevata una differenza significativa della cardinality di un row source.
 
I parametri nascosti che regolano ACS sono:
_optimizer_adaptive_cursor_sharing  (TRUE/FALSE)                    Adaptive Cursor Sharing
_optimizer_extended_cursor_sharing (NONE/UDO)                      Extended Cursor Sharing in presenza di User Defined Operator
_optimizer_extended_cursor_sharing_rel (NONE/SIMPLE)          Extended Cursor Sharing
In blu sono stati evidenziati I valori di default.
 
Comportamento in presenza di istogrammi

L’attribuzione di bind sensitive fa si che venga attivato un monitoring sul cursore al variare del valore passato alla bind variable per stabilire se sia necessario un cambio del piano di esecuzione.
Il monitoraggio può essere osservato sui dati presenti in V$SQL_CS_SELECTIVITY e V$SQL_CS_HISTOGRAM. Di seguito un esempio chiarificatore:

SQL> exec :b1 := 1000;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)
-----------------
                1
 
Plan hash value: 2767038796
------------------------------------------------------------------
| Id  | Operation                              | Name            |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |
|   1 |  SORT AGGREGATE                        |                 |
|   2 |   VIEW                                 | VW_DAG_0        |
|   3 |    HASH GROUP BY                       |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |
------------------------------------------------------------------
 
SQL> select child_number, executions, buffer_gets,is_bind_sensitive BS, is_bind_aware BA, plan_hash_value from v$sql where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          1          40  Y  N      2767038796

SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6avmk9kmqnx0t';

Nessuna riga selezionata

Quindi con i valori imposti alle bind variable alla prima esecuzione dello statement SQL viene restituita una sola riga ed utilizzato un accesso Index range scan. Il test prosegue modificando i valori alle bind variable e rieseguendo la query SQL:
 
SQL> exec :b1 := 1;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)
-----------------
           102400
 
Plan hash value: 2767038796
------------------------------------------------------------------
| Id  | Operation                              | Name            |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |
|   1 |  SORT AGGREGATE                        |                 |
|   2 |   VIEW                                 | VW_DAG_0        |
|   3 |    HASH GROUP BY                       |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |
------------------------------------------------------------------
 
SQL> select child_number, executions, buffer_gets,is_bind_sensitive BS, is_bind_aware BA,plan_hash_value from v$sql where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2       51422  Y  N      2767038796
 
SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6avmk9kmqnx0t';
 
Nessuna riga selezionata

Alla seconda esecuzione, cambiando i valori alle bind variable il numero di righe restituite subisce un sostanziale incremento (e di conseguenza il numero di blocchi, colonna buffer_gets) ma il piano di esecuzione resta comunque invariato. Si fa anche notare che nessuna informazione è stata ancora memorizzata in V$SQL_SELECTIVITY. Mantenendo gli stessi valori alle bind variable si procede con una nuova esecuzione dello statement SQL:
 
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)
-----------------
           102400
 
Plan hash value: 1141924756
-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  SORT AGGREGATE      |          |
|   2 |   VIEW               | VW_DAG_0 |
|   3 |    HASH GROUP BY     |          |
|   4 |     TABLE ACCESS FULL| FEEDBACK |
-----------------------------------------
 
SQL> select child_number, executions, buffer_gets,is_bind_sensitive BS, is_bind_aware BA,plan_hash_value from v$sql where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2       51422  Y  N      2767038796
           1          1       68287  Y  Y      1141924756
 
SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           1 =B1                                               0 0.446524   0.545751
           1 >=B2                                              0 0.900000   1.100000

Alla terza esecuzione (seconda dopo il cambio di valori delle bind variable) la differenza sul numero di righe/blocchi letti determina l’intervento di ACS e quindi il cambio del piano di esecuzione con accesso che passa da Index Range Scan a Table Access Full. Il nuovo child_number 1 che è stato determinato viene etichettato come bind sensitive e bind aware e le caratteristiche del cursore vengono memorizzate in V$SQL_CS_SELECTIVITY. Di seguito vengono invece ripristinati i valori delle bind a quelli della prima esecuzione:
 
SQL> exec :b1 := 1000;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)
-----------------
                1
 
Plan hash value: 2767038796
------------------------------------------------------------------
| Id  | Operation                              | Name            |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |
|   1 |  SORT AGGREGATE                        |                 |
|   2 |   VIEW                                 | VW_DAG_0        |
|   3 |    HASH GROUP BY                       |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |
------------------------------------------------------------------
 
SQL> select child_number, executions, buffer_gets,is_bind_sensitive BS, is_bind_aware BA,plan_hash_value from v$sql where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2       51422  Y  N      2767038796
           1          1       68287  Y  Y      1141924756
           2          1           3  Y  Y      2767038796
 
SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6avmk9kmqnx0t';
 
CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 =B1                                               0 0.000004   0.000005
           2 >=B2                                              0 0.900000   1.100000
           1 =B1                                               0 0.446524   0.545751
           1 >=B2                                              0 0.900000   1.100000
Il piano ritorna quello del primo accesso Index Range Scan e viene creato un nuovo child cursor (2) questa volta  bind sensitive e bind aware e le caratteristiche di questo cursore memorizzate in V$SQL_CS_SELECTIVITY (child_number 2).

È importante comprendere l'importanza delle informazioni presenti nelle V$SQL_CS_SELECTIVITY, per ogni child cursor le colonne LOW e HIGH contengono il range di valori relativi alla selectivity della bind variable.Ad ogni esecuzione viene ricercata la selectivity del predicato corrente, se compresa in un range presente nella vista il piano di esecuzione relativo a quel child number viene condiviso e quindi utilizzato dal cursore in esecuzione altrimenti viene generato un nuovo piano ed inserito nella vista un nuovo child number con un nuovo selectivity range (LOW-HIGH) utile per le esecuzioni successive.

Comportamento in assenza di istogrammi

Come già detto l’utilizzo di ACS è subordinato all’esistenza di un istogramma sulla colonna che è oggetto di confronto con il bind value.

Quindi in assenza di istogrammi il piano di esecuzione di un cursore che utilizza bind variable non potrà mai essere “Adaptive”, il piano calcolato alla prima esecuzione resta invariato anche nelle successive esecuzioni. Ciò è vero nelle versioni 11g e 12cR2 ma una differenza è stata riscontrata utilizzando la versione 12cR1. In quest’ultimo caso infatti è stato notato un comportamento differente utilizzando gli hidden parameter in configurazione di default (nel caso di seguito riportato _optimizer_extended_cursor_sharing non influenza il risultato in quanto nella query non vengono utilizzati User Defined Operator – UDO):

_optimizer_adaptive_cursor_sharing  = TRUE
_optimizer_extended_cursor_sharing_rel = SIMPLE

Il seguente esempio chiarisce il concetto:
prima esecuzione: restituita 1 riga, l’accesso ritenuto ottimale è Index Range Scan

SQL> exec :b1 := 1000;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)           
-----------------           
                1      
 
Plan hash value: 2767038796 
----------------------------------------------------------------------------------------
| Id  | Operation                              | Name            |   Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |        |     3 (100)|
|   1 |  SORT AGGREGATE                        |                 |      1 |            |
|   2 |   VIEW                                 | VW_DAG_0        |      2 |     3  (34)|
|   3 |    HASH GROUP BY                       |                 |      2 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |      2 |     2   (0)|
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |      2 |     1   (0)|
----------------------------------------------------------------------------------------
Seconda esecuzione: il cambio dei valori delle bind variable determina una significativa variazione sul numero di righe restituite ma l’optimizer comunque ritiene che l’accesso ottimale è Index Range Scan

SQL> exec :b1 := 1;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)  
----------------- 
           102400
 
----------------------------------------------------------------------------------------
| Id  | Operation                              | Name            |   Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |        |     3 (100)|
|   1 |  SORT AGGREGATE                        |                 |      1 |            |
|   2 |   VIEW                                 | VW_DAG_0        |      2 |     3  (34)|
|   3 |    HASH GROUP BY                       |                 |      2 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |      2 |     2   (0)|
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |      2 |     1   (0)|
----------------------------------------------------------------------------------------
Terza esecuzione: viene mantenuta la combinazione dei valori delle bind variable, l’optimizer non si accorge ancora della variazione e l’accesso resta inalterato, Index Range Scan

SQL> exec :b1 := 1;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)     
-----------------   
           102400       
                             
Plan hash value: 2767038796        
----------------------------------------------------------------------------------------
| Id  | Operation                              | Name            |   Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |        |     3 (100)|
|   1 |  SORT AGGREGATE                        |                 |      1 |            |
|   2 |   VIEW                                 | VW_DAG_0        |      2 |     3  (34)|
|   3 |    HASH GROUP BY                       |                 |      2 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| FEEDBACK        |      2 |     2   (0)|
|   5 |      INDEX RANGE SCAN                  | FEEDBACK_N1_IDX |      2 |     1   (0)|
---------------------------------------------------------------------------------------

Quarta esecuzione: pur mantenendo la combinazione dei valori di bind variable l’optimizer questa volta decide di cambiare metodo di accesso, Table Access Full:

SQL> exec :b1 := 1;
SQL> exec :b2 := 1;
SQL> select count(distinct n2) from feedback where n1 = :b1 and n2 >= :b2;
 
COUNT(DISTINCTN2)     
-----------------     
           102400     
                              
Plan hash value: 1141924756                                      
---------------------------------------------------------------
| Id  | Operation            | Name     |   Rows | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |        |  7173 (100)|
|   1 |  SORT AGGREGATE      |          |      1 |            |
|   2 |   VIEW               | VW_DAG_0 |    102K|  7173   (1)|
|   3 |    HASH GROUP BY     |          |    102K|  7173   (1)|
|   4 |     TABLE ACCESS FULL| FEEDBACK |    102K|  7021   (1)|
---------------------------------------------------------------
                                                                 
Note       
-----      
   - statistics feedback used for this statement
 
Quindi dopo il cambio di valori delle bind variable, che provoca una significativa differenza della cardinality, occorrono tre esecuzioni prima che l’optimizer Oracle si accorga della differenza e cambi metodo di accesso (Full Table Scan). In realtà il cambio di accesso viene determinato dall’intervento dello Statistics Feedback come si evince dalla sezione Note del piano di esecuzione e non dall’intervento di ACS che non può essere utilizzato in quanto non sono presenti istogrammi sulle colonne referenziate nella where clause. Questo comportamento come già detto è stato notato in 12cR1. Nelle versioni 11g e 12cR2 invece il piano resta quello calcolato alla prima esecuzione e non cambia più. Sembrerebbe quindi che nella versione 12cR2 il comportamento di statistics feedback sia ritornato ad essere quello utilizzato in 11g.
 
Si tenga presente che dalla versione 12cR2 optimizer_adaptive_statistics che controlla statistics feedback risulta disabilitato per default ma tuttavia per accessi su singola tabella, come l’esempio appena discusso, è sempre abilitato, ergo non è quindi il cambio di valore di default in 12cR2 che determina il comportamento dello statistics feedback appena descritto.
Nota: in 12cR2 adaptive_features è stato sostituito da due nuovi parametri: optimizer_adaptive_plans (TRUE per default) e optimizer_adaptive_statistics (FALSE per default).
 
Alcune note su quanto osservato:

• Il piano diventa adaptive quando ECS è acceso (_optimizer_extended_cursor_sharing_rel = TRUE), se ECS è spento e ACS è acceso il piano non sarà mai adaptive, quello individuato alla prima esecuzione resterà invariato anche per le successive;
• Se ACS è spento e sono stati calcolati istogrammi sulle colonne referenziate nella WHERE clause il piano sarà comunque Adaptive in virtù della presenza degli istogrammi;
• Con ACS ed ECS contemporaneamente attivi (configurazione di default) ci vorranno alcune esecuzioni per accorgersi che è necessario un nuovo piano;
• Se viene utilizzato l’hint /*+ BIND_AWARE */  la fase di monitoraggio non viene eseguita;
• L’utilizzo dell’hint /*+ NO_BIND_AWARE */  spegne ACS