[20171109]快取命中率神話.txt
[20171109]快取命中率神話.txt
--//在oracle版本的早期,快取命中率是一個很重要的最佳化指標,實際上這個根本不重要.
--//一般OLTP系統即使出現嚴重的效能問題,這個數值也很高,實際上一個簡單的情況就能說明問題,
--//比如走hash join的計劃,不小心走了nested loop,可能導致邏輯讀上升.快取命令率很高,但是資料庫
--//未必執行在最佳效能.
--//這個也是我學習oracle早期一個不好理解的問題,^_^.
--//.
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> grant select on v_$sysstat to scott ;
Grant succeeded.
--//注:我修改原始碼,加入AUTHID CURRENT_USER .不然報如下錯誤:
SCOTT@book> exec choose_a_hit_ratio(92);
Current ratio is: 90.72333
Another 18142 consistent gets needed...
BEGIN choose_a_hit_ratio(92); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.CHOOSE_A_HIT_RATIO", line 72
ORA-06512: at line 1
--//程式碼如下:
create or replace
procedure choose_a_hit_ratio(p_ratio number default 99,p_show_only boolean default false) AUTHID CURRENT_USER
v_phy number;
v_db number;
v_con number;
v_count number;
v_additional_congets number;
v_hit number;
procedure show_hit is
begin
select p.value, d.value, c.value
into v_phy, v_db, v_con
from
( select value from v$sysstat where name = 'physical reads' ) p,
( select value from v$sysstat where name = 'db block gets' ) d,
( select value from v$sysstat where name = 'consistent gets' ) c;
v_hit := 1-(v_phy/(v_db+v_con));
dbms_output.put_line('Current ratio is: '||round(v_hit*100,5));
end;
begin
--
-- First we work out the ratio in the normal fashion
--
show_hit;
if p_ratio/100 < v_hit or p_ratio > 99.9999999 then
dbms_output.put_line('Sorry - I cannot help you');
return;
end if;
--
-- Flipping the formula we can work out how many more consistent gets
-- we need to increase the hit ratio
--
v_additional_congets := trunc(v_phy/(1-p_ratio/100)-v_db - v_con);
dbms_output.put_line('Another '||v_additional_congets||' consistent gets needed...');
if p_show_only then return; end if;
--
-- Create a simple table to hold 200 rows in a single block
--
begin
execute immediate 'drop table dummy';
exception
when others then null;
end;
execute immediate 'create table dummy (n primary key) organization index as '||
'select rownum n from all_objects where rownum <= 200';
--
-- Turn off any new 9i connect-by features to ensure we still do lots of
-- logical IO
--
begin
execute immediate 'alter session set "_old_connect_by_enabled" = true';
exception
when others then null;
end;
--
-- Grind away until we do all those additional gets
--
execute immediate '
select count(*)
from (
select n
from dummy
connect by n > prior n
start with n = 1 )
where rownum < :v_additional_congets' into v_count using v_additional_congets;
show_hit;
end;
/
--//執行如下:
SCOTT@book> set serveroutput on
SCOTT@book> exec choose_a_hit_ratio(85,true);
Current ratio is: 90.71867
Sorry - I cannot help you
PL/SQL procedure successfully completed.
SCOTT@book> exec choose_a_hit_ratio(92,true);
Current ratio is: 90.72316
Another 18144 consistent gets needed...
PL/SQL procedure successfully completed.
SCOTT@book> exec choose_a_hit_ratio(92);
Current ratio is: 90.86547
Another 16374 consistent gets needed...
Current ratio is: 92.06869
PL/SQL procedure successfully completed.
SCOTT@book> exec choose_a_hit_ratio(98);
Current ratio is: 92.33213
Another 443318 consistent gets needed...
Current ratio is: 98.01083
PL/SQL procedure successfully completed.
SCOTT@book> exec choose_a_hit_ratio(99,true);
Current ratio is: 98.0109
Another 598700 consistent gets needed...
PL/SQL procedure successfully completed.
SCOTT@book> exec choose_a_hit_ratio(99);
Current ratio is: 98.01091
Another 598698 consistent gets needed...
Current ratio is: 99.00114
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2147008/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何提高Redis快取命中率Redis快取
- Laravel 實現二級快取 提高快取的命中率和細粒化快取 keyLaravel快取
- 話說快取快取
- 系統效能調優:提升 CPU 快取的命中率快取
- 資料字典快取命中率低怎麼調整。快取
- 關於快取命中率的幾個關鍵問題!快取
- mysql狀態檢視 QPSTPS快取命中率檢視MySql快取
- oracle實驗記錄 (快取記憶體命中率與成本)Oracle快取記憶體
- mysql狀態檢視 QPS/TPS/快取命中率檢視MySql快取
- 不廢話,程式碼實踐帶你掌握 強快取、協商快取!快取
- [20171109]檢視隱含引數指令碼.txt指令碼
- [20180906]測試同一會話多個子遊標是否快取.txt會話快取
- Lisp天才神話Lisp
- 快取穿透、快取擊穿、快取雪崩、快取預熱快取穿透
- 快取穿透、快取擊穿、快取雪崩快取穿透
- 快取穿透、快取雪崩、快取擊穿快取穿透
- Redis快取擊穿、快取穿透、快取雪崩Redis快取穿透
- HTTP快取——協商快取(快取驗證)HTTP快取
- [Redis]快取穿透/快取擊穿/快取雪崩Redis快取穿透
- 快取穿透 快取雪崩快取穿透
- 快取問題(一) 快取穿透、快取雪崩、快取併發 核心概念快取穿透
- [20171109]簡單探究檔案ab_ASM_SID.dat.txtASM
- 快取穿透、快取擊穿、快取雪崩區別快取穿透
- 快取問題(四) 快取穿透、快取雪崩、快取併發 解決案例快取穿透
- MySQL innodb buffer pool 命中率以及快取了哪些 InnoDB TableMySql快取
- [20210429]檔案頭塊不會快取.txt快取
- ServiceWorker 快取與 HTTP 快取快取HTTP
- mybatis快取-二級快取MyBatis快取
- Cognos會話快取設定(展現實時資料)會話快取
- MyBatis快取機制(一級快取,二級快取)MyBatis快取
- 快取、快取演算法和快取框架簡介快取演算法框架
- 快取淘汰、快取穿透、快取擊穿、快取雪崩、資料庫快取雙寫一致性快取穿透資料庫
- [20171109]資料庫與asm例項的通訊問題.txt資料庫ASM
- 程式效能優化探討(4)——直接對映快取記憶體命中率問題的模擬優化快取記憶體
- Redis詳解(十二)------ 快取穿透、快取擊穿、快取雪崩Redis快取穿透
- 面試總結 —— Redis “快取穿透”、“快取擊穿”、“快取雪崩”面試Redis快取穿透
- 快取穿透,快取擊穿,快取雪崩解決方案分析快取穿透
- 快取穿透、快取雪崩和快取擊穿是什麼?快取穿透