查看: 19389|回复: 22

[精华] Oracle11新特性——SQL缓存结果集

[复制链接]
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2007-9-4 17:26 | 显示全部楼层 |阅读模式
SQL语句的缓存结果集功能是Oracle11g中最吸引我的一个新功能。

DB_BUFFER只能缓存访问过的BLOCK,部分解决了物理读的问题,查询仍然需要大量的逻辑读。

物化视图提供的是查询重写的功能,对于大部分的情况,只是简化了SQL的复杂度,即使是完全满足查询的条件,物化视图的扫描也是不可避免的。

而缓存结果集——RESULT CACHE则截然不同,它缓存的是查询的结果。不在需要大量的逻辑读,不在需要任何的复杂计算,而是直接将已经缓存的结果返回。

Oracle新增了两个HINT,RESULT_CACHE和NO_RESULT_CACHE。通过这两个提示,可以明确的指出下面的语句是否进行RESULT CACHE。

Oracle还增加了几个初始化参数来管理RESULT CACHE功能,如:RESULT_CACHE_MODE、RESULT_CACHE_MAX_SIZE等。RESULT_CACHE_MAX_SIZE指明SGA中RESULT CACHE功能可以使用的最大的内存容量。如果这个参数设置为0,则关闭RESULT CACHE功能。RESULT_CACHE_MODE参数设置Oracle如何使用RESULT CACHE,该参数有三个值:MANUAL、AUTO、FORCE。后面会通过几个例子来说明三种情况的区别。

Oracle提供了DBMS_RESULT_CACHE包来管理和维护RESULT CACHE。

Oracle还新增了几个关于RESULT CACHE的系统视图,用户可以看到和RESULT CACHE相关的各种信息,视图包括:V$RESULT_CACHE_DEPENDENCY、V$RESULT_CACHE_MEMORY、V$RESULT_CACHE_OBJECTS和V$RESULT_CACHE_STATISTICS等。

首先先看一下RESULT_CACHE所带来的性能提升:

[PHP]

SQL> create table t as select * from dba_objects;

表已创建。

SQL> set autot on
SQL> set timing on
SQL> select count(*) from t;

COUNT(*)
----------
68324

已用时间: 00: 00: 00.12

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

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 52976 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------

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

统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1066 consistent gets
1006 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 /*+ result_cache */ count(*) from t;

COUNT(*)
----------
68324

已用时间: 00: 00: 00.21

执行计划
----------------------------------------------------------
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 | 52976 | 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"


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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1064 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 /*+ result_cache */ count(*) from t;

COUNT(*)
----------
68324

已用时间: 00: 00: 00.00

执行计划
----------------------------------------------------------
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 | 52976 | 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"


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

统计信息
----------------------------------------------------------
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

.
[/PHP]

由于第二次执行SQL语句的时候使用了RESULT_CACHE提示,Oracle将这个查询的结果集记录在共享内存中,当第三次查询执行的时候,不需要进行任何的处理,而直接读取结果集,几乎没有任何的消耗,逻辑读为0,运行时间也几乎为0。

下面简单说明一下RESULT_CACHE_MODE的三种模式下,缓存结果集是如何工作的。

当参数值设置为MANUAL时,只有通过HINT明确提示的SQL才会读取缓存结果集。如果不加提示,那么Oracle不会利用已经缓存的结果。

[PHP]

SQL> alter session set result_cache_mode = manual;

会话已更改。

SQL> select /*+ result_cache */ object_name from t where object_name = 'DUAL';

OBJECT_NAME
-----------------------------------------------------------
DUAL
DUAL

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

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE |fhp10hrt50095fufmw3s75txcx| | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DUAL')

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

1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o
ere object_name = 'DUAL'"


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

统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
366 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)
2 rows processed

SQL> select /*+ result_cache */ object_name from t where object_name = 'DUAL';

OBJECT_NAME
------------------------------------------------------------
DUAL
DUAL

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

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | fhp10hrt50095fufmw3s75txcx| | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DUAL')

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

1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o
ere object_name = 'DUAL'"


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

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
366 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)
2 rows processed

SQL> select object_name from t where object_name = 'DUAL';

OBJECT_NAME
-----------------------------------------------------------
DUAL
DUAL

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

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME"='DUAL')

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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1065 consistent gets
0 physical reads
0 redo size
366 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)
2 rows processed

.
[/PHP]

当设置为MANUAL时,只有使用HINT的情况下,Oracle才会利用缓存结果集。而对于AUTO模式,Oracle如果发现缓冲结果集已经存在,那么就会使用。但是如果缓冲结果集不存在,Oracle并不会自动进行缓冲。只有使用HINT的情况下,Oracle才会将执行的结果集缓存。

[PHP]

SQL> alter session set result_cache_mode = auto;

会话已更改。

SQL> select object_name from t where object_name = 'DUAL';

OBJECT_NAME
------------------------------
DUAL
DUAL

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

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE |fhp10hrt50095fufmw3s75txcx| | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DUAL')

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam
t_name = 'DUAL'"


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

统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
366 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)
2 rows processed

SQL> select object_name from t where object_name = 'DUAL';

OBJECT_NAME
------------------------------
DUAL
DUAL

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | fhp10hrt50095fufmw3s75txcx | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DUAL')

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam
t_name = 'DUAL'"


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

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
366 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)
2 rows processed

SQL> select object_name from t where object_name = 'DBA_TABLES';

OBJECT_NAME
------------------------------
DBA_TABLES
DBA_TABLES

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

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w| | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_TABLES')

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam
t_name = 'DBA_TABLES'"


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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1065 consistent gets
0 physical reads
0 redo size
372 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)
2 rows processed

SQL> select object_name from t where object_name = 'DBA_TABLES';

OBJECT_NAME
------------------------------
DBA_TABLES
DBA_TABLES

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_TABLES')

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam
t_name = 'DBA_TABLES'"


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

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1012 consistent gets
0 physical reads
0 redo size
372 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)
2 rows processed

.
[/PHP]

对比上面两种情况,由于第一个SQL的缓存结果集已经存在,Oracle采用了缓存结果集,而对于第二个SQL,Oracle是不会自动使用缓存结果集的。

[PHP]

SQL> select /*+ result_cache */ object_name from t where object_name = 'DBA_TABLES';

OBJECT_NAME
------------------------------
DBA_TABLES
DBA_TABLES

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_TABLES')

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

1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o
ere object_name = 'DBA_TABLES'"


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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1065 consistent gets
0 physical reads
0 redo size
372 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)
2 rows processed

SQL> select object_name from t where object_name = 'DBA_TABLES';

OBJECT_NAME
------------------------------
DBA_TABLES
DBA_TABLES

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_TABLES')

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

1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam
t_name = 'DBA_TABLES'"


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

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
372 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)
2 rows processed

.
[/PHP]

当使用HINT后,结果集被缓存,随后的相同的查询就会使用缓存的结果集。

对于FORCE参数,顾名思义,就是会对所有的SQL进行缓存,除非明确使用NO_RESULT_CACHE提示:

[PHP]

SQL> alter session set result_cache_mode = force;

会话已更改。

SQL> select object_name from t where object_name = 'DBA_VIEWS';

OBJECT_NAME
------------------------------
DBA_VIEWS
DBA_VIEWS

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | bdkgz2jxpb7tc8pkwn478qt3p0 | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_VIEWS')

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

1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select object_name from t wh
BA_VIEWS'"


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

统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
1065 consistent gets
0 physical reads
0 redo size
371 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)
2 rows processed

SQL> select object_name from t where object_name = 'DBA_VIEWS';

OBJECT_NAME
------------------------------
DBA_VIEWS
DBA_VIEWS

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
| 1 | RESULT CACHE | bdkgz2jxpb7tc8pkwn478qt3p0 | | | | |
|* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME"='DBA_VIEWS')

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

1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select object_name from t wh
BA_VIEWS'"


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

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
371 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)
2 rows processed

SQL> select /*+ no_result_cache */ object_name from t where object_name = 'DBA_VIEWS';

OBJECT_NAME
------------------------------
DBA_VIEWS
DBA_VIEWS

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

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME"='DBA_VIEWS')

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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1065 consistent gets
0 physical reads
0 redo size
371 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)
2 rows processed

.
[/PHP]

原文出自:http://yangtingkun.itpub.net/post/468/391015
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2007-9-4 17:26 | 显示全部楼层
上一篇介绍了SQL缓存结果集,这里简单的介绍一下缓存结果集的特点。为了测试方便,将RESULT_CACHE_MODE设置为AUTO模式。

[PHP]

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

.
[/PHP]

设置参数为AUTO后,第二次执行SQL已经使用了RESULT CACHE。下面来看RESULT CACHE的第一个特点,由于RESULT CACHE所使用的内存来自SGA,因此RESULT CACHE的结果对于所有的SESSION都是可见的。

下面启动第二个SQLPLUS SESSION:

[PHP]

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

.
[/PHP]

由于SELECT COUNT(*) FROM T的结果已经保存在SGA中,其他的SESSION也是可以使用这个缓存结果集的。

RESULT CACHE是满足一致性读的,而且当缓存表数据发生变化的时候,Oracle会自动INVALIDATE缓存结果集:

[PHP]

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

.
[/PHP]

由于进行了修改,当前会话查询将无法在利用RESULT CACHE,但是这个时候如果从第二个会话进行查询:

[PHP]

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

.
[/PHP]

由于会话1没有提交,修改对于会话2来说是不可见的。因此会话2仍然可以使用缓存结果集。

[PHP]

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

.
[/PHP]

将会话1的修改回滚,则会话1又可以利用RESULT CACHE。

[PHP]

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

.
[/PHP]

由于进行DDL操作,Oracle自动INVALIDATE了RESULT CACHE结果集,可以通过V$RESULT_CACHE_OBJECTS观察到:

[PHP]

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

.
[/PHP]

最后看一下Oracle RESULT CACHE的另外一个特点,是否使用RESULT_CACHE与Oracle的执行计划无关:

[PHP]

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

.
[/PHP]

从最后几个查询可以看出,RESULT CACHE忽略SQL语句的执行计划,即使执行计划发生了变化,只有最终获取的结果是一样的,Oracle仍然会使用RESULT CACHE。

注意当执行计划不同导致结果不同时,Oracle使用RESULT CACHE可能会得到不正确的结果。


原文出自:http://yangtingkun.itpub.net/post/468/391560

使用道具 举报

回复
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2007-9-4 17:26 | 显示全部楼层
上一篇介绍了RESULT CACHE的一些特点,其中最后提到了使用RESULT CACHE可能会导致查询结果不正确。下面看一个简单的例子:

[PHP]

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

.
[/PHP]

先将RESULT_CACHE_MODE设置为MANUAL,观察全表扫描和索引全扫描两种查询的结果。由于执行计划的不同,虽然语句本身是一样的,但是返回结果中记录的顺序是不同的。在这个例子中,如果使用全表扫描会返回ICOL$,而采用索引扫描,返回结果为C_OBJ#。

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

[PHP]

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

.
[/PHP]

由于第二个查询指定使用了RESULT CACHE,虽然Oracle显示的执行计划是索引扫描,但是利用的缓存结果集是全表扫描生成的,所以第二个查询得到的结果也和第一个查询一样。

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

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

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

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

[PHP]

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

.
[/PHP]

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

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

[PHP]

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

.
[/PHP]

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

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


原文出自:http://yangtingkun.itpub.net/post/468/392028

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
122
马上加薪
日期:2014-02-19 11:55:14ITPUB官方微博粉丝徽章
日期:2011-06-28 19:45:36管理团队成员
日期:2011-05-07 01:45:082010广州亚运会纪念徽章:拳击
日期:2011-03-29 13:11:152010广州亚运会纪念徽章:篮球
日期:2011-02-20 22:50:172011新春纪念徽章
日期:2011-02-18 11:42:492011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2007-9-4 18:11 | 显示全部楼层
还是老杨研究得深入

使用道具 举报

回复
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2007-9-4 18:18 | 显示全部楼层
最初由 NinGoo 发布
[B]还是老杨研究得深入 [/B]


对这个特性比较感兴趣而已。

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-9-4 19:36 | 显示全部楼层
这个特性纯粹就是缓存了一个结果,其他什么都没有吗?抱歉11g还没装呢无法自己测试

比如先执行了一个
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行。
====================================
然后执行SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T WHERE OWNER='YANGTK' GROUP BY OWNER;能使用这个特性吗?

或者先执行select sum(col1) from table
再执行select sum(col1) from table where col2='abc'能用到cache result吗?

我觉得如果要充分利用这个功能也应该引入类似物化视图的query rewrite,仅仅是sql一样才能使用似乎还是笨了点。这个特性应该对OLTP系统帮助很大吧

另外想到一点,如果返回的结果集很大,那么是否会大量消耗RESULT_CACHE这部分内存?难道也用一个LRU列表来维护?

不好意思问题有点多,呵呵

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
122
马上加薪
日期:2014-02-19 11:55:14ITPUB官方微博粉丝徽章
日期:2011-06-28 19:45:36管理团队成员
日期:2011-05-07 01:45:082010广州亚运会纪念徽章:拳击
日期:2011-03-29 13:11:152010广州亚运会纪念徽章:篮球
日期:2011-02-20 22:50:172011新春纪念徽章
日期:2011-02-18 11:42:492011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2007-9-4 19:46 | 显示全部楼层
缓存的不一定是整条sql的结果,可以是某一部分,比如sql中排序部分的结果

单个结果可以占用的内存是有限制的,有参数result_cache_max_result来设定,默认是整个result cache的5%好像

使用道具 举报

回复
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2007-9-4 21:16 | 显示全部楼层
如NinGoo所说,result_cache_max_result设置单个sql语句占用整个RESULT CACHE缓冲区的百分比,默认5%。
RESULT_CACHE_MAX_SIZE用来设置RESULT CACHE的总体大小

使用道具 举报

回复
论坛徽章:
19
生肖徽章:羊
日期:2006-09-06 21:18:482012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15CTO参与奖
日期:2009-02-12 11:45:48生肖徽章2007版:龙
日期:2008-12-16 14:04:41奥运会纪念徽章:篮球
日期:2008-10-24 13:29:38奥运会纪念徽章:沙滩排球
日期:2008-07-02 12:09:31生肖徽章2007版:鼠
日期:2008-01-02 17:35:53ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44会员2007贡献徽章
日期:2007-09-26 18:42:10
发表于 2007-9-4 21:35 | 显示全部楼层
这个功能看着真好,老杨研究也够细,后面一个造成执行计划没按要求走也够可怕,我感觉还好是有点bug的样子啊,如果oracle不认为是bug以后可得注意了。
以后11g上的系统,合理利用sql缓存结果集的话,只要内存够用,很多全表扫描的sql性能也不会那么差了,调优看cost也不准了,看着cost上万而且全表扫描的sql,搞不好结果集被缓存了,即使1分钟执行1000遍,9i和10g上跑再好的机器都抗不住的sql,到11g上反而没啥资源消耗了!!
刚好有个基于报表工具的BI系统,写的有点问题,取相同结果集的sql,总要跑N遍,可惜是自动产生的sql每次都有点小变化,到时候放到11g上看看缓存结果集的功能够不够智能,如果在真实环境下也有试验里看的这么好就很爽了

使用道具 举报

回复
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2007-9-4 22:01 | 显示全部楼层
给RESULT_CACHE_MAX_SIZE多大的空间,以及RESULT CACHE采用何种过期算法还有待研究。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表