oracle 11g result 整理詳細版
11g推出result cache特性
可以在service 端 和client 端 cache 結果集
service 端 由 兩部分組成
1.sql query result cache（儲存SQl查詢的結果集）
2.pl/sql funcation result cache（存pl/sql 函式結果集）
儲存在shared_pool中
SQL> select pool,name ,bytes/1024/1024 from v$sgastat where upper(name) like '%RESULT%';
POOL NAME BYTES/1024/1024
------------ -------------------------- ---------------
shared pool Result Cache: State Objs .002719879
shared pool Result Cache: Memory Mgr .000118256
shared pool Result Cache: Bloom Fltr .001953125
shared pool Result Cache: Cache Mgr .000102997
SQL> select name ,sum(bytes) from v$sgastat where name like 'Result Cache%' group by rollup(name);
NAME SUM(BYTES)
-------------------------- ----------
Result Cache: Bloom Fltr 2048
Result Cache: Cache Mgr 108
Result Cache: Memory Mgr 124
Result Cache: State Objs 2852
5132
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>
*一致性當dml,ddl修改result cache 依賴的表，result cache將失效,即使沒有任何實際變化,也可能會失效（比如一個select for update 後commit）
*result cache通過比較sql_text確認是否有可用的result cache可使用，（空格，大小寫可以忽略）
*要是有bind值，bind的值必須完全一致（這是必須的，不同的bind值 非常可能會有不同的result cache產生）
*當使用pl/sql函式未指定relies_on字句，使用client resutl cache,result_cache_remote_expiration>0 這些情況結果集無法保證一致
與server result cache有關的引數
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）,既所有不包含no_result_cache hint的select都會使用 result cache
result_cache_max_size ：單位位元組，指定用來作為result cache的shared pool大小，0表示禁用result cache，defalut是oracle根據shared pool大小自動推算出來的(system級修改)
result_cache_max_result:~控制單個cache結果可以佔總server result cache大小 單位(%)預設5,取值1-100 (system級修改)
result_cache_remote_expiration :基於遠端物件的結果集的臨時有效期（單位 分鐘），遠端物件（比如通過dblink訪問）的結果集無法由於遠端物件的變更而變成失效，所以這個引數定義多久後失效，預設為0表示遠端result cache 被禁止
與client result cache有關的引數
*cient result cache最大缺點無法保證一致性（輪詢的關係）
引數在server上啟用
client_result_cache_lag :指定2次資料庫呼叫之見最大間隔時間（單位 毫秒），oracle使用輪詢機制確認clent result cache是否失效，既定期執行資料庫呼叫來檢查資料庫引擎
檢視是否有result cache被執行失效操作，這個引數就是輪詢之間的間隔（也可以認為就是無效的資料在clinet cache中保留的最長時間），oracle還會在每次其它原因執行資料庫呼叫時候
同時檢查result cache的有效性,降低輪詢cost(靜參)
client_result_cache_size:每個cilent process可以用來作為result cache分配記憶體的最大大小（單位位元組），預設0 表示禁用result cache cilent(靜參)
cilent的sqlnet.ora中也可以設定引數
oci_result_cache_max_size:設定後會覆蓋server 端設定的初始化引數cilent_resutl_cache_size（前提是client_result_cache_size>0，表示啟用了result cache client）
oci_result_cache_max_rset_size:指定單個結果集可以最多使用的記憶體大小（單位位元組）
oci_result_cache_max_rset_rows:指定單個result cache可以儲存的最大返回記錄（行數）
server result測試
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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 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~~~~~~~~~~~~~~~~~~~~~~清除result cache(存sga shared pool中)
PL/SQL procedure successfully completed.
SQL> select /*+result_cache*/ 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 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | | ~~~~~~自動建立了
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 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
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 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 |
-------------------------------------------------------------------
sys object沒建立result cache
SQL> select /*+result_cache*/ count(*) from xh.xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 5nqh8hwjybg8r8gs3sk5r1qfbs | | |
|
| 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 那麼將會用非常大的記憶體
pl/sql函式的result cache，指令碼來自troubleshooting oracle performance
SQL> show user
USER is "XH"
SQL> CREATE TABLE t
2 AS
3 SELECT rownum AS id, rpad('*',100,'*') AS pad
4 FROM dual
5 CONNECT BY level <= 10000;
Table created.
CREATE OR REPLACE FUNCTION f(p IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (t)
IS
l_ret NUMBER;
BEGIN
SELECT count(*) INTO l_ret
FROM t
WHERE id = p;
RETURN l_ret;
END;
/
*RELIES_ON 比較關鍵，如果沒有表變化後，result cache不會失效，這將會造成不一致
SQL> SQL> execute dbms_result_cache.bypass(bypass_mode => TRUE, session => TRUE)
PL/SQL procedure successfully completed.
SQL> SET TIMING ON
SQL> SELECT count(f(1)) FROM t;
SET TIMING OFF
COUNT(F(1))
-----------
10000
Elapsed: 00:00:13.57 ~~~執行用了13秒
SQL> SQL> execute dbms_result_cache.bypass(bypass_mode => FALSE, session => TRUE)
PL/SQL procedure successfully completed.
SQL> SET TIMING ON
SQL> SELECT count(f(1)) FROM t;
SET TIMING OFF
COUNT(F(1))
-----------
10000
Elapsed: 00:00:00.37 ~~~~使用pl/sql 函式 result cache後 用了0.37S
SQL> SQL>
SQL> INSERT INTO t VALUES (-1, 'invalidate...');
COMMIT;
1 row created.
SQL>
Commit complete
由於有 RELIES_ON，所以pl/sql函式 依賴的表失效了
SQL> select * from v$result_cache_objects where status='Invalid';
ID TYPE STATUS BUCKET_NO HASH NAME NAMES CREATION_ CREATOR_UID DEPEND_COUNT BLOCK_COUNT SCN COLUMN_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT ROW_SIZE_MAX ROW_SIZE_MIN ROW_SIZE_AVG BUILD_TIME LRU_NUMBER OBJECT_NO INVALIDATIONS SPACE_OVERHEAD SPACE_UNUSED CACHE_ID CACHE_KEY
---------- ---------- --------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------
-------------- ----- --------- ----------- ------------ ----------- ---------- ------------ ---------- ---------- ---------- ------------ ------------ ------------ ---------- ---------- ---------- ------------- -------------- ------------ --------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------
2 Result Invalid 858 1404027738 "XH"."F"::8."F"#fac892c7867b54c6 #1
如果沒有relies_on那麼將將獲得失效的結果集合
SQL> CREATE OR REPLACE FUNCTION f(p IN NUMBER)
2 RETURN NUMBER
3 RESULT_CACHE
4 IS
5 l_ret NUMBER;
6 BEGIN
7 SELECT count(*) INTO l_ret
8 FROM t
9 WHERE id = p;
10 RETURN l_ret;
11 END;
12 /
Function created.
SQL> SELECT f(-1) FROM dual;
F(-1)
----------
2
SQL> SQL> INSERT INTO t VALUES (-1, 'invalidate...');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT f(-1) FROM dual;
F(-1)
----------
2 ~~~~返回了失效的結果集合
PL/SQL 函式限制
1.out,in out 函式不行
2.定義invoker's right函式不行
3.管道化表函式不行
4.匿名壞中用函式不行
5.in中型別lob,ref cursor,object和物件型別函式不行
管理包
SQL> desc dbms_result_cache
PROCEDURE BYPASS~~~~~~~~~~~~~~~~臨時禁用result cache
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE BOOLEAN IN
SESSION BOOLEAN IN DEFAULT
FUNCTION FLUSH RETURNS BOOLEAN ~~~~~~~~~清除所有 result cache
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
PROCEDURE FLUSH ~~~~~~~~~清除所有 result cache
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER 讓給定資料object的所有result cache失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE INVALIDATE 讓給定資料object的所有result cache失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION INVALIDATE RETURNS NUMBER 讓給定資料object的所有result cache失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
PROCEDURE INVALIDATE 讓給定資料object的所有result cache失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER 讓一個給定的快取條目失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
PROCEDURE INVALIDATE_OBJECT 讓一個給定的快取條目失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER 讓一個給定的快取條目失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE INVALIDATE_OBJECT 讓一個給定的快取條目失效
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE MEMORY_REPORT 記憶體使用報告
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DETAILED BOOLEAN IN DEFAULT
FUNCTION STATUS RETURNS VARCHAR2 顯示result 狀態
相關view
v$result_cache_objects
v$result_cache_memory
v$result_cache_statistics
v$result_cache_dependency
關於 失效
create table t1 (a int);
declare
begin
for i in 1..10000 loop
insert into t1 values(i);
end loop;
commit;
end;
SQL> execute dbms_stats.gather_table_stats('XH','T1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
SQL> set autotrace trace
SQL> select /*+result_cache*/ * from t1;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 10000 | 30000 | 7
(0)| 00:00:01 |
| 1 | RESULT CACHE | 6d9qvk74vzy5p77k3gwt4j4gcy | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 7
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.T1); name="select /*+result_cache*/ * fr
om t1"
Statistics
----------------------------------------------------------
229 recursive calls
0 db block gets
735 consistent gets
27 physical reads
1268 redo size
136879 bytes sent via SQL*Net to client
7746 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL> select status,row_count,scan_count from v$result_cache_objects where cache_id='6d9qvk74vzy5p77k3gwt4j4gcy';
STATUS ROW_COUNT SCAN_COUNT
--------- ---------- ----------
Published 10000 0
SQL> select /*+result_cache*/ * from t1;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 10000 | 30000 | 7
(0)| 00:00:01 |
| 1 | RESULT CACHE | 6d9qvk74vzy5p77k3gwt4j4gcy | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 7
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.T1); name="select /*+result_cache*/ * fr
om t1"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
136879 bytes sent via SQL*Net to client
7746 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> update xh.t1 set a=2;
10000 rows updated.
SQL> commit;
Commit complete.
SQL> select status,row_count,scan_count from v$result_cache_objects where cache_id='6d9qvk74vzy5p77k3gwt4j4gcy';
STATUS ROW_COUNT SCAN_COUNT
--------- ---------- ----------
Invalid 10000 2
SQL> select /*+result_cache*/ * from t1;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 10000 | 30000 | 7
(0)| 00:00:01 |
| 1 | RESULT CACHE | 6d9qvk74vzy5p77k3gwt4j4gcy | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 7
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.T1); name="select /*+result_cache*/ * fr
om t1"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
690 consistent gets~~~~~~~~~~~~~~~~~~~~~~邏輯讀又回來了，但是這個查詢相當於重建result hint儲存最新的
0 physical reads
0 redo size
135019 bytes sent via SQL*Net to client
7746 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> select /*+result_cache*/ * from t1;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 10000 | 30000 | 7
(0)| 00:00:01 |
| 1 | RESULT CACHE | 6d9qvk74vzy5p77k3gwt4j4gcy | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 7
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.T1); name="select /*+result_cache*/ * fr
om t1"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
135019 bytes sent via SQL*Net to client
7746 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> select status,row_count,scan_count from v$result_cache_objects where cache_id='6d9qvk74vzy5p77k3gwt4j4gcy';
STATUS ROW_COUNT SCAN_COUNT
--------- ---------- ----------
Published 10000 1 ~~~~可以看到這個最新的只執行了一次
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-659687/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- query result cache in oracle 11gOracle
- Oracle 11g新特性：Result CacheOracle
- oracle 11g result_cache分析Oracle
- 聊聊Oracle 11g的Result Cache（一）Oracle
- 聊聊Oracle 11g的Result Cache（二）Oracle
- 聊聊Oracle 11g的Result Cache（三）Oracle
- ORACLE 11g Result cache使用指南Oracle
- 聊聊Oracle 11g的Result Cache（四）Oracle
- oracle 11g建立基線詳細資訊Oracle
- oracle 11G RAC 建立詳細過程Oracle
- Oracle 11G DataGuard重啟詳細過程Oracle
- Linux Oracle 11g Dataguard配置詳細步驟LinuxOracle
- Oracle 11g升級PSU詳細步驟Oracle
- 安裝oracle 11g 使用ASM儲存 詳細OracleASM
- Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC EnvironmentOracleDatabase
- 超詳細oracle 11g安裝步驟 win版本Oracle
- ORACLE 11G 搭建dataguard詳細步驟（所有操作總結）Oracle
- RHEL6（CentOS6）安裝 Oracle 11g R2 手記(完整詳細可用版)CentOSOracle
- SQL Query Result Cache的使用和配置--Oracle 11G新特性SQLOracle
- Oracle 11g 的server結果快取result_cache_modeOracleServer快取
- 11g新特性--result caching
- Oracle從10g升級到11g詳細步驟Oracle
- Oracle 11g在RHEL 6.4下的詳細安裝過程Oracle
- Oracle Query Result CacheOracle
- MySql學習筆記--詳細整理--下MySql筆記
- Oracle從10g升級到11g詳細操作步驟Oracle
- Oracle 11g opiodr aborting process unknown ospid (***) as a result of ORA-28Oracle
- 淺談Oracle Result CacheOracle
- Oracle 11g RAC 環境打PSU補丁的詳細步驟Oracle
- ORACLE EXPDP命令使用詳細Oracle
- oracle 事件診斷詳細Oracle事件
- Oracle 11G DataGuard ORA-16086問題修復詳細過程Oracle
- 詳細整理iOS中UITableView的效能最佳化iOSUIView
- oracle 11g streams 配置詳解Oracle
- 【SCN】Oracle SCN 詳細介紹Oracle
- ORACLE AWR報告詳細分析Oracle
- Oracle Stream配置詳細步驟Oracle
- ORACLE 11G透過SCN做增量備份修復standby庫詳細過程Oracle