Oracle11新特性——SQL缓存结果集（五）

最新推荐文章于 2021-12-28 10:04:43 发布

ckawt40802

最新推荐文章于 2021-12-28 10:04:43 发布

阅读量106

点赞数

文章标签：数据库

打算写一系列的文章介绍11g的新特性和变化。

这篇文章讨论DML对SQL缓存结果集的影响。

Oracle11新特性——SQL缓存结果集（一）：http://yangtingkun.itpub.net/post/468/391015

Oracle11新特性——SQL缓存结果集（二）：http://yangtingkun.itpub.net/post/468/391560

Oracle11新特性——SQL缓存结果集（三）：http://yangtingkun.itpub.net/post/468/392028

Oracle11新特性——SQL缓存结果集（四）：http://yangtingkun.itpub.net/post/468/475130

在看文档的时候发现了这个描述，检查了一下以前的文章，虽然提到了DML影响缓冲结果集，但是侧重点不一样，并没有描述当用户对目标表进行修改后，且事务没有提交之前，没有办法利用缓存结果集的特性，具体情况通过一个例子说明：

[oracle@bjtest ~]$ sqlplus TEST/TEST

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 9 01:16:19 2010

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 100 lines 120
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 as
2 select *
3 from all_objects;

表已创建。

SQL> set autot on
SQL> select /*+ result_cache */ count(*) from t_cache;

COUNT(*)
----------
69433

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

------------------------------------------------------------------------------------------
| Id | Operation           | Name                       | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   196   (1)| 00:00:03 |
|   1 | RESULT CACHE       | cs0yudzh0cqqb1mc32r032mgkq |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T_CACHE                    | 71356 |   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 /*+ result_cache */ count(*) f
rom t_cache"

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

统计信息
----------------------------------------------------------
          4 recursive calls
          0 db block gets
        575 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

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

COUNT(*)
----------
69433

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

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

1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom 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

缓存结果集对第二次查询已经生效，下面当前会话修改一条记录：

SQL> delete t_cache where rownum = 1;

已删除 1 行。

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

-----------------------------------------------------------------------
| Id | Operation           | Name    | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | DELETE STATEMENT    |         |     1 |   196   (1)| 00:00:03 |
|   1 | DELETE             | T_CACHE |       |            |          |
|* 2 |   COUNT STOPKEY     |         |       |            |          |
|   3 |    TABLE ACCESS FULL| T_CACHE | 71356 |   196   (1)| 00:00:03 |
-----------------------------------------------------------------------

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

2 - filter(ROWNUM=1)

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

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

SQL> select /*+ result_cache */ 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 /*+ result_cache */ count(*) f
rom t_cache"

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

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        506 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

SQL> select /*+ result_cache */ 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 /*+ result_cache */ count(*) f
rom t_cache"

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

当前会话由于修改了T_CACHE表，因此原本的缓存结果集对于当前会话的查询是不正确的，但是当前会话的第一次查询并没有讲结果保存到结果集缓存中，第二次执行同样的查询仍然需要访问表。

对于其他的会话由于修改还未提交，因此原本的缓存结果集仍然生效：

SQL> set sqlp 'SQL2> '
SQL2> set autot on
SQL2> select /*+ result_cache */ count(*) from t_cache;

COUNT(*)
----------
69433

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

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

1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"

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

Oracle为了保证一致性读，因此在会话修改了表的记录后，不再启用缓存结果集的机制。

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

转载于:http://blog.itpub.net/4227/viewspace-673011/

ckawt40802

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle11新特性——SQL缓存结果集（五）

打算写一系列的文章介绍11g的新特性和变化。这篇文章讨论DML对SQL缓存结果集的影响。Oracle11新特性——SQL缓存结果集（一）：http://yangtingkun.itpub.net/post/468/391...
复制链接

扫一扫