Oracle11gr2新增表的RESULT CACHE属性

最新推荐文章于 2024-02-02 08:33:16 发布

转载最新推荐文章于 2024-02-02 08:33:16 发布 · 425 阅读

11G NEW FEATURE 专栏收录该内容

4 篇文章

订阅专栏

转自：http://space.itpub.net/4227/viewspace-672612

在11.2中，Oracle新增了一个表的参数属性RESULT_CACHE，设置是否将表的查询结果放到RESULT CACHE中。

在11.1中，Oracle引入了RESULT CACHE功能，设置RESULT CACHE可以通过HINT以及初始化参数两种方式实现。但是使用初始化参数设置RESULT CACHE的覆盖面太大，会导致一些不希望被CACHE的查询也被放到RESULT CACHE中。而使用HINT方式的覆盖面又太窄，只对指定的查询有效，使用相对比较麻烦。

于是在11.2中，Oracle推出了表级的RESULT CACHE，当设置表的RESULT CACHE后，这个表的所有查询都被放到RESULT CACHE缓冲区中：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

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 integer32M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> CREATE TABLE T_CACHE
2 RESULT_CACHE (MODE FORCE)
3 AS SELECT *
4 FROM ALL_OBJECTS;

表已创建。

SQL> SET AUTOT ON
SQL> SELECT COUNT(*) FROM T_CACHE;

COUNT(*)
----------
69432

执行计划
----------------------------------------------------------
Plan hash value: 56254237

------------------------------------------------------------------------------------------
| Id | Operation           | Name                       | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   196   (1)| 00:00:03 |
|   1 | RESULT CACHE       | 563an9xyg23fyb37x3qwjkadfa |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T_CACHE                    | 71348 |   196   (1)| 00:00:03 |
------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="SELECT COUNT(*) FROM T_CACHE"

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
         28 recursive calls
          0 db block gets
        577 consistent gets
        502 physical reads
          0 redo size
        529 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> SELECT COUNT(*) FROM T_CACHE;

COUNT(*)
----------
69432

执行计划
----------------------------------------------------------
Plan hash value: 56254237

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="SELECT COUNT(*) FROM T_CACHE"

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        529 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

当设置了RESULT_CACHE(MODE FORCE)属性后，对于表的查询自动使用RESULT CACHE机制，即使初始化参数RESULT_CACHE_MODE设置了MANUAL，且并没有使用HINT来强制使用RESULT CACHE。

不过需要注意，RESULT_CACHE的默认设置是DEFAULT，只要一个查询访问的表中有一个是DEFAULT状态，整个查询就不会使用RESULT CACHE机制：

SQL> CREATE TABLE T_CACHE2
2 RESULT_CACHE (MODE DEFAULT)
3 AS SELECT *
4 FROM USER_OBJECTS;

表已创建。

SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;

COUNT(*)
----------
13798

执行计划
----------------------------------------------------------
Plan hash value: 3151804270

--------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    26 |   239   (1)| 00:00:04 |
|   1 | SORT AGGREGATE     |          |     1 |    26 |            |          |
|* 2 |   HASH JOIN         |          | 12465 |   316K|   239   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL| T_CACHE2 | 12465 |   158K|    42   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T_CACHE | 71348 |   905K|   196   (1)| 00:00:03 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
         52 recursive calls
          0 db block gets
        742 consistent gets
          0 physical reads
          0 redo size
        529 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

可以看到，Oracle并没有尝试生成RESULT CACHE记录。如果修改T_CACHE2的属性：

SQL> SET AUTOT OFF
SQL> SELECT TABLE_NAME, RESULT_CACHE
2 FROM USER_TABLES
3 WHERE TABLE_NAME LIKE 'T_CACHE%';

TABLE_NAME                     RESULT_
------------------------------ -------
T_CACHE2                       DEFAULT
T_CACHE                        FORCE

SQL> ALTER TABLE T_CACHE2 RESULT_CACHE (MODE FORCE);

表已更改。

SQL> SET AUTOT ON
SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;

COUNT(*)
----------
13798

执行计划
----------------------------------------------------------
Plan hash value: 3151804270

---------------------------------------------------------------------------------------------------
|Id| Operation            | Name                       |Rows |Bytes|Cost (%CPU)|Time    |
-----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT     |                            |    1|   26| 239   (1)|00:00:04|
| 1| RESULT CACHE        | 9ngkv4bm47t0a2u6h7zbrrpyh4 |     |     |           |        |
| 2|   SORT AGGREGATE     |                            |    1|   26|           |        |
|*3|    HASH JOIN         |                            |12465| 316K| 239   (1)|00:00:04|
| 4|     TABLE ACCESS FULL| T_CACHE2                   |12465| 158K|   42   (0)|00:00:01|
| 5|     TABLE ACCESS FULL| T_CACHE                    |71348| 905K| 196   (1)|00:00:03|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.T_CACHE, TEST.T_CACHE2); attributes=(single-row); name="SELECT COUNT(*)
FROM T_CACHE T1, T_CACHE2 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID"

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
        187 recursive calls
          0 db block gets
        756 consistent gets
          0 physical reads
          0 redo size
        529 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;

COUNT(*)
----------
13798

执行计划
----------------------------------------------------------
Plan hash value: 3151804270

-------------------------------------------------------------------------------------------
|Id| Operation            | Name                       |Rows | Bytes |Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT     |                            |   1|    26 | 239   (1)|00:00:04|
| 1| RESULT CACHE        | 9ngkv4bm47t0a2u6h7zbrrpyh4 |   |       |           |        |
| 2|   SORT AGGREGATE     |                            |   1|    26 |           |        |
|*3|    HASH JOIN         |                            |12465|  316K| 239   (1)|00:00:04|
| 4|     TABLE ACCESS FULL| T_CACHE2                   |12465|  158K|   42   (0)|00:00:01|
| 5|     TABLE ACCESS FULL| T_CACHE                    |71348|  905K|  196   (1)|00:00:03|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(TEST.T_CACHE, TEST.T_CACHE2); attributes=(single-row); name="SELECT COUNT(*)
FROM T_CACHE T1, T_CACHE2 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID"

Note
-----
- dynamic sampling used for this statement (level=2)