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