Use Result Cache

Systems with large amounts of memory can take advantage of the result cache to improve response times of repetitive queries.
The result cache stores the results of SQL queries and PL/SQL functions in an area called Result Cache Memory in the shared pool. When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time. The cached results stored become invalid when data in the dependent database objects is modified. The use of the result cache is database-wide decision. Result cache itself is instance specific and can be sized differently on different instances.
Documentation stats "To disable the result cache in a cluster, you must explicitly set the RESULT_CACHE_MAX_SIZE initialization parameter to 0 during every instance startup " but thats not the case setting (11.2.0.1)result_cache_max_size=0 with scope=both and sid='*' will disable it permanently.

Using the Result Cache Mode

You can control the result cache mechanism using the RESULT_CACHE_MODE parameter in the server initialization (init.ora) file. This parameter can be set at the system, session, or table level to the following values:

MANUAL
In MANUAL mode, the results of a query can only be stored in the result cache by using the /*+ result_cache */ hint.
FORCE
In FORCE mode, all results are stored in the result cache. When set to FORCE, all results use the cache, if possible. You can use the no_result_cache hint to bypass the cache when using the FORCE mode.

If the RESULT_CACHE_MODE parameter is set to MANUAL, and you want to store the results of a query in the result cache, then you must explicitly use the /*+ result_cache */ hint in your query, as shown in the following example:

SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;

When you execute the query, OCI will look up the result cache memory to verify if the result for the query already exists in the OCI client cache. If it exists, then the result is retrieved directly out of the cache. Otherwise, the query is executed on the database, and the result is returned as output and stored in the client result cache memory.

If the RESULT_CACHE_MODE parameter is set to FORCE, and you do not want to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query, as shown in the following example:

SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno

Restrictions on Using the SQL Query Result Cache

You cannot cache results when you use the following database objects or functions in your SQL query:

Dictionary and temporary tables
Sequence CURRVAL and NEXTVAL pseudo columns
SQL functions current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, andsys_timestamp
Non-deterministic PL/SQL functions

Cached results are parameterized with the parameter values if any of the following constructs are used in the query:

Bind variables.
The following SQL functions: dbtimezone, sessiontimezone, userenv/sys_context (with constant variables), uid, and user.
NLS parameters.

SQL> set linesize 180
SQL> set timing on
SQL> set autot on explain stat

SQL> SELECT
  2 PRODUCT_ID,sum(QUANTITY),avg(sales_amount)
  3 from sh.order_items
  4 group by product_id;

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   52 80 100
   72 6 72000
   32 6 700
   1 134 14700
   6 2 70
   2 45 2066.66667
   21 1 900
   90 18 16400
   87 4 6200
   12 40 12000
   74 12 8000

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   8 20 8400
   45 2 80

13 rows selected.

Elapsed: 00:00:26.37

Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   963 recursive calls
   0 db block gets
   69 consistent gets
   0 physical reads
   0 redo size
   923 bytes sent via SQL*Net to client
   524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   13 rows processed

SQL>

=====================================With Result Cache Hint =========================

select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id;
SQL> 2
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   32 6 700
   1 134 14700
   2 45 2066.66667
   6 2 70
   87 4 6200
   52 80 100
   72 6 72000
   21 1 900
   8 20 8400
   45 2 80
   90 18 16400

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   74 12 8000
   12 40 12000

13 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
   0 consistent gets
   0 physical reads
   0 redo size
   926 bytes sent via SQL*Net to client
   524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   13 rows processed

====================
session 2 - update -

update sh.order_items set QUANTITY=100 where ORDER_ID=5 and PRODUCT_ID=1;

1 row updated.

SQL> commit;

=====================
Session 1:
SQL> /

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   32 6 700
   52 80 100
   1 194 14700
   72 6 72000
   6 2 70
   2 45 2066.66667
   87 4 6200
   90 18 16400
   21 1 900
   45 2 80
   12 40 12000

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   74 12 8000
   8 20 8400

13 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   0 consistent gets
   0 physical reads
   0 redo size
   926 bytes sent via SQL*Net to client
   524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   13 rows processed

SQL> /

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   1 134 14700
   32 6 700
   72 6 72000
   52 80 100
   6 2 70
   2 45 2066.66667
   8 20 8400
   21 1 900
   45 2 80
   90 18 16400
   87 4 6200

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   12 40 12000
   74 12 8000

13 rows selected.

Elapsed: 00:00:06.29

Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"

Statistics
----------------------------------------------------------
   576 recursive calls
   0 db block gets
   69 consistent gets
   0 physical reads
   0 redo size
   923 bytes sent via SQL*Net to client
   524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   13 rows processed

-----

SQL> /

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   1 134 14700
   32 6 700
   72 6 72000
   52 80 100
   6 2 70
   2 45 2066.66667
   8 20 8400
   21 1 900
   45 2 80
   90 18 16400
   87 4 6200

PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
   12 40 12000
   74 12 8000

13 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   0 consistent gets
   0 physical reads
   0 redo size
   923 bytes sent via SQL*Net to client
   524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   13 rows processed

SQL> 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 = 10496K bytes (10496 blocks)
Maximum Result Size = 524K bytes (524 blocks)
[Memory]
Total Memory = 176680 bytes [0.015% of the Shared Pool]
... Fixed Memory = 12704 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.014% of the Shared Pool]
....... Overhead = 131208 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 4 blocks
................... SQL = 2 blocks (2 count)
................... Invalid = 2 blocks (2 count)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19
SQL> begin
   dbms_result_cache.flush;
end; 2 3
  4
  5 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

--- Invalidate the Indivisual TABLE/OBJECT rather full RESULT cACHE.
SQL > begin
  dbms_result_cache.invalidate('ARUP','CUSTOMERS');
end;

OWNER, OBJECT_NAME ....

Oraconsultancy - Your Multi Cloud Partner

Amazon Partner

Monday, 19 July 2010

Use Result Cache

Using the Result Cache Mode

No comments:

Post a Comment