溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
1、result_cache_mode比表注釋優先使用的情況。
create table test_Result_cache (id number) result_cache (mode default);
mode default這個值僅移除任何已經設置的表注釋，并不允許包含這張表的查詢結果進行緩存。
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
上面創建表的語句與下面創建表的語句其作用是一樣的。
create table test_Result_cache (id number)
下面查看一下相關結果集緩存參數的設置
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
這時需要對結果集進行緩存可以使用查詢提示，如下
select /*+result_cache*/* from test_Result_cache
可以通過下面方式查看結果集是否成功緩存
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：表示成功緩存結果集的數量。
2、result_cache_mode比表注釋優先使用的情況二。
alter table test_result_cache result_cache(mode force);
這時確保result_cache_mode的值為MANUAL
SQL> show parameter result_cache_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
清空結果集緩存中的數據。
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
通過下面的語句測試情況
SQL> select /*+no_result_cache*/* from test_Result_cache;
執行計劃
----------------------------------------------------------
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)
從上面的查看結果中看出，查詢并沒有的使用結果集緩存中的內容。也可以直接查詢相關的視圖
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
其結果也是一樣。
3、表注釋優先于result_cache_mode的情況。
alter table test_result_cache result_cache(mode force);
這時可以查看一下result_cache_mode的值
SQL> show parameter result_cache_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
這時通過下面的查詢會直接讀取結果集緩存中的數據
SQL> select * from test_Result_cache;
執行計劃
----------------------------------------------------------
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)
也可以直接查看緩存結果集的數量
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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
