问题背景

现场反馈业务表空间ICPSP最近增长较快，昨天巡检查询使用比率是92.9%，今天巡检查询使用比率是97.8%，让分析下业务表空间增长快的原因，平均每天大概以超128G的速度增长。

分析过程

查询表空间使用率

－－查询表空间使用率
SELECT tablespace_name as 表空间,round((sum_alloc - nvl(sum_free,0))/1024/1024,1) as 已用空间M,round(sum_max/1024/1024,1) as 总大小M,round(100*(sum_alloc - nvl(sum_free,0))/sum_max,1) As 使用百分比 FROM ( SELECT tablespace_name, sum(bytes) AS sum_alloc, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max FROM dba_data_files GROUP BY tablespace_name),( SELECT tablespace_name AS fs_ts_name, sum(bytes) AS sum_free FROM dba_free_space GROUP BY tablespace_name )WHERE tablespace_name = fs_ts_name(+) order by 使用百分比 desc;
复制

输出结果如下：

－－今天的
表空间	    　　已用空间M	　　总大小M	  使用百分比
ICPSP	    　　3843816	     3932065.6	97.8
UNDOTBS1	　　20507.7	     32768	    62.6
SYSAUX	    　7901.9	     98303	    8
SYSTEM	    　1474.2	     327671	    0.4
USERS	    　　54	         32768	    0.2
UNDOTBS2	　　1	        　 32768	    0
TOPICIS	   　 1	        　 32767	    0

－－昨天的
ICPSP	    　　－　　　　　　－　　　　　　92.9
复制

查看表空间增长情况

输出的天数和AWR报告保留天数有关，AWR报告保留多少天就输出多少天的表空间增长记录。

－－查询ａｗｒ报告保留时间
select * from dba_hist_wr_control;
复制

SELECT day,
       total_GB,
       used_GB,
       total_GB - used_GB free_GB,
       round(100 * used_GB  total_GB, 2) used_percent,
       case
         when (used_GB = used_GB - LAG(used_GB, 1, 0) OVER(ORDER BY day)) then
          null
         else
          used_GB - LAG(used_GB, 1, 0) OVER(ORDER BY day)
       end incr_GB
  from (select to_char(snap.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff') day,
               round(sum(tsu.TABLESPACE_SIZE * dt.BLOCK_SIZE) 
                     (1024 * 1024 * 1024),
                     2) total_GB,
               round(sum(tsu.TABLESPACE_USEDSIZE * dt.BLOCK_SIZE) 
                     (1024 * 1024 * 1024),
                     2) used_GB
          from DBA_HIST_TBSPC_SPACE_USAGE tsu,
               dba_hist_snapshot          snap,
               V$TABLESPACE               vt,
               dba_tablespaces            dt
         where tsu.SNAP_ID = snap.SNAP_ID
           and tsu.DBID = snap.DBID
           and snap.instance_number = 1
           and tsu.TABLESPACE_ID = vt.TS#
           and vt.NAME = dt.TABLESPACE_NAME
           and SUBSTR(to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),
                      12,
                      5) = '00:00'
         group by snap.END_INTERVAL_TIME
         order by snap.END_INTERVAL_TIME desc) a
 order by day desc
复制

找出具体在增长的表空间

select obj_name,
       begin_interval_time,
       used_gb,
       case
         when rownum = 2 then
          used_gb - used_gb2
         else
          null
       end yesterday_increase_gb
  from (select a.*,
               row_number() over(partition by obj_name order by begin_interval_time) row_num,
               lag(used_gb, 1, 0) over(order by obj_name, snap_id) used_gb2
          from (select s.snap_id,
                       ts.name obj_name,
                       to_char(snap.begin_interval_time,
                               'yyyy-mm-dd hh24:mi:ss') begin_interval_time,
                       round(S.TABLESPACE_USEDSIZE * 8  (1024 * 1024), 2) used_GB
                  from dba_hist_tbspc_space_usage s,
                       v$tablespace               ts,
                       dba_hist_snapshot          snap
                 where s.tablespace_id = ts.ts#
                   and snap.snap_id = s.snap_id
                   and snap.begin_interval_time between trunc(sysdate - 1) and
                       trunc(sysdate)
                   and SUBSTR(to_char(snap.begin_interval_time,
                                      'yyyy-mm-dd hh24:mi:ss,ff'),
                              12,
                              5) = '00:00') a) b
 where row_num = 2
 order by yesterday_increase_gb desc;
复制

指定表空间中使用率前10对象查询

－－表空间使用率前10对象查询
SELECT *
  FROM (SELECT BYTES  1024  1024 1024 GB, SEGMENT_NAME, SEGMENT_TYPE, OWNER
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = 'ICPSP'
         ORDER BY BYTES  1024  1024 1024 DESC)
 WHERE ROWNUM <= 10;
复制

查询lob对象和表的关系

--查询lob对象和表的关系
SELECT b.table_name,
a.segment_name,
b.index_name,
a.segment_type,
b.column_name,
a.tablespace_name,
b.chunk,
b.cache,
b.freepools,
b.pctversion,
b.retention
FROM dba_segments a,dba_lobs b
WHERE a.segment_name = b.segment_name
AND a.tablespace_name = 'ICPSP'  and a.SEGMENT_NAME in ('SYS_LOB0000093327C00006$$','SYS_LOB0000091343C00007$$','SYS_LOB0000090782C00003$$',
'SYS_LOB0000090785C00005$$','SYS_LOB0000090748C00003$$','SYS_LOB0000091020C00006$$','SYS_LOB0000090748C00007$$',
'SYS_LOB0000097157C00003$$','SYS_LOB0000090779C00003$$','SYS_LOB0000090540C00025$$');
复制

上面几个表均有大字段

分析对象

分别查询表中有多少条数据(可选)

由于业务高峰期，且表数据量大，未进行表中数据记录的查询。

select count(*) from PUB_CALOG;
复制

收集统计信息(可选)

由于业务高峰期，且表数据量大，未进行表统计信息的收集。

--收集统计信息：
analyze table LIST_20220415 compute statistics;
复制

查看实际占用空间大小

结合上面步骤中的“指定表空间中使用率前10对象查询”得出结果：表小，表中的大字段大。

--查看表实际占用空间大小：
select segment_name,partition_name,round(bytes/1024/1024,2) size_mb from dba_segments where segment_name in ('SIS_HTMLRULEDATUM','REG_BUSMAIINF_NETXMLDATA','REG_BUSMAIINF_XMLDATA','PUB_DEALUPLOADIMG','PUB_ANNOUNCELOG','LOG_EVENTRECORD','PUB_CALOG','PUB_BUSIDATA_CHANGE','PUB_BUSMAIINF');

select segment_name,partition_name,round(bytes/1024/1024,2) size_mb from dba_segments where segment_name  in ('SYS_LOB0000093327C00006$$','SYS_LOB0000091343C00007$$','SYS_LOB0000090782C00003$$',
'SYS_LOB0000090785C00005$$','SYS_LOB0000090748C00003$$','SYS_LOB0000091020C00006$$','SYS_LOB0000090748C00007$$',
'SYS_LOB0000097157C00003$$','SYS_LOB0000090779C00003$$','SYS_LOB0000090540C00025$$');
复制

查看实际占用多少块，是否有空块

select table_name,num_rows,blocks,empty_blocks from dba_tables where table_name in ('SIS_HTMLRULEDATUM','REG_BUSMAIINF_NETXMLDATA','REG_BUSMAIINF_XMLDATA','PUB_DEALUPLOADIMG','PUB_ANNOUNCELOG','LOG_EVENTRECORD','PUB_CALOG','PUB_BUSIDATA_CHANGE','PUB_BUSMAIINF');
复制

空块是0说明不是业务端向这张表中写入了大量数据，然后又执行了delete操作，删除了大量数据，导致高水位线的产生。

以PUB_CALOG表为例，实际上数据只使用了1195781个块，高水位线以下还有0个空块，所以才导致6700319行数据占用了(1195781+0)*8/1024=9342 MB,与dba_segments中查出来的值相同或接近。

解决办法

分别和开发、领导沟通并确认了 PUB_CALOG、LOG_EVENTRECORD 这2个表为日志表(该步骤非常重要)，向开发和领导建议了以下处理办法：

方法1：create table newtablename as select * from tablename;然后truncate table

最终现场同事和领导决定用数据泵进行备份后进行truncate table

--备份表
expdp icpspa/icpspa123 directory_name=IMPDP1_BACKUP dumpfile=tablefulldata_PUB_CALOG_LOG_EVENTRECORD_20240927.dmp logfile=expdp_tablefulldata_PUB_CALOG_LOG_EVENTRECORD_20240927.log   tables=icpspa.PUB_CALOG icpspa.LOG_EVENTRECORD exclude=STATISTICS PARALLEL=50 compression=all

--清理表数据
create table PUB_CALOG_NEW as select * from PUB_CALOG where 1=1;
insert into PUB_CALOG_NEW select * from PUB_CALOG where id >60000;
truncate table PUB_CALOG;
alter table PUB_CALOG_NEW rename to PUB_CALOG;

truncate table LOG_EVENTRECORD;
复制

注意：这里不使用delete操作，因为delete操作不会降低高水位和释放数据库。

参考链接：记一次对oracle数据库表空间异常增长的分析和处理 - 墨天轮 (modb.pro)