商讯信箱
用户名： @
密  码：   注册|忘记密码
登录
个人用户经销商
您的位置：首页 > 技术频道 > 正文

点评Oracle11新特性：SQL缓存结果集

[收藏此页] [打印]
作者：IT168 杨廷琨  2007-10-08
内容导航：
缓存结果集的特点

    为了测试方便，将RESULT_CACHE_MODE设置为AUTO模式。 
SQL> ALTER SESSION SET RESULT_CACHE_MODE = AUTO;
会话已更改。
SQL> SET AUTOT ON
SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ RESULT_CAC
T"
统计信息
----------------------------------------------------------
56 recursive calls
0 db block gets
1019 consistent gets
1007 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
    设置参数为AUTO后，第二次执行SQL已经使用了RESULT CACHE。下面来看RESULT CACHE的第一个特点，由于RESULT CACHE所使用的内存来自SGA，因此RESULT CACHE的结果对于所有的SESSION都是可见的。

    下面启动第二个SQLPLUS SESSION： 
SQL> SET SQLPROMPT 'SQL2> '
SQL2> SET AUTOT ON
SQL2> ALTER SESSION SET RESULT_CACHE_MODE = AUTO;
会话已更改。
SQL2> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
    由于SELECT COUNT(*) FROM T的结果已经保存在SGA中，其他的SESSION也是可以使用这个缓存结果集的。RESULT CACHE是满足一致性读的，而且当缓存表数据发生变化的时候，Oracle会自动INVALIDATE缓存结果集： 

SQL> DELETE T WHERE ROWNUM = 1;
已删除 1 行。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68323
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1011 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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，但是这个时候如果从第二个会话进行查询： 

SQL2> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
    由于会话1没有提交，修改对于会话2来说是不可见的。因此会话2仍然可以使用缓存结果集。 

SQL> ROLLBACK;
回退已完成。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
    将会话1的修改回滚，则会话1又可以利用RESULT CACHE。 
SQL> CREATE INDEX IND_T_OBJECT_ID ON T(OBJECT_ID);
索引已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ALTER TABLE T MODIFY OBJECT_ID NOT NULL;
表已更改。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 3841213438
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 |
---------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
204 recursive calls
0 db block gets
187 consistent gets
151 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
    由于进行DDL操作，Oracle自动INVALIDATE了RESULT CACHE结果集，可以通过V$RESULT_CACHE_OBJECTS观察到： 
SQL> SELECT ID, TYPE, STATUS FROM V$RESULT_CACHE_OBJECTS
2 WHERE NAME = 'SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T';
ID TYPE STATUS
---------- ---------- ---------
1 Result Invalid
最后看一下Oracle RESULT CACHE的另外一个特点，是否使用RESULT_CACHE与Oracle的执行计划无关：
SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 3841213438
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ RESULT_CAC
T"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
158 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 3841213438
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
158 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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 /*+ FULL(T) */ COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+
FROM T"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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 /*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 2391220499
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 153 (1)| 00:00:02 |
| 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FULL SCAN| IND_T_OBJECT_ID | 68324 | 153 (1)| 00:00:02 |
----------------------------------------------------------------------------------------

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+
) FROM T"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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 A;
COUNT(*)
----------
68324
执行计划
----------------------------------------------------------
Plan hash value: 3841213438
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
158 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
377 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忽略SQL语句的执行计划，即使执行计划发生了变化，只有最终获取的结果是一样的，Oracle仍然会使用RESULT CACHE。

    注意当执行计划不同导致结果不同时，Oracle使用RESULT CACHE可能会得到不正确的结果。
1 2 3
【内容导航】
第1页： 介绍SQL缓存结果集 第2页： 缓存结果集的特点
第3页： SQL缓存结果集与DBMS_RESULT_CACHE包
©版权所有。未经许可，不得转载。
[责任编辑：郑重]
[an error occurred while processing this directive]