Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, October 4, 2015

Result Cache:

Result Cache:

A result cache is an area of memory, either in the SGA or client application memory, that stores the result of a database query or query block for reuse. The cached rows are shared across statements and sessions unless they become stale.

Managing the Server Result Cache

The server result cache is a memory pool within the shared pool. This pool contains a SQL query result cache, which stores results of SQL queries, and a PL/SQL function result cache, which stores values returned by PL/SQL functions.

How the Server Result Cache Works

When a query executes, the database looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.

When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

Server Result Cache Initialization Parameters

The following database initialization parameters control the server result cache:
  • RESULT_CACHE_MAX_SIZE
    • This parameter sets the memory allocated to the server result cache. The server result cache is enabled unless you set this parameter to 0, in which case the cache is disabled.
  • RESULT_CACHE_MAX_RESULT
    • This parameter sets the maximum amount of server result cache memory that can be used for for a single result. The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.
  • RESULT_CACHE_REMOTE_EXPIRATION
    • This parameter specifies the expiration time for a result in the server result cache that depends on remote database objects. The default value is 0 minutes, which implies that results using remote objects should not be cached.




Managing Server Result Cache Memory with Initialization Parameters
By default, on database startup, Oracle Database allocates memory to the server result cache in the shared pool. The memory size allocated depends on the memory size of the shared pool and the memory management system. The database uses the following algorithm:
  • When using the MEMORY_TARGET initialization parameter to specify the memory allocation, Oracle Database allocates 0.25% of MEMORY_TARGET to the result cache.
  • When you set the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of SGA_TARGET to the result cache.
  • If you specify the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.
The size of the server result cache grows until reaching the maximum size. Query results larger than the available space in the cache are not cached. The database employs an LRU algorithm to age out cached results, but does not otherwise automatically release memory from the server result cache.

Purge RESULT CACHE:

You can use the DBMS_RESULT_CACHE.FLUSH procedure to purge memory.


Oracle Database will not allocate more than 75% of the shared pool to the server result cache.



RESULT CACHE Report:

SHAIKDB>SET SERVEROUTPUT ON
SHAIKDB>EXECUTE 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
[Parameters]
Block Size        = 1K bytes
Maximum Cache Size  = 1760K bytes (1760 blocks)
Maximum Result Size = 88K bytes (88 blocks)
[Memory]
Total Memory = 10696 bytes [0.005% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.



Client Result Cache Initialization Parameters




Initialization Parameter
Description
CLIENT_RESULT_CACHE_SIZE
Sets the maximum size of the client result cache for each client process. To enable the client result cache, set the size to 32768 bytes or greater. A lesser value, including the default of 0, disables the client result cache.
Note: If the CLIENT_RESULT_CACHE_SIZE setting disables the client cache, then a client node cannot enable it. If the CLIENT_RESULT_CACHE_SIZE setting enables the client cache, however, then a client node can override the setting. For example, a client node can disable client result caching or increase the size of its cache.
CLIENT_RESULT_CACHE_LAG
Specifies the amount of lag time for the client result cache. If the OCI application performs no database calls for a period of time, then the client cache lag setting forces the next statement execution call to check for validations.
If the OCI application accesses the database infrequently, then setting this parameter to a low value results in more round trips from the OCI client to the database to keep the client result cache synchronized with the database. The client cache lag is specified in milliseconds, with a default value of 3000 (3 seconds).
COMPATIBLE
Specifies the release with which Oracle must maintain compatibility. For the client result cache to be enabled, this parameter must be set to 11.0.0.0 or higher. For client caching on views, this parameter must be set to 11.2.0.0.0 or higher.



Result Cache Mode

The result cache mode is a database setting that determines which queries are eligible to store result sets in the client and server result caches.


Value
Default
Description
MANUAL
Yes
Query results can only be stored in the result cache by using a query hint or table annotation. This is the recommended value.
FORCE
No
All results are stored in the result cache. If a query result is not in the cache, then the database executes the query and stores the result in the cache. Subsequent executions of the same statement, including the result cache hint, retrieve data from the cache.
Sessions uses these results if possible. To exclude query results from the cache, you must use the /*+ NO_RESULT_CACHE */ query hint.
Note: FORCE mode is not recommended because the database and clients attempt to cache all queries, which can create significant performance and latching overhead.

Result cache hints at the application level to control caching behavior. The SQL result cache hints take precedence over the result cache mode and result cache table annotations.

Demo:

SHAIKDB>show parameter result

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag          big integer 3000
client_result_cache_size         big integer 0
result_cache_max_result          integer     5
result_cache_max_size            big integer 1760K
result_cache_mode            string     MANUAL
result_cache_remote_expiration         integer     0

SHAIKDB>select name,type,status,object_no,row_count from v$result_cache_objects;

no rows selected



SHAIKDB>select count(*) from result2;

 COUNT(*)
----------
    227328

SHAIKDB>desc result2;
Name                              Null?    Type
----------------------------------------------------- -------- ------------------------------------
EMPLOYEE_ID                           NUMBER(6)
FIRST_NAME                           VARCHAR2(20)
LAST_NAME                          NOT NULL VARCHAR2(25)
EMAIL                              NOT NULL VARCHAR2(25)
PHONE_NUMBER                           VARCHAR2(20)
HIRE_DATE                          NOT NULL DATE
JOB_ID                           NOT NULL VARCHAR2(10)
SALARY                            NUMBER(8,2)
COMMISSION_PCT                        NUMBER(2,2)
MANAGER_ID                           NUMBER(6)
DEPARTMENT_ID                           NUMBER(4)
SSN                               NUMBER(9)

SHAIKDB>set timing on time on
22:08:07 SHAIKDB>

22:08:54 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2048

Elapsed: 00:00:00.02

22:09:41 SHAIKDB>set echo on
22:10:10 SHAIKDB>set autot on exp

22:10:22 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2048

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3939577969

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    13 |   651   (1)| 00:00:08 |
|   1 |  RESULT CACHE        | cqt78fxmq2rgrad2rsrnxyvtsc |     |     |          |      |
|   2 |   SORT AGGREGATE    |                |     1 |    13 |          |      |
|*  3 |    TABLE ACCESS FULL| RESULT2            |  2086 | 27118 |   651   (1)| 00:00:08 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("EMPLOYEE_ID"=100)

Result Cache Information (identified by operation id):
------------------------------------------------------

  1 - column-count=1; dependencies=(TEST2.RESULT2); attributes=(single-row); name="select /*+ resul
t_cache result2 */ count(*) from result2 where employee_id=100"


Note
-----
  - dynamic sampling used for this statement (level=2)


SHAIKDB>set linesize 600
SHAIKDB>col name for a50

SHAIKDB>select name,type,status,object_no,row_count from v$result_cache_objects;


NAME                           TYPE       STATUS     OBJECT_NO  ROW_COUNT
-------------------------------------------------- ---------- --------- ---------- ----------
TEST2.RESULT2                       Dependency Published      78641        0
select /*+ result_cache result2 */ count(*) from r Result     Published      0        1
esult2 where employee_id=100


22:10:26 SHAIKDB>set autot off
22:14:31 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2048

Elapsed: 00:00:00.00


SHAIKDB>alter system flush buffer_cache;

System altered.


22:14:39 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2048

Elapsed: 00:00:00.01


22:15:45 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2048

Elapsed: 00:00:00.00


22:15:45 SHAIKDB>insert into result2 select * from hr.employees where employee_id=100;

1 row created.

Elapsed: 00:00:00.02

22:17:06 SHAIKDB>commit;

Commit complete.

Elapsed: 00:00:00.00


22:17:09 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2049

Elapsed: 00:00:00.03


22:17:16 SHAIKDB>set autot on exp                                                             
22:17:34 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     2049

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3939577969

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    13 |   651   (1)| 00:00:08 |
|   1 |  RESULT CACHE        | cqt78fxmq2rgrad2rsrnxyvtsc |     |     |          |      |
|   2 |   SORT AGGREGATE    |                |     1 |    13 |          |      |
|*  3 |    TABLE ACCESS FULL| RESULT2            |  2086 | 27118 |   651   (1)| 00:00:08 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("EMPLOYEE_ID"=100)

Result Cache Information (identified by operation id):
------------------------------------------------------

  1 - column-count=1; dependencies=(TEST2.RESULT2); attributes=(single-row); name="select /*+ resul
t_cache result2 */ count(*) from result2 where employee_id=100"


Note
-----
  - dynamic sampling used for this statement (level=2)


22:26:41 SHAIKDB>delete result2 where employee_id=100 and rownum <=500;

500 rows deleted.

Elapsed: 00:00:00.03



SHAIKDB>select name,type,status,object_no,row_count from v$result_cache_objects;

NAME                           TYPE       STATUS     OBJECT_NO  ROW_COUNT
-------------------------------------------------- ---------- --------- ---------- ----------
TEST2.RESULT2                       Dependency Published      78641        0
select /*+ result_cache result2 */ count(*) from r Result     Published      0        1
esult2 where employee_id=100

select /*+ result_cache result2 */ count(*) from r Result     Invalid        0        1
esult2 where employee_id=100



22:28:34 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     1549

Elapsed: 00:00:00.05


SHAIKDB>set serveroutput on
SHAIKDB>exec dbms_result_cache.memory_report(detailed=>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  = 1760K bytes (1760 blocks)
Maximum Result Size = 88K bytes (88 blocks)
[Memory]
Total Memory = 174752 bytes [0.082% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.005% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... Cache Mgr  = 5552 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 164056 bytes [0.077% of the Shared Pool]
....... Overhead = 131288 bytes
........... Hash Table      = 64K bytes (4K buckets)
........... Chunk Ptrs      = 24K bytes (3K slots)
........... Chunk Maps      = 12K bytes
........... Miscellaneous = 28888 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 2 blocks
................... SQL     = 1 blocks (1 count)
................... Invalid = 1 blocks (1 count)

PL/SQL procedure successfully completed.


SHAIKDB> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SHAIKDB>select name,type,status,object_no,row_count from v$result_cache_objects;

no rows selected



22:31:51 SHAIKDB>select /*+ result_cache result2 */ count(*) from result2 where employee_id=100;

 COUNT(*)
----------
     1549

Elapsed: 00:00:00.01
22:32:29 SHAIKDB>/

 COUNT(*)
----------
     1549

Elapsed: 00:00:00.00


Dictionary Views:

View/Table
Description
V$RESULT_CACHE_STATISTICS
Lists various server result cache settings and memory usage statistics.
V$RESULT_CACHE_MEMORY
Lists all the memory blocks in the server result cache and their corresponding statistics.
V$RESULT_CACHE_OBJECTS
Lists all the objects whose results are in the server result cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY
Lists the dependency details between the results in the server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$
Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes. This statistics table has entries for each client process that is using result caching. After the client processes terminate, the database removes their entries from this table. The client table lists information similar to V$RESULT_CACHE_STATISTICS.
DBA_TABLES, USER_TABLES, ALL_TABLES
Includes a RESULT_CACHE column that shows the result cache mode annotation for the table. If the table has not been annotated, then this column shows DEFAULT. This column applies to both server and client result caching.


Documentation:
Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)--> 7.6 Managing the Server and Client Result Caches

No comments: