RESULT_CACHE EJEMPLO IMPLEMENTACION

 select /*+ RESULT_CACHE */ department_id, avg(salary) from hr.employees group by department_id

select /*+ NO_RESULT_CACHE */ department_id, avg(salary) from hr.employees group by department_id

Execution Plan

----------------------------------------------------------

Plan hash value: 1192169904

--------------------------------------------------------------------------------

------------------

| Id  | Operation     | Name | Rows  | Bytes | Cost

(%CPU)| Time |

--------------------------------------------------------------------------------

------------------

|   0 | SELECT STATEMENT    | |    11 |    77 |     3

   (0)| 00:00:01 |

|   1 |  RESULT CACHE     | cs8656szabsxu2j64yh85u2ppq | | |

      | |

|   2 |   HASH GROUP BY     | |    11 |    77 |     3

   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3

   (0)| 00:00:01 |

--------------------------------------------------------------------------------

------------------

Result Cache Information (identified by operation id):

------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ RESULT_CACH

E */ department_id, avg(salary) from hr.employees group by department_id"

Statistics

----------------------------------------------------------

  1  recursive calls

  0  db block gets

  0  consistent gets

  0  physical reads

  0  redo size

869  bytes sent via SQL*Net to client

543  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

12  rows processed

SQL> 

select  department_id, avg(salary) from hr.employees group by department_id

SQL> select  department_id, avg(salary) from hr.employees group by department_id;

12 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1192169904

--------------------------------------------------------------------------------

| Id  | Operation    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |    11 |    77 |     3 (0)| 00:00:01 |

|   1 |  HASH GROUP BY    |        |    11 |    77 |     3 (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

  1  recursive calls

  0  db block gets

  7  consistent gets

  0  physical reads

  0  redo size

869  bytes sent via SQL*Net to client

543  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

12  rows processed

SQL> execute dbms_result_cache.flush;

PL/SQL procedure successfully completed.