miércoles, 28 de enero de 2009

Probando SQL Result Cache

A partir de Oracle 11g se agregaron varias funcionalidades interesantes. Una de ellas es lo que se denomina SQL Result Cache. Con este nuevo feature se pueden acelerar sentencias que utilizan tablas con tasa de cambio nula o muy baja, por ejemplo tablas de referencia. La idea de este mecanismo es guardar el resultado de la ejecución de la sentencia para que las subsiguientes ejecuciones lo reutilicen.

Voy a realizar un test con una tabla de esquema de ejemplo HR. Si bien la tabla del ejemplo es muy pequeña y no se va a notar la diferencia en los tiempos, sirve para mostrar el caching de resultados y el cambio de planes.


rop@ROP111> ed
Escrito file afiedt.buf

1  select department_id,
2   count(1) cnt,
3   avg(salary) avg_sal,
4   max(hire_date) max_hire
5   from hr.employees
6*  group by department_id
rop@ROP111> /

DEPARTMENT_ID        CNT    AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
        100          6       8600 07-DIC-99
         30          6       4150 10-AGO-99
                     1       7000 24-MAY-99
         20          2       9500 17-AGO-97
         70          1      10000 07-JUN-94
         90          3 19333.3333 13-ENE-93
        110          2      10150 07-JUN-94
         50         45 3475.55556 08-MAR-00
         40          1       6500 07-JUN-94
         80         34 8955.88235 21-ABR-00
         10          1       4400 17-SEP-87
         60          5       5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
Id   Operation           Name       Rows   Bytes  Cost (%CPU) Time
--------------------------------------------------------------------------------
 0  SELECT STATEMENT                  11    165      4  (25) 00:00:01
 1   HASH GROUP BY                    11    165      4  (25) 00:00:01
 2    TABLE ACCESS FULL EMPLOYEES    107   1605      3   (0) 00:00:01
--------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
        1  recursive calls
        0  db block gets
        7  consistent gets
        6  physical reads
        0  redo size
      946  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       12  rows processed


Vemos que el plan realiza un full scan sobre la tabla y requiere de 7 lecturas logicas y 6 fisicas.

Ahora voy a editar la sentencia y le agrego mediante un hint la directiva para que utilice result cache.


rop@ROP111> ed
Escrito file afiedt.buf

1  select /*+ result_cache */ department_id,
2   count(1) cnt,
3   avg(salary) avg_sal,
4   max(hire_date) max_hire
5   from hr.employees
6*  group by department_id
rop@ROP111> /

DEPARTMENT_ID        CNT    AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
        100          6       8600 07-DIC-99
         30          6       4150 10-AGO-99
                     1       7000 24-MAY-99
         20          2       9500 17-AGO-97
         70          1      10000 07-JUN-94
         90          3 19333.3333 13-ENE-93
        110          2      10150 07-JUN-94
         50         45 3475.55556 08-MAR-00
         40          1       6500 07-JUN-94
         80         34 8955.88235 21-ABR-00
         10          1       4400 17-SEP-87
         60          5       5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
Id   Operation            Name                        Rows   Bytes  Cost (%CPU) Time
--------------------------------------------------------------------------------------------------
 0  SELECT STATEMENT                                    11    165      4  (25) 00:00:01
 1   RESULT CACHE        3kcg089wfhwtj35k7d8fm6b9xc                                
 2    HASH GROUP BY                                     11    165      4  (25) 00:00:01
 3     TABLE ACCESS FULL EMPLOYEES                     107   1605      3   (0) 00:00:01
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

 1 - column-count=4; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache */ department_id,
count(1) cnt,
avg(salary) avg_sal,
max(hire_date) max_hire
from hr.employees
grou"



Estadísticas
----------------------------------------------------------
        0  recursive calls
        0  db block gets
        7  consistent gets
        0  physical reads
        0  redo size
      940  bytes sent via SQL*Net to client
      324  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       12  rows processed

rop@ROP111>



Con la primera ejecución quedó almacenado el resultado, cambió el plan pero seguimos viendo que aun hay 7 lecturas lógicas .

Ahora vamos a ver sucede en la próxima ejecución:


rop@ROP111> /

DEPARTMENT_ID        CNT    AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
        100          6       8600 07-DIC-99
         30          6       4150 10-AGO-99
                     1       7000 24-MAY-99
         20          2       9500 17-AGO-97
         70          1      10000 07-JUN-94
         90          3 19333.3333 13-ENE-93
        110          2      10150 07-JUN-94
         50         45 3475.55556 08-MAR-00
         40          1       6500 07-JUN-94
         80         34 8955.88235 21-ABR-00
         10          1       4400 17-SEP-87
         60          5       5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
Id   Operation            Name                        Rows   Bytes  Cost (%CPU) Time
--------------------------------------------------------------------------------------------------
 0  SELECT STATEMENT                                    11    165      4  (25) 00:00:01
 1   RESULT CACHE        3kcg089wfhwtj35k7d8fm6b9xc                                
 2    HASH GROUP BY                                     11    165      4  (25) 00:00:01
 3     TABLE ACCESS FULL EMPLOYEES                     107   1605      3   (0) 00:00:01
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

 1 - column-count=4; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache */ department_id,
count(1) cnt,
avg(salary) avg_sal,
max(hire_date) max_hire
from hr.employees
grou"



Estadísticas
----------------------------------------------------------
        0  recursive calls
        0  db block gets
        0  consistent gets
        0  physical reads
        0  redo size
      946  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       12  rows processed

rop@ROP111>


Como vemos cambió el plan denotando el uso del valor almacenado previamente. Además hay que notar que las lecturas lógicas (consistent gets) ahora son 0. De esta forma vemos que este nuevo feature de 11g puede resultar muy atractivo para acelerar las sentencias sobre tablas estáticas o con cambios poco frecuentes.
En el caso de que cambie alguna tabla subyacente se invalidará el resultado almacenado y se volverá a ejecutar normalmente realizandose el caching para la subsiguiente ejecución, de forma de mantener la consistencia de los resultados.
SQl Result Cache es un nuevo cache en la SGA, tal como es el buffer cache. Ademas se agregan nuevas parametrización que gobiernan el comportamiento de caching de sql's, tales como result_cache_mode, result_cache_max_Result, etc.
Este nuevo mecanismo puede aplicarse también a funciones PL/SQL haciendo que se almacene el valor de retorno y provocando que en las subsiguientes ejecuciones no se ejecute ninguna línea de código de la función y simplemente se retorne el valor resultado previamente cacheado.
Publicado por en
Etiquetas: , ,

1 comentario:

  1. Anónimo8 de mayo de 2020, 9:36

    Muy buen aporte! Lo voy a estar utilizando en un proyecto en el cual trabajo.

    ResponderEliminar

Suscribirse a: Enviar comentarios (Atom)