oracle 11g result_cache分析

11g推出result cache特性
可以在service 端和client 端 cache 結果集

service 端由兩部分組成
1.sql query result cache（儲存SQl查詢的結果集）
2.pl/sql funcation result cache（存pl/sql 函式結果集）

SQL> show user
USER is "SYS"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5 ~~~控制單個cache結果可以佔總server result cache大小%
result_cache_max_size                big integer 1248K ~~~~cache的大小，若為0表示禁用
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL>

result_cache_mode：manual 查詢時候要手動加hint result_cache 通知優化器才可以
auto:優化器會判斷是否將使用已經存在的result cache（用hint產生的result cache）（依據查詢執行的頻率、生成結果的成本以及針對底層資料庫物件更改的頻率。）,另外只有使用hint的時候產生result cache，oracle才會使用
force:儘可能的cache 查詢結果，將result_cache加到select語句中（no_result_cache 可拒絕cache）

SSQL> create user xh identified by a831115
2 ;

User created.

SQL> grant dba to xh
2 ;

Grant succeeded.

SQL> conn xh/a831115
Connected.
SQL> create table xht as select * from all_objects;

Table created.

SQL> alter system flush buffer_cache;

System altered.
SQL> set autotrace trace
SQL> select count(*) from xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   278   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| XHT | 63554 |   278   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

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

SQL> select count(*) from xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   278   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| XHT | 63554 |   278   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

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

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

Note
-----
- dynamic sampling used for this statement

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

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

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
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> execute dbms_result_cache.flush~~~~~~~~~~~~~~~~~~~~~~

PL/SQL procedure successfully completed.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

Note
-----
- dynamic sampling used for this statement

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

SQL> select dbms_result_cache.status from dual;

STATUS
--------------------------------------------------------------------------------
ENABLED

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 = 1248K bytes (1248 blocks)
Maximum Result Size = 62K bytes (62 blocks)
[Memory]
Total Memory = 98632 bytes [0.065% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 93500 bytes [0.062% of the Shared Pool]
....... verhead = 60732 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.

SQL> select * from v$result_cache_memory where free='NO';

        ID      CHUNK     OFFSET FRE OBJECT_ID   POSITION
---------- ---------- ---------- --- ---------- ----------
         0          0          0 NO           0          0
         1          0          1 NO           1          0

SQL> col name for a40
SQL> select * from v$result_cache_statistics;

        ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 Block Size (Bytes)                             1024
         2 Block Count Maximum                            1248
         3 Block Count Current                              32
         4 Result Size Maximum (Blocks)                     62
         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.

SQL> select id,type,name,block_count,row_count from v$result_cache_objects;

        ID TYPE       NAME                                     BLOCK_COUNT
---------- ---------- ---------------------------------------- -----------
ROW_COUNT
----------
         0 Dependency XH.XHT                                             1
         0

         1 Result     select /*+result_cache*/ count(*) from x           1
                      ht
         1

下面看下auto情況

SQL> alter system set result_cache_mode=AUTO ~~動態
2 ;

System altered.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> alter system flush buffer_cache;

System altered.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

Note
-----
- dynamic sampling used for this statement

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

SQL> select count(*) from xht; 不加 HINT

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT); attributes=(single-row)
; name="select count(*) from xht"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets~~~~~~~~~~~~~~~~~~~~~(要多查幾次才行，因為auto自動根據查詢頻率等判斷是否有必要使用生成的result cache)
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> create table xht2 as select * from all_objects;

Table created.

SQL> select count(*) from xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

Note
-----
- dynamic sampling used for this statement

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

SQL> select count(*) from xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

Note
-----
- dynamic sampling used for this statement

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

SQL> select count(*) from xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets~~~~~~~~~~~~~~~~~~~~但沒有使用
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

QL> select /*+result_cache*/count(*) from xht2; ~~~必須建立這個result cache時用HINT 才可以

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT2); attributes=(single-row); name="se
lect /*+result_cache*/count(*) from xht2"

Note
-----
- dynamic sampling used for this statement

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

SQL> select count(*) from xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

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
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

關於sys object

SQL> set autotrace trace exp
SQL> select /*+result_cache*/ count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> select /*+result_cache*/ count(*) from xh.xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xh.xht"

Note
-----
- dynamic sampling used for this statement

可以看到並不支援sys object ，這也很明顯所有基表，資料字典都屬於sys object 如果對他們可以result cache 那麼將會用非常大的記憶體

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

oracle 11g result_cache分析

相關文章