11g新特性--result caching

lsq_008發表於2012-11-09
開啟result_cache後，sql的查詢結果被快取到shared_pool中，再次執行相同的查詢時，直接讀取結果，不需要執行該sql了，可以通過兩種方式啟用：

1. 加提示 RESULT_CACHE 

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  FROM hr.employees
  3  GROUP BY department_id;

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 3w0d0687vphfr3gvhgbcpfg89s |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
FROM hr.employees
GROUP BY department_id"



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

修改查詢中涉及到的表的result_cache屬性為force：

SQL> ALTER TABLE hr.employees RESULT_CACHE (MODE FORCE);

Table altered.

SQL> SELECT department_id, AVG(salary)
  2  FROM hr.employees
  3  GROUP BY department_id;

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 3w0d0687vphfr3gvhgbcpfg89s |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT department_id, AVG(salary)
FROM hr.employees
GROUP BY department_id"



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

清除result cache中的快取資料：

SQL> exec DBMS_RESULT_CACHE.FLUSH

PL/SQL procedure successfully completed.

檢視result cache的使用情況：

SQL> set serveroutput on
SQL> EXECUTE 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  = 1376K bytes (1376 blocks)
Maximum Result Size = 68K bytes (68 blocks)
[Memory]
Total Memory = 167864 bytes [0.121% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.008% of the Shared Pool]
... Dynamic Memory = 157168 bytes [0.114% of the Shared Pool]
....... verhead = 124400 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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-748860/，如需轉載，請註明出處，否則將追究法律責任。

相關文章