About consistent gets from cache (fastpath)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
Oracle 11g相比10g版本而言，在优化器方面有很多改进，这里不一一列举。在分析某运营商客户的CRM系统时，发现每秒的逻辑读高达100w左右，其中Consistent Gets 就占据了90w之多。由此可见，这可能存在一个巨大的优化空间。然而，当我查询statistics信息时，发现了一个奇怪的事情，如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> l 1 select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.STATISTIC# = b.STATISTIC# 4* and b.NAME like 'consistent gets%' SQL> / NAME VALUE -------------------------------------------------- ---------------------------------- consistent gets 2919902517406 consistent gets from cache 2918741933811 consistent gets from cache (fastpath) 0 consistent gets - examination 947312709390 consistent gets direct 1160585050 SQL> / NAME VALUE -------------------------------------------------- ---------------------------------- consistent gets 2919907774371 consistent gets from cache 2918747190826 consistent gets from cache (fastpath) 0 consistent gets - examination 947314388772 consistent gets direct 1160585845 |
我们可以看出，其中consistent gets from cache(fastpath)为0. 这让我一下就感觉到有点奇怪。首先针对这个统计信息，之前确实没有过多关注过，其次这里value为0，从直觉上来讲就感觉有点问题。
实际上，consistent gets from cache(pastpath)这是Oracle 11g引入的一个新特性，针对buffer pin的一个优化操作，其目的是可以降低Latch的争用，尤其是Cache buffer chains的争用。
首先我们来看下Oracle 10gR2的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
www.killdb.com@create table t( 2 n number, 3 v varchar2(100), 4 constraint pk_n primary key (n)); Table created. www.killdb.com@insert into t 2 select level, rpad('*', 100, '*') 3 from dual 4 connect by level <= 1000; 1000 rows created. www.killdb.com@create or replace procedure get_cg( 2 p_cg out number, 3 p_cg_c out number, 4 p_cgfp out number, 5 p_cg_ex out number, 6 p_cg_dir out number 7 ) is 8 begin 9 select max(case sn.NAME when 'consistent gets' then ms.value end), 10 max(case sn.NAME when 'consistent gets from cache' then ms.value end), 11 max(case sn.NAME when 'consistent gets from cache (fastpath)' then ms.value end), 12 max(case sn.NAME when 'consistent gets - examination' then ms.value end), 13 max(case sn.NAME when 'consistent gets direct' then ms.value end) 14 into p_cg,p_cg_c, p_cgfp,p_cg_ex,p_cg_dir 15 from v$mystat ms, v$statname sn 16 where ms.STATISTIC#=sn.STATISTIC# 17 and sn.NAME in('consistent gets','consistent gets from cache','consistent gets - examination','consistent gets direct'); 18 end get_cg; 19 / Procedure created. www.killdb.com@ declare 2 l_cg_b number; 3 l_cg_a number; 4 p_cg_c_a number; 5 p_cg_c_b number; 6 l_cgfp_b number; 7 l_cgfp_a number; 8 p_cg_ex_b number; 9 p_cg_ex_a number; 10 p_cg_dir_b number; 11 p_cg_dir_a number; 12 begin 13 get_cg(l_cg_b, p_cg_c_b,l_cgfp_b,p_cg_ex_b,p_cg_dir_b); 14 for cur in (select n from (select mod(level, 1000)+1 l from dual connect by 15 level <= 100000) l, t where t.n=l.l) 16 loop 17 null; 18 end loop; 19 get_cg(l_cg_a,p_cg_c_a, l_cgfp_a,p_cg_ex_a,p_cg_dir_a); 20 dbms_output.put_line('consistent gets: '||to_char(l_cg_a-l_cg_b)); 21 dbms_output.put_line('consistent gets from cache: '||to_char(p_cg_c_a-p_cg_c_b)); 22 dbms_output.put_line('consistent gets from cache (fastpath): '||to_char(l_cgfp_a-l_cgfp_b)); 23 dbms_output.put_line('consistent gets - examination: '||to_char(p_cg_ex_a-p_cg_ex_b)); 24 dbms_output.put_line('consistent gets direct: '||to_char(p_cg_dir_a-p_cg_dir_b)); 25 end; 26 / consistent gets: 101001 consistent gets from cache: 101001 consistent gets from cache (fastpath): consistent gets - examination: 100001 consistent gets direct: 0 PL/SQL procedure successfully completed. www.killdb.com@ www.killdb.com@/ consistent gets: 101001 consistent gets from cache: 101001 consistent gets from cache (fastpath): consistent gets - examination: 100001 consistent gets direct: 0 PL/SQL procedure successfully completed. |
我们不难看出，10gR2版本中压根儿就没有这个一项统计指标。下面我继续来看下Oracle 11g版本的情况（实际上Oracle 11.1 版本就引入了该特性）:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
[oracle@killdb admin]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 20 12:22:10 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options www.killdb.com@create table t( 2 n number, 3 v varchar2(100), 4 constraint pk_n primary key (n)); Table created. www.killdb.com@insert into t 2 select level, rpad('*', 100, '*') 3 from dual 4 connect by level <= 1000; 1000 rows created. www.killdb.com@create or replace procedure get_cg( 2 p_cg out number, 3 p_cg_c out number, 4 p_cgfp out number, 5 p_cg_ex out number, 6 p_cg_dir out number 7 ) is 8 begin 9 select max(case sn.NAME when 'consistent gets' then ms.value end), 10 max(case sn.NAME when 'consistent gets from cache' then ms.value end), 11 max(case sn.NAME when 'consistent gets from cache (fastpath)' then ms.value end), 12 max(case sn.NAME when 'consistent gets - examination' then ms.value end), 13 max(case sn.NAME when 'consistent gets direct' then ms.value end) 14 into p_cg,p_cg_c, p_cgfp,p_cg_ex,p_cg_dir 15 from v$mystat ms, v$statname sn 16 where ms.STATISTIC#=sn.STATISTIC# 17 and sn.NAME in('consistent gets','consistent gets from cache','consistent gets from cache (fastpath)','consistent gets - examination','consistent gets direct'); 18 end get_cg; 19 / Procedure created. www.killdb.com@ www.killdb.com@ www.killdb.com@ declare 2 l_cg_b number; 3 l_cg_a number; 4 p_cg_c_a number; 5 p_cg_c_b number; 6 l_cgfp_b number; 7 l_cgfp_a number; 8 p_cg_ex_b number; 9 p_cg_ex_a number; 10 p_cg_dir_b number; 11 p_cg_dir_a number; 12 begin 13 get_cg(l_cg_b, p_cg_c_b,l_cgfp_b,p_cg_ex_b,p_cg_dir_b); 14 for cur in (select n from (select mod(level, 1000)+1 l from dual connect by 15 level <= 100000) l, t where t.n=l.l) 16 loop 17 null; 18 end loop; 19 get_cg(l_cg_a,p_cg_c_a, l_cgfp_a,p_cg_ex_a,p_cg_dir_a); 20 dbms_output.put_line('consistent gets: '||to_char(l_cg_a-l_cg_b)); 21 dbms_output.put_line('consistent gets from cache: '||to_char(p_cg_c_a-p_cg_c_b)); 22 dbms_output.put_line('consistent gets from cache (fastpath): '||to_char(l_cgfp_a-l_cgfp_b)); 23 dbms_output.put_line('consistent gets - examination: '||to_char(p_cg_ex_a-p_cg_ex_b)); 24 dbms_output.put_line('consistent gets direct: '||to_char(p_cg_dir_a-p_cg_dir_b)); 25 end; 26 / consistent gets: 2602 consistent gets from cache: 2602 consistent gets from cache (fastpath): 1400 consistent gets - examination: 1202 consistent gets direct: 0 PL/SQL procedure successfully completed. |
大家可以看出，统计指标有所变化。同样的SQL代码在10g和11g版本中执行，Buffer Gets差距是很大的，从10w降低到2600左右。其中11gR2版本中，consistent gets from cache(fastpath)为1400，占据整个逻辑读consistent gets(2602)的一半之多。由此可见，这是一个很大的性能改善。
经过观察分析，发现Oracle 通过隐含参数来控制该功能，参数为：_fastpin_enable，这些我的虚拟机环境的参数设置(也是默认配置):
|
1 2 3 4 5 6 7 |
www.killdb.com@conn roger/roger Connected. www.killdb.com@show parameter fastpin NAME TYPE VALUE ------------------------------------ ----------- ---------- _fastpin_enable integer 232205313 |
可以看出，该参数的值还是较大的，如果将该参数改成0，那么将会是什么结果呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
www.killdb.com@alter system set "_fastpin_enable"=0 scope=spfile; System altered. www.killdb.com@shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. www.killdb.com@set serveroutput on www.killdb.com@@print_buffer.sql consistent gets: 2639 consistent gets from cache: 2639 consistent gets from cache (fastpath): 0 consistent gets - examination: 1208 consistent gets direct: 0 PL/SQL procedure successfully completed. www.killdb.com@conn roger/roger Connected. www.killdb.com@show parameter fastpin NAME TYPE VALUE ------------------------------------ ----------- ----------- _fastpin_enable integer 0 www.killdb.com@conn /as sysdba Connected. www.killdb.com@@print_buffer.sql PL/SQL procedure successfully completed. www.killdb.com@set serveroutput on www.killdb.com@@print_buffer.sql consistent gets: 2602 consistent gets from cache: 2602 consistent gets from cache (fastpath): 0 consistent gets - examination: 1202 consistent gets direct: 0 PL/SQL procedure successfully completed. |
大家不难看出，当将该参数调整为0之后，consistent gets from cache(fastpath)指标变成0. 经过测试，实际上该参数只要大于1即可启用该新特性。
然而让我感觉到疑惑的地方是，客户的CRM数据库环境中，该参数默认值已经较大了，但是仍然看不到fastpath的指标信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'; 3 Enter value for par: fastpin old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%' new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%fastpin%' NAME VALUE DESCRIB ------------------------------ -------------------- ------------------------------------------------------------ _fastpin_enable 16777216 enable reference count based fast pins SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.STATISTIC# = b.STATISTIC# 4 and b.NAME like 'consistent gets%'; NAME VALUE -------------------------------------------------- ---------------------------------- consistent gets 2920646704900 consistent gets from cache 2919483943030 consistent gets from cache (fastpath) 0 consistent gets - examination 947542697858 consistent gets direct 1162762820 SQL> |
欲知后事如何，请看下回分解！
Leave a Reply
You must be logged in to post a comment.