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

于 2011-12-29 11:51:18 发布
阅读量104 收藏
点赞数
文章标签： 数据库
[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 <=10000;
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



来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714135/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-714135/

cuixun7780
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值