Result Caches and Improved SQL Tuning

Result Caches

I've often wished that the Oracle database would pro-vide a method to retain in memory the result set from a complex query that contains what I like to call reference information. These are data that hardly ever change, but must still be read and used across multiple applications. For instance, a list of all country codes and their corresponding names for lookup when processing addresses for new international customers, or a list of all the zip codes of North India. Oracle Database 11g fills this gap with three new structures called result caches, and each structure has a different purpose:

# The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.

# The PL/SQL function result cache can store the results from a PL/SQL function call.

# Finally, the client result cache can retain results from queries or functions on the application server from which the call originated.

By retaining result sets in these in-memory caches, the results are immediately available for reuse by any user session. For user sessions that connect to the database through an application server, the client cache permits those sessions to simply share the results that are already cached on the application server without having to reissue a query directly against the database. These result caches therefore hold great promise for eliminating unnecessary "round trips" to the database

server to collect relatively static reference data that still.

Improved SQL Tuning

If you've already experienced the advice for SQL performance improvements that Oracle Database 10g's SQL Tuning Advisor and SQL Access Advisor provide, you'll be pleasantly surprised with Oracle Database 11g's enhanced SQL tuning capabilities. Here's a brief sample-

# SQL statements can now tune themselves via an expansion to the automatic SQL tuning features that were introduced in Oracle Database 10g.

# Statistics for the Cost-Based Optimizer (CBO) are now published separately from being gathered. This means that recomputed statistics for the CBO will not necessarily cause existing cursors to become invalidated.

# Multi-column statistics can be collected for two or more columns in a table. This gives the CBO the ability to more accurately select rows based on common multi-column conditions or joins.

# SQL Access Advisor can now make recommendations on how partitioning might be applied to

existing tables, indexes, and materialized views to improve an application's performance.  

# Oracle Database 11g now supports retention of historical execution plans for a SQL statement. This

means that the CBO can compare a new execution plan against the original plan and, if the old plan still offers better performance than the new one, it can decide to continue to use the original execution plan.