Oracle 11g新特性:Server Result Cache测试

现在我们来看一下Server Result Cache的作用。



首先创建一张测试表：

SQL> connect eygle/eygle

Connected.

SQL> create table eygle as select * from dba_objects;



Table created.

在以前版本中，我们第一次执行该SQL可以看到consistent gets和physical reads大致相同：

SQL> set autotrace on

SQL> select count(*) from eygle;



  COUNT(*)

----------

    15993





Execution Plan

----------------------------------------------------------

Plan hash value: 3602634261



--------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time    |

--------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    1 |    64  (0)| 00:00:01 |

|  1 |  SORT AGGREGATE    |      |    1 |            |          |

|  2 |  TABLE ACCESS FULL| EYGLE | 14489 |    64  (0)| 00:00:01 |

--------------------------------------------------------------------



Note

-----

  - dynamic sampling used for this statement





Statistics

----------------------------------------------------------

        28  recursive calls

          0  db block gets

        282  consistent gets

        217  physical reads

          0  redo size

        420  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

再次执行同样查询时，由于数据Cache在内存中，physical reads会减少到0.

但是consistent gets仍然不变：

SQL> select count(*) from eygle;



  COUNT(*)

----------

    15993





Execution Plan

----------------------------------------------------------

Plan hash value: 3602634261



--------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time    |

--------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    1 |    64  (0)| 00:00:01 |

|  1 |  SORT AGGREGATE    |      |    1 |            |          |

|  2 |  TABLE ACCESS FULL| EYGLE | 14489 |    64  (0)| 00:00:01 |

--------------------------------------------------------------------



Note

-----

  - dynamic sampling used for this statement





Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        221  consistent gets

          0  physical reads

          0  redo size

        420  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

现在我们来看看在Server Result Cache下，Oracle的行为。

首先在result_cache_mode参数设置为MANUAL时：

SQL> show parameter result_cache_mode



NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

result_cache_mode                    string      MANUAL

我们需要在SQL中手工指定Cache，这需要通过加入一个hints来实现，这个hints是result_cache：

SQL> select /*+ result_cache */ count(*) from eygle;



  COUNT(*)

----------

    15993





Execution Plan

----------------------------------------------------------

Plan hash value: 3602634261



------------------------------------------------------------------------------------------

| Id  | Operation          | Name                      | Rows  | Cost (%CPU)| Time    |

------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT    |                            |    1 |    64  (0)| 00:00:01 |

|  1 |  RESULT CACHE      | 76rwwyazv6t6c39f1d8rrqh8rb |      |            |          |

|  2 |  SORT AGGREGATE    |                            |    1 |            |          |

|  3 |    TABLE ACCESS FULL| EYGLE                      | 14489 |    64  (0)| 00:00:01 |

------------------------------------------------------------------------------------------



Result Cache Information (identified by operation id):

------------------------------------------------------



  1 - column-count=1; dependencies=(EYGLE.EYGLE);

  attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"





Note

-----

  - dynamic sampling used for this statement





Statistics

----------------------------------------------------------

          4  recursive calls

          0  db block gets

        280  consistent gets

          0  physical reads

          0  redo size

        420  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

注意到这个执行计划已经和以往的不同，RESULT CACHE以76rwwyazv6t6c39f1d8rrqh8rb名称创建。



那么在接下来的查询中，这个Result Cache就可以被利用：

SQL> select /*+ result_cache */ count(*) from eygle;



  COUNT(*)

----------

    15993





Execution Plan

----------------------------------------------------------

Plan hash value: 3602634261



------------------------------------------------------------------------------------------

| Id  | Operation          | Name                      | Rows  | Cost (%CPU)| Time    |

------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT    |                            |    1 |    64  (0)| 00:00:01 |

|  1 |  RESULT CACHE      | 76rwwyazv6t6c39f1d8rrqh8rb |      |            |          |

|  2 |  SORT AGGREGATE    |                            |    1 |            |          |

|  3 |    TABLE ACCESS FULL| EYGLE                      | 14489 |    64  (0)| 00:00:01 |

------------------------------------------------------------------------------------------



Result Cache Information (identified by operation id):

------------------------------------------------------



  1 - column-count=1; dependencies=(EYGLE.EYGLE);

  attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"





Note

-----

  - dynamic sampling used for this statement





Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        420  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

在这个利用到Result Cache的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。

这就是Result Cache的强大之处。



我们可以通过查询v$result_cache_memory视图来看Cache的使用情况：

SQL> select * from V$RESULT_CACHE_MEMORY

  2  where FREE='NO';



        ID      CHUNK    OFFSET FRE  OBJECT_ID  POSITION

---------- ---------- ---------- --- ---------- ----------

        0          0          0 NO          0          0

        1          0          1 NO          1          0

通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息：

SQL> select * from V$RESULT_CACHE_STATISTICS;



        ID NAME                                VALUE

---------- ------------------------------ ----------

        1 Block Size (Bytes)                  1024

        2 Block Count Maximum                  992

        3 Block Count Current                    32

        4 Result Size Maximum (Blocks)          49

        5 Create Count Success                    1

        6 Create Count Failure                    0

        7 Find Count                              1

        8 Invalidation Count                      0

        9 Delete Count Invalid                    0

        10 Delete Count Valid                      0



10 rows selected.

V$RESULT_CACHE_OBJECTS记录了Cache的对象：

SQL> SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNT FROM V$RESULT_CACHE_OBJECTS; 



        ID TYPE      NAME                          BLOCK_COUNT  ROW_COUNT

---------- ---------- ------------------------------ ----------- ----------

        0 Dependency EYGLE.EYGLE                              1          0

        1 Result    select /*+ result_cache */ cou          1          1

                      nt(*) from eygle

一个新的系统包被引入，DBMS_RESULT_CACHE可以用于执行关于Result Cache的管理：

SQL> set serveroutput on

SQL> exec dbms_result_cache.memory_report

R e s u l t  C a c h e  M e m o r y  R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 992K bytes (992 blocks)

Maximum Result Size = 49K bytes (49 blocks)

[Memory]

Total Memory = 100836 bytes [0.059% of the Shared Pool]

... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]

... Dynamic Memory = 95704 bytes [0.056% of the Shared Pool]

....... Overhead = 62936 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 30 blocks

........... Used Memory = 2 blocks

............... Dependencies = 1 blocks (1 count)

............... Results = 1 blocks

................... SQL    = 1 blocks (1 count)



PL/SQL procedure successfully completed.