oracle数据库中cache,Oracle数据库设置表的RESULT_CACHE

SQL> select count(*) from t;

COUNT(*)

----------

13456

SQL> set autot on;

SQL> select count(*) from t;

COUNT(*)

----------

13456

----------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

| Id | Operation          | Name |Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0| SELECT STATEMENT   |      |    1 |    50   (2)| 00:00:01 |

|   1|  SORT AGGREGATE    |     |     1 |            |          |

|   2|   TABLE ACCESS FULL| T    | 12278 |   50   (2)| 00:00:01 |

-------------------------------------------------------------------

SQL> select *+ result_cache */ count(*)from t;

COUNT(*)

----------

13456

------------------------------------------------------------------------------------------

| Id | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT    |                            |     1 |   50   (2)| 00:00:01 |

|   1|  RESULT CACHE       | c8ukugu9jf5474pjzn83wj3zph |       |           |          |

|   2|   SORT AGGREGATE    |                            |     1 |           |          |

|   3|    TABLE ACCESS FULL| T                          | 12278 |    50  (2)| 00:00:01 |

---修改为force，则强制使用resultcache

alter system set result_cache_mode=force;

SQL> create table t1 as select * from t;

SQL> select count(*) from t1;

COUNT(*)

----------

13456

------------------------------------------------------------------------------------------

| Id | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT    |                            |     1 |   50   (2)| 00:00:01 |

|   1|  RESULT CACHE       | 8q8qwtn4uk3vv7sm1ynpzz6t4k |       |           |          |

|   2|   SORT AGGREGATE    |                            |     1 |            |          |

|   3|    TABLE ACCESS FULL| T1                         | 14242 |    50  (2)| 00:00:01 |

------------------------------------------------------------------------------------------

SQL> select count(*) from t1;

COUNT(*)

----------

13456

------------------------------------------------------------------------------------------

| Id | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT    |                            |     1 |   50   (2)| 00:00:01 |

|   1|  RESULT CACHE       | 8q8qwtn4uk3vv7sm1ynpzz6t4k |       |            |          |

|   2|   SORT AGGREGATE    |                            |     1 |            |          |

|   3|    TABLE ACCESS FULL| T1                         | 14242 |    50  (2)| 00:00:01 |

------------------------------------------------------------------------------------------

查询V$RESULT_CACHE_OBJECTS可以得到目前缓存的情况

1*select name,cache_id from V$RESULT_CACHE_OBJECTS

SQL> select name,cache_id fromV$RESULT_CACHE_OBJECTS;

NAME                                                                  CACHE_ID

------------------------------------------------------------------------------------------------------------------------

WJ.T1                                                                 WJ.T1

WJ.T                                                                  WJ.T

select count(*) from t1                                               8q8qwtn4uk3vv7sm1ynpzz6t4k

select *+ result_cache */ count(*) fromt                            c8ukugu9jf5474pjzn83wj3zph

2，使用Table Annotations。 表注释的优先级低于sql语句。通过关键字RESULT_CACHE来实现。有DEFAULT和FORCE两个数值

DEFAULT

If at least one table in a query is set to DEFAULT, then result cachingis not enabled at the table level for this query, unless the RESULT_CACHE_MODEinitialization parameter is set to FORCE or the RESULT_CACHE hint is specified.This is the default value.

FORCE

If all the tables of a query are marked as FORCE, then the query resultis considered for caching. The table annotation FORCE takes precedence over theRESULT_CACHE_MODE parameter value of MANUAL set at the session level.

测试结果如下

SQL> alter table t1 result_cache (MODEforce);

SQL> alter session set result_cache_mode= MANUAL;

--查询T1使用resultcache

SQL> select count(*) from t1;

COUNT(*)

----------

13456

------------------------------------------------------------------------------------------

| Id | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT    |                            |     1 |   50   (2)| 00:00:01 |

|   1|  RESULT CACHE       | 8q8qwtn4uk3vv7sm1ynpzz6t4k |       |           |          |

|   2|   SORT AGGREGATE    |                            |     1 |            |          |

|   3|    TABLE ACCESS FULL| T1                         | 14242 |    50  (2)| 00:00:01 |

------------------------------------------------------------------------------------------

--查询表T没有使用resultcache

SQL> select count(*) from t;

COUNT(*)

----------

13456

-------------------------------------------------------------------

| Id | Operation          | Name |Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0| SELECT STATEMENT   |      |    1 |    50   (2)| 00:00:01 |

|   1|  SORT AGGREGATE    |     |     1 |            |         |

|   2|   TABLE ACCESS FULL| T    | 12278 |   50   (2)| 00:00:01 |