Oracle 11g Server Result Cache Result_cache_mode

For the result set that is often checked, a small number of records are returned, the server side is cacheable, the result set is stored in the shared pool, the binding variable is assumed, and the value of the bound variable is the same.

Sql> Show Parameter Result_cache
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 33440K
Result_cache_mode stringManual
Result_cache_remote_expiration integer 0
--result_cache_max_result Specifies that no matter what single result set can use the size of result_cache_max_size (in percent), it defaults to 5, agreeing to a value from 1 to 100, The result set exceeding this limit is invalidated by two colors.
--result_cache_max_size Specifies the size of the shared pool memory used as the result cache, assuming that it is set to 0, indicating that this feature is disabled.

--Theresult_cache_mode hypothesis is set to MANUAL (which is also the default), only the ability to use the result cache when specifying hint Result_cache; When force, all does not include hint No_ Result_cache query Statements will use the result cache, the second time the query takes effect, and when auto, the same SQL is executed at 11g for the third time, the cache does not work.

< Span style= "FONT-FAMILY:SIMSUN; line-height:22px ">--result_cache_remote_expiration The lifetime of the cached remote object (in minutes), usually feels 0, which means that the caching of query results based on remote objects is forbidden.

-- Result_cache_max_result and result_cache_max_size is a system-level setting, result_cache_mode and result_cache_remote_ Expiration can be changed at the session level.

sql> alter system set Result_cache_mode=force

Sql> SELECT COUNT (1)
2 from Gg_distribution W
3 WHERE w.data_area like ' 03 ' | | ‘%‘
4 and W.create_date > to_date (' 2013-01-01 ', ' yyyy-gg-dd ');
Time used: 00:00:22.48
Run a plan
----------------------------------------------------------
Plan Hash value:3923546474
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 |                 SELECT STATEMENT |     |    1 |   14 | 106K (1) |       00:24:46 |       | |
|  1 |                 SORT AGGREGATE |     |    1 |            14 |          |       |       | |
|   2 |                 PARTITION RANGE All |  |    2173k|   29m| 106K (1) |     00:24:46 |     1 | 2 |
|    3 |                 PARTITION LIST iterator|  |    2173k|   29m| 106K (1) |   00:24:46 |   KEY | KEY |
|* 4 | TABLE ACCESS Full |  gg_distribution |    2173k|   29m| 106K (1) |     00:24:46 |    1 | 48 |
-------------------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
4-filter ("W". " Create_date ">to_date (' 2013-01-01 00:00:00 ', ' syyyy-gg-dd hh24:mi:ss ') and
"W". " Data_area "like ' 3% ')
Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
280123 consistent gets
263679 Physical Reads
0 Redo Size
339 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>/
Time used: 00:00:00.11
Run a plan
----------------------------------------------------------
Plan Hash value:3923546474
--------------------------------------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------- ----------
| 0 |                            SELECT STATEMENT |     |    1 |   14 | 106K (1) |       00:24:46 |       | |
|  1 | RESULT CACHE |       0MR1089P1WXV3919RAQYVTWTSV |       |            |       |  |       | |
|   2 |                            SORT AGGREGATE |     |    1 |            14 |       |  |       | |
|    3 |                            PARTITION RANGE All |  |    2173k|   29m| 106K (1) |     00:24:46 |     1 | 2 |
|     4 |                            PARTITION LIST iterator|  |    2173k|   29m| 106K (1) |   00:24:46 |   KEY | KEY |
|* 5 | TABLE ACCESS Full |  gg_distribution |    2173k|   29m| 106K (1) |     00:24:46 |    1 | 48 |
--------------------------------------------------------------------------------------------------------------- ----------
predicate information (identified by Operation ID):
---------------------------------------------------
5-filter ("W". " Create_date ">to_date (' 2013-01-01 00:00:00 ', ' syyyy-gg-dd hh24:mi:ss ') and" W "." Data_area "
Like ' 3% ')
Result Cache Information (identified by Operation ID):
-----------------------------------------------------
1-column-count=1; Dependencies= (lcam_test. Gg_distribution); Attributes= (Single-row); Parameters= (NLS); Name= "Select COUNT (1)
From Gg_distribution W
WHERE w.data_area like ' 03 ' | | ‘%‘
and W.create_date > To_date (' 2013-01-01 ', ' yyyy-'
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
0 consistent gets
0 physical Reads
0 Redo Size
339 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

0MR1089P1WXV3919RAQYVTWTSV finds its cached information.

< Span style= "FONT-FAMILY:SIMSUN; line-height:22px "> sql> Select S.status,
           s.creation_timestamp,
           s.build_time,
       -& nbsp  s.row_count,
           s.scan_count
      from V$result_cache _objects s
     where cache_id = ' 0MR1089P1WXV3919RAQYVTWTSV ';
STATUS    creation_times build_time  row_count scan_count
--------------------------------------- --------------
Published 20月-June -14            700          1 &N Bsp      

Limit of result cache:

cannot be cached when a query statement uses nondeterministic functions, serial numbers, and temporal tables.

Query statements can result in inconsistent data.

Query statements referencing to the data dictionary view cannot be cached.

Ability to manage caches using Dbms_result_cache.