oracle表注釋與查詢提示(result_cache_mode)的關(guān)系

1、result_cache_mode比表注釋優(yōu)先使用的情況。

create table test_Result_cache (id number) result_cache (mode default);

mode default這個(gè)值僅移除任何已經(jīng)設(shè)置的表注釋?zhuān)⒉辉试S包含這張表的查詢結(jié)果進(jìn)行緩存。

SQL> select t.table_name,t.result_cache from user_Tables t where t.table_name='TEST_RESULT_CACHE' ;
 
TABLE_NAME                                                                       RESULT_CACHE
-------------------------------------------------------------------------------- ------------
TEST_RESULT_CACHE                                                                DEFAULT

上面創(chuàng)建表的語(yǔ)句與下面創(chuàng)建表的語(yǔ)句其作用是一樣的。

create table test_Result_cache (id number)

下面查看一下相關(guān)結(jié)果集緩存參數(shù)的設(shè)置

SQL> show parameter result_cache;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 4608K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

這時(shí)需要對(duì)結(jié)果集進(jìn)行緩存可以使用查詢提示，如下

select /*+result_cache*/* from test_Result_cache

可以通過(guò)下面方式查看結(jié)果集是否成功緩存

SQL> select id,name,value from v$result_cache_statistics;
 
        ID NAME                                                                             VALUE
---------- -------------------------------------------------------------------------------- ---------------------------------------------------         1 Block Size (Bytes)                                                               1024
         2 Block Count Maximum                                                         4608
         3 Block Count Current                                                              32
         4 Result Size Maximum (Blocks)                                             230
         5 Create Count Success                                                           5
         6 Create Count Failure                                                             0
         7 Find Count                                                                               0
         8 Invalidation Count                                                                   0
         9 Delete Count Invalid                                                                 0
        10 Delete Count Valid                                                                  0
        11 Hash Chain Length                                                                1
        12 Find Copy Count                                                                      0
        13 Latch (Share)                                                                            0

Create Count Success：表示成功緩存結(jié)果集的數(shù)量。

2、result_cache_mode比表注釋優(yōu)先使用的情況二。

alter table test_result_cache result_cache(mode force);

這時(shí)確保result_cache_mode的值為MANUAL

SQL> show parameter result_cache_mode;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      MANUAL

清空結(jié)果集緩存中的數(shù)據(jù)。

SQL> exec dbms_result_cache.Flush;
 
PL/SQL procedure successfully completed

SQL> select id,name,value from v$result_cache_statistics;
 
        ID NAME                                                                             VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Block Size (Bytes)                                                               1024
         2 Block Count Maximum                                                              4608
         3 Block Count Current                                                              0
         4 Result Size Maximum (Blocks)                                                     230
         5 Create Count Success                                                             0
         6 Create Count Failure                                                             0
         7 Find Count                                                                       0
         8 Invalidation Count                                                               0
         9 Delete Count Invalid                                                             0
        10 Delete Count Valid                                                               0
        11 Hash Chain Length                                                                0
        12 Find Copy Count                                                                  0
        13 Latch (Share)                                                                    0

通過(guò)下面的語(yǔ)句測(cè)試情況

SQL> select /*+no_result_cache*/* from test_Result_cache;

執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 5006760

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST_RESULT_CACHE |     2 |    26 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

從上面的查看結(jié)果中看出，查詢并沒(méi)有的使用結(jié)果集緩存中的內(nèi)容。也可以直接查詢相關(guān)的視圖

SQL> select id,name,value from v$result_cache_statistics;
 
        ID NAME                                                                             VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Block Size (Bytes)                                                               1024
         2 Block Count Maximum                                                              4608
         3 Block Count Current                                                              0
         4 Result Size Maximum (Blocks)                                                     230
         5 Create Count Success                                                             0
         6 Create Count Failure                                                             0
         7 Find Count                                                                       0
         8 Invalidation Count                                                               0
         9 Delete Count Invalid                                                             0
        10 Delete Count Valid                                                               0
        11 Hash Chain Length                                                                0
        12 Find Copy Count                                                                  0
        13 Latch (Share)                                                                    0

 其結(jié)果也是一樣。

3、表注釋優(yōu)先于result_cache_mode的情況。

alter table test_result_cache result_cache(mode force);

這時(shí)可以查看一下result_cache_mode的值

SQL> show parameter result_cache_mode;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      MANUAL

這時(shí)通過(guò)下面的查詢會(huì)直接讀取結(jié)果集緩存中的數(shù)據(jù)

SQL> select * from test_Result_cache;

執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 5006760

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 5z4pvwymt41zz4hjnb3pwvcfuy |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST_RESULT_CACHE          |     2 |    26 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(DESIGNER.TEST_RESULT_CACHE); name="select * from test_Result_cache"

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

也可以直接查看緩存結(jié)果集的數(shù)量

SQL> select id,name,value from v$result_cache_statistics;
 
        ID NAME                                                                             VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Block Size (Bytes)                                                               1024
         2 Block Count Maximum                                                              4608
         3 Block Count Current                                                              32
         4 Result Size Maximum (Blocks)                                                     230
         5 Create Count Success                                                             6
         6 Create Count Failure                                                             0
         7 Find Count                                                                       0
         8 Invalidation Count                                                               1
         9 Delete Count Invalid                                                             0
        10 Delete Count Valid                                                               0
        11 Hash Chain Length                                                                1
        12 Find Copy Count                                                                  0
        13 Latch (Share)                                                                    0