[20111229]理解consistent gets*相关信息[补充]

1.补充一点资料:
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> create table t1 as select rownum id ,'test' name  from dual connect by level<=100;
SQL> alter table t1 add constraint pk_t1 PRIMARY KEY(id) USING INDEX(CREATE INDEX i_t1_id ON t1(id,name)) ENABLE ;
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME
------------------------------
I_T1_ID

2.
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 id,name from t1 where id=60 ;

        ID NAME
---------- ----
        60 test

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                                                          53(+2)
consistent gets from cache                                     53(+2)
consistent gets - examination                                  5(+0)
consistent gets direct                                                      0

--失望!oracle 看来还是不够聪明,执行计划依旧是索引范围扫描。看来要想实现一个逻辑读，只能使用IOT表。

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9r3x00whhy46y, child number 0
-------------------------------------
select id,name from t1 where id=60

Plan hash value: 4406210

---------------------------------------------
| Id  | Operation        | Name    | E-Rows |
---------------------------------------------
|*  1 |  INDEX RANGE SCAN| I_T1_ID |      1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=60)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


23 rows selected.

3.在11Gr2下重复测试：

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 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 - examinatio                                    4
consistent gets direct                                                 0

SQL> select id,name from t1 where id=60 ;

        ID NAME
---------- ----
        60 test

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                                                          39(+2)
consistent gets from cache                                     39(+2)
consistent gets from cache (fastpath)                   34(+1)
consistent gets - examination                                   4(+0)
consistent gets direct                                                  0

--与10g一样，不过11G增加一个统计信息consistent gets from cache (fastpath).并且有1个读,以后研究看看。

补充: 如果执行这样呢？select id,name from t1 where id=60 and rownum<=1;

 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

15:09:28 SQL> select id,name from t1 where id=60 and rownum<=1;
        ID NAME
---------- ----
        60 test

15:09:54 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                                                           38
consistent gets from cache                                      38
consistent gets from cache (fastpath)                    33
consistent gets - examination                                     4
consistent gets direct                                                    0

逻辑读仅仅为1，不过一般情况下不会这样写。

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

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