Oracle 11g result cache

One of the production system is very slow and users are giving up. when I analyze the AWR report, there are a few things that can be improved on the top "elapsed time" queries, one of the improvement is to enable the result cache on query level by adding a hint to it.


The database is running on 11.2.0.3.


1.    Change parameter result_cache_max_size from 0 to 100m
2.    Change parameter result_cache_max_result from 5 to 100

SELECT /*+ result_cache */ 1

      INTO NCOUNT
      FROM myview

  now I can see that the query statistics:



  from sqlplus:

  set autotrace traceonly

  select /*+ result_cache */ 1...

      Execution Plan

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

Plan hash value: 2064300782



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

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

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

|   0 | SELECT STATEMENT                    |                            |     1 |    80 |    15   (0)| 00:00:01 |

|   1 |  RESULT CACHE                       | 2c9dvp51sa88r414fwndn3v3df |       |       |            |          |


...
      Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        202  bytes sent via SQL*Net to client

        361  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 there are a few system views to get the result cache information:

select * from sys.V_$RESULT_CACHE_DEPENDENCY
select * from sys.V_$RESULT_CACHE_MEMORY
select * from sys.V_$RESULT_CACHE_OBJECTS;
select * from sys.V_$RESULT_CACHE_STATISTICS;

for example:

Enter value for tblname: sys.v_$result_cache_statistics

        ID NAME                                               VALUE
---------- -------------------------------------------------- ------------------------------------------
         1 Block Size (Bytes)                                 1024
         2 Block Count Maximum                                10240
         3 Block Count Current                                32
         4 Result Size Maximum (Blocks)                       10240
         5 Create Count Success                               20
         6 Create Count Failure                               0
         7 Find Count                                         9
         8 Invalidation Count                                 1
         9 Delete Count Invalid                               16
        10 Delete Count Valid                                 0
        11 Hash Chain Length                                  1
        12 Find Copy Count                                    3

 select id,type,name,namespace,status,object_no from sys.v_$result_cache_objects;

        ID TYPE       NAME                                     NAMES STATUS     OBJECT_NO
---------- ---------- ---------------------------------------- ----- --------- ----------
         0 Dependency JSUN.DROPIT                                    Published    1338279
         1 Result     select /*+ result_cache */ * from dropit SQL   Published          0

if you use bind variable in the query, then each different variable will have a new result cache.

col CACHE_ID format a40
col CACHE_KEY format a40
select id,hash,LRU_NUMBER,SPACE_OVERHEAD,SPACE_UNUSED,CACHE_ID,CACHE_KEY from sys.v_$result_cache_objects where id in (5,6);

 ID       HASH LRU_NUMBER SPACE_OVERHEAD SPACE_UNUSED CACHE_ID                                 CACHE_KEY
--- ---------- ---------- -------------- ------------ ---------------------------------------- ---------------------------
  6 2841076041         10            528          496 2c9dvp51sa88r414fwndn3v3df               7ntn14k23rg5xdw7fpwb5r7qu2
  5  653915162          9            528          496 2c9dvp51sa88r414fwndn3v3df               79rzg6ahqmbcq0pyr4xdgsttqm


Be careful when you flush the result_cache, do not forget to turn off bypass after the flush, otherwise the result cache stop working.


BEGIN
   DBMS_RESULT_CACHE.BYPASS(TRUE);
   DBMS_RESULT_CACHE.FLUSH;
DBMS_RESULT_CACHE.BYPASS(FALSE);
END;
/