mysql 单块读 多块读_10046确认多块读无法跨区

1)环境构建，使用笨拙的办法，不断insert,查询extent分配

SQL> insert into scott.e values(8,8,8,8);

SQL> insert into scott.e values(9,9,9,9);

SQL> insert into scott.e values(10,10,10,10);

SQL> insert into scott.e values(11,11,11,11);

SQL> insert into scott.e values(12,12,12,12);

SQL> r

1 select tablespace_name,file_id,extent_id,block_id,

2 bytes/1024 bytes_k,blocks from dba_extents

3 where owner='SCOTT' and segment_name='E'

4*

TABLESPACE_NAM FILE_ID EXTENT_ID BLOCK_ID BYTES_K BLOCKS

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

USERS 4 0 1768 64 8

USERS 4 1 1776 64 8

USERS 4 2 1784 64 8

USERS 4 3 3200 64 8  --终于新扩建一个区

SQL> select rowid from scott.e where id like '%12%';

ROWID

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

AAAS5RAAEAAAAyGAAA

select rowid,

dbms_rowid.rowid_object(rowid) "objct",

dbms_rowid.rowid_relative_fno(rowid) "file",

dbms_rowid.rowid_block_number(rowid) "block",

dbms_rowid.rowid_row_number(rowid) "row"

from scott.e;

ROWID objct file block row

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

AAAS5RAAEAAAAbsAAA 77393 4 1772 0

AAAS5RAAEAAAAbvAAA 77393 4 1775 0

AAAS5RAAEAAAAbwAAA 77393 4 1776 0

AAAS5RAAEAAAAbxAAA 77393 4 1777 0

AAAS5RAAEAAAAb0AAA 77393 4 1780 0

AAAS5RAAEAAAAb2AAA 77393 4 1782 0

AAAS5RAAEAAAAb7AAA 77393 4 1787 0

AAAS5RAAEAAAAb9AAA 77393 4 1789 0

AAAS5RAAEAAAAb/AAA 77393 4 1791 0

AAAS5RAAEAAAAb/AAB 77393 4 1791 1

AAAS5RAAEAAAAb/AAC 77393 4 1791 2

AAAS5RAAEAAAAyGAAA 77393 4 3206 0 ---

12 rows selected.

2)10046跟踪

SQL> alter system flush buffer_cache;

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select count(*) from scott.e;

COUNT(*)

----------

12

SQL> alter session set events '10046 trace name context off';

Session altered.

jx1:/u01/app/oracle/diag/rdbms/jx/jx1/trace$ cat jx1_ora_55917.trc|grep read

Redo thread mounted by this instance: 1

WAIT #139784104305960: nam='db file sequential read' ela= 15423 file#=4 block#=1770 blocks=1 obj#=77393 tim=1545605262033100 (OK)

WAIT #139784104305960: nam='db file scattered read' ela= 650 file#=4 block#=1771 blocks=5 obj#=77393 tim=1545605262034068

WAIT #139784104305960: nam='db file scattered read' ela= 708 file#=4 block#=1776 blocks=8 obj#=77393 tim=1545605262035050

WAIT #139784104305960: nam='db file scattered read' ela= 678 file#=4 block#=1785 blocks=7 obj#=77393 tim=1545605262036010

WAIT #139784104305960: nam='db file scattered read' ela= 12389 file#=4 block#=3200 blocks=8 obj#=77393 tim=1545605262048744

原谅我容易满足，得到单块读现象后，我现在停止了脚步，如有兴趣的朋友，可以继续研究，这些块都是什么对象，为什么这读取，是否存在什么规则。