为什么0条记录,count一下这么慢？
0条记录，怎么会有这么多物理读？想不明白！SQL> set autot on
SQL> select count(*) from emailid;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMAILID'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
219504 consistent gets
187967 physical reads
1692236 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed 全表扫描时，要读取hwm下所有的块
truncate table 后再看看 HIGH WATER MARK代表一个表使用的最大的(top limit)块 。HIGH WATER MARK 记录在segment header中，segment header block中与HWM相关信息说明如下:
EXTENT CONTROL:
Extent Header:: spare1: 0 space2: 0 #extents: 13 #blocks: 1429
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x020004d0 ext#: 12 blk#: 275 ext size: 475
#blocks in seg. hdr’s freelists: 5
#blocks below: 1229
mapblk 0x00000000 offset: 12
Unlocked
==> spare1: this field is no longer used (old inc#, now always 0)
==> space2: this field is no longer used (old ts#, now always 0)
==> #extents: number of extents allocated to segment
==> #blocks: number of blocks allocated to segment
==> last map: address of last extent map block
0 if extent map is entirely in the segment header
==> #maps: number of extent map block
==> offset: offset to end of extent map
==> HWM dba: address of block at highwater mark
==> ext#: HWM extent number relative to segment
==> blk#: HWM block number within extent
==> ext size: HWM extent size (in blocks)
==> #blocks in seg. hdr’s freelists: number of blocks in seg. hdr’s free list
==> #blocks below: number of blocks below HWM
==> mapblk dba: dba of extent map block containing HWM extent
is 0 if HWM is in the segment header
==> offset: offset within extent map block
is the ext# if HWM is in segment header
==> Locked by: if locked by a transaction, the xid is displayed
HWM可以说是已经使用过的存储空间和未使用过的存储空间之间的分界线。在表使用过程中，HWM一直向一个方向移动，插入记录时HWM可能会向增加的方向移动，但是删除记录时HWM并不会向相反的方向移动HIGH WATER MARK之所以重要是因为它对全表扫描性能的影响。当实施一个全表扫描时，Oracle会读取所有HIGH WATER MARK下的块即使它们是空块。当HIGH WATER MARK 下有很多unused block时实施全表扫描会增加额外的不必要的I/O。它也会在全局共享区中填充很多很多空块 1 如果为表分配了大量的extents , 但这些extent 还没使用可以手工收回。并且有如下两种情况
第一种 minextent alter table tablename deallocate unused;
将hwm以上所有没使用的空间释放
第二种 minextent >hwm 则释放minextents 以上的空间。
如果要释放hwm以上的空间则使用keep 0。
alter table tablesname deallocate unused keep 0;
2 truncate table命令可以将minextent 之上的空间完全释放。
3 只是将hwm移动，释放的空间不会被其他段使用--
页:
[1]