Remote DBA Plans
Remote DBA Service

Using the Result Cache Hint in PL/SQL

Oracle 11g New Features Tips by Burleson Consulting
June 27, 2008

Oracle 11g SQL New Features Tips

The result cache is a powerful new feature that caches a result set in the SGA for a specified period of time. The first query will create the result set and subsequent queries will use the cached result set instead of creating their own result set. Basically, the Result Cache hint tells Oracle to check the SGA for a copy of the result set, if found use it, if not, create the result set and cache it.

Using the result cache can impact a database differently based on the database configuration. Like a materialized view, the result cache can be used to eliminate the overhead of repeatedly joining multiple tables in a highly normalized schema. Using prejoined data from the result cache will reduce physical IO on uncached table blocks and reduce consistent gets, CPU on fully cached systems. The result cache does have a cost in the additional contention for space in the SGA.

Below are some result cache details:

The result cache is stored in the SGA and is available across sessions. This is in contrast to a PL/SQL collection, stored in PGA, and normally only available to the session that created it.
The result cache can be implemented by using the result_cache hint, in PL/SQL, and at the session level.
The result cache will remain in use until it times out or is invalidated by changes to the dependent objects. Invalidating a result cache set can be done using a PL/SQL procedure, but not using SQL.
The result cache space in the SGA is available to age out. If space is needed in the SGA, the LRU algorithm can age out a low use result cache set. On first execution, the cached result set will be recreated in the SGA. On an SGA sized constrained system, this can add considerably to SGA contention.
Result Cache sets are managed under two new latches, and latch contention appears to increase exponentially as concurrency increases. This is in addition to the SGA contention that can result from caching large numbers of result sets in a memory constrained SGA.
Result Cache can not be used on Dictionary or temporary tables, sequence pseudo columns, non-deterministic functions, and SQL select date functions such as SELECT current_date, xxx,yyy�
If the SQL contains bind variables, the bind variables are stored with the result set and the cached result set can be used if the SQL statements are the same, or used with the same bind variables. Different bind variables will produce new result sets.

Using the Result Cache in PL/SQL is much more flexible, including the ability to manage cached sets and dependencies to invalidate cached sets. The PL/SQL implementation of the result cache will be covered in the PL/SQL Chapter.

There are three parameters supporting the new result cache feature:

RESULT_CACHE_MAX_SIZE = integer [K | M | G]

Result_cache_max_size defines the maximum amount of SGA memory available to the result cache feature. Setting this parameter to 0 will disable the result cache. The default value, on the other hand, is derived form the shared_pool_size, sga_target, or memory_target , per whichever is currently in use. The ALTER SYSTEM command can be used to dynamically change it.

RESULT_CACHE_MAX_RESULT = integer

Result_cache_max_result defines the maximum percentage of the result_cache_max_size that any one result set can use. This parameter defaults to 5% and can be changed using the ALTER SYSTEM command.

RESULT_CACHE_MODE = { MANUAL | FORCE }

Result_cache_max_mode defines how the result cache is applied to the SQL statement. When set to MANUAL, only SQL with the result_cache hint will use the result cache feature. When set to FORCE, the operator is applied to the root of all SELECT statements. The default value is MANUAL, and the parameter can be modified by ALTER SYSTEM and ALTER SESSION commands,

In the example below the SQL query returns the stores that have above average sales. Here, the execution plan and 18 consistent gets can be seen:

column c1 heading "Store Name" format a30
column c2 heading Total        format 999,999
select
initcap(store_name) c1,
total                c2
from
(select
     store_name,
     sum(quantity) total
   from store join sales using (store_key)
   group by store_name)
where
total > (select avg(total)
           from (select
                   sum(quantity) total
                 from store join sales using (store_key)
                 group by store_name))
;

Store Name                        Total
------------------------------ --------
Borders                          21,860
Books For Dummies                13,000
Wee Bee Books                    13,700
Wild And Lively Books            24,700
Eaton Books                      12,120

------------------------------------------
| Id | Operation                        |
------------------------------------------
|   0 | SELECT STATEMENT                 |
|* 1 | FILTER                          |
|   2 |   HASH GROUP BY                  |
|   3 |    MERGE JOIN                    |
|   4 |     TABLE ACCESS BY INDEX ROWID |
|   5 |      INDEX FULL SCAN             |
|* 6 |     SORT JOIN                    |
|   7 |      TABLE ACCESS FULL           |
|   8 |   SORT AGGREGATE                 |
|   9 |    VIEW                          |
| 10 |     SORT GROUP BY                |
| 11 |      MERGE JOIN                  |
| 12 |       TABLE ACCESS BY INDEX ROWID|
| 13 |        INDEX FULL SCAN           |
|* 14 |       SORT JOIN                  |
| 15 |        TABLE ACCESS FULL         |
------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
         18 consistent gets
          0 physical reads
          0 redo size
        608 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          5 rows processed

The first time the same query is run using the result_cache hint, the statistics are the same.

select /*+ RESULT_CACHE */
initcap(store_name) c1,
total                c2
from
(select
     store_name,
     sum(quantity) total
   from store join sales using (store_key)
   group by store_name)
where
total > (select avg(total)
           from (select
                   sum(quantity) total
                 from store join sales using (store_key)
                 group by store_name))
;

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

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.