Oracle11g result_cache – New Feature for DWH

Introduction
Result Cache is offered in Oracle11g as new Feature
Caching is nothing new to Oracle
Oracle has cached data for a long time now
What’s new is the caching of results This is similar to how a Materialized View works but is more-dynamic
New “result_cache” hint asks oracle to cache query results

Hands On

Example-1

SQL> 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 200M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

-bash-3.00$ sqlplus hitesh/*****
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 28 15:45:19 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET TIMING ON
SQL> SET AUTOTRACE TRACEONLY
SQL> select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl;

Elapsed: 00:00:44.76

Execution Plan
———————————————————-
Plan hash value: 1168074429
———————————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1379K(1)| 04:35:53 |
| 1 | RESULT CACHE | 3b8wqvk98cp33ck9s25pay4zwg | || |
| 2 | SORT AGGREGATE | | 1 || |
| 3 | MERGE JOIN CARTESIAN | | 100M| 1379K(1)| 04:35:53 |
| 4 | MERGE JOIN CARTESIAN | | 1000K| 13798(1)| 00:02:46 |
| 5 | MERGE JOIN CARTESIAN| | 10000 | 141(1)| 00:00:02 |
| 6 | TABLE ACCESS FULL | TEST_TBL | 100 | 3(0)| 00:00:01 |
| 7 | BUFFER SORT | | 100 | 138(1)| 00:00:02 |
| 8 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 9 | BUFFER SORT | | 100 | 13797(1)| 00:02:46 |
| 10 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 11 | BUFFER SORT | | 100 | 1379K(1)| 04:35:53 |
| 12 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
———————————————————————————————–
Result Cache Information (identified by operation id):
——————————————————
1 – column-count=1; dependencies=(HITESH.TEST_TBL); attributes=(single-row);
name=”select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl”
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
22 recursive calls
0 db block gets
81 consistent gets
8 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl;

Elapsed: 00:00:00.02

Execution Plan
———————————————————-
Plan hash value: 1168074429
———————————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1379K(1)| 04:35:53 |
| 1 | RESULT CACHE | 3b8wqvk98cp33ck9s25pay4zwg | || |
| 2 | SORT AGGREGATE | | 1 || |
| 3 | MERGE JOIN CARTESIAN | | 100M| 1379K(1)| 04:35:53 |
| 4 | MERGE JOIN CARTESIAN | | 1000K| 13798(1)| 00:02:46 |
| 5 | MERGE JOIN CARTESIAN| | 10000 | 141(1)| 00:00:02 |
| 6 | TABLE ACCESS FULL | TEST_TBL | 100 | 3(0)| 00:00:01 |
| 7 | BUFFER SORT | | 100 | 138(1)| 00:00:02 |
| 8 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 9 | BUFFER SORT | | 100 | 13797(1)| 00:02:46 |
| 10 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 11 | BUFFER SORT | | 100 | 1379K(1)| 04:35:53 |
| 12 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
———————————————————————————————–
Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; dependencies=(HITESH.TEST_TBL); attributes=(single-row);
name=”select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl”
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
13 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
526 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)
1 rows processed

SQL> select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl;

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 1168074429
———————————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1379K(1)| 04:35:53 |
| 1 | RESULT CACHE | 3b8wqvk98cp33ck9s25pay4zwg | | |
| 2 | SORT AGGREGATE | | 1 || |
| 3 | MERGE JOIN CARTESIAN | | 100M| 1379K(1)| 04:35:53 |
| 4 | MERGE JOIN CARTESIAN | | 1000K| 13798(1)| 00:02:46 |
| 5 | MERGE JOIN CARTESIAN| | 10000 | 141(1)| 00:00:02 |
| 6 | TABLE ACCESS FULL | TEST_TBL | 100 | 3(0)| 00:00:01 |
| 7 | BUFFER SORT | | 100 | 138(1)| 00:00:02 |
| 8 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 9 | BUFFER SORT | | 100 | 13797(1)| 00:02:46 |
| 10 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
| 11 | BUFFER SORT | | 100 | 1379K(1)| 04:35:53 |
| 12 | TABLE ACCESS FULL | TEST_TBL | 100 | 1(0)| 00:00:01 |
———————————————————————————————–
Result Cache Information (identified by operation id):
——————————————————
1 – column-count=1; dependencies=(HITESH.TEST_TBL); attributes=(single-row);
name=”select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, tes
t_tbl”
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 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)
1 rows processed

Example-2

SQL> select /*+ RESULT_CACHE */ count(*) from test_tbl;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 215328055
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)|00:00:01 |
| 1 | RESULT CACHE | 37qx1wzc3051davww9ksc0b0rx | | ||
| 2 | SORT AGGREGATE | | 1 | ||
| 3 | TABLE ACCESS FULL| TEST_TBL | 100 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HITESH.TEST_TBL); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(*) from test_tbl"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
526 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)
1 rows processed

SQL> select /*+ RESULT_CACHE */ count(*) from test_tbl;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 215328055
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)|00:00:01 |
| 1 | RESULT CACHE | 37qx1wzc3051davww9ksc0b0rx | | ||
| 2 | SORT AGGREGATE | | 1 | ||
| 3 | TABLE ACCESS FULL| TEST_TBL | 100 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(HITESH.TEST_TBL); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(*) from test_tbl"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 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)
1 rows processed

Connect sys/*********

SQL> select id, type, status, name from v$result_cache_objects;

ID TYPE STATUS
———- ———- ———
NAME
——————————————————————————–
0 Dependency Published
HITESH.TEST_TBL

2 Result Published
select /*+ RESULT_CACHE */ count(*) from test_tbl

1 Result Published
select /*+ RESULT_CACHE */ count(*) from test_tbl, test_tbl, test_tbl, test_tbl

References
http://blog.yannickjaquier.com/oracle/sql-result-cache.html
http://dbaora.com/sql-result-cache-11g/
http://www.ooug.org/presentations/2010slides/Oracle_11g_Results_Cache.ppt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.