Oracle Live

15/10/2016

DATABASE – Result Cache Manual

Filed under: Database,Laboratorios — mogukiller @ 8:56 am

Se utiliza para almacenar los resultados de una query en memoria, ya sea en la SGA o en la memoria privada del cliente.
Los datos se almacenan en memoria hasta que cambian los valores.

En este laboratorio se realizan varias pruebas utilizando la result cache de forma manual


connect demo/demo

create table t1 as select * from dba_objects;
exec dbms_stats.gather_table_stats ('DEMO','T1');
exec dbms_result_cache.flush;

set autotrace traceonly explain statistics;

******************************************************************************
NB: Todos los tests siguientes se realizan con "result_cache_mode" = MANUAL  *
******************************************************************************

'Primer test: una sentencia muy banal ...            '

select * from t1 where owner = 'DEMO';
/*
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2969 |   292K|   281   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  2969 |   292K|   281   (1)| 00:00:04 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1015  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/

set autotrace off
col name format a30
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                     0
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    0
         6 Create Count Failure                    0
         7 Find Count                              0
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

select * from v$result_cache_objects;
/*
no rows selected
*/

'Segundo test: cacheamos el Query Result  '


set autotrace traceonly explain statistics

select /*+ RESULT_CACHE */ * from t1 where owner = 'DEMO';
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281  (1)| 00:00:04 |
|   1 |  RESULT CACHE      | 739w06wz3rgbh75hsz9m2mnyhf |       |       |     |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281  (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1015  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/

'nota: Esta ejecución a sido como la primera, pero ahora se supone que ha cacheado el resultado.'

SQL> set autotrace off
SQL> select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    1 (*)
         6 Create Count Failure                    0
         7 Find Count                              0
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

SQL> select name, type, cache_id, cache_key from v$result_cache_objects;
/*
NAME                           TYPE       CACHE_ID                       CACHE_KEY
------------------------------ ---------- ------------------------------ ------------------------------
DEMO.T1                        Dependency DEMO.T1                        DEMO.T1
select /*+ RESULT_CACHE      Result     739w06wz3rgbh75hsz9m2mnyhf     gdpjxg5ka90a8ay1v1n9qqprpb
       * from t1
where owner = 'DEMO'
*/

'Tercer test: lanzamos la misma sentencia que en el primer test, a ver si pilla el Result Cache.'

set autotrace traceonly explain statistics
select * from t1 where owner = 'DEMO';

/*
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2969 |   292K|   281   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  2969 |   292K|   281   (1)| 00:00:04 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1015  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/

'nota: No pilla el Result Cache (ver buffer gets) ... vamos a probar con un HINT. '


set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    1
         6 Create Count Failure                    0
         7 Find Count                              0
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

'Cuarto test: lanzamos la misma sentencia que en el primer test, con un HINT, a ver si pilla el Result Cache.'

set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */ * from t1 where owner = 'DEMO';
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | 739w06wz3rgbh75hsz9m2mnyhf |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls); name="select /*+ RESULT_CACHE  * from t1 where owner = 'DEMO'"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/

'nota: Ahora sí ha pillado el Result Cache (ver Buffer Gets !!! )'

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    1
         6 Create Count Failure                    0
         7 Find Count                              1 (*)
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

'Quinto test: Ahora juguemos con alguna Bind Variable '


variable B1 VARCHAR2(16)
exec :B1 := 'DEMO'

set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */ 
       * from t1 
where owner = :B1;
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | aggyfk5n6y85v2gja5g3g19kd7 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls, :B1); name="select /*+ RESULT_CACHE * from t1 where owner = :B1"

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1015  consistent gets
          0  physical reads
          0  redo size
       1331  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

'nota: Ha cacheado el Query Result'

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    2 (*)
         6 Create Count Failure                    0
         7 Find Count                              1
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

select name, type, cache_id, cache_key from v$result_cache_objects;
/*
NAME                           TYPE       CACHE_ID                       CACHE_KEY
------------------------------ ---------- ------------------------------ ------------------------------
DEMO.T1                        Dependency DEMO.T1                        DEMO.T1
select /*+ RESULT_CACHE      Result     aggyfk5n6y85v2gja5g3g19kd7     fsknzwp0myn7v1hyta3vrnwtb0
       * from t1
where owner = :B1

select /*+ RESULT_CACHE      Result     739w06wz3rgbh75hsz9m2mnyhf     gdpjxg5ka90a8ay1v1n9qqprpb
       * from t1
where owner = 'DEMO'
*/

'nota: Volvemos a lanzar lo mismo ...'

set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */ * from t1 where owner = :B1;
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | aggyfk5n6y85v2gja5g3g19kd7 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls, :B1); name="select /*+ RESULT_CACHE 
       * from t1
where owner = :B1"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1331  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

'nota: Aprovecha perfectamente el Result Cache !!! '

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    2
         6 Create Count Failure                    0
         7 Find Count                              2 (*)
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/


'Sexto test: Ahora le cambiamos el valor de la Bind'

variable B1 VARCHAR2(16)
exec :B1 := 'DEMO'
set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */ * from t1 where owner = :B1;
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | aggyfk5n6y85v2gja5g3g19kd7 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls, :B1); name="select /*+ RESULT_CACHE 
       * from t1
where owner = :B1"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1015  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/
'nota: No aprovecha el Result Cache generado en el Segundo Test '

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    3 (*)
         6 Create Count Failure                    0
         7 Find Count                              2
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/
		
'... pero crea uno nuevo.'

select name, type, cache_id, cache_key from v$result_cache_objects;
/*
NAME                           TYPE       CACHE_ID                       CACHE_KEY
------------------------------ ---------- ------------------------------ ------------------------------
DEMO.T1                        Dependency DEMO.T1                        DEMO.T1
select /*+ RESULT_CACHE     Result     aggyfk5n6y85v2gja5g3g19kd7     f6pu1g0cws9uc1rr48zt1q9wja
       * from t1
where owner = :B1

select /*+ RESULT_CACHE      Result     aggyfk5n6y85v2gja5g3g19kd7     fsknzwp0myn7v1hyta3vrnwtb0
       * from t1
where owner = :B1

select /*+ RESULT_CACHE      Result     739w06wz3rgbh75hsz9m2mnyhf     gdpjxg5ka90a8ay1v1n9qqprpb
       * from t1
where owner = 'DEMO'
*/


'Septimo test: Ahora le cambiamos el valor de la Bind, al primer valor ...   '

variable B1 VARCHAR2(16)
exec :B1 := 'DEMO'

set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */  * from t1 where owner = :B1;
/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | aggyfk5n6y85v2gja5g3g19kd7 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls, :B1); name="select /*+ RESULT_CACHE 
       * from t1
where owner = :B1"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1331  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

'Aprovecha el Result Cache generado en el Quinto Test !!! '

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    3
         6 Create Count Failure                    0
         7 Find Count                              3 (*)
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

'Octavo  test: Ahora le volvemos a cambiar el valor de la Bind, al primer valor ...  ' 

variable B1 VARCHAR2(16)
exec :B1 := 'DEMO'

set autotrace traceonly explain statistics
select /*+ RESULT_CACHE */ * from t1 where owner = :B1;

/*
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  2969 |   292K|   281   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | aggyfk5n6y85v2gja5g3g19kd7 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1                         |  2969 |   292K|   281   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=15; dependencies=(DEMO.T1); parameters=(nls, :B1); name="select /*+ RESULT_CACHE 
       * from t1
where owner = :B1"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
*/

'nota: Aprovecha el Result Cache      '

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                   1024
         2 Block Count Maximum                  1152
         3 Block Count Current                    32
         4 Result Size Maximum (Blocks)           57
         5 Create Count Success                    3
         6 Create Count Failure                    0
         7 Find Count                              4 (*)
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0
*/

Conclusiones:
*************
Con result_cache_mode = MANUAL
/*
* El HINT sirve tanto para cachear el resultado de una sentencia, como para aprovechar el cache en las siguientes ejecuciones
* La utilización del Result Cache se hace despues del Binding, sea para cachear el resultado, o para leer del Result Cache,
  por lo tanto se aprovecha siempre de FORMA EFICAZ: es mas eficiente que el Bind Peeking
*/

'Prueba de invalidación de Result Cache !!!'

exec dbms_result_cache.flush

set autotrace traceonly explain statistics

variable B1 NUMBER
exec :B1 := 10

select /*+ RESULT_CACHE */ * from T_PRUEBAS where ID = :B1;
/*
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    34 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | d8jr2dnkdbygbfavpyqcjp98j4 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_PRUEBAS                  |     1 |    34 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_ID                     |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1071  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

*/

set autotrace off
select * from v$result_cache_statistics;
/*
        ID NAME                           VALUE
---------- ------------------------------ -------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            7872
         3 Block Count Current            32
         4 Result Size Maximum (Blocks)   393
         5 Create Count Success           1
         6 Create Count Failure           0
         7 Find Count                     0
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              1
        12 Find Copy Count                0
        13 Latch (Share)                  0
*/

-- Cachea el result cache.

variable B1 NUMBER
exec :B1 := 10

select /*+ RESULT_CACHE */ * from T_PRUEBAS where ID = :B1;

/*
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1071  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
*/

'nota: Utiliza el Result cache !!!'

-- Cambio en la tabla

update T_PRUEBAS set VALOR = 50 where ID = 10;

commit;

variable B1 NUMBER
exec :B1 := 10

select /*+ RESULT_CACHE */ * from T_PRUEBAS where ID = :B1;

/*
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
*/

'nota: Ha invalidado la result Cache, ya que vuelve a realizar buffer gets'

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: