BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter