打算写一系列的文章介绍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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
连接到:
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
------------------------------------------------------------------------------------------
| 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)
统计信息
----------------------------------------------------------
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
------------------------------------------------------------------------------------------
| 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)
统计信息
----------------------------------------------------------
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
------------------------------------------------------------------------------------------
| 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)
统计信息
----------------------------------------------------------
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
当前会话由于修改了T_CACHE表,因此原本的缓存结果集对于当前会话的查询是不正确的,但是当前会话的第一次查询并没有讲结果保存到结果集缓存中,第二次执行同样的查询仍然需要访问表。
对于其他的会话由于修改还未提交,因此原本的缓存结果集仍然生效:
SQL> set sqlp 'SQL2> '
SQL2> set autot on
SQL2> 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)
统计信息
----------------------------------------------------------
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
Oracle为了保证一致性读,因此在会话修改了表的记录后,不再启用缓存结果集的机制。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-673011/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-673011/