About consistent gets from cache (fastpath)

Oracle 11g相比10g版本而言，在优化器方面有很多改进，这里不一一列举。在分析某运营商客户的CRM系统时，发现每秒的逻辑读高达100w左右，其中Consistent Gets 就占据了90w之多。由此可见，这可能存在一个巨大的优化空间。然而，当我查询statistics信息时，发现了一个奇怪的事情，如下所示：

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的情况：

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 版本就引入了该特性）:

[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，这些我的虚拟机环境的参数设置(也是默认配置):

www.killdb.com@conn roger/roger
Connected.
www.killdb.com@show parameter fastpin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
_fastpin_enable                      integer     232205313

可以看出，该参数的值还是较大的，如果将该参数改成0，那么将会是什么结果呢？

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的指标信息：

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>

欲知后事如何，请看下回分解！