oracle result cache 結果集快取的使用
結果集快取
快取是一種最常見的在計算機系統用來提高效能的技術.硬體和軟體被廣泛的使用.oracle資料庫也不會例外.例如在緩衝區快取中快取資料檔案塊,在字典快取中快取字典資訊,在庫快取中快取遊標.在oracle11G中結果集快取也是可用的.
結果集快取是如何工作的
oracle資料庫引擎提供了三種結果集快取:
伺服器結果集快取也叫查詢結果快取,是一種伺服器端快取,它用來儲存查詢的結果集.
pl/sql函式結果集快取是一種伺服器端快取,它用來儲存pl/sql函式返回的結果集.
客戶端結果集快取是一種客戶端快取,用來儲存查詢結果集.
伺服器結果集快取
伺服器結果集快取是用來避免重複執行查詢.簡單來說查詢第一次執行,它的結果集被儲存在共享池中.然後後續執行相同的查詢時從結果集快取中直接提取結果集而不用重新計算.注意這兩個查詢被認為是相等的.因此能使用相同的結果集.另外如果出現繫結變數,那麼它們的值必須相同.這是必須的,因為很明顯,繫結變數作為引數輸入並傳給查詢.因此不同的繫結變數值會有不同的結果集.注意結果集快取是儲存在共享池中,對於一個指定的例項所有連線的會話都能共享相同的快取條目.
下面來舉例說明.注意在查詢時指定了result_cache提示來啟用結果集快取.第一次執行花了1.04秒.可以看到在執行計劃中操作result cache確定了對查詢啟用了結果集快取.在執行計劃中starts列清楚的說明了所有的操作都至少要被執行一次.執行計劃中所有的操作都是必須的因為是第一次執行這個查詢.因此結果集快取還不包含結果集.
SQL> alter session set statistics_level=all; SQL> SELECT /*+ result_cache */ 2 p.prod_category, c.country_id, 3 sum(s.quantity_sold) AS quantity_sold, 4 sum(s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c.country_id; 已選擇81行。 已用時間: 00: 00: 01.04 SQL> select * from table(dbms_xplan.display_cursor(null,null,'all allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID g9sxqz9qgutu7, child number 0 ------------------------------------- SELECT /*+ result_cache */ p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id Plan hash value: 1866882273 -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 985 (100)| | | | 81 |00:00:00.01 | | | | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | | | | | | | 81 |00:00:00.01 | | | | | 2 | SORT GROUP BY | | 1 | 68 | 3808 | 985 (7)| 00:00:12 | | | 0 |00:00:00.01 | 73728 | 73728 | | |* 3 | HASH JOIN | | 1 | 968 | 54208 | 984 (7)| 00:00:12 | | | 0 |00:00:00.01 | 940K| 940K| | | 4 | VIEW | index$_join$_003 | 1 | 72 | 1512 | 3 (34)| 00:00:01 | | | 0 |00:00:00.01 | | | | |* 5 | HASH JOIN | | 1 | | | | | | | 0 |00:00:00.01 | 1269K| 1269K| | | 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 1 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 1 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 8 | VIEW | VW_GBC_9 | 1 | 968 | 33880 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | | | | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | 56M| 4744K| | |* 10 | HASH JOIN | | 1 | 918K| 23M| 941 (3)| 00:00:12 | | | 0 |00:00:00.01 | 3056K| 1398K| | | 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 406 (1)| 00:00:05 | | | 0 |00:00:00.01 | | | | | 12 | PARTITION RANGE ALL| | 1 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | | 13 | TABLE ACCESS FULL | SALES | 28 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
第二次執行只花了0.03秒.在執行計劃中starts列顯示了所有操作的執行次數.RESULT CACHE被執一次.其它的操作沒有被執行.換句話說直接使用了儲存在結果集快取中的結果集.
SQL> SELECT /*+ result_cache */ 2 p.prod_category, c.country_id, 3 sum(s.quantity_sold) AS quantity_sold, 4 sum(s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c.country_id; 已選擇81行。 已用時間: 00: 00: 00.03 SQL> select * from table(dbms_xplan.display_cursor(null,null,'all allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID g9sxqz9qgutu7, child number 0 ------------------------------------- SELECT /*+ result_cache */ p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id Plan hash value: 1866882273 -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 985 (100)| | | | 162 |00:00:00.01 | | | | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | | | | | | | 162 |00:00:00.01 | | | | | 2 | SORT GROUP BY | | 0 | 68 | 3808 | 985 (7)| 00:00:12 | | | 0 |00:00:00.01 | 73728 | 73728 | | |* 3 | HASH JOIN | | 0 | 968 | 54208 | 984 (7)| 00:00:12 | | | 0 |00:00:00.01 | 940K| 940K| | | 4 | VIEW | index$_join$_003 | 0 | 72 | 1512 | 3 (34)| 00:00:01 | | | 0 |00:00:00.01 | | | | |* 5 | HASH JOIN | | 0 | | | | | | | 0 |00:00:00.01 | 1269K| 1269K| | | 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 0 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 0 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 8 | VIEW | VW_GBC_9 | 0 | 968 | 33880 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | | | | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | 56M| 4744K| | |* 10 | HASH JOIN | | 0 | 918K| 23M| 941 (3)| 00:00:12 | | | 0 |00:00:00.01 | 3056K| 1398K| | | 11 | TABLE ACCESS FULL | CUSTOMERS | 0 | 55500 | 541K| 406 (1)| 00:00:05 | | | 0 |00:00:00.01 | | | | | 12 | PARTITION RANGE ALL| | 0 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | | 13 | TABLE ACCESS FULL | SALES | 0 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
在執行計劃中要注意一個名字快取ID它與操作result cache相關.如果知道這個快取ID,可以查詢v$resul_cache_objects檢視來顯示關於快取資料的資訊.下面的查詢顯示快取結果集已經被髮布(換句話說可以使用).檢視資訊會顯示快取結果集是何進建立的,建立它花了多長時間,有多少行記錄被儲存和它被引用了多少次.其它提供關於快取結果集資訊的檢視還有v$result_cache_dependency,v$result_cache_memory和v$result_chace_statistics.
SQL> SELECT status, creation_timestamp, build_time, row_count, scan_count 2 FROM v$result_cache_objects 3 WHERE cache_id = 'cu8pf4s7jn9d05yda1swvwpd7y'; STATUS CREATION_TIMESTAMP BUILD_TIME ROW_COUNT SCAN_COUNT --------- ------------------ ---------- ---------- ---------- Published 2013-7-5 9:21:26 94 81 3
為了保證結果的一致性(也就是說結果集是相同的就是看它是來自快取還是來自資料庫計算).每當查詢中所引用的物件發生改變,那麼依賴於這些表的快取條目將會無效.事實情況就是這樣,即使真正的改變沒有發生.例如.甚至一個select * for update語句後面緊跟著一個commit提交也會導致依賴於select表的快取條目變為無效.
有一些動態初始化引數可以控制伺服器結果集快取:
result_cache_max_size:以byte為單位來指定在共享池中用於結果集快取的記憶體總量.如果它被設定為0,這個功能將會被禁用.預設值是一個比0大的值,它是從共享池中派生出來的.記憶體分配是動態的,因此初始化引數只能指定它的上限.可以使用下面的查詢來顯示當前分配的記憶體大小:
SQL> SELECT name, sum(bytes) FROM v$sgastat WHERE name LIKE 'Result Cache%' GROUP BY rollup(name);
NAME SUM(BYTES)
-------------------------- ----------
Result Cache 161680
Result Cache: Bloom Fltr 2048
Result Cache: Cache Mgr 208
Result Cache: Memory Mgr 200
Result Cache: State Objs 2896
167032
下面的語句顯示了resulr_cache_max_size的值為15424K
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
result_cache_max_size big integer 15424K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
result_cache_mode:指定在什麼情況下使用結果集快取.可以將它設定為manual手動這是預設值或者設定為force.當使用manual時結果集快取只有當指定result_cache提示時才使用.當使用force時那麼會對所有沒有使用no_result_cache提示的所有查詢使用結果集快取.因為在大多數情況下我們只會對少量的查詢使用結果集快取,所以讓該引數設定為它的預設值manual當只需要使用時在查詢中指定result_cache來啟用它.
result_cache_max_result:指定單一個結果集快取的佔用result_cache_max_size伺服器結果集快取大小的百分比.它的預設值是5.它的值可以是0到100的任何一個數字.超過這個限制的結果集快取將是無效的.
result_cache_remote_expiration:指定基於遠端物件的結果集的有效時間(以分鐘為單位).這是必須的,因當遠端物件已經發生改變了基於這些遠端物件的失效的結果集快取不能被執行.當經過初始化引數所指定的有效時間後這些結果集才變為無效.這個引數的預設值是0,意味著基於遠端物件的查詢快取功能被禁用.
初始化引數result_cache_max_size和result_cache_max_result只能在系統級別進行修改.其它的result_cache_mode
和result_cache_remote_expiration能在會話級別進行修改.
注意:將result_cache_remote_expiration引數設定為一個比0大的數字會導致過時的結果集快取存在.因此只有你真正地理解這樣做的影響後才能將它設定為比0大.
使用結果集快取有幾個很明顯的限制:
查詢使用非確定性的函式,序列和臨時表是結果集不會被快取
查詢違反了讀一致性時結果集將不會被快取.例如,當透過會話建立結果集時所引用的表正經歷大量的事務這樣的結果
集不會被快取.
引用資料字典檢視的查詢的結果集不會被快取.
dbms_result_cache包
可以使用dbms_result_cache包來管理結果集快取,它提供了以下程式來進行管理:
bypass:在會話或系統級別臨時禁用或啟用結果集快取.
flush:從結果集快取中清空所有物件
invalidate:讓依賴於指定的資料庫物件的所有結果集快取變為無效
invalidate_object:讓單個結果集快取條目變為無效
memory_report:生成一個記憶體使用情況報告
status:顯示結果集快取的狀態.
例如:
SQL> select dbms_result_cache.status from dual; STATUS -------------------------------------------------------------------------------- ENABLED
pl/sql函式結果集快取
pl/sql函式結果集快取類似於伺服器結果集快取,但是它支援pl/sql函式,還和伺服器結果集快取共享相同的記憶體結構.它的目的是在結果集快取中儲存函式返回的值(僅僅只有函式返回的值,過程返回的值不能被快取).明顯地使用不同輸入值的函式是以不同的快取條目被快取在結果集快取中的.在下面的例子中顯示了一個啟用結果集快取的函式.為了啟用pl/sql函式結果集快取,要指定result_cache子句.還可以指定relies_on子句來指定函式依賴於哪個表來返回結果.
SQL> CREATE OR REPLACE FUNCTION f 2 RETURN NUMBER 3 RESULT_CACHE RELIES_ON(t) IS 4 l_ret NUMBER; 5 BEGIN 6 SELECT count(t.indi_id) INTO l_ret FROM impl_chenzhou.bs_insured t; 7 RETURN l_ret; 8 END; 9 / Function created
在下面的例子中將會呼叫函式2480625次f不使用結果集快取(透過使用bypass過程來臨時禁用結果集快取)共用了4.69秒
SQL> execute dbms_result_cache.bypass(bypass_mode => TRUE, session => TRUE); SQL> select count(f) from impl_chenzhou.bs_insured t; COUNT(F) ---------- 2480625 Elapsed: 00:00:04.69 在下面的例子中將會呼叫函式2480625次f使用結果集快取共用了0.32秒 SQL> execute dbms_result_cache.bypass(bypass_mode => FALSE, session => TRUE) SQL> select count(f) from impl_chenzhou.bs_insured t; COUNT(F) ---------- 2480625 Elapsed: 00:00:00.32
注意:如果relies_on子句沒有指定或者包含錯誤資訊,當函式依賴的物件發生修改結果集快取不會變為無效.因此可能會出現過時的結果集.
使用pl/sql函式結果集有一些限制,下面的函式不能使用結果集快取:
使用out和(或者)in out引數的函式
定義了使用呼叫者許可權的函式
管道化表函式
從匿名塊呼叫函式
使用in引數或者返回值有以下型別的函式:LOB,REF CURSOR,物件和記錄
客戶端結果快取集
客戶端結果集快取是用客戶端快取來存查詢的結果集.它的目的和工作類似於伺服器端結果集快取.與伺服器端的實現進行比較有兩個重要的不同.第一它避免了需要在客戶端/伺服器之間來回地執行sql語句.這是一大優點.第二結果集的失效是基於一種輪詢機制,因此一致性不能保證這是一大缺點.
為了實現這種輪詢客戶端必須定期地執行資料庫呼叫來檢查資料庫引擎看看它的結果集快取是否已經變為無效了.為了輪詢的開銷最小化,每一次客戶端由於其它原因執行一個資料庫呼叫時它將檢查結果集快取的有效性.這樣,就可以避免掉那些用於對快取的結果集進行失效操作的資料庫呼叫.使客戶端能持續地執行"正常的"資料庫呼叫.
儘管它是客戶端快取但還是要在伺服器端來啟用它.下面有些引數來控制客戶端快取:
client_result_cache_size:指定每一個客戶端程式能使用的結果集快取的最大記憶體大小以byte為單位.如果它設定為0,這也是預設值那麼這個功能將禁用.這個初始化引數是靜態的只能在例項級別進行修改.修改完後必須對例項進行重啟才會生效.
client_result_cache_lag:指定兩次資料庫呼叫之間的最大時間間隔以毫秒為單位.也就是說它指定無效的結果集能在客戶端快取中保留多長的時間.預設值是3000.這個初始化引數是靜態的只能在例項級別進行修改.修改完後必須對例項進行重啟才會生效.
oci_result_cache_max_size:會覆蓋伺服器端設定的初始化引數client_result_cache-size.然而要注意是如果在伺服器禁用了結果集快取這個引數將不能啟用它.
oci_result_cache_max_rset_size:指定單個結果集快取最多能使用的記憶體大小以byte為單位.
oci_result_cache_max_rset_rows:指定單個結果集快取最多能儲存的返回行數.
何時使用
當你遇到由程式反覆執行相同的操作所導致的效能問題時,你要麼減少執行的頻率要麼減少操作的響應時間.理想的情況時兩者都做.然而有時(例如由於應用程式程式碼不能修改)你只能實現後者.為了減少響應時間可以使用各種最佳化技術,如果還不能滿足要求那就只能使用高階最佳化技術了象結果集快取.基本上要有效使用結果集快取要滿足兩個條件.第一相對於修改資料來說同樣的資料查詢的更頻繁.第二要有足夠的記憶體來儲存結果集.
在大多數情況下不能對所有的查詢都啟用結果集快取.事實上在大多數時候只有特定的查詢才能從結果集快取中獲益而對於其它的情況來說,結果集快取只不過是一種純粹的額外的開銷說不定還會使用快取過載.還要記住的是伺服器端快取是對所有會話共享的,因此它們的訪問是要同步的(它們也會象所有的共享資源一樣變成一個串處理點).因此只有在真正查詢請求它們的時候才會結果集快取.也就是說只當真正需要使用它們來提高效能時才在查詢中指定result_cache提示.
伺服器端結果集快取無法完全避免執行一個查詢的額外開銷.這意味著如果一個查詢在不使用結果集快取的情況下對於每一行執行了最少的邏輯讀(不是物理讀)了,那麼使用結果集快取效能不會提高很多.請記住快取記憶體和結果集快取都是儲存在相同的共享記憶體中的.
pl/sql函式結果集快取對於經常在sql語句中使用的函式來說特別有用.事實上常遇到這樣的情況被處理或被返回的每一行都會呼叫一次這個函式,同時輸入的引數也只有幾個不同的值,然而這個函式經常從pl/sql中被頻繁地呼叫它能使用結果集快取.
因為一致性的問題客戶端快取只應該用在只讀表或主要是讀的表上.
最後要注意的是可以同進使用伺服器和客戶端結果集快取.然而對於客戶端執行的查詢不能選擇只使用伺服器結果集快取而不使用客戶端結果集快取.也就是說兩種結果集快取都會被使用.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2141766/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g 的server結果快取result_cache_modeOracleServer快取
- 11g result cache 結果快取記憶體快取記憶體
- oracle小知識點15--result cache結果快取記憶體Oracle快取記憶體
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速MySql快取
- Oracle11新特性——SQL快取結果集（五）OracleSQL快取
- Oracle11新特性——SQL快取結果集（三）OracleSQL快取
- oracle cache快取Oracle快取
- Oracle 11.2.0.1 Result Cache 測試 - 11 各種場景測試結果Oracle
- Oracle Query Result CacheOracle
- 11g新特性之結果集快取快取
- 淺談Oracle Result CacheOracle
- ORACLE 11g Result cache使用指南Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 12 DBMS_RESULT_CACHE管理包Oracle
- PHP PDO獲取結果集PHP
- query result cache in oracle 11gOracle
- 聊聊Oracle 11g的Result Cache（一）Oracle
- 聊聊Oracle 11g的Result Cache（二）Oracle
- 聊聊Oracle 11g的Result Cache（三）Oracle
- 聊聊Oracle 11g的Result Cache（四）Oracle
- Oracle11新特性——PLSQL函式快取結果（一）OracleSQL函式快取
- Oracle11新特性——PLSQL函式快取結果（三）OracleSQL函式快取
- Oracle11新特性——PLSQL函式快取結果（二）OracleSQL函式快取
- Oracle 11.2.0.1 Result Cache 測試 - 1Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 5Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 6Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 7Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 8Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 9Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 10Oracle
- Oracle 11g新特性：Result CacheOracle
- oracle 11g result_cache分析Oracle
- SQL Query Result Cache的使用和配置--Oracle 11G新特性SQLOracle
- DDL,DML操作對結果快取的影響快取
- Oracle 11.2.0.1 Result Cache 測試 - 3 引數及使用，限制Oracle
- 使用RESULT CACHE加速SQL查詢效率SQL
- PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取-開啟函式結果快取SQL函式快取
- 注意 Laravel 清除快取 PHP artisan cache:clear 的使用Laravel快取PHP