| |
 |
|
Oracle result_cache
tips
Oracle11g Tips by Burleson Consulting
November 20, 2011
|
These are work in
progress excerpts from
the book "Oracle
11g New Features" authored by John Garmany, with Oracle ACE's Steve
Karam, Lutz Hartmann, V.J. Jain and Brian Carr.
Oracle 11g has
over 480 new enhancements and it's often difficult to tease out the
important new features. But one thing is clear; the
result_cache syntax may become very important to the Oracle
developer, yet another tool that will join related caching and
pre-summarization techniques:
Oracle notes that the result_cache feature is
very different from traditional caching and pre-summarization
mechanisms and that the result_cache hint causes Oracle to
check and see if a result for your execution plan already resides in
the cache. If so, Oracle by skip the fetch step of execution
and call your rows directly from the cache instead of reading the
rows from the daabase buffers,
The Oracle
result_cache documentation notes that creating a result cache
is conceptually similar to other existing Oracle data passing tools
such as a shared PL/SQL collection (except that result_cache
is a shared array) or a materialized view (except that
result_cache in stored in SGA RAM).
The result cache is new to Oracle 11g and provides enhanced
query performance for SQL and PL/SQL applications by caching the
results of SQL queries into memory. The following Oracle 11g
database initialization parameters affect the operation and
function of the result cache for Oracle 11g:
- client_result_cache_lag
- client_result_cache_size
- result_cache_max_result
- result_cache_max_size
- result_cache_mode
- result_cache_remote_expiration
Unlike
a PL/SQL collection which reside in private PGA RAM, a result cache shareable and is stored in SGA
memory. Unlike materialized views, the result_cache output is stored in the
RAM of the SGA, and in 11g RAC, each node will have it's own private
area for storing result_cache output.
Oracle
saves query output with materialized views, global temporary tables, PL/SQL
collections and with
the new 11g result cache.
There are two
ways to visualize a 11g result_cache output:
-
A sharable
PL/SQL collection - Oracle PL/SQL allows for result sets to be
saved in RAM for later use by the session using an array heap
called a "collection". The
result_cache hint expands this functionality to allow for
inter-session access to pre-summarized row data in RAM. Also,
PL/SQL collections (arrays) were stored in the PGA, whereas the
result_cache output is stored in the SGA region.
-
An ad-hoc,
RAM-based Materialized View - Unlike traditional
materialized views where tables are pre-joined and stored on
slow disk, the result_cache hint allows the denormalize
for of the table rows to be stored in super-fast RAM with over
100x faster access speeds.
Unlike the
process of writing a
materialized view to a mechanical platter disk, the result
cache is stored in super-fast RAM,
alleviating the need to index the result set, as you might with a
materialized view on a disk. Also, creating a
result cache does not require direct DBA intervention, just the
setting of four new init.ora parms.
Since the result
cache is all about improving performance, let's examine what we can
expect when we implement this feature.
Result_cache speed; your mileage
may vary
Oracle's claims
of super-faster result set delivery are predicated on the assumption
that without the result set, the database will experience extra disk
I/O and/or a CPU overhead latency. ( The CPU latency is
because we must reconstruct the result set from the RAM buffer
blocks). There are also concerns that using the
result_cache approach may not scale-up for large dynamic
systems.
In general, a
summary or aggregation will precipitate a full-table scan which will
indeed incur physical disk I/O.
But the
performance will be different when using the result cache to
pre-join tables together. With a properly-sized data buffer
(per
v$db_cache_advice or AMM) will already have the working set
cached, and the "materialization" of a result cache involves no disk
I/O. Instead, Oracle will assemble the result set RAM-to-RAM,
joining tables in memory.
Historically, these logical I/O's
are recorded as "consistent gets", and they tend to be CPU
intensive. For small databases, Oracle 10g's automatic memory
management (AMM) should optimize the size of your RAM buffer, and
for large databases, the DBA optimizes the data buffer cache sizes
using intelligent sizing
scripts:
An optimized db_cache_size should have all
result cache blocks in RAM already
Internally, result_cache waits
are displayed with the "RC enqueue", and as shop being deploying the
result_cache approach we will learn more about it's
scalability in production environments.
The
11g scalability documentation also suggests that the
result_cache can aid in performance and scalability:
"Internal tests show that
the use of Server Result Cache can enhance the performance for
read-intensive workloads by as much as 200%."
The 11g new
features guide also notes that "Concurrent, multi-user applications
that use this [result_cache] feature experience better
response times. Applications that implement a session-private scheme
consume significantly less memory by using this feature and,
therefore, experience improved scalability.?
Administrators can monitor and manage
the contents of the Server Result Cache via a new PL/SQL interface
and you can view result_cache details using the
v$result_cache_objects, and other related views such as
v$result_cache_statistics, v$result_cache_memory,
v$result_cache_objects, and v$result_cache_dependency
Let's explore more details on the
tradeoffs between using the result_cache and other caching
mechanisms.
Result
cache vs. Materialized Views
One of the best
uses for materialized views is reducing the need
to repeatedly summarize or aggregate data and to pre-join highly
normalized tables. This idea of pre-joining tables and
pre-summarizing data is not new, but the mechanism is quite complex.
Storing the pre-joined tables is the easy part, sharing the
materialization is more difficult.
Pre-computing and saving result sets
In materialized
view, Oracle created a transparent "query rewrite" mechanism to allow SQL to
automatically re-write SQL to access the summary. In PL/SQL arrays, a
subsequent procedure could refer to the collection data using "ref
cursors", a call by reference, the act of passing a pointer
to the array, rather than the data itself.
Anyway, let's examine how the
result cache compares to a PL/SQL collection.
Result
cache vs. PL/SQL Collections
PL/SQL guru
Steve Feuerstein did a quick test of PL/SQL collections
(arrays) vs. the result cache and came-up with these rules of
thumb for result cache performance:
-
The
Oracle Database 11g PL/SQL function result cache is, indeed,
much faster than repetitive querying. In this test, it was
over an order of magnitude faster.
-
A
packaged collection cache is even faster, most likely
because the PL/SQL runtime engine can access the data from
PGA memory rather than SGA memory. Unfortunately, this also
means that the consumption of memory occurs on a per-session
basis, which is not very scalable.
-
The
packaged collection approach consumed additional PGA memory,
but the Oracle Database 11g function result cache did not.
Let's take
a closer look at the 11g result cache and see when to use it.
How to use
the result cache
Oracle
notes that the result_cache functionality can be enabled
in two ways:
-
Alter session - you can issue the command "alter
session cache results;" to cache your session data.
-
PL/SQL - You can create a PL/SQL function using the
result_cache keyword to store the result, and use the
new relies_on keyword to tell Oracle what tables will trigger an
invalidation (should the rows become out-of-date with the
current table data).
-
SQL
- You can add the /*+ result_cache */ hint to any SQL
statement, and invoking
the result_cache hint is far easier than creating a PL/SQL array
or materialized view because the syntax is very straightforward:
select /*+ result_cache */
stuff
from tab1 natural join tab2;
Note: Only the PL/SQL uses the relies_on keyword, and it
is unclear how Oracle manages the invalidation mechanism with
SQL and "alter session" syntax.
With all of
these choices, when does it make sense to use a result_cache
hint in lieu of a materialized view or PL/SQL array?
Remember, the
result_cache hint is intended for relatively static (unchanging) data,
making it a great solution for data warehouse and DSS
applications, or any systems where data is loaded during a batch load
and queries are run in a non-DML environment. In general,
the benefits of result cache include:
-
Removing repeated table joins - The result_cache
hint is great for denormalizing a legacy database.
Remember, back in the days of Oracle7, disk was over a
hundred times more expensive than today and IT shops
deliberately designed their schemas to have as little
redundancy as possible (third normal form, or 3NF).
The result_cache hint can be used to pre-join 3NF
tables together into a redundant 1NF representation, and 0NF
is possible if you define result sets with the VARRAY,
repeating columns.
-
Caching deterministic function output - For PL/SQL
deterministic functions, using result_cache can greatly
reduce the overhead of repeated re-computation of static
values.
-
Reducing CPU overhead - Without a result cache to store
pre-joined or pre-summarized results, the raw data must be
fetched from the data buffer (db_cache_size), causing an
increase in CPU consumption.
-
Reducing disk I/O - When compared to materialized views
(stored on disk) a result set in immediately available in
RAM without any physical I/O.
For more details on tuning with
materialized views, see my book "Oracle
Tuning: The Definitive Reference".
Usage tips
for result cache
The
result_cache promises to be a great tool for the
Oracle developer because of the shortcomings of traditional
methods. Materialized views required DBA intervention, a
stale refresh mechanism and a sophisticated query rewrite
capability and PL/SQL arrays may not have a usage scope beyond
the control of the creating session.
Given these
tradeoffs, let's take a look at some general guidelines for
using the result cache instead of traditional materialized views
or PL/SQL arrays.
Caveats to
the result_cache approach
Let's take
a closer look at how the result_cache output behaves in a
production environment. As the world's most robust
database, Oracle offers something for everyone, and the
result_cache may not be right for you. Some of the
limitations and shortcomings in 11g include
-
Limited
Scope - Unlike a PL/SQL collection that can only be shared
with complex coding, the result_cache is
transparently sharable
by other concurrent sessions.
-
Limited Persistence - There is an issue of
long-term persistence, since the result_cache output will survive
only for the number of minutes specified by
result_cache_remote_expiration. In any database
with even meager update activity against the source tables,
we can expect that the result cache would be invalidated by
DML far before the expire time. Also, the Oracle
documentation says that a result cache is eligible for a
page-out, indicating that the pool for the result cache uses
an LRU algorithm (i.e. a least-recently-used FIFO queue).
-
Blanket Invalidation - Just like a materialized view, the row data
from a result_cache output could quickly become out-of-date
(stale). However, The documentation says that the
result_cache invalidation is triggered at the table level,
so it?s possible for a result_cache will be marked as
invalid whenever any table rows are changed, even if the
changed rows do not participate in your result set.
This limits the use of the result cache to systems with very
little runtime updates.
-
Restricted function usage - The
Oracle documentation lists these restrictions for using
result_cache in a PL/SQL function:
- It is not defined in a
module that has invoker's rights or in an
anonymous block.
- It is not a pipelined
table function.
- It has no
OUT
or
IN
OUT
parameters.
- No
IN
parameter has one of the following types,
BLOB, CLOB, NCLOB, REF CURSOR, Collection
Object, Record.
- The return type is none
of the following, BLOB, CLOB, NCLOB, REF
CURSOR, Object, Record or PL/SQL collection
that contains one of the preceding
unsupported return types.
Ideal databases for using result_cache
Given the benefits and limitations of the result cache,
it appears that the result cache functionality will be best for databases with
these characteristics:
- Legacy (highly normalized) designs
- RAM materializations are great for databases that were designed to be
highly normalized. Back in the old days, DBA's made a conscious
trade-off to minimize disk space by removing redundant data, knowing the
re-assemble would cost extra CPU cycles. With Materialized view
denormalization, it's not uncommon to do a 12-way table join once, thereby
saving overhead for thousands of subsequent SQL statements.
- Largely read-only tables - Remember, a
change to any row in any of the source tables invalidates the result cache
regardless of whether the changed rows participate in the result cache.
The result cache is ideal for large ad-hoc databases where data is loaded
nightly and run read-only during the processing window.
- Databases with CPU bottlenecks -
Remember, the result_cache may not make much performance improvement if you
already have a large enough data cache to buffer your
working set
of frequently-referenced data blocks.
- I/O bound databases - Shops using
solid-state flash storage will find materialized views a far better choice than
the result cache since all of their materialized views will reside in RAM.
You can also cache materialized views by defining them in your KEEP pool.
When not to use result sets
here are several types of database
configurations that are sub-optimal for result set caching:
- Volatile databases - Remember, a
result cache disappears when ANY DML is executed against the relies_on
table. High volume ML databases (e.g. OLTP) should consider
materialized views, segregated into their
KEEP pool for full caching.
- Solid-state databases - In a databases
using solid-state disk or a large data buffer, the result cache can be
rebuilt from the RAM blocks in just a few microseconds. The result
cache functionality can help these shops by reducing the CPU overhead
associated with joining tables from the data buffer. For more details
on solid-state Oracle, see my book "Oracle
Solid-state Disk Tuning" and "Oracle
Tuning: The Definitive Reference".
In sum, the result_cache feature will be
very useful for specific types of Oracle shops, yet another choice within the
world's most flexible and robust database.
For more details on tuning Oracle with RAM
caches, see my book "Oracle
Tuning: The Definitive Reference".
result_cache usage References:
As a reference, let's examine how
to set-up and use the result cache feature.
Configuring the database for result_cache
There are
several new 11g parameters required to enable the result_cache
feature:
-
result_cache_max_size - specifies the maximum amount of SGA
memory (in bytes) that the Result Cache can use. The
defaylt is zero, but you can use the "alter system set
result_cache_max_size" to enable the feature at runtime.
-
result_cache_max_result - This specifies the maximum percentage of
the Result Cache that any single result can use.
-
result_cache_mode - This can be set to "manual" or
"force", it specifies when a ResultCache operator is spliced
into a query's execution plan.
-
result_cache_remote_expiration - This is the value, in
minutes that a result cache will be alive.
The result_cache views include v$result_cache_statistics, v$result_cache_memory,
v$result_cache_objects, and v$result_cache_dependency plus the equivalent
RAC views gv$result_cache_statistics,
gv$result_cache_memory, gv$result_cache_objects
and gv$result_cache_dependency.
Note that each
RAC instance will have a private area in each
SGA for result set caching.
Using the dbms_result_cache package
Oracle has a new 11g dbms_result_cache
built-in package that allow you to interrogate,
bypass and flush the result cache:
See my
related notes on materialized views to appreciate the difference
from the result_cache feature:
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
