[20171109]緩存命中率神話.txt

--//在oracle版本的早期,緩存命中率是一個很重要的優化指標,實際上這個根本不重要.
--//一般OLTP系統即使出現嚴重的性能問題,這個數值也很高,實際上一個簡單的情況就能說明問題,
--//比如走hash join的計劃,不小心走了nested loop,可能導致邏輯讀上升.緩存命令率很高,但是數據庫
--//未必運行在最佳性能.

--//這個也是我學習oracle早期一個不好理解的問題,^_^.

--//https://connor-mcdonald.com/2017/11/07/buffer-cache-hit-ratio-blast-from-the-past/給出一個例子,能很好的說明問題.

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.

總結

以上是生活随笔為你收集整理的[20171109]缓存命中率神话.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。