1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

why isn't my result cache not working?

Discussion in 'General' started by ashwanth, Mar 25, 2014.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    why isn't my result cache not working?

    1) without result cache-19s
    2) with result cache-19s-physical read 0,consistent gets 0
    3) with result cache-19s-physical read 0,consistent gets 0
    4) with result cache-20s-physical read 0,consistent gets 0

    show paramter result_cache

    NAME TYPE VALUE
    ------------------------------------ ----------- ----------
    client_result_cache_lag big integer 3000
    client_result_cache_size big integer 0
    result_cache_max_result integer 5
    result_cache_max_size big integer 52448K
    result_cache_mode string MANUAL
    result_cache_remote_expiration integer 0


    sql> SELECT DBMS_RESULT_CACHE.status() FROM dual;

    ORA-00904: : invalid identifier
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Under what user you launch select? Probably you have no rights for execution of this package
    Check right :
    Code (SQL):

    SELECT * FROM user_tab_privs u
    WHERE u.privilege = 'EXECUTE'
    AND u.TABLE_NAME = 'DBMS_RESULT_CACHE'
     
    Can you provide output this query :

    Code (SQL):

        SELECT * FROM v$result_cache_objects;
    SELECT * FROM v$result_cache_statistics;

     
     
    ashwanth likes this.
  3. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    output of v$result_cache_statistics;
    ID NAME VALUE
    1 Block Size (Bytes) 1024
    2 Block Count Maximum 52448
    3 Block Count Current 32
    4 Result Size Maximum (Blocks) 2622
    5 Create Count Success 4
    6 Create Count Failure 0
    7 Find Count 8
    8 Invalidation Count 2
    9 Delete Count Invalid 0
    10 Delete Count Valid 0
    11 Hash Chain Length 1
    12 Find Copy Count 3

    SQL> select name,id,status from v$result_cache_objects;

    NAME ID STATUS
    ---------------------------------------- ---------- ---------
    FCST.HRY_RP_GBURECON_TYPE_SELECT 15 Published
    FCST.LEV_RP_GBURECON_TYPE 14 Published
    FCST.HRY_ACCT_CURR_SRAP 9 Published
    FCST.USERS 7 Published
    FCST.ACTION_LOG 0 Published
    SELECT /*+ result_cache */ FCST.Lev_RP_G 16 Published
    BURECON_TYPE.Member_Key, FCST.Lev_RP_GBU
    RECON_TYPE.Member_ID, FCST.Lev_RP_GBUREC
    ON_TYPE.

    SELECT /*+ result_cache */ Level_Key, Me 10 Published

    NAME ID STATUS
    ---------------------------------------- ---------- ---------
    mber_Key FROM FCST.Hry_ACCT_CURR_SRAP

    SELECT /*+ result_cache */ * FROM fcst.U 8 Invalid
    sers WHERE Object_Key=35280

    select /*+ result_cache */OBJECT_KEY fro 1 Invalid
    m ACTION_LOG where USER_KEY ='2831765'

    meanwhile this is execution plan

    Execution plan :

    without /*+ result_cahe */ hint

    SQL> SELECT FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBURECON_TYPE.Member_
    Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hry_RP_GBURECON_TYPE_SE
    LECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE_SELECT.Member_Key AN
    D FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;

    Elapsed: 00:00:02.11

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1484964662

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

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

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

    | 0 | SELECT STATEMENT | | 4 | 288 |
    8 (25)| 00:00:01 |

    | 1 | SORT ORDER BY | | 4 | 288 |
    8 (25)| 00:00:01 |

    |* 2 | HASH JOIN | | 4 | 288 |
    7 (15)| 00:00:01 |

    |* 3 | TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT | 4 | 48 |
    3 (0)| 00:00:01 |

    | 4 | TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE | 4 | 240 |
    3 (0)| 00:00:01 |

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


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
    "MEMBER_KEY

    ")
    3 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)


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



    with /*+ result_cahe */ hint


    SQL> SELECT /*+ result_cache */ FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_G
    BURECON_TYPE.Member_Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hry
    _RP_GBURECON_TYPE_SELECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE_
    SELECT.Member_Key AND FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;

    Elapsed: 00:00:02.16

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1484964662

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

    | Id | Operation | Name | Rows | Bytes | Cos
    t (%CPU)| Time |

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

    | 0 | SELECT STATEMENT | | 4 | 288 |
    8 (25)| 00:00:01 |

    | 1 | RESULT CACHE | 5auhmbpa5j4x789bfdp5mu3m3u | | |
    | |

    | 2 | SORT ORDER BY | | 4 | 288 |
    8 (25)| 00:00:01 |

    |* 3 | HASH JOIN | | 4 | 288 |
    7 (15)| 00:00:01 |

    |* 4 | TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT | 4 | 48 |
    3 (0)| 00:00:01 |

    | 5 | TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE | 4 | 240 |
    3 (0)| 00:00:01 |

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


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
    "MEMBER_KEY"

    )
    4 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)

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

    1 - column-count=5; dependencies=(FCST.LEV_RP_GBURECON_TYPE, FCST.HRY_RP_GBUR
    ECON_TYPE_SELECT); parameters=(nls); name="SELECT /*+ result_cache */ FCST.Lev_R
    P_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBU
    RECON_TYPE."



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


    again with /*+ result_cahe */ hint



    SQL> SELECT /*+ result_cache */ FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_
    BURECON_TYPE.Member_Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hr
    _RP_GBURECON_TYPE_SELECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE
    SELECT.Member_Key AND FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;

    Elapsed: 00:00:02.16

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1484964662

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

    | Id | Operation | Name | Rows | Bytes | Cos
    t (%CPU)| Time |

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

    | 0 | SELECT STATEMENT | | 4 | 288 |
    8 (25)| 00:00:01 |

    | 1 | RESULT CACHE | 5auhmbpa5j4x789bfdp5mu3m3u | | |
    | |

    | 2 | SORT ORDER BY | | 4 | 288 |
    8 (25)| 00:00:01 |

    |* 3 | HASH JOIN | | 4 | 288 |
    7 (15)| 00:00:01 |

    |* 4 | TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT | 4 | 48 |
    3 (0)| 00:00:01 |

    | 5 | TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE | 4 | 240 |
    3 (0)| 00:00:01 |

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


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
    "MEMBER_KEY"

    )
    4 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)

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

    1 - column-count=5; dependencies=(FCST.LEV_RP_GBURECON_TYPE, FCST.HRY_RP_GBUR
    ECON_TYPE_SELECT); parameters=(nls); name="SELECT /*+ result_cache */ FCST.Lev_R
    P_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBU
    RECON_TYPE."



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

    my question why there is no drop in time during subsequent executions
     
  4. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    SQL> select name,id,status from v$result_cache_objects;

    NAME ID STATUS
    ---------------------------------------- ---------- ---------
    FCST.HRY_RP_GBURECON_TYPE_SELECT 15 Published
    FCST.LEV_RP_GBURECON_TYPE 14 Published
    FCST.HRY_ACCT_CURR_SRAP 9 Published
    FCST.USERS 7 Published
    FCST.ACTION_LOG 0 Published
    SELECT /*+ result_cache */ FCST.Lev_RP_G 16 Published
    BURECON_TYPE.Member_Key, FCST.Lev_RP_GBU
    RECON_TYPE.Member_ID, FCST.Lev_RP_GBUREC
    ON_TYPE.

    SELECT /*+ result_cache */ Level_Key, Me 10 Published

    NAME ID STATUS
    ---------------------------------------- ---------- ---------
    mber_Key FROM FCST.Hry_ACCT_CURR_SRAP

    SELECT /*+ result_cache */ * FROM fcst.U 8 Invalid
    sers WHERE Object_Key=35280

    select /*+ result_cache */OBJECT_KEY fro 1 Invalid
    m ACTION_LOG where USER_KEY ='2831765'


    9 rows selected.

    SQL>
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    As you can see, you are all working properly when re-run the query with Hint RESULT_CACHE .

    SQL * Net Frome / To I / O - remain

    630 bytes sent via SQL * Net to client
    248 bytes received via SQL * Net from client.

    You no disk reads or sorts and etc:
    0 recursive calls
    0 db block gets
    Consistent gets 0
    0 physical reads
    0 sorts (memory)
    0 sorts (disk)
     
  6. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    @krasnoslobodtsev_si:then why there is no improvement in time?the query should run faster during subsequent executions
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    if result_cache works, we see results and on request runtime (regarding the response from a database) and on values of statistics.

    you can run this little script and display the results?


    Code (SQL):


    SET echo ON
    SET timing ON
    DROP TABLE tst_result_cache purge;
    CREATE TABLE tst_result_cache
    ( id INT PRIMARY KEY,
      val varchar2(100)
      );
      INSERT INTO tst_result_cache
      SELECT
           level,
           'str '||level
      FROM dual
      CONNECT BY level < 101;
      commit;
      EXEC dbms_stats.gather_table_stats(OWNNAME=> USER,TABNAME =>'TST_RESULT_CACHE',CASCADE => TRUE);
      SELECT /* result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15;
      SELECT /*+ result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15;
      SELECT /*+ result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15;

    SQL>


    > SET timing ON
    > DROP TABLE tst_result_cache purge
    TABLE TST_RESULT_CACHE dropped.
    Elapsed: 00:00:00.197
    > CREATE TABLE tst_result_cache
    ( id INT PRIMARY KEY,
      val varchar2(100)
      )
    TABLE TST_RESULT_CACHE created.
    Elapsed: 00:00:00.155
    > INSERT INTO tst_result_cache
      SELECT
           level,
           'str '||level
      FROM dual
      CONNECT BY level < 101
    100 ROWS inserted.
    Elapsed: 00:00:00.074
    > commit
    committed.
    Elapsed: 00:00:00.012
    > EXEC dbms_stats.gather_table_stats(OWNNAME=> USER,TABNAME =>'TST_RESULT_CACHE',CASCADE => TRUE)
    anonymous block completed

    > SELECT /* result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15
            ID VAL                                                                                                
    ---------- ----------------------------------------------------------------------------------------------------
            10 str 10                                                                                              
            11 str 11                                                                                              
            12 str 12                                                                                              
            13 str 13                                                                                              
            14 str 14                                                                                              
            15 str 15                                                                                              

     6 ROWS selected

    Elapsed: 00:00:00.024


    > SELECT /*+ result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15
            ID VAL                                                                                                
    ---------- ----------------------------------------------------------------------------------------------------
            10 str 10                                                                                              
            11 str 11                                                                                              
            12 str 12                                                                                              
            13 str 13                                                                                              
            14 str 14                                                                                              
            15 str 15                                                                                              

     6 ROWS selected


    Elapsed: 00:00:00.012



    > SELECT /*+ result_cache */ * FROM tst_result_cache WHERE id BETWEEN 10 AND 15
            ID VAL                                                                                                
    ---------- ----------------------------------------------------------------------------------------------------
            10 str 10                                                                                              
            11 str 11                                                                                              
            12 str 12                                                                                              
            13 str 13                                                                                              
            14 str 14                                                                                              
            15 str 15                                                                                              

     6 ROWS selected

    Elapsed: 00:00:00.003

     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not if the result cache is enabled; it will use it regardless of the presence of the hint. Sergey is correct, you do not have execute privilege on the dbms_result_cache package which is why you can't see the status. Let's do an experiment:


    Code (SQL):

    SQL>
    SQL> --
    SQL> -- Verify result cache enabled
    SQL> --
    SQL> SELECT dbms_result_cache.STATUS FROM dual;
    STATUS
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ENABLED
    SQL>
    SQL> --
    SQL> -- Query the EMP table
    SQL> --
    SQL> -- We just built it so nothing should be
    SQL> -- in the result cache
    SQL> --
    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT empno, ename, job, mgr, deptno
      2  FROM emp;
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902         20
          7499 ALLEN      SALESMAN        7698         30
          7521 WARD       SALESMAN        7698         30
          7566 JONES      MANAGER         7839         20
          7654 MARTIN     SALESMAN        7698         30
          7698 BLAKE      MANAGER         7839         30
          7782 CLARK      MANAGER         7839         10
          7788 SCOTT      ANALYST         7566         20
          7839 KING       PRESIDENT                    10
          7844 TURNER     SALESMAN        7698         30
          7876 ADAMS      CLERK           7788         20
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7900 JAMES      CLERK           7698         30
          7902 FORD       ANALYST         7566         20
          7934 MILLER     CLERK           7782         10
          7939 DUKE       CEO                          10
          7949 PRINCE     CFO                          10
          7959 QUEEN      CIO                          10
          7869 JACK       PRESIDENT                    10
    18 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3956160932
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    18 |   450 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    18 |   450 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    Note
    -----
       - SQL plan baseline "SQL_PLAN_g6050atduj3dcd8a279cc" used FOR this statement

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo SIZE
           1468  bytes sent via SQL*Net TO client
            530  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             18  ROWS processed
    SQL>
    SQL> --
    SQL> -- Let's run that query again and
    SQL> -- see the result cache in action
    SQL> --
    SQL>
    SQL> SELECT empno, ename, job, mgr, deptno
      2  FROM emp;
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902         20
          7499 ALLEN      SALESMAN        7698         30
          7521 WARD       SALESMAN        7698         30
          7566 JONES      MANAGER         7839         20
          7654 MARTIN     SALESMAN        7698         30
          7698 BLAKE      MANAGER         7839         30
          7782 CLARK      MANAGER         7839         10
          7788 SCOTT      ANALYST         7566         20
          7839 KING       PRESIDENT                    10
          7844 TURNER     SALESMAN        7698         30
          7876 ADAMS      CLERK           7788         20
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7900 JAMES      CLERK           7698         30
          7902 FORD       ANALYST         7566         20
          7934 MILLER     CLERK           7782         10
          7939 DUKE       CEO                          10
          7949 PRINCE     CFO                          10
          7959 QUEEN      CIO                          10
          7869 JACK       PRESIDENT                    10
    18 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3956160932
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    18 |   450 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    18 |   450 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    Note
    -----
       - SQL plan baseline "SQL_PLAN_g6050atduj3dcd8a279cc" used FOR this statement

    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo SIZE
           1468  bytes sent via SQL*Net TO client
            530  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             18  ROWS processed
    SQL>
    SQL> --
    SQL> -- Let's now use the /*+ result_cache */ hint
    SQL> --
    SQL>
    SQL> SELECT /*+ result_cache */ empno, ename, job, mgr, deptno
      2  FROM emp;
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902         20
          7499 ALLEN      SALESMAN        7698         30
          7521 WARD       SALESMAN        7698         30
          7566 JONES      MANAGER         7839         20
          7654 MARTIN     SALESMAN        7698         30
          7698 BLAKE      MANAGER         7839         30
          7782 CLARK      MANAGER         7839         10
          7788 SCOTT      ANALYST         7566         20
          7839 KING       PRESIDENT                    10
          7844 TURNER     SALESMAN        7698         30
          7876 ADAMS      CLERK           7788         20
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7900 JAMES      CLERK           7698         30
          7902 FORD       ANALYST         7566         20
          7934 MILLER     CLERK           7782         10
          7939 DUKE       CEO                          10
          7949 PRINCE     CFO                          10
          7959 QUEEN      CIO                          10
          7869 JACK       PRESIDENT                    10
    18 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3956160932
    -------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                            |    18 |   450 |     3   (0)| 00:00:01 |
    |   1 |  RESULT CACHE      | 7n098fdw8gtbyahac2d91n3r0p |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| EMP                        |    18 |   450 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------
    RESULT Cache Information (IDENTIFIED BY operation id):
    ------------------------------------------------------
       1 - column-COUNT=5; dependencies=(GRIBNAUT.EMP); name="select /*+ result_cache */ empno, ename, job, mgr, deptno
    from emp"


    Note
    -----
       - SQL plan baseline "SQL_PLAN_12bfc52rz4u8td8a279cc" used FOR this statement

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo SIZE
           1468  bytes sent via SQL*Net TO client
            530  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             18  ROWS processed
    SQL>
     

    What you don't take into consideration is that you've changed the SQL_ID by adding the hint; that causes Oracle to hard parse the query and also means there is no data in the result cache for that new SQL_ID. If we run that same query again you WILL see the result cache working:


    Code (SQL):

    SQL>
    SQL> --
    SQL> -- Let's run that same query again
    SQL> --
    SQL>
    SQL> SELECT /*+ result_cache */ empno, ename, job, mgr, deptno
      2  FROM emp;
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902         20
          7499 ALLEN      SALESMAN        7698         30
          7521 WARD       SALESMAN        7698         30
          7566 JONES      MANAGER         7839         20
          7654 MARTIN     SALESMAN        7698         30
          7698 BLAKE      MANAGER         7839         30
          7782 CLARK      MANAGER         7839         10
          7788 SCOTT      ANALYST         7566         20
          7839 KING       PRESIDENT                    10
          7844 TURNER     SALESMAN        7698         30
          7876 ADAMS      CLERK           7788         20
         EMPNO ENAME      JOB              MGR     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7900 JAMES      CLERK           7698         30
          7902 FORD       ANALYST         7566         20
          7934 MILLER     CLERK           7782         10
          7939 DUKE       CEO                          10
          7949 PRINCE     CFO                          10
          7959 QUEEN      CIO                          10
          7869 JACK       PRESIDENT                    10
    18 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3956160932
    -------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                            |    18 |   450 |     3   (0)| 00:00:01 |
    |   1 |  RESULT CACHE      | 7n098fdw8gtbyahac2d91n3r0p |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| EMP                        |    18 |   450 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------
    RESULT Cache Information (IDENTIFIED BY operation id):
    ------------------------------------------------------
       1 - column-COUNT=5; dependencies=(GRIBNAUT.EMP); name="select /*+ result_cache */ empno, ename, job, mgr, deptno
    from emp"


    Note
    -----
       - SQL plan baseline "SQL_PLAN_12bfc52rz4u8td8a279cc" used FOR this statement

    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo SIZE
           1468  bytes sent via SQL*Net TO client
            530  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             18  ROWS processed
    SQL>