10046确认多块读无法跨区
10046确认多块读无法跨区
SQL查询记录，记录是如何从磁盘中读取的呢？ 有几种访问方式？
本次测试探讨数据文件离散读无法跨区，因此单块读（顺序读）存在特殊场景,一个表的一个块在某个区中，Oracle查询记录及时全表扫描，也会产生单块读现象。
文档结构如下:
1.解释说明oracle数据文件从磁盘读取有几种方式
2.创建测试对象，并进行多块读测试解释说明
3.创建测试环境，一个表已分配多个区，存在一个区只使用一个块，次数对测试表全表扫描，验证是否存在单块读现象。
一、解释说明oracle数据文件从磁盘读取有几种方式
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
---------- ---------- ----------------------------------- ------------- ---------- ---------- ----------
9 2171045634 remote db file read clientid count intr Network
146 2652584166 db file sequential read file# block# blocks User I/O
147 506183215 db file scattered read file# block# blocks User I/O
151 834992820 db file parallel read files blocks requests User I/O
197 3926164927 direct path read file number first dba block cnt User I/O
198 861319509 direct path read temp file number first dba block cnt User I/O
269 3999678875 securefile direct-read completion User I/O
7 rows selected.
三种：方式依次为 多块读，单块读，直接路径读
db file scattered read
db file sequential read
direct path read
多块读：一次IO读取多个块，读取无法跨区，常见与全表扫描
单块读：一次IO读取单个块，常见索引rowid回表提取记录，非常见全表扫描有个块跨区落单了
直接路径读：读取的数据块，不放入buffer cache中，直接提取到会话PGA中，常见与LOB字段读取
二、多块读测试
1）创建测试数据 SQL> create table scott.e(id char(2000),name char(2000),
deptno char(2000),salary char(2000)); Table created. 2）插入测试数据 declare v_id int; begin for v_id in 1 .. 7 loop insert into scott.e values(v_id,v_id||'name',
v_id||'deptno',v_id||'salary'); end loop; commit; end; / 3）查询数据存储记录
SQL> select tablespace_name,file_id,extent_id,block_id,
bytes/1024 bytes_k,blocks from dba_extents
where owner='SCOTT' and segment_name='E'
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
4）查询rowid
SQL> select rowid,substr(id,1,2) from scott.e; ROWID SUBSTR(ID,1,2) ------------------ ---------------- AAAS5RAAEAAAAbsAAA 2 AAAS5RAAEAAAAbvAAA 1 AAAS5RAAEAAAAbwAAA 3 AAAS5RAAEAAAAbxAAA 4 AAAS5RAAEAAAAb0AAA 6 AAAS5RAAEAAAAb2AAA 5 AAAS5RAAEAAAAb/AAA 7 7 rows selected.
rowid解释说明，从左到有，可以发现表7行数据占用7个块
数据对象编号（6位显示）
相关数据文件编号（3位显示）
数据块编号（6位显示）
数据块中行编号（3位显示）
5）根据rowid查询块编号
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 AAAS5RAAEAAAAb/AAA 77393 4 1791 0 7 rows selected.
6）数据文件离散读测试
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(*)
———-
7
SQL> alter session set events ‘10046 trace name context off’;
Session altered.
jx1:/u01/app/oracle/diag/rdbms/jx/jx1/trace$ cat jx1_ora_30782.trc |grep scatt
WAIT #140130911224448: nam=’db file scattered read’ ela= 713 file#=4 block#=1771 blocks=5 obj#=77393 tim=1545603274223872
WAIT #140130911224448: nam=’db file scattered read’ ela= 735 file#=4 block#=1776 blocks=8 obj#=77393 tim=1545603274224854
WAIT #140130911224448: nam=’db file scattered read’ ela= 1281 file#=4 block#=1785 blocks=7 obj#=77393 tim=1545603274226337
可以发现执行一次全表扫描，数据文件多块读，读取了三次，每次返回不同的块数量
三、创建测试环境，一个表已分配多个区，存在一个区只使用一个块，次数对测试表全表扫描，验证是否存在单块读现象
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
原谅我容易满足，得到单块读现象后，我现在停止了脚步，如有兴趣的朋友，可以继续研究，这些块都是什么对象，为什么这读取，是否存在什么规则。