1. 云图网首页
  2. 技术专区
  3. 数据库

Oracle HWM 实验

• 数据库

基本概念

关于Oracle数据库概念性的知识Oracle高水位标志:high-water mark 可以用以下几点对high-water mark进行理解

  1. 指一个表中曾经被用过的最后一个块
    2 .假如有数据被插入表，high-water mark 就移到到被使用的最后一个块
  2. 假如有数据被删除，high-water mark的位置不会变
  3. high-water mark被储存在表的段头(segment header of the table)
  4. 当对表执行全表扫描时，oracle server 被所有的块直到high-water mark

原理

Oracle HWM 实验

实验

证明假如有数据被删除，high-water mark的位置不会降低

创建测试表

1
2
3
4
 
14:13:48 [OCP@tf](mailto:OCP@tf)> create table n (id number, remark char(100));
表已创建。
已用时间: 00: 00: 00.09  
14:13:51 [OCP@tf](mailto:OCP@tf)> set timi off;

插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
 
14:14:47 [OCP@tf](mailto:OCP@tf)> edit  
已写入 file afiedt.buf
 1 begin  
 2 for i in 1..1000000 loop  
 3 insert into n values ( i ,'bbk');  
 4 end loop;  
 5 commit;  
 6* end;  
14:15:03 [OCP@tf](mailto:OCP@tf)> /
PL/SQL 过程已成功完成。
14:17:46 [OCP@tf](mailto:OCP@tf)>  
14:17:46 [OCP@tf](mailto:OCP@tf)> set timi on;  
14:17:50 [OCP@tf](mailto:OCP@tf)> set autotrace on;

查看统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
14:17:56 [OCP@tf](mailto:OCP@tf)> select count(*) from n;
 COUNT(*)  
----------  
 1000000
已用时间: 00: 00: 08.78

执行计划  
----------------------------------------------------------  
Plan hash value: 1185471427
-------------------------------------------------------------------  
| Id | Operation | Name | Rows | Cost (%CPU)| Time |  
-------------------------------------------------------------------  
| 0 | SELECT STATEMENT | | 1 | 3427 (2)| 00:00:42 |  
| 1 | SORT AGGREGATE | | 1 | | |  
| 2 | TABLE ACCESS FULL| N | 989K| 3427 (2)| 00:00:42 |  
-------------------------------------------------------------------
Note  
-----  
 - dynamic sampling used for this statement
  
统计信息  
----------------------------------------------------------  
 29 recursive calls  
 1 db block gets  
 31038 consistent gets  
 8493 physical reads  
 1106036 redo size  
 408 bytes sent via SQL*Net to client  
 385 bytes received via SQL*Net from client  
 2 SQL*Net roundtrips to/from client  
 0 sorts (memory)  
 0 sorts (disk)  
 1 rows processed

发现 physical reads 8493 ,下面进行表分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
14:18:37 [OCP@tf](mailto:OCP@tf)> edit  
已写入 file afiedt.buf
 1 begin  
 2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'OCP', TABNAME=> 'N', PARTNAME=> NULL);  
 3* end;  
14:18:52 [OCP@tf](mailto:OCP@tf)> /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 12.82  
14:19:06 [OCP@tf](mailto:OCP@tf)> select owner,table_name, num_rows,blocks from dba_tables where table_name='N';
OWNER TABLE_NAME NUM_ROWS BLOCKS  
------------------------------ ------------------------------ ---------- ----------  
OCP N 998179 15458
  
14:19:11 [OCP@tf](mailto:OCP@tf)> set autotrace off;  

14:19:18 [OCP@tf](mailto:OCP@tf)> select owner,table_name, num_rows,blocks from dba_tables where table_name='N';
OWNER TABLE_NAME NUM_ROWS BLOCKS  
------------------------------ ------------------------------ ---------- ----------  
OCP N 998179 15458
14:19:35 [OCP@tf](mailto:OCP@tf)> col segment_name format a30;  
14:19:55 [OCP@tf](mailto:OCP@tf)> select owner,segment_name,bytes/1024/1024M from dba_segments where segment_name='N';
OWNER SEGMENT_NAME M  
------------------------------ ------------------------------ ----------  
OCP N 122

14:19:57 [OCP@tf](mailto:OCP@tf)> delete from n;

已删除1000000行。

1
2
 
14:23:12 [commit">OCP@tf>commit](mailto:OCP@tf);
提交完成。

DELETE数据后再次查看执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
14:23:12 [OCP@tf](mailto:OCP@tf)>  
14:23:12 [OCP@tf](mailto:OCP@tf)> set autotrace on;  
14:23:24 [OCP@tf](mailto:OCP@tf)> select count(*) from n;
 COUNT(*)  
----------  
 0
已用时间: 00: 00: 03.42
执行计划  
----------------------------------------------------------  
Plan hash value: 1185471427
-------------------------------------------------------------------  
| Id | Operation | Name | Rows | Cost (%CPU)| Time |  
-------------------------------------------------------------------  
| 0 | SELECT STATEMENT | | 1 | 3428 (2)| 00:00:42 |  
| 1 | SORT AGGREGATE | | 1 | | |  
| 2 | TABLE ACCESS FULL| N | 998K| 3428 (2)| 00:00:42 |  
-------------------------------------------------------------------
  
统计信息  
----------------------------------------------------------  
 1 recursive calls  
 0 db block gets  
 15473 consistent gets  
 11988 physical reads  
 0 redo size  
 407 bytes sent via SQL*Net to client  
 385 bytes received via SQL*Net from client  
 2 SQL*Net roundtrips to/from client  
 0 sorts (memory)  
 0 sorts (disk)  
 1 rows processed

physical reads 为11988 ,不但不减少,而且增多

再次查看统计信息

1
2
3
4
5
6
7
8
9
10
 
14:23:30 [OCP@tf](mailto:OCP@tf)> select owner,table_name, num_rows,blocks from dba_tables where table_name='N';
OWNER TABLE_NAME NUM_ROWS BLOCKS  
------------------------------ ------------------------------ ---------- ----------  
OCP N 998179 15458
14:24:04 [OCP@tf](mailto:OCP@tf)> set autotrace off;  
14:24:09 [OCP@tf](mailto:OCP@tf)> select owner,table_name, num_rows,blocks from dba_tables where table_name='N';
OWNER TABLE_NAME NUM_ROWS BLOCKS  
------------------------------ ------------------------------ ---------- ----------  
OCP N 998179 15458
已用时间: 00: 00: 00.01

表分析一下

1
2
3
4
5
6
7
8
9
10
11
12
 
14:24:12 [OCP@tf](mailto:OCP@tf)> begin  
14:24:23 2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'OCP', TABNAME=> 'N', PARTNAME=> NULL);  
14:24:25 3 end;  
14:24:26 4 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 08.31  
14:24:36 [OCP@tf](mailto:OCP@tf)> select owner,table_name, num_rows,blocks from dba_tables where table_name='N';
OWNER TABLE_NAME NUM_ROWS BLOCKS  
------------------------------ ------------------------------ ---------- ----------  
OCP N 0 15458
已用时间: 00: 00: 00.01  
14:24:38 [OCP@tf](mailto:OCP@tf)>

发现数据delete后,占用的BLOCKS没有释放,也就是高水位并没有降下去

  • 说明一: DBA_TABLES字段empty_blocks 显示HWM以上有多少块,即没有被使用的块
  • 说明二 : HWM以下的块有些被使用,有些是空块,以下SQL查询
    1
    2
    3
    4
    5
    6
    		 
    select blocks "Actual_blocks_uses_below_HWM",  
     round( d.avg_row_len*d.num_rows/1024/8 ,0)"Blocks_need_below_HWM" ,  
     blocks - round( d.avg_row_len*d.num_rows/1024/8 ,0) "Wasted_blocks_below_HWM",  
     tablespace_name  
    from dba_tables d  
    where owner='OCP' and table_name='TEST_1'

说明表OCP.TEST_1中HWM以下共用285个块,其中HWM以下有76个空块,实际有209块有数据

降低HWM方法

  • 1 导出,并导入此表
  • 2使用ALTER TABLE…MOVE…命令重建表

dba_extents

1
2
3
4
 
08:12:04 [OCP@tf](mailto:OCP@tf)> select sum(blocks) from dba_extents where segment_name='TEST_1' and owner='OCP';
SUM(BLOCKS)  
-----------  
 384

这里是384个BLOCK,多出的部分是段头;

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/236350.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论