Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>Oracle11新特性——SQL缓存结果集（四）

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

时间：2010-09-08 来源：yangtingkun

打算写一系列的文章介绍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为了保证一致性读，因此在会话修改了表的记录后，不再启用缓存结果集的机制。