New Result Caching Functionality - Oracle 11g

Oracle Database 11g introduces several new caching features that let you utilize memory more efficiently,which results in faster query processing.There are actually two types of caching features: the server result cache that caches SQL query results as well as PL//SQL function results in the SGA and the OCI consistent client cache (client cache) that lets you cache query results on the client.The client cache is especially useful when you’re using large-scale stateles web applications driven by frameworks such as PHP. We’ll review the two types of caching features in the following sections, starting with the server result cache.

Using the Server Result Cache to Enhance Performance

The server result cache is a new concept of Oracle Database 11g that enables the database to cache SQL query and PL/SQL function results in memory.The database serves the results for frequently executed SQL queries and PL/SQL functions straight from the cache instead of reexecuting the actual query or function all over again. You can imagine the dramatic savings in resource usage (for example, IO) as well as the improved response times when you use cached results.Both logical as well as physical IO waits are virtually eliminated since the database fetches the necessary results from memory.The actual server result cache contains a SQL query result cache and a PL/SQL function result cache, both of which share an identical infrastructure.

All database sessions can share the cached results of a query, as long as they share execution plans even partially. Oracle’s internal tests show that the server result cache leads to gains as high as a 200 percent improvement in performance for workloads that are read-intensive.

In Oracle Database 11g, there is a new SGA component called result cache, which is actually part of the shared pool in the SGA. By default,the server result cache uses a small part of the shared pool, even when you don’t explicitly allocate memory for the cache.The defaul maximum size depends on the size of the SGA as well as the memory management method you’re using. However, to set the size for the result cache memory size,you use the result_ache_max_size initialization parameter. If you’re manually managing the shared pool,make sure you increase the shared pool size when you increase the result cache size,since the result cache draws its memory from the shared pool.Once you set the result cache memory size, the automatic shared memory management infrastructure will automatically manage the memory you allocate for the server-side result cache.

The new PL/SQL package DBMS_RESULT_CACHE provides various procedures to administer the result cache feature, including monitoring and managing the cache.The V$RESULT_CACHE_*views let you determine the success of a cached SQL query or a PL/SQL function by determining he cache-hit success of the query or function.

In the following sections, you’ll learn how the two components of the server result cache—the SQL query result cache and the PL/SQL result cache—work. You’ll also learn how to manage the two types of caches, which together make up the server result cache.

SQL Query Result Cache

You can now cache the results of frequently executed SQL query results in the SQL query result cache.It doesn’t take a rocket scientist to figure out that extracting results from the cache takes far less time than actually running the SQL query. You’ll see significant database-wide performance improvements when you use the SQL query result cache.

Query result caching is ideal in the following circumstances:

  • The query processes a large number of rows to yield just a handful of rows or even a single row.
  • The database executes the query frequently, with little or no changes in the data itself.

Thus,although technically speaking you can apply the SQL query result cache to any kind of workload, data warehousing applications are the most commonbeneficiaries of the cache.

SQL Query Result Cache

The database automatically invalidates the cached results of a SQL query whenthere is a change in the data that’s part of the query or a change in any of the objects that are part of the query.

Administering SQL Query Result Caching

Three new initialization parameters—result_cache_mode,result_cache_max_size,and result_cache_max_result—are crucial in managing the server-side result cache.The initialization para meter result_cache_mode determines whether and under what circumstance query result caching will apply.Theresult_cache_max_size initialization parameter determines thesize of the result cache memory allocation. We’ll talk about the three result cache–related initialization parameters in the following discussion.

You can control when the database uses the SQL query result cache by setting the result_cache_mode initialization parameter.If you turn caching on,aResult Cache operator is added to the execution plan of the cached SQL query.The setting of the result_cache_mode parameter determines when the optimizer will add the ResultCache operator to a query’s execution plan.Here are the three possible values for the parameter:

  • The default value of this parameter is manual, which means the result cache operator will be added (results cached) only if you use the new result_cache hint in the SQL query.
  • If you set the value to auto, the cost optimizer will determine when it should cache the query results, based on factors such as the frequency of execution, the cost of execution, and how frequently the database objects that are part of the query are changing.
  • If you set the value to force, the database caches the results of all SQL statements, as long as it’s valid to cache the result. That is, the database will cache the results of all SQL statements where it’s possible to do so.

Here’s how you use the alter system statement to enable result caching in a database:

SQL> alter system set result_cache_mode = force

The result_cache_mode parameter helps you enable the query result cache at the database level.You can enable just session-level caching by setting the parameter using the alter session statement.You can override the setting of theresult_cache_mode initialization parameter by specifying the new optimizer hints result_cache and no_result_cache in order to turn SQL query caching on and off.The hints will override both the auto and force settings of theresult_cache_mode parameter.

If you set the result_cache_mode parameter to manual, you must then specify the result_cache hint in a query so the database can cache the queryresults.The result_cache hint tells the database to cache the current query&r squo;s results and to use those results for future executions of the query or query fragment.If you set the result_cache_mode parameter to auto or force,on the other hand,the database will try to cache the results of all the queries .If you don’t want to cache the results of any query under these circums tances, you must specify the no_result_cache hint in the query.

The no_result_cache hint,like the result_cache hint,over rides the value set for the result_cache_mode parameter Here’s how you’d use the result_cache hint in a query to turn query caching on for that query:

Although the result_cache_mode initialization parameter determines whether the database caches the query results,the result_cache_max_size parameterdetermines the maximum amount of the SGA that the database can allocate to the result cache.Note that the result cache size you set by using this parameter applies to both components of the server-side result cache—the SQL query result cache as well as the PL/SQL result cache.

Even if you don’t set a specific size for the result cache,theresult_cache_max_size parameter always has a positive default size,derived mainly from the memory_target parameter (or the sga_target orshared_pool_size parameter,if you configure one of them).By default,the cache takes 0.25 percent of the memory_target parameter’s value,0.5 percent of the sga_target value,and 1 percent of shared_pool_size,if you set it.However, the maximum allocation can’texceed 75 percent of the size of the shared pool.In the following example, we haven’t used the result_cache_max_size parameter in the parameter file.However,the database internally allocated a default amount of 393,216 bytes for the parameter (Maximum Cache Size in the output).In the example,we generate a server result cache usage report emplo ying the DBMS_RESULT_CACHE package:

Since we haven’t cached any SQL query results in the cache, the cache memory is 0 bytes, as shown in the output.The maximum value for this parameter is dependent on the operating system you’re using.You can completely disable result caching of any kind by explicitly setting the result_cache_max_size parameter to zero. After setting the parameter to zero, the memory report will show the following:

The memory_report procedure displays the memory usage reports for the result cache.The result_cache_max_size parameter is static, so you can’t modify it without restarting the database.

You may be wondering,what if a single large SQL query result takes up the entire result cache? By default,any single query result is limited to amaximum of 5 percent of the result ache(the size of which is set with the result_cache_max_size parameter).However,you can modify this default value by using theresult_ cache_ max_ result parameter,which specifies the maximum proportion of theresult cache any single query result can take up, in percentage terms.You can set any percentage value between 0 and 100 for the result_cache_max_result parameter.

There is a third result cache–related initialization parameter calledresult_ cache_ remote_ expiration. You can use this dynamic initializationparameter to specify the time (in minutes)for which query results involvingtables in remote databases stay valid. The default value of this parameter is 0, meaning the database won’t cache any results involving remote objects. Exercise care when you set this parameter to a positive value, since you may get incorrect results if the remote tables in the query have been modified in the meanwhile.

Viewing the Result Cache with EXPLAIN PLAN

You will notice that the explain plan for any query with a result_cache hint will contain the ResultCache operator,as shown in the following example:

You can use the cache_id value provided in the explain plan to find details about the cached query results using the V$RESULT_CACHE_OBJECTS view, as shown here:

The STATUS column’s value (Published) in the V$RESULT_CACHE_OBJECTS view reveals that the result for this query is available for use. The otherpossible values for the STATUS column are new (result under construction), bypass, expired and invalid.The NAMESPACE column shows that this is a SQL state ment.The other value for the NAMESPACE column is PLSQL..

Examples of Query Result Caching

We’ll use a simple example to show how the SQL query result cache feature works.First specify result caching for your query using the result_cachehint. Before you create the newly cached SQL query,flush the server result cache as well as the shared pool so you start fresh with the new query whose results you want to store in the server result cache.

The DBMS_RESULT.CACHE_FLUSH procedure purges the server result cache.In this example,we used a simple invocation of the flush procedure,but you can also specify that the database retain the free memory in the cache as well as the existing cache statistics. Alternately, instead of flushing the entire contents of the server result cache,you can execute the invalidate procedure instead to specify only those results that depend on a specified database object or are owned by a specific user.You most likely are familiar with the alter system flush shared_pool statement,which flushes the shared pool, so you have no prior executions of the test SQL queries in the shared pool already.

Here’s the test SQL query, with the result_cache hint:

Note that we provided the optional q_name parameter within the result_cache hint to specify a name (Query1) for our new SQL query whose results the data base will store in the server result cache.If you check the execution plan for the new SQL query, you’ll see that the query uses the result_cache optimizer hint:


Now that you know that the execution plan includes the result cache, execute the actualquery, as shown here:

When you first execute the query with the result_cache hint, the result_cache operator checks the server result cache to see whether it already contains the results for this query.If the results are present there,of course, no execution is necessary and the query returns the results from the server result cache. In this example,since there are no prior results in the server result cache (we actually purged the result cache before we issued our query), the first execution ofthe test query will execute the SQL statement and store the results in the server result cache. Execute the same statement again so it can use the cached results now. Query the V$RESULT_CACHE_STATISTICS view to examine the usage statistics for the new query:

The create count success column shows a value of 1,meaning the data base successfully created one query whose results will be cached in the server result cache.

The Find Count column also has a value of 1,meaning the cache results were found successfully by the second execution of the query.

The example here used a simple SQL query,but you can just as easily cache the results of an inline view as well by specifying a result_cache hint.Here’s an example:

In the inline view shown here,some of the optimizations such as view merging and column projec tion aren’t employed.Thus,the view takes a longer time to execute the first time,but the cached results mean that later executions will finish very fast.Note that in addition to caching results of the queries with identical predicate values,the caching feature ensures that queries usinga different predicate value for the column inside the where clause(prod_category) will also get back their results quickly.

Here’s a simple example showing the dramatic improvement in query performance when you use the result cache.

  1. First,check to ensure that the database isn’t currently caching query results:
  2. Note that result_cache_mode has the value MANUAL, meaning result caching is turned off at the database level.You can override this by using the result_cache hint in your queries.Of course,you can set the value of the result_cache_mode parameter to either auto or force,as explained earlier, to turn caching on at the database level.

  3. Execute the following query first,and check the time it takes to get the results back:
  4. The elapsed time is almost 20 seconds for this query.

  5. From a different session,turn on system-wide server query result caching using the following command:
  6. Return to the first session to run your test query again,and note the execution time:
  7. Issue the query again,now that the query results are cached by the database,and check the timing:

Note the dramatic improvement in query timing because of the use of the query result cache,since all the database had to do was merely get the cached result set for the server result cache.

Managing the Query Result Cache

You manage the SQL query result cache through the DBMS_RESULT_CACHE package (we’ve used this package earlier in this section),which provides you statistics that help manage the memory allocation for the query result cache.Here are the key procedures of the DBMS_RESULT_CACHEpackage:

  • exec dbms_result_cache.flush:Removes all results and clears the cache memory

Managing the Query Result Cache

  • exec dbms_result_cache.invalidate(ABC','TESTTAB'):Invalidates cache results for a specified object.
  • select dbms_result_cache.status from dual:Shows status of the result cache.
  • exec dbms_result_cache.memory_report: Shows result cache memory usage You can also use the following views to find out details about the query result cache:
  • V$RESULT_CACHE_STATISTICS: Shows cache settings and memory usage statistics.
  • V$RESULT_CACHE_OBJECTS: Lists all cached objects and their attributes.
  • V$RESULT_CACHE_DEPENDENCY: Shows the dependency information
    between the cached results and dependencies.
  • V$RESULT_CACHE_MEMORY: Shows all memory blocks and their statistics.

The V$RESULT_CACHE_OBJECTS view shows both cached results and alldependencies.The STATUS column can take the following values:

  • new:The cached result is still being built.
  • published:The cached result is available for use by other queries.
  • bypass:Other queries will bypass the cached result.
  • expired:The cached result has crossed the expiration time limit.
  • invalid:The cached result is unavailable for use by other queries.

Here’s a query that shows the type,status, and name of objects in the result cache:

The output of the previous query shows that there are two cached results in the result cache,and that both are available for use.

The following limitations apply with regard to the operation of the query result cache:

  • When dealing with bind variables,a query can reuse a cached result only for identical variable values.Different values for the bind variables or different bind variable names lead to a cache miss.
  • Oracle won’t cache results if the query uses a noncurrent version of the data because of the enforcement of the read consistency principle.
  • Oracle won’t cache results if there are pending transactions on the tables that are part of
    the query.
  • • Oracle won’t cache flashback queries.
    • All queries containing the following are ineligible for query result caching:
    • Nondeterministic PL/SQL functions
    • Currval and nextval
    • SQL functions such as sysdata and current_date
    • Temporary tables
    • Dictionary tables

PL/SQL Function Result Cache

PL/SQL functions sometimes return results of computations performed by queries that involve PL/SQL functions. When you’re dealing with frequently executed PL/SQL functions whose queries access infrequentlychanging data, the PL/SQL function result cache feature lets you specify that the function’s results be cached.Once you do this, when the data base invokes a function with similar arguments as the cached function,instead of re executing that function, the database returns the cached results of the ear lier execution of that function instead.Of course,performance would be dramatically improved. If any of the tables that are part of the functions queries undergo a DML change, the database purges the PL/SQL result cache.

You can cache the results of PL/SQL functions executed by a session in the SGA and make them available to all other sessions in the database.You don't have to design a cache management policy to take advantage of this new feature—it is completely automatic. Frequently invoked complex PL/SQLfunctions that operate on relatively fixed data are ideal candidates for caching.

Enabling PL/SQL Result Caching

As we mentioned earlier,both server-side result caches—the SQL query result cache and the PL/SQL result cache—share the same infrastructure.

The database uses the same result cache buffer for both SQL query result caching as well as PL/SQL function result caching.Thus, PL/SQL result caching is automatically enabled in the database, with a default amount of cache size memory (based on the SGA size) as long as you don’t explicitly turn caching off by setting the result_cache_max_size parameter to zero.

An Example

You can activate the PL/SQL function result cache by using the result_cache clause in a PL/SQL function,as shown in the following example:

The previous code enables result caching on the function count_emp.The relies_on clause is optional,and you use it to indicate that the function relies or depends on the table(emp in this example) for its results.You can use the relies_on clause to specify tables or views on which the function depends.You can also specify a set of tables or views with the relies_on clause.

The clause ensures that the database purges (invalidates) the server result cache when the dependent table undergoes a DML operation. This automatic purging of the results maintains cache consistency.

Once you create a function that populates the result cache using theresult_cache option,it’s time to test the function.To do this,you must call the PL/SQL function from inside a SQL query,as shown here:

When you issue the previous SQL query for the first time,it invokes the PL/SQL function and stores the results it retrieves in the result cache.When you subsequently execute the same query,it’ll simply retrieve the results directly from the result cache.You can examine the V$RESULT_CACHE_STATISTICS view,as in the case of the SQL query cache,to view the usage statistics for the PL/SQL result cache.All columns such as create count success and find count hold the same meaning for the PL/SQL result cache, as they did for the SQL query result cache.

If you apply a code patch to a PL/SQL unit such as a procedure, then theresults in the PL/SQL result cache won’t reflect the correct results for thatPL/SQL unit, unless you bounce the instance. In cases such as these, you can bypass the result cache, as shown here:

  1. Place the result cache in bypass mode:
  2. SQL> exec dbms_result_cache.bypass(true) PL/SQL procedure successfully completed.
  3. Flush the existing cached results:
    SQL> exec dbms_result_cache.flush PL/SQL procedure successfully completed.
  4. Apply the PL/SQL code patch.
  5. Turn off the PL/SQL result cache bypass mode:
  6. SQL> exec dbms_result_cache.bypass(false) PL/SQL procedure successfully completed.

When you turn result cache bypassing on, the database doesn’t use any cached results,and no results are stored there until you turn bypass mode off. Please refer to Chapter 11 for a more programmatic discussion of the PL/SQL cross-section result cache, as well as a listing of the limitations of the cache.

OCI Consistent Client Cache

The OCI consistent client cache,also called simply the client cache, extends server-side query caching by letting the client machines leverage their memory to cache query results.

The client cache resides in the OCI client process memory and can store tables or entire data sets,usually on the application server.Instead of having the server repeatedly execute frequently usedSQL queries,the client simply gets the results from the client cachedirectly, saving unnecessary round-trips between the client and the server. Increased scalability results from the lower CPU usage.Oracle’s bench mark studies show that for simple queries such as the ones that use lookup tables,involving usually a read but not a write,performance gains could be dramatic, as high as a 500 percent reduction in elapsed time and a 200 percent drop in CPU time.

Queries from different client sessions that have a match in things such as their SQL text and bind values share the cached query result sets.This leads to a significant reduction in response time for frequently executed SQL queries as well as an increase in scalability.

The best aspect of the client cache feature is that you don’t have to make any changes to your application code to take advantage of it.Any application that uses an Oracle Database 11g OCI client can avail of the client cache.The OCI clients include ODBC, ODB.NET, PHP, JDBC-OCI (Thick) Driver, and various Oracle precompilers.

The OCI client cache is particularly suitable for queries involving lookup tables that produce repeatable result sets or small static result sets.This type of caching is also ideal for frequently executed queries.

Once you enable client result caching,the result can be cached on the client or on the server.When you think the results aren’t consistent any longer or simply incorrect,you can disable the client caching feature.

The Mechanics of Query Result Set Caching

You use the following two new initialization parameters to configure the client result cache

  • client_result_cache_size:You can turn on client result caching by setting this parameter to a value greater than zero.The parameter sets the maximum size of the client per-process result cache size in bytes.By default, the para meter has a value of zero,meaning the client cache is disabled by default (in contrast,the server cache is enabled by default).The value of this para meter (in bytes) sets the limit on the result set cache for each client process.The database assigns all OCI client processes a result cache that is equal to the value of this parameter.The client_result_cache_size parameter ranges from a minimum value of 0 to a maximum value that’s dependent on the operatingsystem. You must restart the database after setting the initial value for this para meter or after making an adjustment, since it’s a static initialization par ameter.You can override the client_result_cache_size parameter with the client configuration parameteroci_result_cache_max_size.
  • client_result_cache_lag:This parameter sets the maximum time since the latest round trip to the server after which the OCI client query makes a fresh round-trip to get new data for the cached queries on the client.

You can also use an optional client configuration file(or make the settings part of the sqlnet.ora file on the client) to enable and disable OCI client caching for OCI client processes that use the file.

The settings in the client configuration file override the client cache settingsn made through the server parameter file on the server. Any OCI client process that uses the configuration file will have OCI client caching enabled for it.You can set the following optionalparameters for clients in the client configuration file.

  • oci_result_cache_max_size: Sets the maximum size of the query cache for a process(in bytes).This parameter overrides the value set for theclient_result_cache_size initialization parameter on the server.
  • oci_result_cache_max_rset_size: Sets the maximum size (in bytes) of a single query result in the query cache for a process.
  • oci_result_cache_max_rset_rows: Sets the maximum size of a query result set(in rows)for a process.

Enabling and Disabling Client Caching

OCI applications enable and disable client result caching by using OCIStmt Execute() mode with the mode values.

OCI_RESULT_CACHE and OCI_NO_RESULT_CACHE to override the SQL hints no_result_cache and result_cache.

Intra Unit Inlining

Intra unit inlining,also called procedure inlining when applied to PL/SQL procedures,is another Oracle Database 11g new feature that promises tremendous gains in PL/SQL performance.Oracle claims that by using this new feature appropriately,you could achieve performance gains by a factor of two to ten times. Intra unit inlining is a process that replaces a call to aprocedure, function, sub routine, or method with a copy of the body of that object, which always runs much faster than the actual procedure, function, or subroutine.

The database automatically finds candidates for inlining,but you can influence the process with the initialization parameterplsql_optimization_level and the pragma inline directive.Oracle suggests you inline only small, frequently run procedures.The PL/SQL performance tool plstimer can help you identify procedures that may benefit from inlining.

By setting the initialization parameter plsql_optimize_level to a value of 3, you can make the PL/SQL complier search for PL/SQL calls that are candidates for inlining and inline the most promising of those calls.You can also specify the plsql_optimization_level parameter dynamically inside a procedure,as shown here:

You can also use the pragma inline directive inside a sub routine to direct the data base to inline the subroutine.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics