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

上一篇介绍了RESULT CACHE的一些特点，其中最后提到了使用RESULT CACHE可能会导致查询结果不正确。下面看一个简单的例子：

SQL> SET AUTOT ON
SQL> ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;

会话已更改。

SQL> SELECT /*+ FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;

OBJECT_NAME
------------------------------
ICOL$

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter(ROWNUM=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 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) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;

OBJECT_NAME
------------------------------
C_OBJ#

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

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
343 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_MODE设置为MANUAL，观察全表扫描和索引全扫描两种查询的结果。由于执行计划的不同，虽然语句本身是一样的，但是返回结果中记录的顺序是不同的。在这个例子中，如果使用全表扫描会返回ICOL$，而采用索引扫描，返回结果为C_OBJ#。

下面看看启用缓存结果集的情况：

SQL> SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;

OBJECT_NAME
------------------------------
ICOL$

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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM=1)

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

1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME
= 1"


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 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 /*+ RESULT_CACHE INDEX(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;

OBJECT_NAME
------------------------------
ICOL$

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

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM=1)

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

1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE INDEX(T) */ OBJECT_NAM
M = 1"


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
342 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，虽然Oracle显示的执行计划是索引扫描，但是利用的缓存结果集是全表扫描生成的，所以第二个查询得到的结果也和第一个查询一样。

对于以前的系统中，试图利用索引按照顺序返回结果集的特点来避免排序的写法，在利用RESULT CACHE时，很可能会出现问题。

不知道Oracle是否会认为这是一个bug，是否以后会有所改进。

RESULT CACHE不仅对查询的最终结果生效，查询的中间结果也是可以使用RESULT CACHE的。

在进行测试之前，先将前面的RESULT CACHE清除掉。Oracle提供了一个DBMS_RESULT_CACHE包来管理RESULT CACHE：

SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;

COUNT(*)
----------
5

SQL> EXEC DBMS_RESULT_CACHE.FLUSH

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;

COUNT(*)
----------
0

这个包还包括其他一些功能，比如BYPASS可以设置当前实例绕过RESULT CACHE机制。INVALIDATE可以设置某个对象关联的所有RESULT CACHE失效。STATUS返回结果集状态。

下面看看Oracle如何对查询的中间结果使用RESULT CACHE：

SQL> SET AUTOT ON
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SEGMENTS;

表已创建。

SQL> SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER;

OWNER NUM
------------------------------ ----------
WKSYS 371
MDSYS 1281
YANGTK 13
.
.
.
WMSYS 315
SI_INFORMTN_SCHEMA 8

已选择22行。

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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 132 | 287 (3)| 00:00:04 |
| 1 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 2 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------

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

1 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed

SQL> SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER;

OWNER K
------------------------------ ----------
WKSYS 7616
MDSYS 47744
YANGTK 11392
.
.
.
SYS 854656
WMSYS 7296

已选择18行。

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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 209K| 36 (6)| 00:00:01 |
| 1 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 2 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

1 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"


Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
68 recursive calls
0 db block gets
183 consistent gets
109 physical reads
0 redo size
773 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed

SQL> SELECT A.OWNER, NUM, K FROM
2 (SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER) A,
3 (SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER) B
4 WHERE A.OWNER = B.OWNER;

OWNER NUM K
------------------------------ ---------- ----------
WKSYS 371 7616
MDSYS 1281 47744
YANGTK 13 11392
.
.
.
SYS 29743 854656
WMSYS 315 7296

已选择18行。

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

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 418K| 323 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 7146 | 418K| 323 (3)| 00:00:04 |
| 2 | VIEW | | 22 | 660 | 287 (3)| 00:00:04 |
| 3 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 4 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 5 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
| 6 | VIEW | | 7146 | 209K| 36 (6)| 00:00:01 |
| 7 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 8 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 9 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

1 - access("A"."OWNER"="B"."OWNER")

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

3 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"

7 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"


Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
127 recursive calls
8 db block gets
194 consistent gets
0 physical reads
0 redo size
889 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
18 rows processed

需要注意，对于SQL中间结果使用RESULT CACHE必须使用RESULT_CACHE提示进行强制。

RESULT CACHE功能对于下列情况是无效的：系统表和临时表；序列的NEXTVAL和CURRVAL伪列；SYSDATE、SYSTIMESTAMP等函数；所有非确定性PL/SQL函数