前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle 表碎片检查及整理方案

Oracle 表碎片检查及整理方案

作者头像
JiekeXu之路
发布2022-03-31 20:41:01
1.8K0
发布2022-03-31 20:41:01
举报
文章被收录于专栏:JiekeXu之路JiekeXu之路

大家好,我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 表碎片检查及整理方案

很多时候,对于某张表有大量的 delete 删除操作,但是发现空间并未释放,这是高水位未下降的原因,何为“高水位线”大概就是定义为 Oracle 段中已使用和未使用空间之间的分界。大量删除操作会导致表的碎片过多,从而影响性能,检查表的碎片率一般使用存储过程 SPACE_USAGE 程序。下面详细介绍一下 SPACE_USAGE 程序。

SPACE_USAGE 程序

此过程有两个变体来显示空间使用情况。

程序的第一种形式显示了高水位线段下数据块的空间使用情况。您可以计算 LOB、LOB 分区和 LOB 子分区的使用率。这个过程只能用于使用自动段空间管理创建的表空间。位图块、段头和区段图块不在这个过程中计算。注意,此重载不能在 SECUREFILE LOB 上使用。

过程的第二种形式返回有关 SECUREFILE LOB 空间使用情况的信息。它将返回 LOB 段中所有 SECUREFILE LOB 所使用的块中的空间量。这个过程显示 LOB 列正在使用的空间、过期保留的已释放空间和未过期保留的已释放空间。注意,这种重载只能在SECUREFILE LOB 上使用。

对于 LOB 段,从返回的full_blocks块unformatted_blocks数实际上是 LOB 段的块数。

语法 1:
代码语言:javascript
复制
DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN  VARCHAR2,
   segment_name            IN  VARCHAR2,
   segment_type            IN  VARCHAR2,
   unformatted_blocks      OUT NUMBER,
   unformatted_bytes       OUT NUMBER,
   fs1_blocks              OUT NUMBER,
   fs1_bytes               OUT NUMBER,
   fs2_blocks              OUT NUMBER,
   fs2_bytes               OUT NUMBER,
   fs3_blocks              OUT NUMBER,
   fs3_bytes               OUT NUMBER,
   fs4_blocks              OUT NUMBER,
   fs4_bytes               OUT NUMBER,
   full_blocks             OUT NUMBER,
   full_bytes              OUT NUMBER,
   partition_name          IN  VARCHAR2 DEFAULT NULL)
语法 2:
代码语言:javascript
复制
DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN    VARCHAR2,
   segment_name            IN    VARCHAR2,
   segment_type            IN    VARCHAR2,
   segment_size_blocks     OUT   NUMBER,
   segment_size_bytes      OUT   NUMBER,
   used_blocks             OUT   NUMBER,
   used_bytes              OUT   NUMBER,
   expired_blocks          OUT   NUMBER,
   expired_bytes           OUT   NUMBER,
   unexpired_blocks        OUT   NUMBER,
   unexpired_bytes         OUT   NUMBER,
   partition_name          IN    VARCHAR2 DEFAULT NULL);
各字段含义:

普通表碎片查看示例

代码语言:javascript
复制
set serverout on size 1000000
declare
p_fs1_bytes number;
p_fs2_bytes number;
p_fs3_bytes number;
p_fs4_bytes number;
p_fs1_blocks number;
p_fs2_blocks number;
p_fs3_blocks number;
p_fs4_blocks number;
p_full_bytes number;
p_full_blocks number;
p_unformatted_bytes number;
p_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => 'TEST',
segment_name => 'ORIGINAL',
segment_type => 'TABLE',
fs1_bytes => p_fs1_bytes,
fs1_blocks => p_fs1_blocks,
fs2_bytes => p_fs2_bytes,
fs2_blocks => p_fs2_blocks,
fs3_bytes => p_fs3_bytes,
fs3_blocks => p_fs3_blocks,
fs4_bytes => p_fs4_bytes,
fs4_blocks => p_fs4_blocks,
full_bytes => p_full_bytes,
full_blocks => p_full_blocks,
unformatted_blocks => p_unformatted_blocks,
unformatted_bytes => p_unformatted_bytes
);
dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);
dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);
dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);
dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);
dbms_output.put_line('Full blocks = '||p_full_blocks);
end;
/
FS1: blocks = 0
FS2: blocks = 2
FS3: blocks = 0
FS4: blocks = 0
Full blocks = 11

输出解释:FS1表明有 0 个数据块具有 0%-25% 的空闲空间,FS2 表明有 2 个数据块具有 25%-50% 的空闲空间,FS3 表明有 0 个数据块具有 50%-75% 的空闲空间,FS4 表明有 0 个数据块具有 75%-100% 的空闲空间,FULL 表明有 11 个 满的数据块。

分区表碎片查看示例

代码语言:javascript
复制
--分区表碎片查看
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('TEST', 'ORIGINAL', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, 'PAR1'); --PAR1 指分区名
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

分区表相关视图

上面示例需要查询分区名,一般使用 DBA_TAB_PARTITIONS 视图如下 SQL 查看,以下收集了关于分区表的相关视图,有需要的可以看看。

代码语言:javascript
复制
col TABLE_OWNER for a30
col TABLE_NAME for a30
col PARTITION_NAME for a30
col TABLESPACE_NAME for a30
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER='TEST' and TABLE_NAME='T1';
  • 显示当前用户可访问的所有分区表信息﹕ ALL_PART_TABLES
  • 显示当前用户所有分区表的信息﹕ USER_PART_TABLES
  • 显示表分区信息 显示数据库所有分区表的详细分区信息﹕ DBA_TAB_PARTITIONS
  • 显示当前用户可访问的所有分区表的详细分区信息﹕ ALL_TAB_PARTITIONS
  • 显示当前用户所有分区表的详细分区信息﹕ USER_TAB_PARTITIONS
  • 显示子分区信息 显示数据库所有组合分区表的子分区信息﹕ DBA_TAB_SUBPARTITIONS
  • 显示当前用户可访问的所有组合分区表的子分区信息﹕ ALL_TAB_SUBPARTITIONS
  • 显示当前用户所有组合分区表的子分区信息﹕ USER_TAB_SUBPARTITIONS
  • 显示分区列 显示数据库所有分区表的分区列信息﹕ DBA_PART_KEY_COLUMNS
  • 显示当前用户可访问的所有分区表的分区列信息﹕ ALL_PART_KEY_COLUMNS
  • 显示当前用户所有分区表的分区列信息﹕ USER_PART_KEY_COLUMNS
  • 显示子分区列 显示数据库所有分区表的子分区列信息﹕ DBA_SUBPART_KEY_COLUMNS
  • 显示当前用户可访问的所有分区表的子分区列信息﹕ ALL_SUBPART_KEY_COLUMNS
  • 显示当前用户所有分区表的子分区列信息﹕ USER_SUBPART_KEY_COLUMNS

用户级别查看碎片

当检查的表比较多时,甚至是全库时,使用如下 SQL 查看。

代码语言:javascript
复制
set lines 200 pages 1000
col frag format 999999.99
col owner format a30;
col table_name format a30;
col frag for a20

select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
       round(b.seg_bytes_mb, 2) seg_bytes_mb,
       decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) || '%' frag_percent
  from dba_tables a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
          from dba_segments
         group by owner, segment_name) b
 where a.table_name = b.segment_name
   and a.owner = b.owner
   --and a.owner not in ('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')
   and a.owner in ('TEST_JIEKE','PROD','SCOTT')
   --and a.table_name='T_ZDW_DOWN_SYNC_REC'
   and decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) > 50
 order by b.seg_bytes_mb desc;

表碎片整理

通过碎片整理来收缩空间,调整高水位线,方法比较简单,首先需要启用行移动,然后 alter table …… shrink space 即可完成,期间不会阻塞 DML 操作,可能时间会很久,建议业务低峰期间操作。

示例如下:

代码语言:javascript
复制
--启用行移动功能
alter table prod.T_DOWN_SYNC_REC enable row movement;

--收缩表
alter table prod.T_DOWN_SYNC_REC shrink space cascade;
--cascade 可以收缩与索引段相关的空间

--禁用行移动功能
alter table prod.T_DOWN_SYNC_REC disable row movement;

除了使用 shrink space 外,还有截断表,移动表,导入导出。但是 truncate 表直接清理数据,一般情况下不可取;alter table t move;移动表会使索引失效,移动完需要 rebuild 重新建索引,移动表时对数据行的 rowid 有所变更,而索引中又包含了 rowid, 故 move 表会使索引失效。最后导出导入数据时,不能有新数据进入,这点也不太友好,除非停止业务,故此推荐 shrink space 。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-03-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 JiekeXu之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • SPACE_USAGE 程序
    • 语法 1:
      • 语法 2:
        • 各字段含义:
        • 普通表碎片查看示例
        • 分区表碎片查看示例
        • 分区表相关视图
        • 用户级别查看碎片
        • 表碎片整理
        相关产品与服务
        数据库
        云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档