Friday, December 14, 2012

Oracle 11g new features: Result cache

One of the best feature for Oracle 11g is setting result cache. Result cache reside in Shared Pool area in SGA. Now database can serves result for queries from the cache instead of re-execute query again and again. But when depending database object is modified cache result stays invalid. Using result cache you can improve performance of your query. It is reduce I/O, sorts, computations.

There are some restrictions for result cache which could not store data:
  • Data dictionary objects;
  • Temporary tables;
  • Queries with bind variables (if different) ;
  • Flashback query`s result;
  • Some SQL functions : current_date, current/local_timestamp, sysdate etc;
  • Function which has any OUT or IN OUT parameter;
  • Function has IN parameter that are CLOB, BLOB, REF CURSOR etc;
  • etc
How to configure result cache
New initialize parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MODE exists on Oracle 11g. You can enable/disable result cache using  RESULT_CACHE_MAX_SIZE parameter.
If value of RESULT_CACHE_MAX_SIZE = 0  it means this feature is disabled.
Memory allocate for the result cache is taken from the shared pool.

There are three different modes you can use and they are managed with RESULT_CACHE_MODE init parameter. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. Default is MANUAL.

Using result_cache hint you can use to manually cache a result of query to override the default setting of there parameter in a session level. Also there is has no_result_cache hint.

There are has several views:

V$RESULT_CACHE_STATISTICS
Lists various server result cache settings and memory usage statistics.
V$RESULT_CACHE_MEMORY
Lists all the memory blocks in the server result cache and their corresponding statistics.
V$RESULT_CACHE_OBJECTS
Lists all the objects whose results are in the
server result cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY
Lists the dependency details between the results in the server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$
Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes.

Check resulting our database`s parameters for result cache

SQL> col name format a30
SQL> col value format a20
SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                                        VALUE
------------------------------      --------------------
result_cache_mode                      MANUAL
result_cache_max_size                1605632
result_cache_max_result              5
result_cache_remote_expiration   0

Now check memory utilization in SHARED POOL:

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                                    BYTES
------------ --------------------------        ----------
shared pool  Result Cache: State Objs         2852
shared pool  Result Cache: Memory Mgr     124
shared pool  Result Cache: Bloom Fltr         2048
shared pool  Result Cache: Cache Mgr        4416

SQL> select * from V$RESULT_CACHE_DEPENDENCY;

no rows selected

SQL> desc dbms_result_cache;
PROCEDURE BYPASS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BYPASS_MODE                    BOOLEAN                 IN
 SESSION                        BOOLEAN                 IN     DEFAULT
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
PROCEDURE DELETE_DEPENDENCY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE DELETE_DEPENDENCY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION FLUSH RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETAINMEM                      BOOLEAN                 IN     DEFAULT
 RETAINSTA                      BOOLEAN                 IN     DEFAULT
 GLOBAL                         BOOLEAN                 IN     DEFAULT
PROCEDURE FLUSH
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETAINMEM                      BOOLEAN                 IN     DEFAULT
 RETAINSTA                      BOOLEAN                 IN     DEFAULT
 GLOBAL                         BOOLEAN                 IN     DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
PROCEDURE INVALIDATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
FUNCTION INVALIDATE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE INVALIDATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             BINARY_INTEGER          IN
PROCEDURE INVALIDATE_OBJECT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CACHE_ID                       VARCHAR2                IN
PROCEDURE INVALIDATE_OBJECT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CACHE_ID                       VARCHAR2                IN
PROCEDURE MEMORY_REPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DETAILED                       BOOLEAN                 IN     DEFAULT
FUNCTION STATUS RETURNS VARCHAR2

SQL> 

Use memory utilization report for result cache:

SQL> begin dbms_result_cache.memory_report; end;
  2  /
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1568K bytes (1568 blocks)
Maximum Result Size = 78K bytes (78 blocks)
[Memory]
Total Memory = 9440 bytes [0.005% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> select * from v$result_cache_objects;

no rows selected

SQL>

For now result cache is clear, let`s cache query result.

I will use result_cache hint override the default setting of parameter in the session.

No I will create 2 same tables with same structure and data. Then using hint I will execute query and will take explain plan (also without hint I will execute query on base another table also will take explain plan) 
and at the end I will compare explain plans and execution times.


SQL> create table ulfet.big_table as select * from dba_objects;

Table created.

SQL> insert into ulfet.big_table select * from ulfet.big_table;

72526 rows created.

SQL> /

145052 rows created.

SQL> /

290104 rows created.

SQL> /

580208 rows created.

SQL> commit;

Commit complete.

SQL>


SQL> select count(1) from big_table;

  COUNT(1)
----------
   1160416

SQL>

Now create second table on base big_table

SQL> create table big_table2 as select * from big_table;

Table created.

SQL> 

Now using below query I will cache query result

SQL> SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;  2    3    4  

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

SQL> 

#Now looking at the explain plan

SQL> set autotrace trace explain statistics
SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;
SQL>   2    3    4  
44 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 446130829

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

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

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

|   0 | SELECT STATEMENT     |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   1 |  RESULT CACHE        | 2dp92r6vy7zz94wfhzvrp1jz3w |       |       |
       |          |

|   2 |   SORT ORDER BY      |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   3 |    HASH GROUP BY     |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   4 |     TABLE ACCESS FULL| BIG_TABLE                  |  1393K|    14M|  465
7   (1)| 00:00:56 |

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


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

   1 - column-count=2; dependencies=(ULFET.BIG_TABLE); parameters=(nls); name="S
ELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1"


Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> 

Now check result cache object view:

SQL> SELECT id, type, name, scn, row_count FROM  v$result_cache_objects;


Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |   131 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCOBJ |     1 |   131 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("INST_ID"=USERENV('INSTANCE'))


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

SQL>


SQL> SELECT * FROM v$result_cache_statistics;

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3700223219

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |   118 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCSTA |     1 |   118 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("INST_ID"=USERENV('INSTANCE'))


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

SQL> SELECT * FROM v$result_cache_statistics;

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

11 rows selected.

Elapsed: 00:00:00.03
SQL> 

Now enable timing to check elapsed time of query

SQL> set autotrace off
SQL> set timing on
SQL> SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;SQL>   2    3    4  

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.02
SQL> 

repeat again

SQL> /

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.00
SQL> 

Waw, 00:00:00, EXCELLENT, is it ? :)

Now check another table`s statistic.

SQL> set autotrace trace explain statistics
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1;  2    3

44 rows selected.

Elapsed: 00:00:06.44

Execution Plan
----------------------------------------------------------
Plan hash value: 244325093

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

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

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

|   0 | SELECT STATEMENT    |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   1 |  SORT ORDER BY      |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   2 |   HASH GROUP BY     |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   3 |    TABLE ACCESS FULL| BIG_TABLE2 |   960K|    10M|  4531   (1)| 00:00:55
 |

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


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
      16585  consistent gets
      16711  physical reads
          0  redo size
       1569  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL> set autotrace off
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1;  2    3

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 RULE
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 UNDEFINED

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 INDEXTYPE
       160 DIRECTORY
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.80

Re-execute query again

SQL> /

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 RULE
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 UNDEFINED

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 INDEXTYPE
       160 DIRECTORY
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.37

As you see Oracle use Shared Pool (before used SQL query) but it is not affected as result cache feature.

To monitor result cache use cache_id value provided explain plan on V$RESULT_CACHE_OBJECTS:

SQL> select
NAME, STATUS, ROW_COUNT, BLOCK_COUNT, NAMESPACE, CREATION_TIMESTAMP
from   V$RESULT_CACHE_OBJECTS
where  CACHE_ID='2dp92r6vy7zz94wfhzvrp1jz3w';  2    3

NAME                           STATUS     ROW_COUNT BLOCK_COUNT NAMES CREATION_
------------------------------ --------- ---------- ----------- ----- ---------
SELECT /*+ RESULT_CACHE */     Published         44           1 SQL   14-DEC-12
count(*) , object_type
FROM ulfet.big_table GROUP BY
object_type
order by 1

Elapsed: 00:00:00.01
SQL>


You can also use below statement for monitoring:

SQL> select DBMS_RESULT_CACHE.STATUS from dual;


Result cache can be flushed using :

SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24

Check statistic again

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ --------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            1568
         3 Block Count Current            0
         4 Result Size Maximum (Blocks)   78
         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
        11 Hash Chain Length              0

11 rows selected.

SQL>

Conclusion :
Result cache provide us data (result of query) from memory and not read it from file. But it`s expensive, you can compare above query`s explain plan and can easy see the different.

Source:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF987
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF978
http://www.dba-oracle.com/oracle11g/oracle_11g_result_cache_sql_hint.htm


1 comment: