direct path read 和 db file sctter read

warmbreeze

os: aix 5.3
db:10.2.0.3 RAC

insert into a select * from b where ....
1.没有并行
2.b是full table scan

为什么等待事件是direct path read 而不是 db file sctter read
oracle如何选择这两种方式？

[ 本帖最后由 warmbreeze 于 2012-5-20 02:03 编辑 ]

Maclean Liu(刘相兵

贴出完整的SQL 和其执行计划

warmbreeze

是lob的原因，这个表有lob字段，direct path read 的block都是lob数据
SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
5bfasqamzm4v8 insert into APPGRADERESULT select * from APPGRADERESULT_bak where info
              _receive_date > to_date('2012-04-14','yyyy-mm-dd')
Plan hash value: 1995021937
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |                         |       |       | 10813 (100)|          |
|   1 |  TABLE ACCESS FULL| APPGRADERESULT_BAK |  1407K|   111M| 10813   (2)| 00:02:10 |
---------------------------------------------------------------------------------------------

CREATE TABLE "GRADERULEINFO_BAK"
   (    "APPLICATION_SN" VARCHAR2(20) NOT NULL ENABLE,
        "ID_NO" VARCHAR2(20) NOT NULL ENABLE,
        "ID_TYPE" VARCHAR2(3) NOT NULL ENABLE,
        "COB_XML" CLOB,
        "PBOC_RESULT" VARCHAR2(11),
        "INFO_RECEIVE_DATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 4227858432 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
LOB ("COB_XML") STORE AS (
  ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

direct path read 事件的dlock dump:
Start dump data blocks tsn: 6 file#: 23 minblk 179527 maxblk 179527
buffer tsn: 6 rdba: 0x05c2bd47 (23/179527)
scn: 0x0000.493b494b seq: 0x02 flg: 0x04 tail: 0x494b2802
frmt: 0x02 chkval: 0xa797 type: 0x28=PAGETABLE MANAGED LOB BLOCK

[ 本帖最后由 warmbreeze 于 2012-5-21 12:08 编辑 ]