[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存在许多弊端，建议最好避免。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1376793/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1376793/