This is a series about 11g New Features:
We already had:
SQL Analyzer (Sql Replay) in part I of this series ,
Database Replay in part II ,
11g New Features for the query optimizer. as part III.
One of the real highlights in Oracle 11g is the possibility to cache the results of queries as well as the return values of functions.
I want to discuss only the SQL Query Result Cache here and will post about the PL/SQL result cache later on.
Before Oracle 11g there was only one way to communicate between sessions – it was the built in package DBMS_PIPE we had to use.
Just imagine you are using a DB_LINK to read data from a remote database. Wouldn’t it be nice to have teh result for your query already in the cache over there instead of having to parse and cause physical reads from disk and all these things?
This is possible now with Oracle 11g!
As of Oracle 11gR1 it is possible to cache results of queries in a special section of the SHARED POOL called RESULT CACHE.
Let’s see how it works:
There are three different modes you can use and they are managed with the initialization parameter RESULT_CACHE_MODE. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. The default is MANUAL and it is also possible to use AUTO and FORCE. With force every result is stored in the result cache. With Auto the optimizer makes the descision what to cache.
And there are few more parameters which can be used to control caching of queries and memory utilization of the result cache of the instance.
SYSTEM @ orcl11 SQL> show parameter result
NAME TYPE VALUE
———————————— ——————————– ——————————
client_result_cache_lag big integer 3000c
lient_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 768K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
Let’s have a look at the memory utilization in the SHARED POOL:
SYSTEM @ orcl11 SQL> SELECT *
FROM v$sgastat 2
WHERE lower(name) like ‘%result%’;
POOL NAME BYTES
———— ————————– ———-
shared pool Result Cache: State Objs 2852
shared pool Result Cache: Memory Mgr 200
shared pool Result Cache: Chunk Ptrs 96
shared pool Result Cache: Bloom Fltr 2056
shared pool Result Cache: Cache Mgr 8304
Let’s also have a look at a package which you can use to get a memory utilization report for the result cache and to flush the query results :
SYSTEM @ orcl11 SQL> desc dbms_result_cache
PROCEDURE BYPASS
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
BYPASS_MODE BOOLEAN
INFUNCTION FLUSH RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
PROCEDURE FLUSH
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
RETAINMEM BOOLEAN IN
DEFAULT RETAINSTA BOOLEAN IN
DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OBJECT_ID BINARY_INTEGER IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OBJECT_ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
ID BINARY_INTEGER IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CACHE_ID VARCHAR2 IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CACHE_ID VARCHAR2 IN
PROCEDURE MEMORY_REPORT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DETAILED BOOLEAN IN DEFAULT
FUNCTION STATUS RETURNS VARCHAR2
SYSTEM @ orcl11 SQL> set serveroutput on
SYSTEM @ orcl11 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 = 1024 bytes
Maximum Cache Size = 786432 bytes (768 blocks)
Maximum Result Size = 38912 bytes (38 blocks)[Memory]
Total Memory = 13508 bytes [0.010% of the Shared Pool]…
Fixed Memory = 10656 bytes [0.008% of the Shared Pool]…
State Object Pool = 2852 bytes [0.002% of the Shared Pool]…
Cache Memory = 0 bytes (0 blocks) [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
Also there are a number of dynamic performace views which can be used to monitor the result cache:
SYSTEM @ orcl11 SQL> desc v$result_cache_objects
Name Null? Type
—————————————————————– ——– ——————————————–
ID NUMBER TYPE VARCHAR2(10)
STATUS VARCHAR2(9)
BUCKET_NO NUMBER
HASH NUMBER
NAME VARCHAR2(128)
NAMESPACE VARCHAR(5)
CREATION_TIMESTAMP DATE
CREATOR_UID NUMBER
DEPEND_COUNT NUMBER
BLOCK_COUNT NUMBER
SCN NUMBER
COLUMN_COUNT NUMBER
PIN_COUNT NUMBER
SCAN_COUNT NUMBER
ROW_COUNT NUMBER
ROW_SIZE_MAX NUMBER
ROW_SIZE_MIN NUMBER
ROW_SIZE_AVG NUMBER
BUILD_TIME NUMBER
LRU_NUMBER NUMBER
OBJECT_NO NUMBER
INVALIDATIONS NUMBER
SPACE_OVERHEAD NUMBER
SPACE_UNUSED NUMBER
CACHE_ID VARCHAR2(93)
CACHE_KEY VARCHAR2(93)
SYSTEM @ orcl11 SQL> select * from v$result_cache_objects;
no rows selected
Now let’s cache a query result:
There is a new result_cache hint which you can use to manually cache a query result in odrder to override the default setting of the parameter in the session.
(There also is a no_result_cache hint)
SYSTEM @ orcl11 SQL> SELECT /*+ RESULT_CACHE */
2 count(*) , owner
3 FROM lutz.t GROUP BY OWNER;
COUNT(*) OWNER
———- ——————————
4 LUTZ 12
TSMSYS 94256
PUBLIC 36
OUTLN 48
FLOWS_FILES 8
HR 2064
SYSTEM 32
ORACLE_OCM 1212
EXFSYS 220
DBSNMP 13136
SYSMAN 3136
XDB 6112
FLOWS_030000
SYS 117224
WMSYS 1260 15 rows selected.
# let’s take a look at the explain plan!
# I just run the statement again
SYSTEM @ orcl11 SQL> set autotrace trace explain statistics
SYSTEM @ orcl11 SQL> /
15 rows selected.
Execution Plan
———————————————————-
Plan hash value: 47235625
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|————————————————————————————————–
| 0 | SELECT STATEMENT | | 295K| 4907K| 1028 (7)| 00:00:13 |
| 1 | RESULT CACHE | 3dvq9p812snk55sb94f98ss805 | | | | |
| 2 | HASH GROUP BY | | 295K| 4907K| 1028 (7)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T | 295K| 4907K| 976 (2)| 00:00:12
|————————————————————————————————–
Result Cache Information (identified by operation id):——————————————————
1 – column-count=2; dependencies=(LUTZ.T); parameters=(nls); name=”SELECT /*+ RESULT_CACHE */count(*) , owner FROM lutz.t GROUP BY OWNER”
Note—–
- dynamic sampling used for this statement
Statistics
———————————————————-
5 recursive calls
0 db block gets
84 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed SYSTEM @ orcl11 SQL> set autotrace off
SYSTEM @ orcl11 SQL> SELECT id, type, name, scn, row_count
2* FROM v$result_cache_objects;
ID TYPE NAME SCN ROW_COUNT
———- ———- —————————— ———- ———-
0 Dependency LUTZ.T 1267310 0
1 Result SELECT /*+ RESULT_CACHE */ 1267310 15
count(*) , owner
FROM lutz.t
GROUP BY OWNER
SYSTEM @ orcl11 SQL> desc v$result_cache_statistics
Name Null? Type
—————————————————————– ——– ——————————————– ID NUMBER
NAME VARCHAR2(128)
VALUE NUMBER
SYSTEM @ orcl11 SQL> SELECT * FROM v$result_cache_statistics ;
ID NAME VALUE
———- —————————— ———-
1 Block Size (Bytes) 1024
2 Block Count Maximum 768
3 Block Count Current 32
4 Result Size Maximum (Blocks) 38
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
What I have described up to here is the server side query result cache.
If you are using an OCI application you can also configure a client side query result cache.
Again it is a set of parameters which are used to manage the OCI query result cache:
SYSTEM @ orcl11 SQL> show parameter client_result NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer
On the client side you can use an optional configuraton file to manage the client side settings in order to override the server side client settings.
September 14, 2007 at 12:33 pm |
SYSTEM @ orcl11 SQL> SELECT /*+ RESULT_CACHE */
2 count(*) , owner
3 FROM lutz.t GROUP BY OWNER;
COUNT(*) OWNER
———- ——————————
4 LUTZ 12
TSMSYS 94256
PUBLIC 36
OUTLN 48
FLOWS_FILES 8
HR 2064
SYSTEM 32
ORACLE_OCM 1212
EXFSYS 220
DBSNMP 13136
SYSMAN 3136
XDB 6112
FLOWS_030000
SYS 117224
WMSYS 1260 15 rows selected.
it starts by having the count as first column and continues with count as second column. Note Lutz has 3 columns and FLOWS_030000 has only one colum?
why do not you use tags for formating?
<pre>
<code>
COL1 COL2
---- ----
1 2
</code>
</pre>
September 14, 2007 at 12:34 pm |
(the expected effect has not been achieved
)
September 14, 2007 at 2:29 pm |
Hi Laurent,
I am always fighting with this stupid editor in wordpress.
The one in blogger was much better!
I am already about to find out how to move back to blogger.
Do you know a way to do so?
Thanks,
Lutz
November 6, 2007 at 8:29 pm |
Have you tested the RESULT_CACHE_MODE = AUTO?
I have done several tests with AUTO mode, but nothing is being cached.
Do you have an idea when the optimizer decides to cache in AUTO mode?
November 7, 2007 at 8:21 am |
Hi Peter, how do you know that nothing was cached?
Did you check with
exec dbms_result_cache.memory_report
?
Flush the result cache and run a statement multiple times!!!!
and you will see that there is something in the cache after a while.
Let me know the results of your tests, o.k.?
=;-)
November 9, 2007 at 12:52 pm |
Hello Lutz, that is exactly what I did. But I ran about 25 different queries on 5 different tables, but only once a query was cached. That was on my largest test table (40.000 records). But other queries on that table were not cached. So I still can’t find a pattern in the AUTO caching.
November 9, 2007 at 1:01 pm |
Hi Peter,
how often did you run the queries?
=;-)
November 12, 2007 at 8:17 am |
I ran the queries dozens of times, even with 2 different user accounts.
Have you found a pattern in de AUTO caching? My guess is that queries that are executed lots of times, and take some time to retrieve will be cached. And of course the result set is not very large.