select不仅仅是查询出结果

SQL> conn scott
输入口令:
已连接。
SQL> alter session set events '10200 trace name context forever, level 10';

会话已更改。

SQL> set autot on
SQL> select * from a where object_id=1399;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED        LAST_DDL_TIME  TIMESTAMP           STATUS  T G S
-------------- -------------- ------------------- ------- - - -
SYS                            GV_$AW_ALLOCATE_OP
                                     1399                VIEW
14-3月 -08     14-3月 -08     2008-03-14:18:47:34 VALID   N N N

 

执行计划
----------------------------------------------------------
Plan hash value: 248677823

--------------------------------------------------------------------------------

----

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
   |

--------------------------------------------------------------------------------

----

|   0 | SELECT STATEMENT            |      |     1 |    93 |     2   (0)| 00:00:

01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| A    |     1 |    93 |     2   (0)| 00:00:

01 |

|*  2 |   INDEX RANGE SCAN          | I_A  |     1 |       |     1   (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----

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

   2 - access("OBJECT_ID"=1399)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1209  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

 

生成的trc文件为orcl_ora_1736.trc，

$ grep "Consistent read start" orcl_ora_1736.trc | less
Consistent read started for block 4 : 0100089c     --读index的branch块。
Consistent read started for block 4 : 0100089f      --读index的leaf
Consistent read started for block 4 : 0100082c     --表A
Consistent read started for block 4 : 0100089f      --index的leaf
Consistent read started for block 3 : 0040080a     --sys.HIST_HEAD$
Consistent read started for block 3 : 0040080a     --sys.HIST_HEAD$
Consistent read started for block 0 : 00400a72    -- index sys.SYS_C00648 on sys.KOTTD$
Consistent read started for block 0 : 00400a73   -- index sys.SYS_C00648 on sys.KOTTD$
Consistent read started for block 0 : 00402ac1    --KOTTD$

Consistent read started for block 0 : 00400a72   -- index sys.SYS_C00648 on sys.KOTTD$

Consistent read started for block 0 : 00400a73  -- index sys.SYS_C00648 on sys.KOTTD$

Consistent read started for block 0 : 00400a5b    --KOTTD$
Consistent read started for block 0 : 00400b22   --INDEX I_KOPM1 on table KOPM$
Consistent read started for block 0 : 00400b1a    --SYS.KOPM$
Consistent read started for block 3 : 0040080a    --SYS.HIST_HEAD$
(END)

 

发现实际上产生了15次逻辑读，而sqlplus提示只有4次。

 

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

转载于:http://blog.itpub.net/23132642/viewspace-718837/