[20141219]result cache与view.txt

[20141219]result cache与view.txt

--result cache是11g的新特性，能一定程度减少逻辑读，我个人的感觉特别适合很少修改，经常访问的小表，而应用中经常扫描的表，
--我经常把这种应用模式叫刷屏软件....
--前一阵子我在做优化工作中，遇到的一些问题，做一些总结：

SCOTT@test> @ver1

PORT_STRING          VERSION        BANNER
-------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx  11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> alter table dept result_cache (mode force);
Table altered.

--这样在访问表dept时，执行计划会出现

SCOTT@test> set autotrace traceonly
SCOTT@test> select * from dept ;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |     6 |   108 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 53wb6s8an5tjq1dukvxhkvaruk |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SCOTT.DEPT); name="select * from dept "
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        829  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)
          6  rows processed

--多执行几次，可以发现以后执行consistent gets=0.

--但是当我们建立视图有会出现什么情况呢？

SCOTT@test> create view v_dept as select * from dept ;
View created.

SCOTT@test> select * from v_dept ;
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   108 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     6 |   108 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        837  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)
          6  rows processed

--可以发现无论执行多次，逻辑读依旧存在。执行计划也没有出现RESULT CACHE的操作。

--加入提示看看：

SCOTT@test> select /*+ result_cache */ * from v_dept ;
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |     6 |   108 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 0bt1tqpkmhcnbb4nzrpfdxgp3b |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=3; dependencies=(SCOTT.DEPT, SCOTT.V_DEPT); name="select /*+ result_cache */ * from v_dept "
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        829  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)
          6  rows processed

--可以发现加入提示才有效。看看是否能修改视图定义来解决问题问题。

create  or replace  view v_dept as select /*+ result_cache */ * from dept ;

SCOTT@test> select  * from v_dept ;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3953577376
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     6 |   180 |     4   (0)| 00:00:01 |
|   1 |  VIEW               | V_DEPT                     |     6 |   180 |     4   (0)| 00:00:01 |
|   2 |   RESULT CACHE      | dnjgv6csdqu5dfabfssvp9zpwj |       |       |            |          |
|   3 |    TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   2 - column-count=3; dependencies=(SCOTT.DEPT); name="SCOTT.V_DEPT"
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        829  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)
          6  rows processed

--取消对表的result_cache。
SCOTT@test> alter table dept result_cache (mode manual);
Table altered.

SCOTT@test> select  * from v_dept ;
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3953577376
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     6 |   180 |     4   (0)| 00:00:01 |
|   1 |  VIEW               | V_DEPT                     |     6 |   180 |     4   (0)| 00:00:01 |
|   2 |   RESULT CACHE      | dnjgv6csdqu5dfabfssvp9zpwj |       |       |            |          |
|   3 |    TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   2 - column-count=3; dependencies=(SCOTT.DEPT); name="SCOTT.V_DEPT"

Statistics
----------------------------------------------------------
        124  recursive calls
          0  db block gets
         89  consistent gets
          0  physical reads
          0  redo size
        829  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
          6  rows processed

--可以发现视图的提示依旧有效！

--如果修改视图定义加入只读特性呢？

create  or replace  view v_dept as select /*+ result_cache */ * from dept  with read only;

SCOTT@test> set autotrace traceonly
SCOTT@test> select  * from v_dept ;
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   108 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     6 |   108 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        837  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
--晕！视图加入with read only后，result_cache的提示无效。为什么？感觉这个是bug。

SCOTT@test> select  /*+ result_cache */ * from v_dept ;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |     6 |   108 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 0bt1tqpkmhcnbb4nzrpfdxgp3b |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=3; dependencies=(SCOTT.DEPT, SCOTT.V_DEPT); name="select  /*+ result_cache */ * from v_dept "
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        829  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
--需要再加提示才有效！

--再回过来看看定义视图加入提示的情况：
create  or replace  view v_dept as select /*+ result_cache */ * from dept;
--前面已经提到这样执行执行select * from v_dept有效。

SCOTT@test> select   * from v_dept where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3953577376
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     6 |   180 |     4   (0)| 00:00:01 |
|*  1 |  VIEW               | V_DEPT                     |     6 |   180 |     4   (0)| 00:00:01 |
|   2 |   RESULT CACHE      | dnjgv6csdqu5dfabfssvp9zpwj |       |       |            |          |
|   3 |    TABLE ACCESS FULL| DEPT                       |     6 |   108 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=10)
Result Cache Information (identified by operation id):
------------------------------------------------------
   2 - column-count=3; dependencies=(SCOTT.DEPT); name="SCOTT.V_DEPT"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--虽然逻辑读是0，而且执行计划是全表扫描，这样执行计划要从结果集里面找到deptno=10的行，需要花费的大量的cpu，特别在结果集
--很大的情况下。

总结：可以发现如果对表定义了result_cache (mode force);,对于定义的视图并没有效果，需要在视图定义中加入提示result_cache.
      而在视图中定义提示result_cache存在许多弊端，建议最好避免。

时间： 2024-07-30 10:52:45

[20141219]result cache与view.txt的相关文章

[20141223]result cache 3.txt

[20141223]result cache 3.txt --上午的测试有一些问题,做一些更正. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86

[20150924]result cache problem.txt

[20150924]result cache problem.txt --昨天看了连接,看到一个关于result cache的例子,重复测试看看: --链接 https://jonathanlewis.wordpress.com/2015/09/22/result-cache/ 1.环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --

[20160919]Result cache问题.txt

[20160919]Result cache问题.txt --看了链接http://blog.dbi-services.com/result-cache-side-effects-on-number-of-calls/,重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------

[20151014]关于result cache.txt

[20151014]关于result cache.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://blo

[20151015]关于result cache 2.txt

[20151015]关于result cache 2.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://b

[20111230]11Gr2 result cache[1].txt

11G的result cache是一个很吸引人的特性,可以大幅减少逻辑读取,特别对于一些经常执行的语句,而结果不是经常变化的,效果不错,我的测试遇到一个小问题. SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0

1223 result cache,sql profile,sql patch

[20141223]result cache 与sql profile,sql patch.txt --前面blog已经提到result cache的好处与缺点,对于第三方优化,sql profile可以改变稳定执行计划,是否可以通过改变提示来稳定 --执行计划,这样对频繁执行的语句较少逻辑读,提高服务器响应有积极意义. --sql patch 也具有相似的作用,看看这种方式是否可行. SCOTT@test> @ver1 PORT_STRING                    VERSIO

oracle result cache 结果集缓存的使用

结果集缓存缓存是一种最常见的在计算机系统用来提高性能的技术.硬件和软件被广泛的使用.oracle数据库也不会例外.例如在缓冲区缓存中缓存数据文件块,在字典缓存中缓存字典信息,在库缓存中缓存游标.在oracle11G中结果集缓存也是可用的. 结果集缓存是如何工作的oracle数据库引擎提供了三种结果集缓存:服务器结果集缓存也叫查询结果缓存,是一种服务器端缓存,它用来存储查询的结果集. pl/sql函数结果集缓存是一种服务器端缓存,它用来存储pl/sql函数返回的结果集. 客户端结果集缓存是一种客户

oralce 12.1中出现大量Result Cache: RC Latch处理

昨天有个朋友找到我说他们的12.1的库在业务高峰期非常慢,希望我们给予优化支持,经过awr分析,定位到问题为latch free问题,具体定位为:Result Cache: RC Latch.优化之前awr部分信息 awr整体负载情况,证明当前这个库已经比较忙,业务反馈很慢 addr信息和top wait信息,确定是latch free问题比较突出 latch信息统计和ash信息,找出来突出的latch,定位为Result Cache: RC Latch引起该问题 补充大量异常sql 类似sql