[20111229]Consistent gets from cache (fastpath).txt

[20111229]Consistent gets from cache (fastpath).txt

SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from v$statname where NAME like 'consistent gets%';
STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
        67 consistent gets                                                           8            4162191256
        68 consistent gets from cache                                      8            2839918855
        69 consistent gets from cache (fastpath)                    8            2173751438
        70 consistent gets - examination                                  8            1701530557
        71 consistent gets direct                                                 8            420374750

如果对比10g可以发现，11G增加一个consistent gets from cache (fastpath)统计，google发现如下链接:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:580696400346550153
http://afatkulin.blogspot.com/2009/01/consistent-gets-from-cache-fastpath.html

1.重复测试:
create table t1 nologging as select rownum id ,a.* from all_objects a where rownum create table t2 nologging as select * from t1;
create index idx_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');

2.在11GR2下执行如下：
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                              37
consistent gets from cache                                         37
consistent gets from cache (fastpath)                      33
consistent gets - examination                                      4
consistent gets direct                                                     0

SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
  COUNT(*)
----------
     10000

10:26:20 SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          324(+287)
consistent gets from cache                                     324(+287)
consistent gets from cache (fastpath)                   298(+265)
consistent gets - examination                                        5(+1)
consistent gets direct                                                       0

执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  518mw211s3mmv, child number 0
-------------------------------------
select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
--------------------------------------------------
| Id  | Operation           | Name      | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT    |           |        |
|   1 |  SORT AGGREGATE     |           |      1 |
|   2 |   NESTED LOOPS      |           |  10000 |
|   3 |    TABLE ACCESS FULL| T2        |  10000 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |      1 |
--------------------------------------------------

3.在10G下执行:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            51
consistent gets from cache                                       51
consistent gets - examination                                     5
consistent gets direct                                                    0

SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          10208(+10157)
consistent gets from cache                                     10208(+10157)
consistent gets - examination                                           6(+1)
consistent gets direct                                                          0

--可以发现逻辑读在10g下增加许多，在11G下仅仅287个逻辑读。11g下逻辑读明显减少。

4.对比执行计划：

11G:

SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
  COUNT(*)
    ----------
     10000

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      1 |00:00:00.03 |     287 |
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.03 |     287 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.03 |     287 |
|   3 |    TABLE ACCESS FULL| T2        |      1 |  10000 |  10000 |00:00:00.01 |     139 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.02 |     148 |
-------------------------------------------------------------------------------------------

10G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;

  COUNT(*)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST OUTLINE PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id =t2.id

Plan hash value: 3211355954

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.09 |   10157 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.12 |   10157 |
|   3 |    TABLE ACCESS FULL| T2        |      1 |  10000 |  10000 |00:00:00.01 |     135 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.07 |   10022 |
-------------------------------------------------------------------------------------------

--可以发现执行计划一样的,差别主要在扫描IDX_T1_ID索引上的buffers，可以知道11G改进了一些东西。因为T2插入是按照的id号的顺序插入的，在扫描t1的IDX_T1_ID索引时id=1,id=2.....应该在同一索引数据块上。

试想一下，如果T2表插入的数据不是有序的，逻辑读没有这么少了。

5.建立表T3,打乱id的顺序。重复以上的测试：

11G:
SQL> create table t3 nologging as select * from t1 order by dbms_random.random;
SQL> exec dbms_stats.gather_table_stats(user,'T3');

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          37
consistent gets from cache                                     37
consistent gets from cache (fastpath)                   33
consistent gets - examination                                   4
consistent gets direct                                                  0

SQL> select/*+use_nl(t3 t1)  gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            10198
consistent gets from cache                                       10198
consistent gets from cache (fastpath)                     10193
consistent gets - examination                                            5
consistent gets direct                                                          0

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cwdqmh7ryywx8, child number 0
-------------------------------------
select/*+use_nl(t3 t1)  gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id
Plan hash value: 1864576943
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      1 |00:00:00.04 |   10161 |
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.04 |   10161 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.05 |   10161 |
|   3 |    TABLE ACCESS FULL| T3        |      1 |  10794 |  10000 |00:00:00.01 |     139 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.04 |   10022 |
-------------------------------------------------------------------------------------------

10g下：
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          51
consistent gets from cache                                     51
consistent gets - examination                                   5
consistent gets direct                                                  0

SQL> select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            10208
consistent gets from cach                                         10208
consistent gets - examinaton                                             6
consistent gets direct                                                           0

时间： 2024-07-30 10:52:37

[20111229]Consistent gets from cache (fastpath).txt的相关文章

[20151015]关于result cache 2.txt

[20151015]关于result cache 2.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://b

[20161216]关于library cache lock.txt

[20161216]关于library cache lock.txt --这几天一直在关注这个链接,http://www.itpub.net/thread-2073170-1-1.html --就是library cache lock导致挂死业务,一般引起这个问题编译包,而应用正好在使用执行这个包,以及11g口令大小写导致无法登录的问题, --我自己很久以前也遇到过一些,那时的系统是10g,http://www.itpub.net/thread-1842463-1-1.html,但是只要分析某个

[20150924]result cache problem.txt

[20150924]result cache problem.txt --昨天看了连接,看到一个关于result cache的例子,重复测试看看: --链接 https://jonathanlewis.wordpress.com/2015/09/22/result-cache/ 1.环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --

[20160919]Result cache问题.txt

[20160919]Result cache问题.txt --看了链接http://blog.dbi-services.com/result-cache-side-effects-on-number-of-calls/,重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------

[20141223]result cache 3.txt

[20141223]result cache 3.txt --上午的测试有一些问题,做一些更正. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86

[20111230]11Gr2 result cache[1].txt

11G的result cache是一个很吸引人的特性,可以大幅减少逻辑读取,特别对于一些经常执行的语句,而结果不是经常变化的,效果不错,我的测试遇到一个小问题. SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0

[20111229]date time timestamp? ansi liternals.txt

今天看一篇文章,发现日期可以写成这样date '2011-1-1' google发现如下链接:http://laurentschneider.com/wordpress/2005/11/ansi-literals.html 自己测试一下,一般我个人喜欢定义环境变量如下: $ export | grep NLSdeclare -x NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"declare -x NLS_LANG="AMERICAN_AMERI

[20160104]enq RC-Result Cache Contention

[20160104]enq RC - Result Cache Contention.txt --今天检查awr报表,无意间发现enq RC - Result Cache Contention排在靠前的位置.我们服务器很强劲,出现这个给仔细检查. 1.环境: SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> @ &r/ver1 PORT_STRING                    VERSION        BANNER ----------------------

Buffer cache 的调整与优化(一)

--============================== -- Buffer cache 的调整与优化(一) --==============================       Buffer Cache是SGA的重要组成部分,主要用于缓存数据块,其大小也直接影响系统的性能.当Buffer Cache过小的时候,将会造成更多的 free buffer waits事件. 下面将具体描述Buffer Cache的作用,调整与优化.   一.SGA的所有组件     从动态视图v$sg