Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 11g Result Cache

By Saurabh Gupta | on April 28, 2011 |
Oracle 11g Tutorials

Result Caching is a new feature introduced in Oracle 11g. Since its induction in the 11g release, caching of result sets has emerged out as one of the most efficient performance tip in modern database applications. It can be implemented in both SQL and PL/SQL. In this tutorial, we shall see the result cache feature implementation and study its impacts.

 Result Cache: Background

Prior to 11g release, Oracle used to cache the database blocks, and not the result sets, in buffer cache component of Oracle memory. Since this storage was distributed, it was efficient only for first few subsequent executions of the query. Oracle 11g edition has upgraded this feature by caching the complete result sets in a new memory component, known as Result Cache.

Result Cache: How it works?

On a result cache enabled database server, if an SQL query or PL/SQL function is executed in cache mode, Oracle caches the data result set in the cache component of the memory. This component has been newly introduced in Oracle 11g memory architecture as shown in the below figure.

Now, this cached result would be shared by the server for all the subsequent executions of the query or function call, occurring across the sessions. The result sharing process evolves out as much efficient and performance oriented than the normal repetitive execution process.

The cached result also maintains the latest status of underlying participating tables (SQL) and input parameters (PL/SQL). If the table data is updated, table definition is altered, or function definition is altered, the corresponding cache gets invalidated.

Result Cache Parameters

Oracle uses four initialization parameters to enable result caching feature. This parameter setting is a necessary exercise to allow server to cache the results for the SQL statements and PL/SQL functions executed on it.

  • RESULT_CACHE_MODE – The parameter describes the cache mode to be followed by the server. Currently, there are two behavioral modes available, FORCE and MANUAL.
    .
    In FORCE mode, server caches the query results at high priority. The RESULT_CACHE hint is insignificant in this mode. But, if result is not required to be cached, NO_RESULT_CACHE hint is required.
    .
    In MANUAL mode, developer has to specify the cache hint RESULT_CACHE explicitly.
    .
  • RESULT_CACHE_MAX_SIZE – The parameter defines the maximum portion in SGA, which is allocated for cache memory. If the parameter value is zero, caching feature is disabled. It must be a positive integral value. Following the recommendation from Oracle, the parameter value is advised to be derived from any of the below calculations.
    • 1. 0.25% of MEMORY_TARGET parameter value
    • 2. 0.5% of SGA_TARGET parameter value
    • 3. 1% of SHARED_POOL_SIZE parameter value
  • RESULT_CACHE_MAX_RESULT – The parameter allocates the maximum size of a single result set to be cached. It is expressed as a percentage value of RESULT_CACHE_MAX_SIZE. It must be a positive integral value. By default, its value is 5%.
    .
  • RESULT_CACHE_REMOTE_EXPIRATION – The parameter takes the cached result retention time in minutes. By default, its value is zero.

These parameters can be set at server level by using ALTER SYSTEM command.

ALTER SYSTEM SET RESULT_CACHE_MODE = MAUAL
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 300M
ALTER SYSTEM SET RESULT_CACHE_MAX_RESULT = 10
ALTER SYSTEM SET RESULT_CACHE_REMOTE_EXPIRATION = 100

At session level, the parameters can be set using ALTER SESSION command.

Their value can be viewed by querying the initialization parameters in V$PARAMETERS or simply by using SHOW PARAMETER in command line SQL

SHOW PARAMETER RESULT_CACHE ; 
 
NAME                               TYPE     VALUE 
------------------------------- ---------- ------- 
result_cache_max_result          INTEGER     10 
result_cache_max_size            big INTEGER 300M 
result_cache_mode                string      MANUAL 
result_cache_remote_expiration   INTEGER     100

Caching Performance Views

The dictionary views listed below contain information about the server cache configuration and result caching related information.

Dictionary view

Purpose

V$RESULT_CACHE_STATISTICS

Captures the server cache performance stats, including block count and create count values.

V$RESULT_CACHE_MEMORY

Captures the server cache memory stats

V$RESULT_CACHE_ OBJECTS

Captures the cached result sets information, including status

V$RESULT_CACHE_DEPENDENCY

Captures the dependencies of a result cache

Result Cache in SQL

For caching a result set in an SQL query, Oracle 11g introduces a new optimizer hint RESULT_CACHE. I shall illustrate the hint usage with the below example.

Illustration 1:

The SQL query below selects the salary of the employee 160. Note the usage of RESULT_CACHE hint in the SELECT statement. This is first execution of the query.

SQL> SET TIMING ON
 
SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
    SALARY
----------
      4200
 
Elapsed: 00:00:00.60

The explain plan of the above query shows the usage of the RESULT CACHE operator and generates the CACHE_ID as ‘94waajh6x9swf27stbrhmf46mt’.

The above cache id can be queried in V$RESULT_CACHE_OBJECTS dictionary view. The dictionary view captures and maintains the details of cached result, SQL query, and its status.

SELECT ID, TYPE, CREATION_TIMESTAMP, STATUS, NAME
FROM V$RESULT_CACHE_OBJECTS
WHERE CACHE_ID = ’94waajh6x9swf27stbrhmf46mt’

        ID TYPE     CREATION_  STATUS    NAME
——– ———- ——— ——— ———————————————————-
         1  Result     07-APR-11     Published     SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =160

Note the TYPE, STATUS and NAME of the cached result set. For SQL statement result cache, TYPE is ‘Result’. For successful and valid cached result, STATUS is ‘Published’. NAME column captures the complete SQL statement associated with the cached result.

Now, we re-execute the above query and compare the execution time.

SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
 
    SALARY
----------
      4200
 
 Elapsed: 00:00:00.10

The query execution time has considerably decreased by ~85%. The analytic results demonstrate the power of server caching in application performance.

As stated earlier, if the data in the referenced table undergoes an update, the Cached Result gets invalidated.

SQL> UPDATE EMPLOYEES
     SET SALARY = SALARY+1000
     WHERE EMPLOYEE_ID=160
1 ROW updated.
 
SQL> COMMIT;
Commit complete

Check the status of the above cache id in the V$RESULT_CACHE_OBJECTS view. Now, re-executing the query in cache mode would bring back the ‘Published’ status. Henceforth, it can share the result set on the subsequent executions.

SELECT ID, TYPE, CREATION_TIMESTAMP, STATUS, NAME
FROM V$RESULT_CACHE_OBJECTS
WHERE CACHE_ID = ’94waajh6x9swf27stbrhmf46mt’

        ID TYPE       CREATION_ STATUS    NAME|
———- ———- ——— ——— ———————————————————-
         1 Result     07-APR-11 Invalid   SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =160

Now, re-executing the query in cache mode would bring back the ‘Published’ status. Henceforth, it can share the result set on the subsequent executions.

SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
    SALARY
----------
      5200
Elapsed: 00:00:00.57

Caching Tips in SQL

The most favorable situations for SQL result caching are when the query execution is repetitive and frequent operation, and the tables contain huge volume of data or the SQL query returns less than 2% of the total data. To get most use of caching, the data is expected to be persistent or change at negligible rate. Interestingly, one must be aware of the fact that result cache uses the most recently committed data and the table must not be in floating state.

Result Cache: Unsupported

Being one of the trump features of Oracle 11g, result cache still provides no support for

  • Temporary tables
  • Sequences
  • Pseudo columns
  • Date/Time functions

The reason is the above listed objects are involuntarily randomly changing objects, for which caching can of no use.

PL/SQL Function Result Cache

Oracle 11g can cache the results returned by the PL/SQL stored functions. A stored function can be enabled for result cache by including a new clause RESULT_CACHE to the function definition. The new clause directs the server to cache the function output upon the first call. In the subsequent calls of the function, for the same set of input parameters involved, the result would be retrieved from the server cache without executing the function body.

Note that, the function result is cached along with the set of input parameters. If the function is invoked with a different set of parameters, server would execute the complete function body to arrive at the output. Therefore, it implies that PL/SQL cached result can be uniquely identified as function output, acquainted with the input parameters.

Syntax
CREATE OR REPLACE FUNCTION [FUNCTION NAME]
    RETURN [RETURN TYPE]     
    RESULT_CACHE 
    RELIES ON [TABLE NAME]
    AS
BEGIN
    …
    …
END;

In the syntax, RESULT_CACHE is a mandatory clause to enable result caching for the function. RELIES_ON is an optional clause, which was introduced in Oracle 11g R1to include the names of dependent tables and views. Oracle 11g Release 2 has lifted this restriction by making the clause obsolete.

PL/SQL result cache can be enabled by setting the initialization parameters, exactly in the same manner, as described earlier,. I shall demonstrate the PL/SQL result cache with the help of an illustration.

Illustration 2:

A function F_GET_SAL returns the salary of the employee, whose employee id is the input to the function.

CREATE OR REPLACE FUNCTION F_GET_SAL (P_EMPID NUMER)
    RETURN NUMBER
    RESULT_CACHE
    RELIES ON (EMPLOYEES) IS
    L_SALARY NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE(‘F_GET_SAL called – No cache till yet’);
 
    SELECT SALARY
    INTO L_SALARY
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=P_EMPID;
 
RETURN L_SALARY;
END F_GET_SAL;

 

Now, executing the above function for the employee id 160

SQL>SET SERVEROUTPUT ON
SQL>SET TIMING ON
SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(160));
 
F_GET_SAL called – No cache till yet
4200 
 
PL/SQL PROCEDURE successfully completed. 
 
Elapsed: 00.00.05.12

During this execution, the function is called, executed and server caches the result in the cache memory. Now, we shall see what happens in the second execution.

SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(160));
4200 
 
PL/SQL PROCEDURE successfully completed. 
 
Elapsed: 00.00.00.03

Note the function has not been called and the result appears same as earlier. This time the result comes from the server cache, and not from the function. Amazingly, the execution time difference has improved by ~90%.

If the same function is invoked with a different new employee id, oracle re-executes the function to get the output.

SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(130));
F_GET_SAL called – No cache till yet
2300 
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00.00.04.89

PL/SQL Result Cache: Unsupported

Likewise SQL, PL/SQL result caching feature follows some restrictions. PL/SQL result caching feature cannot be enabled for a function with invoker’s rights or a pipelined table function. The function must not have call by value parameters and the IN parameters of LOB, ref cursor or Collection type. The return type of the function must be of a primitive data type (VARCHAR2, NUMBER, or DATE). 

« « PL/SQL Native Compilation in Oracle 11g
Compound Triggers in Oracle 11g » »

Author Description

Avatar

Free Training

RSSSubscribe 391 Followers
  • Popular
  • Recent
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • Oracle 11g Exception Handling

    June 9, 2011 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Functions and Procedures

    June 7, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT