Adaptive Cursor Sharing II

Siguiendo con el post anterior he probado con otra distribucion de datos y pasando las variables de STATE del 0 al 10:

     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  

Planes generados   

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

Y en vez de 6 planes ahora genera 7 asi que parece que la sospecha es correcta. El CBO hace bind peeking hasta que encuentre un punto estable, que puede ser despues de varias consultas y ve que la selectividad es my similar y para ahi.

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.

Invalidar/echar a los cursores en Oracle 11g

Alguno preguntara que sentido tiene invalidar un cursor del shared pool pues, si tienes el bind peeking habilitado tiene mucho sentido! Porque invalidando un cursor invalidas el plan de ejecucion y obligas al optimizador reparsear y generar un plan de ejecucion nuevo. Es muy util para la situacion de la tabla de factura que he mencionado en el otro post Mas alla de Bind Peeking

Las versiones anteriores de 11g la unica manera que yo sepa para invalidar los cursores es lanzando cualquier tipo de DDL (no destructivo claro) sobre una de las tablas del cursor que quieres invalidar. Por ejemplo para invalidar una query como esta:

select /* PURGE */
ename, dname
from emp, dept
where emp.deptno = dept.deptno
and emp.deptno = :deptno;

Una manera de invalidar seria alter table emp logging. Cual es el problema con este metodo? Pues que invalida todos los cursores que referencia a la tabla emp, en un entorno con bastante concurrencia esto significa un pico de Parsing y posibles contenciones.

Hoy por casualidad he estado mirando el package DBMS_SHARED_POOL de 11g para ver si ya se puede usar sql_id en vez de hash value para ciertas operaciones y veo que existe tiene un procedimiento nuevo, PURGE y te permite “echar” el cursor del shared pool. Viendolo me vino a la cabeza lo que podria hacer con eso!

Ejemplo (abrimos dos sesiones uno lanzando la query y la otra monitorizando v$sqlarea con sys/system):

----------
PASO 1
----------
sesion 1 (lanzamos dos sentencias referenciando a la tabla emp, una con JOIN y la otra sin):

var deptno number
exec :deptno := 30

select  /* PURGE */
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno;

select  /* PURGE */
   empno, ename
from emp
where emp.deptno = :deptno;

sesion 2:

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC';

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             0 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          1             1             0 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

2 rows selected.

----------
PASO 2
----------
A la antigua usanza e invalidamos el cursor:

sesion 1:
alter table emp logging;

sesion 2:
select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC';

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             1 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          1             1             1 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

2 rows selected.

en el campo V$SQLAREA.INVALIDATIONS ha incrementado de 0 a 1, acabamos de invalidar los cursores, si volvemos a ejecutar la query veremos como incrementa el VERSION_COUNT de 1 a 2 e INVALIDATIONS se resetea a cero. Como se puede comprobar se han invalidado los dos cursores por el DDL.

----------
PASO 3
----------
Con el metodo nuevo DBMS_SHARED_POOL invalidamos la Query con la JOIN:

sesion 2:

alter system flush shared_pool;

sesion 1:

select  /* PURGE */
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno;

select  /* PURGE */
   empno, ename
from emp
where emp.deptno = :deptno;

sesion 2:

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC'

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             0 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          3             2             2 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

Sesion 2:
exec dbms_shared_pool.purge(name => '2DD77FA0, 2983748266', flag => 'C')

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC'

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               2          3             2             2 4001409383 2DD756EC      2204715243           2
   empno, ename
from emp
where emp.deptno = :deptno

1 row selected.

Vemos que el cursor de la JOIN ha desaparecido del shared pool. Semanticamente hablando la verdad es que lo que hace el package es echar y no invalidar…. pero bueno la idea es obtener un plan de ejecucion nuevo.

Como puedo saber si el parche soporta Rolling Upgrade?

Los parches de bases de datos se pueden aplicar de varias maneras en RAC:

1. Todo a la vez (parada total)

2. Algunos nodos primero y otros despues

3. De nodo en nodo (Rolling Upgrade)

Para mantener la disponibilidad lo ideal es aplicar de nodo en nodo pero no todos los parches (hablamos de one-off patch no patchsets) soporta Rolling Upgrade, para saber si el parche soporta Rolling Upgrade usaremos opatch

1. Descomprimir el parche

2. Nos colocamos en el directorio descomprimido

3. opatch query -all | grep “rolling patch”

La linea tendria este formato,

Patch is a rolling patch: true

Mas alla de Bind Peeking

Estoy seguro que muchos de nosotros hemos tenido algun problema de mayor o menor escala con Bind Peeking introducido en Oracle 9i, especialmente los que migramos hace años de 8i a 9i.

Lo que hace bind peeking es si se utiliza las variables bind el primer parsing de un cursor (sentencia SQL) la variable se sustituye por el valor real y con ese valor se genera el plan de ejecucion, esto esta bien si todas las consultas usan ese valor que se ha generado el plan. Por ejemplo cuando consultas contra una tabla (con 500000 resgirtos) de facturas y quieres consultar las pendientes (estado), que podria representar solamente el 1% de la tabla, lo ideal es que el optimizador utilice un indice sobre el campo de estado, imaginense el primer parsing de la query es una consulta que en vez de consultar por el estado pendiente va por el estado cobrado, el plan de ejecucion seria un Full Table Scan sin embargo el resto de la gente que van a consultar por el estado pendiente seran las victimas de ese plan, una consulta que tarda millisegundos ppdria llegar a tardar hasta 30 segundos. (siempre partiendo con la idea de que la tabla tiene histogramas sobre el campo estado) Lo que es peor es que la consulta depende de los dias funciona bien ó mal y genera inestabilidades.

El bind peeking introduce de alguna manera alternativas e inteligencia que no existia en las versiones anteriores sin embargo se ha comprobado en numerosos casos que son los causantes de los problemas de rendimiento. De hecho aplicaciones como SAP, Siebel recomiendan deshabilitar bind peeking. En Oracle E-Business Suite me ha dado algun problemillas tambien.

Se ha visto que un plan de ejecucion no es optimo para todo los valores posibles de bind por lo tanto es necesario algun tipo de mecanismo mas inteligente que sea capaz de generar un plan de ejecucion por cada tipo de bind. En Oracle 11g se ha introducido el concepto de cursores inteligentes (Adapative Cursor Sharing) y esto ayudara a generar los posibles planes de ejecucion para los diferentes valores de bind. Con esto supongo que se pretende eliminar los problemas que hemos sufrido por el bind peeking. Pero habria que estudiarlo muy bien porque parece que necesita varios parsing de un cursor para llegar a un punto estable y tener todos los planes de ejecucion, y parece ser que es necesario Soft Parsing para que esto funcione (que pasaria con Packages de PL/SQL?).

En cuando pueda pondre un ejemplo demostrando la funcionalidad.