【Oracle数据库优化实战】从5T瘦身至1T—高效压缩存储空间的秘诀与实践

邱传豪

已于 2024-12-27 13:34:32 修改

阅读量1.1k

点赞数 14

分类专栏： oracle 文章标签：数据库 oracle 性能优化

于 2024-12-27 13:06:30 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43400157/article/details/144758321

版权

oracle 专栏收录该内容

6 篇文章

订阅专栏

1.系统表空间清理

oracle数据库中，system为系统表空间，存放着一些我们经常用到的系统表和视图，sysaux为辅助表空间。这两个表空间不宜添加数据文件，会使系统表空间过于臃肿，从而影响数据库的使用。

下面我们讲述一下如何通过清理这两个表空间达到oracle数据库瘦身的目的。

1.1 查询表空间的使用率

查询SYSAUX、SYSTEM表空间使用率
SELECT df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) 
AND df.file_id = free.file_id(+)
and df.tablespace_name IN ('SYSAUX','SYSTEM')
GROUP BY df.tablespace_name

1.2 清理system表空间

1.2.1 查询system表空间中大于100M的对象

1.查询system大于100m的对象

select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 size_mb from dba_segments
where tablespace_name='SYSTEM' group by segment_name)
where size_mb>100 order by size_mb desc;

SEGMENT_NAME	SIZE_MB
AUD$	        100549
LOGIN_HISTORY	14801
IDL_UB1$	    288

2.查询审计表
SELECT * FROM dba_audit_trail WHERE rownum <= 10; -- 查看前10条记录

SELECT username, timestamp, action_name, returncode FROM dba_audit_trail
WHERE timestamp >= SYSTIMESTAMP - INTERVAL '7' DAY -- 最近7天的记录
AND username = 'SCOTT'; -- 特定用户的记录

查询结果发现AUD$这个对象占用了大量的空间，AUD$是一个审计表。在Oracle 11gR2及更高版本中，系统默认启用了数据库级别的审计功能 (audit_trail=DB)，这会将大量的数据库操作记录到SYS.AUD$表中。随着时间的推移，这些审计记录会导致所在表空间迅速膨胀，占用大量存储空间。

1.2.2 清理SYS.AUD$表或迁移表空间

要解决SYS.AUD$表占用过多空间的问题，主要有两种模式可以选择：清理该表或将其移动到其他表空间。这两种方法都不会影响数据库的正常运行。

但在决定是否清理审计表之前，应该仔细评估业务需求、法律法规以及安全策略的影响。审计表在追踪用户行为、性能分析与优化起着重要作用，许多国家和行业的法律法规要求保留特定时间段内的操作记录。

方法 1 ：使用DBMS_AUDIT_MGMT包或直接删除过期的审计记录。

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    use_last_arch_timestamp => TRUE
  );
END;
/

方法2 压缩表

使用ALTER TABLE ... SHRINK SPACE命令来压缩表并调整高水位线（HWM）。

-- 启用行移动
ALTER TABLE sys.aud$ ENABLE ROW MOVEMENT;

-- 收缩表并调整HWM
ALTER TABLE sys.aud$ SHRINK SPACE CASCADE;

方法3 清空表（不建议）

1.清理
TRUNCATE TABLE SYS.AUD$ reuse storage;

2.查询大小
select bytes/1024/1024 
from dba_segments 
where segment_name='AUD$';

方法4：迁移审计表空间

--1. 迁移审计表空间到TBS_AUDIT（首先要新建TBS_AUDIT，此处省略）
--moves table AUD$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_AUDIT');
END;
/

--moves table FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'TBS_AUDIT');
END;
/

-- 2.检查表是否成功移动
select table_name,tablespace_name from dba_tables where table_name in ('AUD$','FGA_LOG$') order by table_name;

1.2.3 调整审计策略

-- 关闭审计功能（不建议）
alter system set audit_trail=none scope=spfile;
showdown immediate;
startup; 

--自动清理策略

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
audit_trail_property_value => 102400 /* 100MB*/ \
);
END;
/* audit_trail_type: 指定要管理的审计追踪类型。这里设置为AUDIT_TRAIL_OS，表示操作系统的审计文件。
audit_trail_property: 指定要设置的属性。OS_FILE_MAX_SIZE用于定义单个审计文件的最大尺寸。
audit_trail_property_value: 设置该属性的具体值。这里的值为102400，单位是KB，因此相当于100MB。
上面这条语句的作用是将每个审计文件的最大大小限制为100MB。当一个文件达到这个大小时，系统会开始一个新的文件以继续记录审计信息。 */

/
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
audit_trail_property_value => 5 /* days */
);
END;
/
/*audit_trail_type: 同样指定为AUDIT_TRAIL_OS，即操作系统级别的审计文件。
audit_trail_property: 这次设置的是OS_FILE_MAX_AGE，用于定义审计文件的最大保留时间。
audit_trail_property_value: 设置该属性的具体值。这里的值为5，单位是天。
这条语句的作用是设置审计文件的最大保留时间为5天。超过这个时间后，旧的审计文件将被自动删除或归档，具体行为取决于数据库的配置和策略。
*/

1.3 清理SYSAUX表空间

SYSAUX表空间作为SYSTEM的辅助表空间，存储了AWR快照、统计信息和审计记录等数据。检查SYSAUX表空间时，发现占用较大空间的对象多为以WRH$开头的AWR相关对象。

1.3.1 查看SYSAUX大于100M的对象

select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 size_mb from dba_segments
where tablespace_name='SYSAUX' group by segment_name)
where size_mb>100 order by size_mb desc;

1.3.2 清理 AWR

默认情况下，AWR报告通过DELETE方式清理过期信息，这会产生大量碎片，尤其是在启用了自动扩展数据文件的表空间中，问题更为严重。使用DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE删除快照非常耗时，并且在生产库中执行此操作存在风险。这是因为该过程实际执行的是基表的DELETE操作，可能导致频繁的归档日志切换、产生大量归档文件，进而引发归档目录空间不足和UNDO表空间压力。

1）少量快照清理（推荐使用 DROP_SNAPSHOT_RANGE）
适用于需要清理的快照数量较少的情况。

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
    low_snap_id  => <起始快照ID>,
    high_snap_id => <结束快照ID>,
    dbid         => <数据库ID>
  );
END;
/
参数说明：
low_snap_id：开始清理的快照ID。
high_snap_id：结束清理的快照ID。
dbid：数据库ID，默认为当前数据库。

2 大量快照清理（建议分步进行）

对于大规模快照清理，直接使用 DROP_SNAPSHOT_RANGE 可能耗时过长且资源消耗大，建议分步进行：

a. 准备阶段
停用自动扩展（如果可能）：暂时禁用表空间的自动扩展功能，避免碎片化加剧。
监控归档日志和UNDO表空间：确保有足够的归档日志和UNDO空间，防止因频繁切换导致问题。

b. 分批清理快照
通过脚本或手动方式分批执行 DROP_SNAPSHOT_RANGE，每次处理一定数量的快照，减少单次操作的影响

BEGIN
  FOR i IN (SELECT snap_id FROM dba_hist_snapshot WHERE snap_id BETWEEN <起始快照ID> AND <结束快照ID>)
  LOOP
    DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
      low_snap_id  => i.snap_id,
      high_snap_id => i.snap_id,
      dbid         => <数据库ID>
    );
    -- 可以在此处添加延时或检查点，确保系统稳定
  END LOOP;
END;
/

c. 优化表空间
重建索引：清理后重建受影响的索引，以提高性能。
压缩表：考虑对相关表进行在线压缩，减少碎片。

3）使用 TRUNCATE（需谨慎）
经过充分评估和测试后，可以考虑使用 TRUNCATE 操作。这一步骤应由经验丰富的DBA执行，并确保不影响现有业务。

-- 注意：此操作会永久删除所有历史数据，请务必谨慎执行
TRUNCATE TABLE SYS.WRH$<table_name>;

参数说明：<table_name> 替换为具体的AWR历史数据表名（如WRH$_SQLSTAT、WRH$_SYSMETRIC_HISTORY等）

1.3.3 统计信息保留时间

修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

select
dbms_stats.get_stats_history_retention
from dual; 

exec
dbms_stats.alter_stats_history_retention(7);

2.索引重建

2.1 分区及非分区重建索引

--分区
alter index xx.xxx rebuild partition xxx online parallel 4;
ALTER INDEX xx.xxx NOPARALLEL；

--非分区
alter index xx.xxx rebuild online parallel 4;
ALTER INDEX xx.xxx NOPARALLEL；

2.2 清理索引创建失败信息

declare
isClean boolean;
begin
isClean := FALSE;
while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
dbms_repair.lock_wait);
dbms_lock.sleep(2);
end loop;
exception
when others then
RAISE;
end;
/

3. 降低水位线

高水位线（HWM）的存在会使大表及其索引变得臃肿，导致查询速度减慢并增加I/O资源消耗。为确保高效性能和合理使用存储空间，建议定期降低HWM，以优化查询效率并减少资源开销。

3.1 缩小数据文件

-- 设置环境，关闭命令回显，格式化输出
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

-- 获取数据库块大小并存储为变量 blksize
column value new_val blksize
select value from v$parameter where name = 'db_block_size';

-- 分析数据文件的使用情况
prompt Analyzing data files for potential resizing...
select file_name,
       ceil((nvl(hwm,1)*&&blksize)/1024/1024) smallest,
       ceil(blocks*&&blksize/1024/1024) currsize,
       ceil(blocks*&&blksize/1024/1024) -
       ceil((nvl(hwm,1)*&&blksize)/1024/1024) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
order by savings desc;

-- 动态调整数据文件大小
prompt Resizing data files based on analysis...
BEGIN
  FOR rec IN (
    select file_name, smallest
    from (
      select file_name,
             ceil((nvl(hwm,1)*&&blksize)/1024/1024) smallest,
             ceil(blocks*&&blksize/1024/1024) currsize,
             ceil(blocks*&&blksize/1024/1024) -
             ceil((nvl(hwm,1)*&&blksize)/1024/1024) savings
      from dba_data_files a,
           ( select file_id, max(block_id+blocks-1) hwm
               from dba_extents
              group by file_id ) b
      where a.file_id = b.file_id(+)
      and ceil(blocks*&&blksize/1024/1024) >
          ceil((nvl(hwm,1)*&&blksize)/1024/1024)
      order by savings desc
    )
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE ''' || rec.file_name || ''' RESIZE ' || rec.smallest || 'M';
    dbms_output.put_line('Resized ' || rec.file_name || ' to ' || rec.smallest || 'M');
  END LOOP;
END;
/

3.2 Oracle表收缩操作（shrink）

以下是经过优化和整理的Oracle表收缩操作代码，包含启用行移动、回收空间、调整高水位线（HWM）、处理依赖对象以及收缩LOB列的操作

-- 1. 启用行移动（Enable Row Movement）

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

/*  启用行移动：允许行在表中移动，这是执行SHRINK SPACE操作的前提条件之一。默认情况下，行移动是禁用的，因为它可能导致额外的I/O操作   */

-- 2. 回收空间并调整高水位线（HWM）

ALTER TABLE scott.emp SHRINK SPACE;

/*    回收空间并调整HWM：重新组织表的数据段，回收未使用的空间，并将高水位线（HWM）下移。这会减少表的存储空间并提高性能。适用场景：当表中有大量删除或更新操作后，可以使用此命令来优化存储。*/

-- 3. 回收空间但不调整高水位线（HWM）

ALTER TABLE scott.emp SHRINK SPACE COMPACT;

/*    仅回收空间，不调整HWM：重新组织表的数据段，回收未使用的空间，但不调整高水位线（HWM）。
适用场景：适用于希望减少碎片但不想影响HWM的情况。 */


-- 4. 回收空间并处理所有依赖对象

ALTER TABLE scott.emp SHRINK SPACE CASCADE;

/*    回收空间并处理所有依赖对象：不仅收缩表本身，还会对所有依赖对象（如索引、LOB等）进行相应的收缩。适用场景：当表有多个依赖对象时，使用此命令可以一次性优化所有相关对象。    */

-- 5. 收缩LOB列

-- 单个LOB列的空间收缩
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);

-- LOB列及其依赖对象的空间收缩
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

/*    收缩LOB列：专门针对LOB（大对象）列进行空间回收，CASCADE选项会同时处理LOB段及其依赖对象。
适用场景：当LOB列占用大量空间且存在碎片时，可以使用这些命令来优化存储。    */


--  移动lob表空间
ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);