玉面飞龙的BLOG

以前以为direct path read一定会先触发这个object checkpoint，其实不然。

当db cache中没有dirty buffer或者根本没有buffer的时候，direct path read不需要checkpoint.10046 结果如下

PARSING IN CURSOR #6 len=30 dep=0 uid=25 oct=3 lid=25 tim=4120328937892 hv=2114664892 ad=’ce86e7a0′
select count(*) from HISTOGRAM
END OF STMT
PARSE #6:c=0,e=157,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4120328937882
BINDS #6:
EXEC #6:c=0,e=208,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4120328938275
WAIT #6: nam=’SQL*Net message to client’ ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=4120328938366
WAIT #6: nam=’db file sequential read’ ela= 118 file#=3 block#=12563 blocks=1 obj#=22018 tim=4120328938807
WAIT #6: nam=’direct path read’ ela= 282 file number=3 first dba=12564 block cnt=13 obj#=22018 tim=4120328951081
WAIT #6: nam=’db file sequential read’ ela= 73 file#=2 block#=89 blocks=1 obj#=0 tim=4120328951943
WAIT #6: nam=’direct path read’ ela= 19 file number=3 first dba=12578 block cnt=15 obj#=22018 tim=4120328952325
WAIT #6: nam=’direct path read’ ela= 15 file number=3 first dba=12594 block cnt=15 obj#=22018 tim=4120328953423
WAIT #6: nam=’direct path read’ ela= 15 file number=3 first dba=12674 block cnt=7 obj#=22018 tim=4120328954439

又或者当buffer中有很多该块的dirty buffer,oracle也不会触发object checkpoint将这些dirty buffer写回磁盘。而直接执行direct path read.但这个dirty buffer的比例不好测试出具体数值，根表的大小和buffer的大小或许相关。

作些DML,dirty足够多的buffer
SQL> select dirty,count(*) from v$bh group by dirty;
DIR COUNT(*)
— ———-
N 33614
Y 5629

PARSING IN CURSOR #3 len=30 dep=0 uid=25 oct=3 lid=25 tim=4042622745059 hv=2114664892 ad=’ce86e7a0′
select count(*) from HISTOGRAM
END OF STMT
PARSE #3:c=0,e=264,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4042622745049
BINDS #3:
EXEC #3:c=0,e=227,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4042622745469
WAIT #3: nam=’SQL*Net message to client’ ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=4042622745564
WAIT #3: nam=’reliable message’ ela= 360 channel context=16378268528 channel handle=16361576096 broadcast message=16394637816 obj#=-
1 tim=4042622746331
WAIT #3: nam=’direct path read’ ela= 890 file number=3 first dba=12564 block cnt=13 obj#=22018 tim=4042622760742
WAIT #3: nam=’direct path read’ ela= 3 file number=3 first dba=12578 block cnt=15 obj#=22018 tim=4042622762041
WAIT #3: nam=’direct path read’ ela= 21 file number=3 first dba=12594 block cnt=15 obj#=22018 tim=4042622763422
WAIT #3: nam=’direct path read’ ela= 20 file number=3 first dba=12674 block cnt=7 obj#=22018 tim=4042622764725
WAIT #3: nam=’direct path read’ ela= 14 file number=3 first dba=12777 block cnt=8 obj#=22018 tim=4042622765403

虽然等待了’reliable message’，但并没有触发object checkpoint事件。

在此情况下，如果执行parallel查询，则会触发object checkpoint。

测试环境

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

NAME                   VALUE
—————————-
_adaptive_direct_read   TRUE
_serial_direct_read     TRUE
_small_table_threshold  1000