ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11gr2新增表的RESULT CACHE属性

Oracle11gr2新增表的RESULT CACHE属性

Linux操作系统 作者:wzhalal 时间:2014-06-27 13:16:47 0 删除 编辑

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 Database 11g Enterprise Edition Release 11.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 integer 32M
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

------------------------------------------------------------------------------------------
| 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)


统计信息
----------------------------------------------------------
          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)


统计信息
----------------------------------------------------------
          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,利用RESULT CACHE来提升系统性能变得更加容易和方便。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28894640/viewspace-1196072/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-06

  • 博文量
    60
  • 访问量
    249789