Using Oracle 11g result_cache hints
 

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:

The Oracle 11g documentation notes that the result_cache feature is very different from traditional caching and presummarization mechanisms:

“Note the new Operator ‘RESULT CACHE’. Issuing this hint causes the operator to examine the Result Cache to determine if a result for this execution plan is already present in the cache.

If the result is found in the cache, then the operator bypasses the execution of the underlying execution plan and returns rows directly from the Result Cache.”

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).

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.

Read more about the advantages of using Oracle 11g results_cache hints:

http://www.dba-oracle.com/oracle11g/oracle_11g_result_cache_sql_hint.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf