Oracle 11g Server Results Cache Result_cache_mode

For a frequently-checked result set, a small number of records are returned, the server side is cacheable, the result set is saved in the shared pool, and the value of the bound variable is the same if it is a bound variable.

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 any single result set can use the size of result_cache_max_size (in percent), default is 5, allow values 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 that is used as the result cache, if set to 0, indicating that this attribute is disabled.

--Result_cache_mode if set to MANUAL (which is also the default), the result cache can only be used when the hint Result_cache is specified; When force, all does not contain 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 run at 11g for the third time, the cache will work.

< Span style= "FONT-FAMILY:SIMSUN; line-height:22px ">--result_cache_remote_expiration The lifetime of the cached remote object, in minutes, because the result set based on the remote object cannot automatically become invalid due to changes to the remote object.

-- Result_cache_max_result and result_cache_max_size is a system-level setting, result_cache_mode and result_cache_remote_ Expiration can be modified 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
Execution 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
Execution 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

can be based on 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 temporary tables.

Query statements can result in inconsistent data.

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

You can use Dbms_result_cache to manage the cache.