绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
全表扫描读高水位以下的块
2020-05-13 08:21:49
做个实验给你演示一下:以表t1为例,对段t1做dump
1.t1表就一条数据
gyj@BXOCP> select * from t1;
ID NAME
---------- ----------
1 AAAAA

2.找t1段的段头块
gyj@BXOCP> select header_file,header_block from dba_segments where segment_name='T1' and wner='GYJ';
HEADER_FILE HEADER_BLOCK
----------- ------------
7 130

3另开一个会话:dump段头块( 7文件130号块)
[root@guoyj ~]# su - oracle
[oracle@guoyj ~]$ sqlplus / as sysdba
sys@BXOCP> alter system dump datafile 7 block 130;

4.dump的trace内容
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8 --红色的就为高水位地址
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01c00080
Level 1 BMB for Low HWM block: 0x01c00080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01c00081
Last Level 1 BMB: 0x01c00080
Last Level II BMB: 0x01c00081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 78183 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01c00080 length: 8

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01c00080 Data dba: 0x01c00083
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01c00081

End dump data blocks tsn: 7 file#: 7 minblk 130 maxblk 130

5.对表t1做一个全表扫描
gyj@BXOCP> set autot traceonly;
gyj@BXOCP> select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 14M| 759 (2)| 00:00:10 |
| 1 | TABLE ACCESS FULL| T1 | 999K| 14M| 759 (2)| 00:00:10 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets --全表扫描读了6个块
5 physical reads
0 redo size
596 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

6.怎么算出来的呢这个6
gyj@BXOCP> select file_id,block_id,blocks from dba_extents where segment_name='T1';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
7 128 8
这个t1段一共用了8个块分别是128 129 130 131 132 133 134 135
高水位: 0x01c00088 即7号文件的136号块
读了一次段头块:7文件130号块
读了高水位之下的131号块 132号块 133号块 134号块 135号块五个块
这样一共就读了6个块

注:全表扫描不坊128,129号块,对应的如下关系
Last Level 1 BMB: 0x01c00080 -->128号块
Last Level II BMB: 0x01c00081 -->129号块
分享好友

分享这个小栈给你的朋友们,一起进步吧。

聊聊云和数据
创建时间:2020-05-12 22:16:12
聚焦云计算、数据库、大数据等技术领域,分享行业新技术,迎欢大家一起交流学习,共同进步!
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • 云贝学院 郭一军
    栈主

小栈成员

查看更多
  • 小雨滴
  • 吾念阳光
  • jingjing0809
戳我,来吐槽~