Leveraging Result Cache to enhance the SQL performance

Everyone uses Cache for performance as accessing data from disk is always slower compared to memory (currently). Storages like EMC have cache, so that read/write can happen in cache instead of disk. Oracle Database has buffer cache to avoid physical reads.

The SQL query result cache enables explicit caching of query result sets and query fragments in database memory. A dedicated memory buffer stored in shared pool can be used for storing and retrieving the cached result. Good candidate statements for result cache are the statements which access many rows and returns less rows though it can be used for any statement.

Create a TEST User and Table:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user TEST identified by test default tablespace USERS temporary tablespace TEMP;

User created.

SQL> grant connect,resource to TEST;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table test_cache ( c1 varchar2(4000));

Table created.

SQL> insert into test_cache values('This is a test table to demonstrate Result Cache Feature of Oracle 11g');

1 row created.

Insert approx 2 millions rows:

SQL> insert into test_cache select * from test_cache;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> /

262144 rows created.

SQL> /

524288 rows created.

SQL> /

1048576 rows created.

SQL> insert into test_cache values('ONE');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> grant dba to TEST;

Grant succeeded.

SQL>

Check whether anything is cached or not:

SQL> conn test/test
Connected.
SQL> select TYPE,STATUS,NAME,CREATION_TIMESTAMP,ROW_COUNT,ROW_SIZE_MAX,OBJECT_NO,ROW_SIZE_AVG,INVALIDATIONS from v$result_cache_objects;

no rows selected


Run a Complex Self-Join query with AUTOTRACE ON

SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace on
SQL> SELECT count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t6
Where t1.c1='ONE' and
T2.c1='ONE' and
T3.c1='ONE' and
T4.c1='ONE' and
T5.c1='ONE' and
T6.c1='ONE';

COUNT(*)
----------
1

Elapsed: 00:00:04.77

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 | 12012 | | |
| 2 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 3 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 7 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 8 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 9 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 10 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 11 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 12 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 13 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 14 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 15 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 16 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 17 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
----------------------------------------------------------------------------------------

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

7 - filter("T6"."C1"='ONE')
9 - filter("T5"."C1"='ONE')
11 - filter("T4"."C1"='ONE')
13 - filter("T3"."C1"='ONE')
15 - filter("T2"."C1"='ONE')
17 - filter("T1"."C1"='ONE')

Note
-----
- dynamic sampling used for this statement


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

Ran the above sql statement couple of times, even after database blocks are cached, it was consistently taking 4.77 seconds and ~133k consistent gets and physical reads.


Execute the query with the result_cache hint

Alter system flush buffer_cache;

First time:

SQL> SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t6
Where t1.c1='ONE' and
T2.c1='ONE' and
T3.c1='ONE' and
T4.c1='ONE' and
T5.c1='ONE' and
T6.c1='ONE'
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15

COUNT(*)
----------
1

Elapsed: 00:00:04.64

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | RESULT CACHE | ag7yp31pfqyv00m3vhbfwxjjz1 | | | | |
| 2 | SORT AGGREGATE | | 1 | 12012 | | |
| 3 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 8 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 9 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 10 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 11 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 12 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 13 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 14 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 15 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 16 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 17 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 18 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
---------------------------------------------------------------------------------------------------------

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

8 - filter("T6"."C1"='ONE')
10 - filter("T5"."C1"='ONE')
12 - filter("T4"."C1"='ONE')
14 - filter("T3"."C1"='ONE')
16 - filter("T2"."C1"='ONE')
18 - filter("T1"."C1"='ONE')

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

1 - column-count=1; dependencies=(TEST.TEST_CACHE); attributes=(single-row); parameters=(nls); name="SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t"


Note
-----
- dynamic sampling used for this statement


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

SQL> SQL>

First time, it took the same time as previous query took.

Second time

SQL> /

COUNT(*)
----------
1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 156414347

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12012 | 6513T (2)|999:59:59 |
| 1 | RESULT CACHE | ag7yp31pfqyv00m3vhbfwxjjz1 | | | | |
| 2 | SORT AGGREGATE | | 1 | 12012 | | |
| 3 | MERGE JOIN CARTESIAN | | 260T| 2780P| 6513T (2)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 1031G| 9389T| 25T (2)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 4080M| 29T| 101G (2)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 16M| 90G| 403M (2)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN| | 63879 | 243M| 1597K (2)| 05:19:33 |
|* 8 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6292 (2)| 00:01:16 |
| 9 | BUFFER SORT | | 253 | 494K| 1591K (2)| 05:18:18 |
|* 10 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 11 | BUFFER SORT | | 253 | 494K| 403M (2)|999:59:59 |
|* 12 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 13 | BUFFER SORT | | 253 | 494K| 101G (2)|999:59:59 |
|* 14 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 15 | BUFFER SORT | | 253 | 494K| 25T (2)|999:59:59 |
|* 16 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
| 17 | BUFFER SORT | | 253 | 494K| 6513T (2)|999:59:59 |
|* 18 | TABLE ACCESS FULL | TEST_CACHE | 253 | 494K| 6290 (2)| 00:01:16 |
---------------------------------------------------------------------------------------------------------

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

8 - filter("T6"."C1"='ONE')
10 - filter("T5"."C1"='ONE')
12 - filter("T4"."C1"='ONE')
14 - filter("T3"."C1"='ONE')
16 - filter("T2"."C1"='ONE')
18 - filter("T1"."C1"='ONE')

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

1 - column-count=1; dependencies=(TEST.TEST_CACHE); attributes=(single-row); parameters=(nls); name="SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t"


Note
-----
- dynamic sampling used for this statement


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

Note – Second time onwards it only took .01 second and 0 consistent gets and physical reads.

Check v$result_cache_objects:

SQL> set autot off
SQL> l
1* select TYPE,STATUS,NAME,CREATION_TIMESTAMP,ROW_COUNT,ROW_SIZE_MAX,OBJECT_NO,ROW_SIZE_AVG,INVALIDATIONS from v$result_cache_objects
SQL> /

TYPE STATUS NAME
---------- --------- --------------------------------------------------------------------------------------------------------------------------------
CREATION_ ROW_COUNT ROW_SIZE_MAX OBJECT_NO ROW_SIZE_AVG INVALIDATIONS
--------- ---------- ------------ ---------- ------------ -------------
Dependency Published TEST.TEST_CACHE
26-AUG-08 0 0 70304 0 0

Result Published SELECT /*+ result_cache */ count(*)
from
TEST_CACHE t1,
TEST_CACHE t2,
TEST_CACHE t3,
TEST_CACHE t4,
TEST_CACHE t5,
TEST_CACHE t
26-AUG-08 1 5 0 5 0


As it is clear the result of the query is cached and published which means Result is available to use.

Check how much memory is being used by Result Cache

SQL> set serveroutput on
SQL> 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 = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 147280 bytes [0.029% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.000% of the Shared Pool]
....... Cache Mgr = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = bytes
... Dynamic Memory = 144880 bytes [0.029% of the Shared Pool]
....... Overhead = 112112 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 9712 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

Flush the Result Cache

Following supplied package can be used to flush the result cache:

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

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 = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 2400 bytes [0.000% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.