Oracle Illustrated

Feb 21 2010   8:28AM GMT

Migrating from 9i to 11g – Result caching

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

The new Result caching feature in Oracle 11g enables brilliant memory utilization efficiently. By caching SQL / PLSQL – re-execution directly fetches from the cache instead of re-executing the query / function again. Widely there are two types of result cache
PL/SQL result cache
SQL result cache

V$RESULT_CACHE related view(s) shows the results of cache SQL / PL/SQL functions

RESULT_CACHE is a new SGA component from Oracle 11g. Usually, results are cached to the SGA and based on the LRU algorithm they will be replaced with the latest results as memory allocated for this is usually a small part. From Oracle 11g RESULE_CACHE_MAX_SIZE parameter can be used to set the cache memory. There is also a new PLSQL package DBMS_RESULT_CACHE that provides various procedures.

1 SQL Result caching

SQL Result cache – The results of FREQUENTLY used SQL queries can be cached in the memory. So that the SQL query results are cached and on re-executing the same it gets the results from the memory and does not run again – and by experience we know fetching from memory is usually faster than executing the query all over again!!
Note : In Oracle RAC environment each NODE has its own result cache and that cannot be shared with another instance in the RAC.

* If the result_Cache_mode parameter is MANUAL – then, results would be cached only if we include the RESULT_CACHE Hint
* If the result_Cache_mode parameter is FORCE – then, results would be cached automatically for all the queries
* If the result_cache_mode is set to AUTO then the results caching would be decided by the CBO

Is there a way in Oracle 9i?
Actually – Not possible

Though for FULL TABLE SCANS CACHE hint is available which will cache the data into memory. But again this is based on LRU algorithm – this does not have a separate memory :result_cache like in 11g.

Oracle 11g

RESULT_CACHE_MODE = MANUAL

Must give result_Cache hint

SQL> select /*+ result_cache */ empno from emp;

Elapsed: 00:00:00.48

Execution Plan
———————————————————-
Plan hash value: 179099197

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 56 | 1(0)| 00:00:01 |
| 1 | RESULT CACHE | 9tgtkj1bvaz1c2rdc9bxr6jcju | | | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 56 | 1(0)| 00:00:01 |
———————————————————————————————–

Result Cache Information (identified by operation id):
——————————————————
1 – column-count=1; dependencies=(SCOTT.EMP); attributes=(ordered); name=”select /*+ RESULT_CACHE */ empno from emp ”

Note: I tried to use RESULT_CACHE from my SYSTEM user – it did not work. Then I logged in as SCOTT to see whether it works – it worked !! – net net we cannot use result cache from SYS / SYSTEM / SYSDBA.

RESULT_CACHE_MODE = FORCE

RESULT_CACHE_MODE = FORCE

Results are cached by default- internally RESULT_CACHE hint is used – check the result cache information section

select empno from emp

Elapsed: 00:00:00.48

Execution Plan
———————————————————-
Plan hash value: 179099197
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 56 | 1(0)| 00:00:01 |
| 1 | RESULT CACHE | 9tgtkj1bvaz1c2rdc9bxr6jcju | | | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 56 | 1(0)| 00:00:01 |
———————————————————————————————–

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

1 – column-count=1; dependencies=(SCOTT.EMP); attributes=(ordered); name=”select /*+ RESULT_CACHE */ empno from emp ”

To view the details based on result cache id check V$RESULT_CACHE_OBJECTS -

select status,name,namespace
from v$result_cache_objects where
cache_id=’9tgtkj1bvaz1c2rdc9bxr6jcju’;

STATUS NAME NAMES
———————————————————————————————
Published select /*+ RESULT_CACHE */ empno from emp SQL
RESULT_CACHE_MODE = ‘AUTO’

the CBO would determine when the results should be cached based on the following factors – frequency of query execution, cost, data changing factors etc.,

select empno from emp

Execution Plan
———————————————————-
Plan hash value: 179099197

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |

Result cache information

Report before caching

SQL> set serveroutput on
SQL> set autotrace off
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
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 768K bytes (768 blocks)
Maximum Result Size = 38K bytes (38 blocks)
[Memory]
Total Memory = 5140 bytes [0.004% of the Shared Pool]
… Fixed Memory = 5140 bytes [0.004% of the Shared Pool]
… Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

Notes: Since we have not cached any SQL so far – there is no CACHE Information.

Report after caching

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
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 103536 bytes [0.080% of the Shared Pool]
… Fixed Memory = 5140 bytes [0.004% of the Shared Pool]
… Dynamic Memory = 98396 bytes [0.076% of the Shared Pool]
……. Overhead = 65628 bytes
……. Cache Memory = 32K bytes (32 blocks)
……….. Unused Memory = 24 blocks
……….. Used Memory = 8 blocks
…………… Dependencies = 1 blocks (1 count)
…………… Results = 7 blocks
………………. SQL = 7 blocks (7 count)

PL/SQL procedure successfully completed.

Notes: The above shows that SQL query results are cached – total cache memory used 32k out of 2M allocated.

Note: the above is just to show RESULT_CACHE output only. Definitely it is not a great idea to do a result cache on full table !!

RESULT CACHE FOR BIND VARIABLES

SQL> variable empno number;
SQL> exec :empno := 7369

SQL> select /*+ result_cache */ * from emp where empno = :empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM D
EPTNO
———- ———- ——— ———- ——— ———- ———- —–
—–
7369 SMITH CLERK 7902 17-DEC-80 800
20

Elapsed: 00:00:00.71

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3
7 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

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

3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

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

1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parame
ters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

Second time -

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

Result Cache Information (identified by operation id):
——————————————————
1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parameters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

** second time with the same value

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

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

3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

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

1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parame
ters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

With another value

SQL> variable empno number;
SQL> exec :empno := 7499

….

Query on V$RESULT_CACHE_OBJECTS shows 2 hits to EMP table. Note, there were 3 queries executed – two queries with same bind variable value but third with a different bind variable value – this leads to a cache miss.

select status,name,namespace
from v$result_cache_objects where
cache_id=’8d01c2ttvh1h99u09ynz53jnys’

STATUS NAME
NAMES
——— ——————————————————————-
———————————————————- —–
Published select /*+ result_cache */ * from emp where empno = :empno
SQL
Published select /*+ result_cache */ * from emp where empno = :empno
SQL

RESULT CACHE – TEMPORARY TABLE

create global temporary table temp_test (a number) on commit preserve rows
/

Elapsed: 00:00:00.01
SQL> set autotrace on

SQL> select /*+ result_cache */ * from temp_Test;

A
———-
1

Elapsed: 00:00:00.07

Execution Plan
———————————————————-
Plan hash value: 322435456

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEMP_TEST | 1 | 13 | 2 (0)| 00:00:01 |
——————————————————————————-

Note
—–
– dynamic sampling used for this statement

SQL>

Note – Cannot cache TEMPORARY table results.

Which query needs to be cached using RESULT_CACHE – My take on this – Any frequent particular STATIC lookups can be cached in the memory. Definitely it wouldn’t be a good idea to cache millions or even 100,000 of rows into the memory. Also its better to use result_cache on queries that have aggregations, that behalves like materialized views etc., It is not designed for – huge number of concurrent users or readonly lookup tables – client result cache is for this. With SQL result caching we can cache normal queries, sub-queries and inline views. DBMS_RESULT_CACHE.FLUSH removes all the cached information from result_cache area in SGA. The result cache would keep growing until it reaches the maximum cache size We need to manually purge the result cache if it fills up fully.
Other important procedure in DBMS_RESULT_CACHE is status (shows the status of the result cache).
Some of the V$views are -
** V$RESULT_CACHE_STATISTICS
** V$RESULT_CACHE_OBJECTS
** V$5RESULT_CACHE_DEPENDENCY
** V$RESULT_CACHE_MEMORY
Result cache can cache the results for the following
** Bind variables. If the bind variable value differs then, it would lead to cache miss
** If there is any pending transaction to the query that we fire then it will not be cached into the memory
** will not cache flashback queries
** not eligible for caching – non-deterministic functions; currval or nextval in sequence; sql inbuilt functions such as sysdate or curr date; temporary table and dictionary tables.

**********************************************************************************

Function Result caching

Oracle 9i
Not possible – May be we can create functions inside a package and pin it in memory. – but even then results cannot be cached !!

Oracle 11g

create or replace function check_dept_details (dept_no number) return number
result_cache
is
check_avail number;
begin
select 1 into check_avail from dept
where deptno = dept_no;
return dept_no;
end;
/

SELECT check_dept_details(380) from dept where dname = ‘ACCOUNTING’

SQL> SELECT check_dept_details(380) from dept where dname = ‘ACCOUNTING’;

CHECK_DEPT_DETAILS(380)
———————-
380

Elapsed: 00:00:00.04
SQL> /

CHECK_DEPT_DETAILS(380)
———————-
380
Elapsed: 00:00:00.00

insert into dept values (600, ‘FO’, ‘SINGAPORE’);

SQL> SELECT check_dept_details(600) from dept where dname = ‘ACCOUNTING’;

CHECK_DEPT_DETAILS(600)
———————–
600

Elapsed: 00:00:00.01

Result cache is invalidated for new inserts

SQL> SELECT check_dept_details(120) from dept where dname = ‘ACCOUNTING’;

CHECK_DEPT_DETAILS(120)
———————–
120

Elapsed: 00:00:00.00

** The above is just for example only

create or replace function check_dept_details (dept_no number) return number
result_cache relies on(dept)
is
check_avail number;
begin
select 1 into check_avail from dept
where deptno = dept_no;
return dept_no;
end;
/

** Note – Relies on clause is not working in Oracle 11g R1

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: