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:
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.
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:
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:Explained.
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.
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.
The elapsed time is almost 20 seconds for this query.
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:
The V$RESULT_CACHE_OBJECTS view shows both cached results and alldependencies.The STATUS column can take the following values:
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:
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.
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:
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
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.
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.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.