Sunday, September 02, 2007

Query Result Cache

I was reading this article and decided to publish some more information about the SQL Query Result Cache. It was pending as a draft for some time already, so after a revision I pressed 'Publish Post' :-)

Overview [quoting the documentation]:
SQL Query Result Cache

Results of queries and query fragments can be cached in memory in the SQL query result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the SQL query result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached. Users can annotate a query or query fragment with a result cache hint to indicate that results are to be stored in the SQL query result cache.

You can set the RESULT_CACHE_MODE initialization parameter to control whether the SQL query result cache is used for all queries (when possible), or only for queries that are annotated.

The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.
  • result_cache_max_size specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled.
  • result_cache_max_result specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use.
  • result_cache_remote_expiration specifies the number of minutes that a result using a remote object is allowed to remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers (for example, if the remote table used by a result is modified at the remote database).
  • result_cache_mode specifies when a ResultCache operator is spliced into a query's execution plan.
    • manual the ResultCache operator is added only when the query is annotated (that is, hints).
    • force the ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
    • auto:

    • SQL> ALTER SESSION SET result_cache_mode=fake;
      ORA-00096: invalid value FAKE for parameter result_cache_mode, must be from among FORCE, MANUAL, AUTO

      As you see only two parameters are explicitly exposed. AUTO means that CBO is going to use all its intellect to deal with the result caching.
  • _result_cache_block_size [1024] result cache block size
  • _result_cache_timeout [60] maximum time (sec) a session waits for a result

  • Update 01/16/2012: Although a purpose of this blog post was to illustrate the RC generic behavior rather than pointing out the actual default values, it was noticed that the default value for _result_cache_timeout was changed from 60 to 10 seconds. It turned out that the change was introduced in several weeks after this blog post had been published. Thanks to V.Nikotin for asking a question about it. Also let me repeat this: one must not rely on values of undocumented parameters and use it in production w/o Oracle Support guidance. If you still want to play (use your test environment) and ensure that the tests below produce the same results you can set the parameter back to 60 seconds.

  • _result_cache_auto_execution_threshold [1] result cache auto execution threshold
  • _result_cache_auto_size_threshold [100] result cache auto max size allowed
  • _result_cache_auto_time_distance [300] result cache auto time distance
  • _result_cache_auto_time_threshold [1000] result cache auto time threshold

  • 10040 disable result-cache
  • 43903 always result-cache node
  • 43905 result cache tracing event

  • Result Cache: Latch
  • Result Cache: SO Latch

  • RCBG Result Cache: BackGround process

Views/Fixed Tables
  • gv$result_cache_objects [x$qesrcobj] displays all the objects (both cached results and dependencies) and their attributes.
  • gv$result_cache_statistics [x$qesrcsta] displays various Result Cache settings and usage statistics.
  • gv$result_cache_dependency [x$qesrcdep] displays the depends-on relationship between cached results and dependencies.
  • gv$result_cache_memory [x$qesrcmem] cache memory
  • [x$qesrcso] cache state objects
Execution Plan

EXEC dbms_stats.gather_table_stats(ownname => USER, tabname => 'T');

     , SUM(v) s
  FROM t

| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT    |                            |     2 |    12 |     3  (34)| 00:00:01 |
|   1 |  RESULT CACHE       | arywjvw3q677c984x4jj9rhzy0 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     2 |    12 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T                          |     3 |    18 |     2   (0)| 00:00:01 |

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

Column Projection Information (identified by operation id):

   1 - "G"[NUMBER,22], SUM("V")[22]
   2 - (#keys=1) "G"[NUMBER,22], SUM("V")[22]
   3 - "G"[NUMBER,22], "V"[NUMBER,22]

Result Cache Information (identified by operation id):

   1 - column-count=2; dependencies=(J.T); name="SELECT /*+ RESULT_CACHE */
     , SUM(v) s
  FROM t

As you can see a new operator RESULT CACHE was introduced. The name arywjvw3q677c984x4jj9rhzy0 represents gv$result_cache_objects.cache_id. The other column of the plan_table stores extra information about the result cache usage. That information is visualized in the dbms_xplan report for the corresponding RESULT CACHE operator.

The DBMS_RESULT_CACHE package provides an interface to allow the DBA to administer that part of the shared pool that is used by the SQL result cache and the PL/SQL function result cache. Both these caches use the same infrastructure. Therefore, for example, DBMS_RESULT_CACHE.BYPASS determines whether both caches are bypassed or both caches are used, and DBMS_RESULT_CACHE.FLUSH flushes both all the cached results for SQL queries and all the cached results for PL/SQL functions.
SQL> EXEC dbms_result_cache.memory_report;
R e s u l t   C a c h e   M e m o r y   R e p o r t
Block Size          = 1K bytes
Maximum Cache Size  = 224K bytes (224 blocks)
Maximum Result Size = 11K bytes (11 blocks)
Total Memory = 103528 bytes [0.165% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.008% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.156% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

Some interesting information about the result set cache can be obtained by executing dbms_result_cache package routines.

Result Cache Enqueue

This enqueue is new in 11g. It helps to support the result cache consistency protecting it when sessions manipulate the result cache concurrently e.g. when a session builds the cache.

The important parameter here is _result_cache_timeout. You can execute the code below from two sessions and check for how long the waiter is blocked (ctime of the AE enqueue can be a good marker, presuming that you start a new session everytime) while playing with different values of _result_cache_timeout parameter. The blocked session (process) is posted every 10 seconds and if the timeout is expired the kernel falls back to the normal query processing.

ALTER SESSION SET "_result_cache_timeout"=1;
SELECT type, id1, id2, lmode, request, ctime
  FROM v$lock

Having the parameter set to the default value you see something like this in the trace file:

*** 2007-08-12 12:53:45.224
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10002225 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762825224306
*** 2007-08-12 12:53:55.228
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10003951 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762835228392
*** 2007-08-12 12:54:05.236
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10007942 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762845236495
*** 2007-08-12 12:54:15.238
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10001965 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762855238593
*** 2007-08-12 12:54:25.241
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10002976 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762865241693
*** 2007-08-12 12:54:35.245
WAIT #4: nam='enq: RC - Result Cache: Contention' ela= 10003972 name|mode=1380122628 chunkNo=1 blockNo=5 obj#=-1 tim=1188762875245796
(QERRCA_TRACE)Result Set Search Failed...

Note the posting time. Also it's interesting to note how the behavior changes if the value of _result_cache_timeout is set to 0. So, it's worth to mention explicitly: the new functionality may introduce contention on reading the data — readers block other readers! [For those who are going to quote the statement: please make sure that you quote it correctly i.e. providing the example above. I have used version of Oracle RDBMS.]

to be continued...


_Nikotin said...

Владимир, в квадратных скобках ты указывал значения по умолчанию для параметров?

Не ошибся ли ты указав 60 здесь:

_result_cache_timeout [60] maximum time (sec) a session waits for a result

Я нашёл эту цифру в различных местах, кроме как в базе :)

(такое ощущение что все использовали твой блог как источник знаний, так как это самая первая содержательная публикация про смысл этого параметра)

После установки значение по умолчанию - 10. Собственно ниже ты и упоминаешь эти 10 секунд.

Vladimir Begun said...

Умалчиваемые могут меняться от версии к версии. То не умалчиваемые значения как таковые, а те, что стояли в настройках экземпляра моей базы для разработки. Цель и суть была показать что будет происходить когда некоторые параметры имеют неадекватные значения (10 тоже может быть неадекватным при старте системы и заполнении кэшей), поэтому они и были "задраны" для иллюстрации. Когда будет время я исправлю содержание.

p.s.: сообщение твоё о GROUP BY я видел.

Vladimir Begun said...

Мы на самом деле говорим о немного разных версиях :) Изменения 60->10 были сделаны несколько позже [неделей или двумя чтоли, не суть, по крайней мере на окончание лета 2007 там было 60 -- я сейчас пересмотрел историю изменений. Здесь тебе придётся просто поверить, факты я приводить не буду] написания данной статьи блога. Но как я указал выше, цель была не в этом -- что там по-умолчанию не так важно, как понимание принципов происходящего.