数据库中如何降低高水位

58次阅读
没有评论

共计 10482 个字符，预计需要花费 27 分钟才能阅读完成。

这篇文章主要为大家展示了“数据库中如何降低高水位”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“数据库中如何降低高水位”这篇文章吧。

降低高水位方法 1. move

a.move 不但可以重置水位线（HWM），解决松散表带来的 IO 浪费，还可以解决表中的行迁移问题；

b.move 可以将表移动到其他表空间，也可以在原表空移动，这样可以一定程度解决表空间碎片；

c. 如果表空间上有大量表、索引被 drop（或者 truncate），导致表空间前半部分出现大量空闲空间，可以通过 move 将靠后的表移动到前面的空闲空间，从而收缩数据文件。

 

实验：

sys@ORCL conn
shall/shall

Connected.

shall@ORCL create
table zhong(x int);

Table
created.

 

shall@ORCL begin

  2  for
i in 1..100000 loop

  3 
insert into zhong values(i);

  4  end
loop;

  5 
commit;

  6  end;

  7  /

 

PL/SQL
procedure successfully completed.

 

—- 收集统计信息

shall@ORCL analyze
table zhong compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  244  12

 

—-delete 表 zhong

shall@ORCL delete
zhong;

100000
rows deleted.

 

shall@ORCL analyze
table zhong compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  244  12

 

—-move 整理碎片

shall@ORCL alter
table zhong move;

Table
altered.

或者 alter table zhong move tablespace hct;  —-move 到 hct 表空间

/*

如果 move 到 hct 表空间了，可以看见表空间已经变了，如下

shall@ORCL select
table_name,tablespace_name from user_tables;

TABLE_NAME  TABLESPACE_NAME

——————————
——————————

TTTT  USERS

ZHONG   HCT

*/

 

shall@ORCL analyze
table zhong compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG   0  8

 

—- 高水位已经降下来了。move 到该表空间，需要保证有足够的剩余空间

 

—- 重建索引

shall@ORCL
alter index inx_t_x rebuild;

Index
altered.

或 alter index inx_t_x rebuild tablespace users;

 

—- 查看索引状态

SCOTT@test
set linesize 200

SCOTT@test
select index_name,table_name,tablespace_name,status from user_indexes;

 

 

—- 注意事项：

—-Rebuild
index

在对表进行 move 操作后，表中的 rowid 发生了改变，这样导致索引无法定位到原来表中的数据，从而触发了索引失效，所以需要 alter index index_name rebuild [online] 的命令进行重建。

—- 空间分配

alter
table move 操作，必须给 move 的表空间足够的剩余空间，否则可能会出现 ORA-01652 告警。

—-exclusive lock

move 操作相当于将表中所有数据移动，因此在 move 的过程中，oracle 会对表放置了 exclusive lock 锁，此时只能对它进行 select 操作。

 

2. shrink space

此命令为 Oracle 10g 新增功能，shrink 操作是将原本松散的数据存放结构，通过将表中靠后的行向前面的空闲块迁移，在完成后将完全空闲的区释放，并前置 HWM 到表中最后一个使用块的位置，从而实现松散表重新结构紧凑。

 

使用条件

  自动段管理模式。只支持 ASSM 管理的表空间，如果不是会报 ORA-10635: Invalid segment or
tablespace type

  打开行移动  
alter table table_name enable row movement

 

参数：

alter
table TABLE_NAME shrink space [compact|cascate]

 

alter
table TABLE_NAME shrink space; 整理碎片并回收空间

alter
table TABLE_NAME shrink space compact; 只整理碎片 不回收空间

alter
table TABLE_NAME shrink space cascate; 整理碎片回收空间 并连同表的级联对象一起整理 (比如索引)

 

使用步骤

1. alter
table t1 enable ROW MOVEMENT;

2. shrink 操作

3. alter
table t1 disable ROW MOVEMENT;

 

实验：

—- 查看表空间段管理模式

sys@ORCL select
tablespace_name,block_size,extent_management,allocation_type,segment_space_management
from dba_tablespaces order by segment_space_management;

TABLESPACE_NAME  BLOCK_SIZE EXTENT_MAN ALLOCATIO
SEGMEN

——————————
———- ———- ——— ——

SYSAUX  8192 LOCAL  SYSTEM 
AUTO

HCT  8192
LOCAL  SYSTEM  AUTO

USERS  8192 LOCAL  SYSTEM 
AUTO

EXAMPLE  8192 LOCAL   SYSTEM 
AUTO

TEMP  8192
LOCAL  UNIFORM  MANUAL

UNDOTBS1  8192 LOCAL  SYSTEM 
MANUAL

SYSTEM   8192 LOCAL  SYSTEM 
MANUAL

 

—- 查看 shall 用户使用的默认表空间

sys@ORCL select
username,default_tablespace,temporary_tablespace from dba_users where
username= SHALL

USERNAME  DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE

——————————
—————————— ——————————

SHALL  USERS  TEMP

 

—- 创建表及插入数据

sys@ORCL conn
shall/shall

Connected.

shall@ORCL create
table shall(ttt int);

Table
created.

 

sys@ORCL begin

  2  for
i in 1..1000000 loop

  3 
insert into shall values(i);

  4  end
loop;

  5 
commit;

  6  end;

  7  /

PL/SQL
procedure successfully completed.

 

shall@ORCL analyze
table shall compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= SHALL

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

SHALL  1630  34

 

—-delete 表 shall

shall@ORCL delete
shall;

1000000
rows deleted.

 

shall@ORCL analyze
table shall compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= SHALL

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

SHALL  1630  34

 

—- 开始 shrink 整理碎片

shall@ORCL alter
table shall enable row movement;

Table
altered.

 

shall@ORCL alter
table shall shrink space;

Table
altered.

 

shall@ORCL alter
table shall disable row movement;

Table
altered.

 

—- 为刷新统计信息之前，高水位未降

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= SHALL

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

SHALL  1630  34

 

shall@ORCL analyze
table shall compute statistics;

Table
analyzed.

 

shall@ORCL select
table_name,blocks,empty_blocks from user_tables where table_name= SHALL

TABLE_NAME   BLOCKS EMPTY_BLOCKS

——————————
———- ————

SHALL  1  7

 

使用 shrink 降低高水位的优点：

1）能在线进行，不影响表上的 DML 操作，当然，并发的 DML 操作在 shrink 结束的时刻会出现短暂的 block；

2）shrink 的另外一个优点是在碎片整理结束后，表上相关的 index 仍然 enable。

  对于第二点进一步说明下，shrink 在整理表碎片的时候，行的 rowid 已经发生改变，那为什么相关的索引还能 enable 呢？其实 oracle 在进行 shrink 的时候会对相应的索引进行维护，以保证 index 在 shrink 结束的时候 index 仍然有效。这个维护不同于索引 rebuild，不会对索引的空间进行整理，shrink 有 cascede 选项，如果在 shrink 的时候加上该选项，就会对表上相应的索引空间进行整理。 ALTER TABLE tablename SHRINK
SPACE CASCADE;

 
   

shrink 也可以分两步进行

1）先执行 ALTER TABLE tablename SHRINK SPACE
compact, 此时 oracle 会在高水位线以下将 row 尽量向 segment 的顶部移动，但不收缩高水位线，即不释放空间。这个操作对于那些在尝试读取已经被释放的块的查询是有益的。

2）然后在执行 ALTER TABLE test SHRINK SPACE，此时第一步中的结果已经存储到磁盘，不会重新在整理碎片，只是收缩高水位，释放空间。第二步操作应该在系统不繁忙时候进行。

 

shrink 的工作原理

shrink 的算法是从 segment 的底部开始，移动 row 到 segment 的顶部，移动的过程相当于 delete/insert 操作的组合，在这个过程中会产生大量的 undo 和 redo 信息。

 

另外，  对于空间的要求，shrink 不需要额外的空间，move 需要两倍的空间。

 

3. rename to

复制要保留的数据到临时表 t，drop 原表，然后 rename to 临时表 t 为原表

验证：

  begin

  for i in 1..100000 loop

   
insert into t2 values(i);

  end loop;

  commit;

  end;

  /

  analyze table t2 compute statistics;

   select
table_name,blocks,empty_blocks

  from dba_tables

  where table_name= T2

 

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

T2  152  103

 

SQL
delete t2;

100000
rows deleted.

SQL
create table t3 as select * from t2;

SQL
analyze table t2 compute statistics;

SQL
select table_name,blocks,empty_blocks

  2  from
dba_tables

  3 
where table_name= T2

 

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

T2  152  103

 

SQL
drop table t2;

SQL
alter table t3 rename to t2;

SQL analyze
table t2 compute statistics;

SQL
select table_name,blocks,empty_blocks

  2  from
dba_tables

  3 
where table_name= T2

 

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

T2  1  6

 

4. exp/imp

用 EXP 导出后，删除原表 / 表空间，之后用 IMP 重新导入

实验：

shall@ORCL create
table zhong(id int);

Table
created.

 

shall@ORCL begin

  2  for
i in 1..1000000 loop

  3 
insert into zhong values(i);

  4  end
loop;

  5 
commit;

  6  end;

  7  /

PL/SQL
procedure successfully completed.

 

shall@ORCL analyze
table zhong compute statistics;

Table
analyzed.

sys@ORCL
select table_name,blocks,empty_blocks from dba_tables  where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  1630  34

 

—- 删除然后导出表

shall@ORCL delete
zhong where id 50000;

950000
rows deleted.

[oracle@zyx
~]$ exp \ / as sysdba\ tables=shall.zhong file=zhong.dmp log=zhong.log

Export:
Release 11.2.0.4.0 – Production on Sun May 1 18:34:39 2016

Copyright
(c) 1982, 2011, Oracle and/or its affiliates. 
All rights reserved.

Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit
Production

With the
Partitioning, OLAP, Data Mining and Real Application Testing options

Export
done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to
export specified tables via Conventional Path …

Current
user changed to SHALL

. .
exporting table 
ZHONG  50000 rows exported

Export
terminated successfully without warnings.

[oracle@zyx
~]$

 

—-drop 原表

shall@ORCL drop
table zhong;

Table
dropped.

 

—- 导入表

[oracle@zyx
~]$ imp \ / as sysdba\ tables=zhong file=zhong.dmp fromuser=shall touser=shall;

Import:
Release 11.2.0.4.0 – Production on Sun May 1 18:37:44 2016

Copyright
(c) 1982, 2011, Oracle and/or its affiliates. 
All rights reserved.

Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit
Production

With the
Partitioning, OLAP, Data Mining and Real Application Testing options

Export
file created by EXPORT:V11.02.00 via conventional path

import
done in ZHS16GBK character set and AL16UTF16 NCHAR character set

.
importing SHALL s objects into SHALL

. .
importing table 
ZHONG   50000 rows
imported

Import
terminated successfully without warnings.

[oracle@zyx
~]$

—- 未刷新统计信息时

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  1630  0

 

—- 刷新统计信息后

shall@ORCL analyze
table zhong compute statistics;

Table
analyzed.

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  110  1554

 

—- BLOCKS  列代表该表中曾经使用过得数据库块的数目，即水线。EMPTY_BLOCKS  代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块

 

 

5. deallocate unused

alter
table table_name deallocate unused;

注：这证明，DEALLOCATE UNUSED 为释放 HWM 上面的未使用空间，但是并不会释放 HWM 下面的自由空间，也不会移动 HWM 的位置。

truncate table 后，有可能表空间仍没有释放，可以使用如下语句：

  alter
table 表名称
deallocate   UNUSED KEEP 0;

例如：

alter table tablename deallocate UNUSED KEEP
0;

或者：

truncate table  tablename DROP STORAGE; 才能释放表空间

注意: 如果不加 KEEP 0 的话，表空间是不会释放的。

实验：接上面导入导出实验

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  110  1554

 

—- 开始整理

sys@ORCL alter
table shall.zhong deallocate unused keep 0;

Table
altered.

 

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  110  1554

 

sys@ORCL analyze
table shall.zhong compute statistics;

Table
analyzed.

 

—- 整理之后

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

——————————
———- ————

ZHONG  110  18

 

6. truncate

尽量使用 truncate（如：truncate t1）

实验：接上面实验

sys@ORCL select table_name,blocks,empty_blocks
from dba_tables  where
table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

—————————— ———-
————

ZHONG  110  18

 

sys@ORCL truncate table shall.zhong;

Table truncated.

 

sys@ORCL analyze table shall.zhong
compute statistics;

Table analyzed.

 

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

—————————— ———-
————

ZHONG  0  128

 

sys@ORCL alter table shall.zhong
deallocate unused keep 0;

Table altered.

 

sys@ORCL analyze table shall.zhong
compute statistics;

Table analyzed.

 

sys@ORCL select
table_name,blocks,empty_blocks from dba_tables 
where table_name= ZHONG

TABLE_NAME  BLOCKS EMPTY_BLOCKS

—————————— ———-
————

ZHONG  0  24

以上是“数据库中如何降低高水位”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

正文完
move orcl shall table zhong
发表至： 数据库
2023-07-20
 
丸趣
版权声明：本站原创文章，由 丸趣 2023-07-20发表，共计10482字。
转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。
Mac下更换MySQL版本后怎么恢复原有的数据库表
如何使用zabbix监控MySQL
phpmyadmin 1045错误无法登录MySQL服务器的解决方法
oracle如何使用单行函数
评论（没有评论）