Adaptive Cursor Sharing I (ACS)

Esta semana que he estado probando lo de Adaptive Cursor Sharing mencionado en Mas alla de Bind Peeking. Hace cosas raras, hay que estudiarlo mas a fondo pero funciona, ahora lo veremos.

Generamos datos para tener una distribucion de datos no-uniforme:

 
SQL> @create 
SQL> drop table t1;   

Table dropped.   

SQL> 
SQL> create table t1 
  2  ( 
  3          id      number, 
  4          text1   varchar2(20), 
  5          text2   varchar2(20), 
  6          state   number 
  7  );   

Table created.   

SQL> 
SQL> insert into t1 
  2     with tdata as 
  3          (select rownum id 
  4             from all_objects 
  5            where rownum <= 1000) 
  6     select rownum, 
  7            dbms_random.string ('u', 20), 
  8            dbms_random.string ('u', 20), 
  9            case 
 10                   when rownum/100000  create index t1_i1 
  2  on t1(state);   

Index created.   

SQL> 
SQL> exec dbms_stats.gather_table_stats('LSC', 'T1', ESTIMATE_PERCENT => 20, - 
>                                    METHOD_OPT => 'FOR COLUMNS STATE SIZE 10', CASCADE => TRUE); -- PASAR HISTOGRAMS PARA QUE PODAMOS USAR ESTA FUNCIONALIDAD   

PL/SQL procedure successfully completed.   

SQL> 
SQL> select state, count(*) cnt_per_state, (count(*)*100)/nr state_percent 
  2  from t1, (select max(id) nr 
  3              from t1) 
  4  group by state, nr;   

     STATE CNT_PER_STATE STATE_PERCENT 
---------- ------------- ------------- 
         2            10           .01 
         0            10           .01 
         3            10           .01 
         8            10           .01 
         1            10           .01 
        10         99900          99.9 
         5            10           .01 
         4            10           .01 
         7            10           .01 
         9            10           .01 
         6            10           .01   

11 rows selected.  

Hemos generado unos datos que teoricamente utilizaremos indice para esos STATE que no sean el 10. Pues bien a principio esto era muy confuso porque no importaba el valor el caso es que nunca dejaba de usar el indice, incluso para el STATE 10 (deberia hacer un FTS). La prueba era usando esta query y pasando los valores del 0 a 10 a la bind variable :STATE, hasta llegar las 11 ejecuciones.

 
set autotrace trace exp stat 
var state number   

exec :state := 0 
select  /* ACS-LSCC */ 
        id, state, count(*) 
from t1 
where state = :state 
group by id, state;   

pause Press enter to continue   

exec :state := 1 
select  /* ACS-LSCC */ 
        id, state, count(*) 
from t1 
where state = :state 
group by id, state;   

pause Press enter to continue   

exec :state := 2 
select  /* ACS-LSCC */ 
        id, state, count(*) 
from t1 
where state = :state 
group by id, state;   

pause Press enter to continue 
etc etc ....................... hasta 10   

y durante cada ejecucion en otra sesion monitorizando v$sqlstats con esta query   

select /* ACS-QRY */ 
sql_fulltext, sql_id, parse_calls, executions, version_count, invalidations 
from v$sqlstats 
where sql_fulltext like '%ACS-LSC%' 
  and sql_fulltext not like '%ACS-QRY%'   

El caso es que el VERSION_COUNT siempre se quedaba en uno, es decir que no generaba mas que un plan de ejecucion, y me pregunto pero esto funciona? Hasta que le di la vuelta a las variables, en vez de pasar del 0 a 10 del 10 a 0, y magicamente a partir del valor 8 (el 9 ni se inmutaba) empieza a generar otro plan, el 7 generaba otro, el 6 otro pero el 5, 4 no han generado ningunplan nuevo hasta el STATE 3. Curioso para 11 valores donde 10 son iguales me ha generado 6 planes de ejecucion.

 
Estado final usando los valores del 10 al 0   

SQL_FULLTEXT                                                     SQL_ID        PARSE_CALLS EXECUTIONS VERSION_COUNT INVALIDATIONS 
---------------------------------------------------------------- ------------- ----------- ---------- ------------- ------------- 
select  /* ACS-LSCC */                                           1pq13qpqfza6x          11         11             6             0 
        id, state, count(*) 
from t1 
where state = :state  

Y los 6 planes de ejecucion donde solo voy a demostrar 2 porque el resto son iguales pero con diferente coste en Ilas operaciones de NDEX RANGE SCAN y TABLE ACCESS BY INDEX ROWID:

 
SQL> @XPLAN 
SQL> select * 
  2  from table(dbms_xplan.display_cursor('1pq13qpqfza6x', 0));   

PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------------------ 
SQL_ID  1pq13qpqfza6x, child number 0 
------------------------------------- 
select  /* ACS-LSCC */         id, state, count(*) from t1 where state 
= :state group by id, state   

Plan hash value: 136660032   

--------------------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |      |       |       |   245 (100)|          | 
|   1 |  HASH GROUP BY     |      |     1 |    16 |   245   (3)| 00:00:03 | 
|*  2 |   TABLE ACCESS FULL| T1   | 99998 |  1562K|   240   (1)| 00:00:03 | 
---------------------------------------------------------------------------   

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

   2 - filter("STATE"=:STATE)   

SQL> select * 
  2  from table(dbms_xplan.display_cursor('1pq13qpqfza6x', 1));   

PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------------------ 
SQL_ID  1pq13qpqfza6x, child number 1 
------------------------------------- 
select  /* ACS-LSCC */         id, state, count(*) from t1 where state 
= :state group by id, state   

Plan hash value: 2629508439   

-------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          | 
|   1 |  HASH GROUP BY               |       |     1 |    16 |     3  (34)| 00:00:01 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     5 |    80 |     2   (0)| 00:00:01 | 
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     5 |       |     1   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------   

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

   3 - access("STATE"=:STATE) 

La verdad es que no entiendo muy bien lo de 6 planes, pero pensando bien puede ser asi es ACS, se adapta a los diferentes valores y logicamente no va a parar con dos planes porque podria tener perfectamente 2,3 o 4 valores que necesiten FTS y no ir por el indice. Por ejemplo si tuviesemos esta distribucion de datos es logico hacer bind peeking cada dos por tres.

      STATE CNT_PER_STATE STATE_PERCENT
---------- ------------- -------------
         0         10000            10
         1            10           .01
         2         15000            15
         3            10           .01
         4            10           .01
         5         22000            22
         6            10           .01
         7            10           .01
         8            10           .01
         9         15000            15
        10         37940         37.94 

El caso es que esto parece un arma de doble filo ya que dependiendo de los valores que hay en el campo podriamos terminar con muchos cursores hijos. Habra que subir el shared pool cuando upgradeamos a 11g ó aumentar MEMORY_TARGET, MEMORY_MAX_TARGET si vamos a usar el Automatic Memory Management (AMM).
De todos modos si no generamos ningun tipo de histogramas esto no supondria ningun problema.

Y como dije en otro post que pasaria con PL/SQL? Usamos el siguien Procedure para ver lo que pasa.

 
create or replace procedure LSC$ASC 
as 
        l_id    number; 
        l_state number; 
        l_cnt   number; 
begin 
        for i in reverse 1..10 
        loop 
                select /* ACS-LSC */ 
                       id, state, count(*) 
                into   l_id, l_state, l_cnt 
                from   t1 
                where state = i 
                  and rownum < 2 
                group by id, state; 
        end loop; 
end; 
/   

resultado   

SQL_FULLTEXT                                                                     SQL_ID        PARSE_CALLS EXECUTIONS VERSION_COUNT INVALIDATIONS 
-------------------------------------------------------------------------------- ------------- ----------- ---------- ------------- ------------- 
SELECT ID, STATE, COUNT(*) FROM T1 WHERE STATE = :B1 AND ROWNUM < 2 GROUP BY ID, 52r38wma52qbp           1         10             1             0   

Como sospechaba, como PL/SQL no parsea mas que una vez (por eso nos gusta PL/SQL) solamente genera un plan de ejecucion.

Finalmente lo de que no genera planes diferentes si pasamos del 1 a 10 sospecho que es porque despues de varios intentos el CBO ve que el plan sigue siendo el mismo y asume que no es necesario generar planes diferentes. Lo tendre que probarlo.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s