This tutorial describes how Result Cache improves query execution time and application performance in Oracle Database 11g.
Approximately 40 minutes
This tutorial covers the following topics:
|
Overview |
|
Prerequisites |
|
Implementing SQL Query Result Cache |
|
Using PL/SQL Function Result Cache |
|
Summary |
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
What Is Result Cache?
Result Cache enables caching SQL query and PL/SQL function results to be stored in memory. Subsequent executions of the same query or function can be served directly out of the cache, improving response times. This technique can be especially effective for SQL queries and PL/SQL functions that are executed frequently.
By default the server-side Result Cache is configured to use a very small portion of the shared pool. You can manually set the result cache memory size using the RESULT_CACHE_MAX_SIZE initialization parameter. Setting RESULT_CACHE_MAX_SIZE to 0 disables the server-side Result Cache.
Before you perform this tutorial, you should:
| 1. | Install Oracle Database 11g |
|
| 2. | Download and unzip the res_cache.zip file into your working directory (that is, wkdir ) and navigate into your working directory. |
|
The new SQL Query Result Cache enables explicit caching of queries and query fragments in an area of the shared pool called Result Cache Memory. When a query is executed the result cache is built up and the result is returned. The database can then use the cached results for subsequent query executions, resulting in faster response times. Cached query results become invalid when data in the database object(s) being accessed by the query is (are) modified.
You can enable Query Result Cache at the database level using the RESULT_CACHE_MODE initialization parameter in the database initialization parameter file. The same parameter can also be used at the session level using the ALTER SESSION command. RESULT_CACHE_MODE can be set to:
AUTO � the optimizer will decide based on a number of factors whether or not to cache the result. Decision factors include the frequency of query execution, the cost of building the result and the frequency of changes against the underlying database objects.
MANUAL � (default) you have to add the RESULT_CACHE hint to your queries in order for results to be cached or to be served out of the cache. The RESULT_CACHE hint can also be added in sub queries and in-line views.
FORCE � results are always stored in the Result Cache Memory if possible.
The use of the SQL Query Result Cache introduces the ResultCache operator in the query execution plan.
Perform the following steps to understand the use of Query Result Cache
| 1. |
Open a terminal window and log on to SQL*Plus. Connect to the database as SYS. (In this OBE the password was set as oracle). $ sqlplus sys /oracle as sysdba
|
|
| 2. |
Clear the Shared Pool and the Result Cache by running the flush.sql script . SQL>@flush
|
|
| 3. |
Examine the memory cache by running the baseline.sql script. Cache Memory is 0 bytes because nothing has yet been cached. SQL>@baseline.sql
|
|
| 4. |
Run the plan_query1.sql script and examine the execution plan. The query uses the RESULT_CACHE optimizer hint. SQL>@plan_query1
| |
| 5. |
Run the plan_query2.sql script and examine the execution plan. This query also uses the RESULT_CACHE optimizer hint. SQL>@plan_query2
|
|
| 6. | Now execute both queries by running the query3.sql script. SQL>@query3
|
|
| 7. | Query V$RESULT_CACHE_STATISTICS to view memory allocation and usage statistics. Run the v_stat.sql script. Note that the CREATE COUNT SUCCESS column has a value of 2. This is the number of cache results successfully created (one for each query statement). SQL>@v_stat.sql
|
|
| 8. | Now run the query3.sql script again, re-executing both queries it contains. SQL>@query3
|
|
| 9. | Query V$RESULT_CACHE_STATISTICS again to view memory allocation and usage statistics. Run the v_stat.sql script. Note that the FIND COUNT column now has a value of 2. This is the number of cache results that were successfully found (one for each query statement). SQL>@v_stat.sql
|
|
Oracle Database 11g provides the ability to mark a PL/SQL Function to indicate that its result should be cached. This allows lookup rather than recalculation the next time the function is called with the same parameter value. The cache is system-wide so that all sessions invoking the function can benefit from the cached return value.
You can activate the PL/SQL Function Cache by using the RESULT_CACHE option in the function declaration. In addition you can indicate you want the cache to be purged when a dependent table experiences a DML operation by using the RELIES_ON clause.
Perform the following steps to understand the use of the PL/SQL Function Cache.
| 1. |
Clear the Shared Pool and the Result Cache by running the flush.sql script . SQL>@flush
|
| 2. |
Create a PL/SQL function by running the cre_func.sql script . The function uses the RESULT_CACHE clause, indicating that its results should be cached. It also uses the RELIES_ON clause, specifying the table or view the function result depends on. SQL>@cre_func
|
| 3. |
Call the PL/SQL function inside a query by running the call_func.sql script . SQL>@call_func
|
| 4. |
Verify memory allocation by running the baseline.sql script. Notice the Result Cache has been built for a PL/SQL block. SQL>@baseline
|
| 5. |
Query V$RESULT_CACHE_STATISTICS to view memory allocation and usage statistics. Run the v_stat.sql script. Note that the CREATE COUNT SUCCESS column has a value of 1. This is the number of cache results successfully created. SQL>@v_stat
|
| 6. |
Call the PL/SQL function again by running the call_func.sql script . SQL>@call_func
|
| 7. |
Query V$RESULT_CACHE_STATISTICS to view memory allocation and usage statistics. Run the v_stat.sql script. Note that the FIND COUNT SUCCESS column has a value of 1. This is the number of cache results that were successfully found. SQL>@v_stat
|
| 8. |
Query V$RESULT_CACHE_OBJECTS to view memory allocation and usage statistics. Run the v_obj.sql script. Notice the SCAN_COUNT column has a value of 1, meaning the cached result has been used. SQL>@v_obj
|
In this tutorial, you learned how to:
|
use the SQL Query Result Cache | |
|
use PL/SQL Function Result Cache | |
Place the cursor over this icon to hide all screenshots.