Tuesday, March 24, 2009

Improving performance with Result Cache in Oracle 11g

Initially in previous versions(before 11g), Oracle Database started off with caching data block in memory, which inturn is used to construct results for user’s request. Even though data blocks are cached in memory, it should construct results from cache which takes from time, resource, locks (latches & enqueues) etc., Oracle has gone to next level of caching in 11g database. Effective from Oracle 11g, Oracle can cache result sets. Oracle can cache the results of both SQL & PL/SQL. In this article, I am going to discuss basics of SQL result cache and I am planning to write about PL/SQL result cache in future articles. Quite obvious, at the end of article, most people will feel that this is the most interesting performance tuning hints while working in Oracle 11g database.

Few basics about result cache
Oracle introduced the memory structure called Result Cache to store the results of SQLs & PL/SQL. The database serves the results for the executed SQL queries and PL/SQL functions from the result cache instead of re-executing the actual query, quite obvious this feature will increase the performance. Memory allocated(can be controlled using RESULT_CACHE_MAX_SIZE parameter) for the result cache is taken from the shared pool. By now one might come to the conclusion that result cache will improve the performance of the repeatly used PL/SQL & SQL. RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the result cache feature is disabled.

Here are some of the parameters that control the behaviour of the result cache.

SQL> col name format a30
SQL> col value format a25
SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                           VALUE
------------------------------ -------------------------
result_cache_mode              MANUAL
result_cache_max_size          5373952
result_cache_max_result        5
result_cache_remote_expiration 0


Brief description about the above parameters:
RESULT_CACHE_MODE: RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan. If set to FORCE, all the queries are cached if they are qualifed and fit in cache. The default is MANUAL, which indicates that only queries with the hint will be cached. 
RESULT_CACHE_MAX_SIZE: Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size. Also query result.
RESULT_CACHE_MAX_RESULT: Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy. (Just to prevent the big result set from throwing out all other small resultset).
RESULT_CACHE_REMOTE_EXPIRATION: Specifies the number of minutes that a cached result that accesses a remote object will remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value may produce stale answers (for example, if the remote table used by a result is modified at the remote database). I personally think it should be set to positive value if remote database is read only database.

From the below query result, it is evident that result cache is part of shared pool.

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  Result Cache: Memory Mgr          200
shared pool  Result Cache: Bloom Fltr         2048
shared pool  Result Cache: Cache Mgr           152

Like almost all memory structure in oracle instance, Result cache is also protected by latches. Latches, being serialization devices, are scalability inhibitors. Don’t just to come to conclusion that Latches are evil; afterall we expect database to maintain ACID property.

SQL> select * from v$latchname where name like 'Result Cache%';

    LATCH# NAME                                 HASH
---------- ------------------------------ ----------
       377 Result Cache: Latch            1545889529
       378 Result Cache: SO Latch          986859868

Oracle 11g provides couple of views to see the behavious/statistics of result cache. Click on the below view names to see the details in Oracle documentation.

APIs for the result cache management is available in PL/SQL package DBMS_RESULT_CACHE.

Couple of examples on how to use APIs on DBMS_RESULT_CACHE package.

SQL> set serveroutput on size 99999
SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 2400 bytes [0.001% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.001% of the Shared Pool]
....... Cache Mgr  = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs =  bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.


SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

DBMS_RESULT_CACHE package has lot more functions/procedures and if you are looking for more, login into 11g using SQL* Plus and execute “desc DBMS_RESULT_CACHE” and the documentation for the same can be found here.

Background Process

SQL> select description from V$BGPROCESS where name like 'RC%';

DESCRIPTION
----------------------------------------------------------------
Result Cache: Background

Watch my blog to see result set in action.

As usual, comments are always welcome.

-Thiru

3 comments:

Biswa said...

Useful information presented concisely - Thanks.

Anonymous said...

Hi,

I've noticed that there is a CORRUPT status in the DBMS_RESULT_CACHE specification that is not documented.

Do you know when the cache could be marked with this status and how to workaround it without downtime.

We've experienced this status by hitting the following Bug:
ORA-00600 [qesrcDO_RemRO] When Result Cache Is Enabled in 11.2 [ID 1373176.1]

Even dbms_result_cache.flush fails with this ORA-00600 error. The only way to get rid of it was to restart the database.

Thanks

Jeremy said...

This helped me in a very high profile situation. Thanks!