Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


Oracle result_cache
tips


Oracle11g Tips by Burleson Consulting

November 20, 2011

These are work in progress excerpts from the book "Oracle 11g New Features" authored by John Garmany, with Oracle ACE's Steve Karam, Lutz Hartmann, V.J. Jain and Brian Carr.


Oracle 11g has over 480 new enhancements and it's often difficult to tease out the important new features.  But one thing is clear; the result_cache syntax may become very important to the Oracle developer, yet another tool that will join related caching and pre-summarization techniques:

  • Materialized Views

  • The "materialize" hint (the SQL99 WHEN clause)

  • Global temporary tables

  • A PL/SQL collection (array)

Oracle notes that the result_cache feature is very different from traditional caching and pre-summarization mechanisms and that the result_cache hint causes Oracle to check and see if a result for your execution plan already resides in the cache.  If so, Oracle by skip the fetch step of execution and call your rows directly from the cache instead of reading the rows from the daabase buffers,

The Oracle result_cache documentation notes that creating a result cache is conceptually similar to other existing Oracle data passing tools such as a shared PL/SQL collection (except that result_cache is a shared array) or a materialized view (except that result_cache in stored in SGA RAM).

The result cache is new to Oracle 11g and provides enhanced query performance for SQL and PL/SQL applications by caching the results of SQL queries into memory. The following Oracle 11g database initialization parameters affect the operation and function of the result cache for Oracle 11g:

  • client_result_cache_lag
  • client_result_cache_size
  • result_cache_max_result
  • result_cache_max_size
  • result_cache_mode
  • result_cache_remote_expiration

Unlike a PL/SQL collection which reside in private PGA RAM, a result cache shareable and is stored in SGA memory.  Unlike materialized views, the result_cache output is stored in the RAM of the SGA, and in 11g RAC, each node will have it's own private area for storing result_cache output.
 

Oracle saves query output with materialized views, global temporary tables, PL/SQL collections and with the new 11g result cache.

There are two ways to visualize a 11g result_cache output:

  • A sharable PL/SQL collection - Oracle PL/SQL allows for result sets to be saved in RAM for later use by the session using an array heap called a "collection".  The result_cache hint expands this functionality to allow for inter-session access to pre-summarized row data in RAM.  Also, PL/SQL collections (arrays) were stored in the PGA, whereas the result_cache output is stored in the SGA region.
     

  • An ad-hoc,  RAM-based Materialized View - Unlike traditional materialized views where tables are pre-joined and stored on slow disk, the result_cache hint allows the denormalize for of the table rows to be stored in super-fast RAM with over 100x faster access speeds

Unlike the process of writing a materialized view to  a mechanical platter disk, the result cache is stored in super-fast RAM, alleviating the need to index the result set, as you might with a materialized view on a disk.  Also, creating a result cache does not require direct DBA intervention, just the setting of four new init.ora parms.

Since the result cache is all about improving performance, let's examine what we can expect when we implement this feature.

Result_cache speed; your mileage may vary

Oracle's claims of super-faster result set delivery are predicated on the assumption that without the result set, the database will experience extra disk I/O and/or a CPU overhead latency. (  The CPU latency is because we must reconstruct the result set from the RAM buffer blocks).  There are also concerns that using the result_cache approach may not scale-up for large dynamic systems.

In general, a summary or aggregation will precipitate a full-table scan which will indeed incur physical disk I/O. 

But the performance will be different when using the result cache to pre-join tables together.  With a properly-sized data buffer (per v$db_cache_advice or AMM) will already have the working set cached, and the "materialization" of a result cache involves no disk I/O.  Instead, Oracle will assemble the result set RAM-to-RAM, joining tables in memory. 

Historically, these logical I/O's are recorded as "consistent gets", and they tend to be CPU intensive.  For small databases, Oracle 10g's automatic memory management (AMM) should optimize the size of your RAM buffer, and for large databases, the DBA optimizes the data buffer cache sizes using intelligent sizing scripts:

An optimized db_cache_size should have all result cache blocks in RAM already

Internally, result_cache waits are displayed with the "RC enqueue", and as shop being deploying the result_cache approach we will learn more about it's scalability in production environments. 

The 11g scalability documentation also suggests that the result_cache can aid in performance and scalability:

"Internal tests show that the use of Server Result Cache can enhance the performance for read-intensive workloads by as much as 200%."

The 11g new features guide also notes that "Concurrent, multi-user applications that use this [result_cache] feature experience better response times. Applications that implement a session-private scheme consume significantly less memory by using this feature and, therefore, experience improved scalability.?

Administrators can monitor and manage the contents of the Server Result Cache via a new PL/SQL interface and you can view result_cache details using the v$result_cache_objects, and other related views such as v$result_cache_statistics, v$result_cache_memory, v$result_cache_objects, and v$result_cache_dependency

Let's explore more details on the tradeoffs between using the result_cache and other caching mechanisms.

Result cache vs. Materialized Views

One of the best uses for materialized views is reducing the need to repeatedly summarize or aggregate data and to pre-join highly normalized tables.  This idea of pre-joining tables and pre-summarizing data is not new, but the mechanism is quite complex.  Storing the pre-joined tables is the easy part, sharing the materialization is more difficult. 

Pre-computing and saving result sets

In materialized view, Oracle created a transparent "query rewrite" mechanism to allow SQL to automatically re-write SQL to access the summary.  In PL/SQL arrays, a subsequent procedure could refer to the collection data using "ref cursors", a call by reference, the act of passing a pointer to the array, rather than the data itself. 

Anyway, let's examine how the result cache compares to a PL/SQL collection.

Result cache vs. PL/SQL Collections

PL/SQL guru Steve Feuerstein did a quick test of PL/SQL collections (arrays) vs. the result cache and came-up with these rules of thumb for result cache performance:

  • The Oracle Database 11g PL/SQL function result cache is, indeed, much faster than repetitive querying. In this test, it was over an order of magnitude faster.
     

  • A packaged collection cache is even faster, most likely because the PL/SQL runtime engine can access the data from PGA memory rather than SGA memory. Unfortunately, this also means that the consumption of memory occurs on a per-session basis, which is not very scalable.
     

  • The packaged collection approach consumed additional PGA memory, but the Oracle Database 11g function result cache did not.

Let's take a closer look at the 11g result cache and see when to use it.

How to use the result cache

Oracle notes that the result_cache functionality can be enabled in two ways:

  • Alter session - you can issue the command "alter session cache results;" to cache your session data.
     

  • PL/SQL - You can create a PL/SQL function using the result_cache keyword to store the result, and use the new relies_on keyword to tell Oracle what tables will trigger an invalidation (should the rows become out-of-date with the current table data).
     

  • SQL - You can add the /*+ result_cache */ hint to any SQL statement, and invoking the result_cache hint is far easier than creating a PL/SQL array or materialized view because the syntax is very straightforward:

select /*+ result_cache */
stuff
from tab1 natural join tab2;

Note:  Only the PL/SQL uses the relies_on keyword, and it is unclear how Oracle manages the invalidation mechanism with SQL and "alter session" syntax.

With all of these choices, when does it make sense to use a result_cache hint in lieu of a materialized view or PL/SQL array? 

Remember, the result_cache hint is intended for relatively static (unchanging) data, making it a great solution for data warehouse and DSS applications, or any systems where data is loaded during a batch load and queries are run in a non-DML environment.  In general, the benefits of result cache include:

  • Removing repeated table joins - The result_cache hint is great for denormalizing a legacy database.  Remember, back in the days of Oracle7, disk was over a hundred times more expensive than today and IT shops deliberately designed their schemas to have as little redundancy as possible (third normal form, or 3NF).  The result_cache hint can be used to pre-join 3NF tables together into a redundant 1NF representation, and 0NF is possible if you define result sets with the VARRAY, repeating columns.
     

  • Caching deterministic function output - For PL/SQL deterministic functions, using result_cache can greatly reduce the overhead of repeated re-computation of static values.
     

  • Reducing CPU overhead - Without a result cache to store pre-joined or pre-summarized results, the raw data must be fetched from the data buffer (db_cache_size), causing an increase in CPU consumption.
     

  • Reducing disk I/O - When compared to materialized views (stored on disk) a result set in immediately available in RAM without any physical I/O.

For more details on tuning with materialized views, see my book "Oracle Tuning: The Definitive Reference". 

Usage tips for result cache

The result_cache promises to be a great tool for the Oracle developer because of the shortcomings of traditional methods.  Materialized views required DBA intervention, a stale refresh mechanism and a sophisticated query rewrite capability and PL/SQL arrays may not have a usage scope beyond the control of the creating session.

Given these tradeoffs, let's take a look at some general guidelines for using the result cache instead of traditional materialized views or PL/SQL arrays.

Caveats to the result_cache approach

Let's take a closer look at how the result_cache output behaves in a production environment.  As the world's most robust database, Oracle offers something for everyone, and the result_cache may not be right for you.  Some of the limitations and shortcomings in 11g include

  • Limited Scope - Unlike a PL/SQL collection that can only be shared with complex coding, the result_cache is transparently sharable by other concurrent sessions.
     

  • Limited Persistence - There is an issue of long-term persistence, since the result_cache output will survive only for the number of minutes specified by result_cache_remote_expiration.  In any database with even meager update activity against the source tables, we can expect that the result cache would be invalidated by DML far before the expire time.  Also, the Oracle documentation says that a result cache is eligible for a page-out, indicating that the pool for the result cache uses an LRU algorithm (i.e. a least-recently-used FIFO queue).
     

  • Blanket Invalidation - Just like a materialized view, the row data from a result_cache output could quickly become out-of-date (stale).  However, The documentation says that the result_cache invalidation is triggered at the table level, so it?s possible for a result_cache will be marked as invalid whenever any table rows are changed, even if the changed rows do not participate in your result set.  This limits the use of the result cache to systems with very little runtime updates.
     

  • Restricted function usage - The Oracle documentation lists these restrictions for using result_cache in a PL/SQL function:

  • It is not defined in a module that has invoker's rights or in an anonymous block.
  • It is not a pipelined table function.
  • It has no OUT or IN OUT parameters.
  • No IN parameter has one of the following types, BLOB, CLOB, NCLOB, REF CURSOR, Collection
    Object, Record.
  • The return type is none of the following, BLOB, CLOB, NCLOB, REF CURSOR, Object, Record or PL/SQL collection that contains one of the preceding unsupported return types.

Ideal databases for using result_cache

Given the benefits and limitations of the result cache, it appears that the result cache functionality will be best for databases with these characteristics:

  • Legacy (highly normalized) designs - RAM materializations are great for databases that were designed to be highly normalized.  Back in the old days, DBA's made a conscious trade-off to minimize disk space by removing redundant data, knowing the re-assemble would cost extra CPU cycles.  With Materialized view denormalization, it's not uncommon to do a 12-way table join once, thereby saving overhead for thousands of subsequent SQL statements.
     
  • Largely read-only tables - Remember, a change to any row in any of the source tables invalidates the result cache regardless of whether the changed rows participate in the result cache.  The result cache is ideal for large ad-hoc databases where data is loaded nightly and run read-only during the processing window.
     
  • Databases with CPU bottlenecks - Remember, the result_cache may not make much performance improvement if you already have a large enough data cache to buffer your working set of frequently-referenced data blocks.  
     
  • I/O bound databases - Shops using solid-state flash storage will find materialized views a far better choice than the result cache since all of their materialized views will reside in RAM.  You can also cache materialized views by defining them in your KEEP pool.

When not to use result sets

here are several types of database configurations that are sub-optimal for result set caching:

  • Volatile databases - Remember, a result cache disappears when ANY DML is executed against the relies_on table.  High volume ML databases (e.g. OLTP) should consider materialized views, segregated into their KEEP pool for full caching.
     
  • Solid-state databases - In a databases using solid-state disk or a large data buffer, the result cache can be rebuilt from the RAM blocks in just a few microseconds.  The result cache functionality can help these shops by reducing the CPU overhead associated with joining tables from the data buffer.  For more details on solid-state Oracle, see my book "Oracle Solid-state Disk Tuning" and "Oracle Tuning: The Definitive Reference".

In sum, the result_cache feature will be very useful for specific types of Oracle shops, yet another choice within the world's most flexible and robust database.

For more details on tuning Oracle with RAM caches, see my book "Oracle Tuning: The Definitive Reference".


result_cache usage References:

As a reference, let's examine how to set-up and use the result cache feature.

Configuring the database for result_cache

There are several new 11g parameters required to enable the result_cache feature:

  • result_cache_max_size - specifies the maximum amount of SGA memory (in bytes) that the Result Cache can use.  The defaylt is zero, but you can use the "alter system set result_cache_max_size" to enable the feature at runtime.
     

  • result_cache_max_result - This specifies the maximum percentage of the Result Cache that any single result can use.
     

  • result_cache_mode - This can be set to "manual" or "force", it specifies when a ResultCache operator is spliced into a query's execution plan.
     

  • result_cache_remote_expiration - This is the value, in minutes that a result cache will be alive.
     

The result_cache views include v$result_cache_statistics, v$result_cache_memory, v$result_cache_objects, and v$result_cache_dependency plus the equivalent RAC views gv$result_cache_statistics, gv$result_cache_memory, gv$result_cache_objects and gv$result_cache_dependency.

Note that each RAC instance will have a private area in each SGA for result set caching.
 

Using the dbms_result_cache package

Oracle has a new 11g dbms_result_cache built-in package that allow you to interrogate, bypass and flush the result cache:

Subprogram Description
BYPASS Procedure Sets the bypass mode for the Result Cache
FLUSH Function & Procedure Attempts to remove all the objects from the Result Cache, and depending on the arguments retains or releases the memory and retains or clears the statistics
INVALIDATE Functions & Procedures Invalidates all the result-set objects that dependent upon the specified dependency object
INVALIDATE_OBJECT Functions & Procedures Invalidates the specified result-set object(s)
MEMORY_REPORT Procedure Produces the memory usage report for the Result Cache
STATUS Function Checks the status of the Result Cache

See my related notes on materialized views to appreciate the difference from the result_cache feature:

 

 

 

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.