11g New Feature: SQL Result Cache
Posted at Friday, October 26, 2007
In previous versions it was possible to manipulate the instance's memory structures to improve the performance of user queries. For instance, a table or index could be "pinned" into memory via usage of the db_keep_cache. Alternatively, tables could be "cached," prohibiting tables - accessed via a full scan - from being placed on the tail-end of the LRU.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
In my case should result in
Similarly,
Should result in
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
Results in
The memory report also reflects the result cache is now being used.
Of course it is subsequent submissions of the same query which yield the performance gain.
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
SELECT name "Parameter"
, round(value/1024/1024) "MB"
FROM v$spparameter
WHERE name IN ('shared_pool_size',
'result_cache_max_size',
'memory_target',
'memory_max_target',
'sga_target',
'pga_aggregate_target')
ORDER BY 2 DESC;
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
Parameter MB
---------------------------------------- ----------
memory_max_target 256
memory_target 240
shared_pool_size 64
result_cache_max_size 16
pga_aggregate_target 0
sga_target 0
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
SELECT
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=4)/
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=1) "Result Set Max Size in MB" FROM DUAL;
In my case should result in
16
Similarly,
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;
Should result in
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 = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 5132 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
DROP TABLE SCOTT.MY_EMPLOYEE;
CREATE TABLE SCOTT.MY_EMPLOYEE
(
EMPNO NUMBER(32) NOT NULL,
ENAME VARCHAR2(100),
SAL NUMBER(16,2),
GRADE NUMBER(1),
UPDATE_TS TIMESTAMP(6) DEFAULT systimestamp)
TABLESPACE USERS;
CREATE UNIQUE INDEX SCOTT.PK_MY_EMPLOYEE ON SCOTT.MY_EMPLOYEE
(EMPNO)
TABLESPACE USERS;
ALTER TABLE SCOTT.MY_EMPLOYEE ADD (CONSTRAINT PK_MY_EMPLOYEE PRIMARY KEY (EMPNO) USING INDEX);
set serverout on
declare
begin
execute immediate 'truncate table scott.my_employee';
for i in 1..10000000 loop
insert into scott.my_employee (empno, ename, sal, grade) values (i,'James'||i,i,1);
end loop;
commit;
execute immediate 'analyze table scott.my_employee estimate statistics sample 1 percent';
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.56
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
2457 recursive calls
0 db block gets
20424 consistent gets
19930 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
84 sorts (memory)
0 sorts (disk)
1 rows processed
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19877 consistent gets
19876 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
SQL> execute dbms_result_cache.flush;
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
1000 recursive calls
0 db block gets
20322 consistent gets
20113 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
42 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
SELECT type,
cache_id,
object_no,
space_overhead
FROM V$RESULT_CACHE_OBJECTS;
Results in
TYPE CACHE_ID OBJECT_NO SPACE_OVERHEAD
---------- ---------------------------------------- ---------- --------------
Dependency SCOTT.MY_EMPLOYEE 71439 0
Result 9kuwb9z28jrdqdcpuxk1j0ry8v 0 238
The memory report also reflects the result cache is now being used.
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 = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 103528 bytes [0.095% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.090% of the Shared Pool]
....... Overhead = 65628 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)
Of course it is subsequent submissions of the same query which yield the performance gain.
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS;
ID NAME VALUE
---------- -------------------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 16384
3 Block Count Current 32
4 Result Size Maximum (Blocks) 16384
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 3
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
_507.png)
Subscribe
