oracle构建索引index后table的10046数据块读取测试
测试目的
1,未创建索引前的表的扫描情况
1,扫描哪些数据块
2,数据块之间的关系
3,物理读
4,逻辑读
5,以上测试区分：全表扫描与部分表记录扫描
6,扫描数据块是采用单块读取还是多块读取还是先单块读后多块读取？
2,小结：
1,表扫描速度与数据块大小的关系
2,表扫描与并行度设置的关系
3,表扫描与db cache的关系
前文测试了全表扫描的数据块读取情况；如果对表建立了索引，先读取索引，然后根据ROWID再读取对应表记录的数据块
SQL> create table t_detail(a int);
Table created.
--插入10000条记录
SQL> insert into t_detail select level from dual connect by level<=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_detail on t_detail(a);
Index created.
--跟踪已建索引的查询
SQL> alter system set events '10046 trace name context level 8';
System altered.
--因表数据量10000条，10046 trace对查询速度有一定影响
SQL> select count(a) from t_detail where a=2000;
COUNT(A)
----------
1
--关闭10046 trace
SQL> alter system set events '10046 trace name context off';
System altered.
--仅摘录10046 trace重要内容
WAIT #2: nam='Disk file operations I/O' ela= 886 FileOperation=2 fileno=10 filetype=2 obj#=69559 tim=31824399508 --先是一个等待事件
WAIT #2: nam='db file sequential read' ela= 20687 file#=10 block#=276483 blocks=1 obj#=69559 tim=31824420353 －单块读 file#=10 block#=276483 blocks=1 obj#=69559
WAIT #2: nam='db file sequential read' ela= 823 file#=10 block#=276488 blocks=1 obj#=69559 tim=31824421542 --继续单块读 file#=10 block#=276488 blocks=1 obj#=69559
FETCH #2:c=0,e=23170,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1976055679,tim=31824421699 --然后提取数据了
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=2 pr=2 pw=0 time=0 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=69559 p='INDEX RANGE SCAN IDX_T_DETAIL (cr=2 pr=2 pw=0 time=0 us cost=1 size=13 card=1)'
--上述2个单块读的数据块是什么呢？表还是表所属索引的数据块
--可知上述TRACE中的对象不是表
SQL> select owner,object_name,object_id from dba_objects where object_name='T_DETAIL' and wner='SCOTT';
OWNER OBJECT_NAME OBJECT_ID
------------------------------ -------------------------------------------------------------------------------- ----------
SCOTT T_DETAIL 69558
--是不是索引呢,就是索引，所以单块读先是读取索引的数据块
SQL> select owner,object_name,object_id from dba_objects where object_name='IDX_T_DETAIL' and wner='SCOTT';
OWNER OBJECT_NAME OBJECT_ID
------------------------------ -------------------------------------------------------------------------------- ----------
SCOTT IDX_T_DETAIL 69559
--既然读取索引的数据块，哪这是索引的哪个位置的数据块呢
--index的段头块为276482
SQL> select segment_name,HEADER_FILE,header_block from dba_segments ds where ds.segment_name='IDX_T_DETAIL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
IDX_T_DETAIL 10 276482
--表占用的块
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where wner='SCOTT' and segment_name='T_DETAIL';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_DETAIL 0 220392 8
T_DETAIL 1 276336 8
T_DETAIL 2 276344 8
--index占用的块
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where wner='SCOTT' and segment_name='IDX_T_DETAIL';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
IDX_T_DETAIL 0 276480 8
IDX_T_DETAIL 1 276488 8
IDX_T_DETAIL 2 276496 8
IDX_T_DETAIL 3 276504 8
而上述的10046的trace中单块读 file#=10 block#=276483 blocks=1 obj#=69559，正好位于第一个extent之内
另一个单块读file#=10 block#=276488 blocks=1 obj#=69559，刚好是第二个extent的头一个块
--我们看看这2个数据块的内容
---1,block 276483
Start dump data blocks tsn: 8 file#:10 minblk 276483 maxblk 276483
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219523
BH (0x000007FF023F7FE8) file#: 10 rdba: 0x02843803 (10/276483) class: 1 ba: 0x000007FF02360000
--中间部分略
Block header dump: 0x02843803
Object id on Block? Y
seg/obj: 0x10fb7 csc: 0x00.5826bb itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2843800 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.005826bb
Branch block dump --分支块转储
---中间部分略
row#0[8051] dba: 42219525=0x2843805
col 0; len 3; (3): c2 05 57
col 1; TERM
row#1[8042] dba: 42219526=0x2843806
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8033] dba: 42219527=0x2843807
col 0; len 3; (3): c2 0f 2d
col 1; TERM
row#3[8024] dba: 42219528=0x2843808
col 0; len 3; (3): c2 14 18
col 1; TERM
row#4[8015] dba: 42219529=0x2843809
col 0; len 3; (3): c2 19 03
col 1; TERM
row#5[8006] dba: 42219530=0x284380a
col 0; len 3; (3): c2 1d 52
col 1; TERM
row#6[7997] dba: 42219531=0x284380b
col 0; len 3; (3): c2 22 3d
col 1; TERM
row#7[7988] dba: 42219532=0x284380c
col 0; len 3; (3): c2 27 28
col 1; TERM
row#8[7979] dba: 42219533=0x284380d
col 0; len 3; (3): c2 2c 13
col 1; TERM
row#9[7970] dba: 42219534=0x284380e
col 0; len 3; (3): c2 30 62
col 1; TERM
row#10[7961] dba: 42219535=0x284380f
col 0; len 3; (3): c2 35 4d
col 1; TERM
row#11[7952] dba: 42219537=0x2843811
col 0; len 3; (3): c2 3a 38
col 1; TERM
row#12[7943] dba: 42219538=0x2843812
col 0; len 3; (3): c2 3f 23
col 1; TERM
row#13[7934] dba: 42219539=0x2843813
col 0; len 3; (3): c2 44 0e
col 1; TERM
row#14[7925] dba: 42219540=0x2843814
col 0; len 3; (3): c2 48 5d
col 1; TERM
row#15[7916] dba: 42219541=0x2843815
col 0; len 3; (3): c2 4d 48
col 1; TERM
row#16[7907] dba: 42219542=0x2843816
col 0; len 3; (3): c2 52 33
col 1; TERM
row#17[7898] dba: 42219543=0x2843817
col 0; len 3; (3): c2 57 1e
col 1; TERM
row#18[7889] dba: 42219544=0x2843818
col 0; len 3; (3): c2 5c 09
col 1; TERM
row#19[7880] dba: 42219545=0x2843819
col 0; len 3; (3): c2 60 58
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276483 maxblk 276483
--转储第二个索引块
SQL> alter system dump datafile 10 block 276488;
System altered
--略去无关内容
Block header dump: 0x02843808
Object id on Block? Y
seg/obj: 0x10fb7 csc: 0x00.5826bb itc: 2 flg: E typ: 2 - INDEX
brn: 1 bdba: 0x2843800 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 --说明索引块也会记录itl事务槽内容
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.005826bb
Leaf block dump --说明是叶子块
===============
header address 224405092=0xd602664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
－－略去中间内容
kdxlebksz 8036
row#0[8023] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 18
col 1; len 6; (6): 02 83 5c ee 02 5a
row#1[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 19
col 1; len 6; (6): 02 83 5c ee 02 5b
row#2[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 1a
col 1; len 6; (6): 02 83 5c ee 02 5c
row#3[7984] flag: ------, lock: 0, len=13
－－略去中间内容
row#477[1827] flag: ------, lock: 0, len=12
col 0; len 2; (2): c2 19
col 1; len 6; (6): 02 83 5c ef 01 a3
row#478[1814] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 19 02
col 1; len 6; (6): 02 83 5c ef 01 a4
----- end of leaf block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276488 maxblk 276488
--转储下其它索引块
－－276496
Start dump data blocks tsn: 8 file#:10 minblk 276496 maxblk 276496
frmt: 0x02 chkval: 0xc06a type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 2 nranges: 2 parent dba: 0x02843801 poffset: 1
--276504索引块
Start dump data blocks tsn: 8 file#:10 minblk 276504 maxblk 276504
*** 2013-03-15 20:04:18.821
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219544
Block dump from disk:
buffer tsn: 8 rdba: 0x02843818 (10/276504)
scn: 0x0000.005826c4 seq: 0x02 flg: 0x04 tail: 0x26c40602
frmt: 0x02 chkval: 0x14da type: 0x06=trans data
－－－中间内容略
Leaf block dump
header address 224405092=0xd602664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
－－中间内容略
----- end of leaf block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276504 maxblk 276504
小结：1，在有索引的情况下，查询表先是扫描索引块的分支块，然后是叶子块，通过叶子块找到最终的记录
逻辑读为2，我分析因为分子块和叶子块各为两次；故为2；如果索引的高度增高，这样逻辑读次数就会变大了；所以重构索引很重要
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1976055679
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX_T_DETAIL | 1 | 13 | 1 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-756282/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
