ITPub博客

首页 > 数据库 > Oracle > [20141219]result cache与view.txt

[20141219]result cache与view.txt

原创 Oracle 作者:lfree 时间:2014-12-23 09:22:25 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6467207