问题背景
现场反馈业务表空间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操作不会降低高水位和释放数据库。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
