SQL> select * from v$version where rownum=1; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 www.askmaclean.com 使用目前最新的12c R1 12.1.0.1版本测试 alter session set events '10046 trace name context forever,level 12'; set autotrace traceonly; alter system flush buffer_cache; ==》保证物理读 alter session set "_optimizer_batch_table_access_by_rowid"=true; // BATCH IO一般都会用到db file parallel read oracle@localhost:~$ strace -o read.log -p 16410 -t -s 200 在实际操作前使用Strace对IO CALL做TRACE select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;我们可以从10046 TRACE中获取如下信息, 接着我们到Strace日志中去找对应的SYSCALL:
1:WAIT #140194991664112: nam='db file sequential read' ela= 12 file#=6 block#=1553 blocks=1 obj#=92176 tim=33606113133 2:WAIT #140194991664112: nam='db file sequential read' ela= 13 file#=6 block#=9324 blocks=1 obj#=92176 tim=33606113191 3:WAIT #140194991664112: nam='db file parallel read' ela= 236 files=1 blocks=13 requests=13 obj#=92176 tim=33606114196 4:WAIT #140194991664112: nam='db file sequential read' ela= 20 file#=6 block#=2192 blocks=1 obj#=92176 tim=33606114373我们把10046 trace和 Strace对应起来:
列出Extent MAP: 1* select extent_id,file_id,block_id ,block_id+blocks-1 from dba_extents where segment_name='SAMPLE' SQL> / EXTENT_ID FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1 ---------- ---------- ---------- ----------------- 0 6 176 183 1 6 184 191 2 6 192 199 3 6 200 207 4 6 208 215 5 6 216 223 6 6 224 231 7 6 232 239 8 6 240 247 9 6 248 255 10 6 256 263 11 6 264 271 12 6 272 279 13 6 280 287 14 6 288 295 15 6 296 303 16 6 384 511 17 6 512 639 18 6 640 767 19 6 768 895 对应于1: 07:21:28 pread(257, "\6\242\0\0\21\6\200\1BZ\36\0\0\0\1\4\211\350\0\0\1\0\0\0\20h\1\0BZ\36\0\0\0\0\0\3\0002\0\0\6\200\1\377\377\0\0\0\ 0\0\0\0\0\0\0\0\0\0\0\0\200\0\0\3079\36\0\2\0\37\0\36\7\0\0-p\0\1\203\0010\0\0\200\0\0\315Y\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\221\2\377\3774\0055\5\1\0\1\0\0\0\221\2b\37D\37&\37\10\37\352\36\314\36\256\36\220\36r\36T\0366\36\30\3 6\372\35\334\35\276\35\240\35\202\35d\35F\35(\35\n\35\355\34\317\34\261\34\223\34u\34W\0349\34\33\34"..., 8192, 12722176) = 8192 12722176/8192=1553 block# 07:21:28 write(12, "WAIT #140194991652056: nam='db file sequential read' ela= 221 file#=6 block#=1553 blocks=1 obj#=92176 tim=33620843 632", 117) = 117 对应于2: 07:21:28 pread(257, "\6\242\0\0l$\200\1BZ\36\0\0\0\1\4yN\0\0\1\0\37\0\20h\1\0BZ\36\0\0\0\0\0w\0372\0\3!\200\1\2\0\37\0\36\7\0\0\36p\0\ 1\203\1E\0\0\200\0\0\315Y\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\200\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\200\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\200\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\200\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 76382208) = 8192 76382208/8192= 9324 block# 07:21:28 write(12, "WAIT #140194991652056: nam='db file sequential read' ela= 175 file#=6 block#=9324 blocks=1 obj#=92176 tim=33620844 373", 117) = 117 对应3: 07:21:28 mmap(0x7f81b0151000, 1114112, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0x7f81b0151000 07:21:28 pread(257,..., 8192, 2269184) = 8192 ==> block#=277 07:21:28 pread(257,..., 8192, 2449408) = 8192 ==> block#=299 07:21:28 pread(257,..., 8192, 4055040) = 8192 ==> block#=495 07:21:28 pread(257,..., 8192, 5382144) = 8192 ==> block#=657 5799936 5832704 5922816 8863744 8937472 9699328 10371072 12042240 13131776 07:21:28 write(12, "WAIT #140194991652056: nam='db file parallel read' ela= 3982 files=1 blocks=13 requests=13 obj#=92176 tim=33620849 111", 117) = 117
这里看到db file parallel read 物理读等待事件涉及到的数据块均是不连续的、同时还可以跨越Extent,这点不像db file scattered read。 db file parallel read 等待事件是Oracle 可以对多个数据文件实施并行地物理读取并加载到不连续的内存空间中(可能是PGA也可能是Buffer Cache)。
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/p2