经验分享(2) 一次表空间不足引起的连锁反应

大大刺猬 • 2023-01-02 • 云技术社区 • 266 阅读

分享一个大概1年前的案例, 还挺经典的. 主要是涉及的知识点多.

以下内容并非真实环境的, 当时没有记录. 现在来大概模拟一下.

环境:

oracle 12.2 RAC

事发当天

业务人员反应前台报错: 无法新增XXXXX. 第一反应就是可能空间不够了.

查看表空间使用率,sql如下:

            SELECT a.tablespace_name,
                     TRUNC (tablespace_size * b.block_size / 1024 / 1024 )
                        "TOTAL(MB)",
                     TRUNC (used_space * b.block_size / 1024 / 1024 ) "USED(MB)",
                     TRUNC (
                        (TABLESPACE_SIZE - used_space) * b.block_size / 1024 / 1024 )
                        "FREE(MB)",
                     ROUND (USED_PERCENT , 2) "USED_PERCENT(%)"
                FROM DBA_TABLESPACE_USAGE_METRICS a, dba_tablespaces b
               WHERE a.tablespace_name = b.tablespace_name
            ORDER BY USED_PERCENT DESC;

都还有很多啊, 但是data表空间使用率很高(超过了95%), 但是还剩几百GB呢. 登录服务器查看日志(tail -100f $ORACLE_BASE/diag/rdbms/ddcw/ddcw/trace/alert*.log)报错大概如下:

那就是碎片的问题了. 虽然每天都巡检,但是没注意到碎片能有这么大. 表空间碎片这玩意一时半会讲不清, 查询表空间碎片语句如下(这样的SQL,网上很多的):

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 decode(a.num_rows, 
 0, 
 100, 
 (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
 b.seg_bytes_mb, 
 2)) * 100) > 30 
 order by b.seg_bytes_mb desc;

那咋办??

加表空间数据文件呗, 用的ASM, 还剩好几十T呢, 遗憾的是不行, 因为表空间数据文件加到上限了....

ORA-01686: max # files (1023) reached for the tablespace DATA

猜猜为什么是1023个呢, 提示:rowid

查看oracle官方文档发现. smallfile的数据文件限制为1022, 块数量限制为2的22次方(如果每个块为8KB的话, 那就是8KB*2^22=32GB). 我实际上是1023个32GB的数据文件, 也就是32T左右, 已经达到上限了.

1+1022=1023

那咋办呢?

第一反应是迁移表/表分区, 那迁哪张表呢? 也不知道啊.

查询表空间下的大表(segment).

select owner,segment_name,segment_type,tablespace_name,BYTES/1024/1024 size_mb  from dba_segments where tablespace_name='DATA' order by size_mb;

好家伙, 排名前面的基本上都是是历史表, 再查询gv$sql有没有会话使用该表

select * from gv$sql where SQL_TEXT like '%table_name%';

居然没有, 就建议开发的删除掉该表. 但是开发的说可能会使用.... 那就迁移表吧, 在线迁移还是锁表迁移? 在线迁移不锁表, 但是巨慢无比(迁1T左右大概20+小时), 锁表迁移好一点, 反正也没得人使用. 最终决定是:把那几张历史表导出来,再删...... 啊,这....

这个决定为后续埋下了一个坑. 上T的表导出很慢啊, 但是空间不够的问题依然存在啊, 由于大量用户使用的都是data表空间, 而data表空间已经没有可用空间了. 所以数据库宕机了.... 哦豁. (PS: 现在看来当时的做法欠妥, 应该优先恢复数据库使用, 所以应该先迁移一部分不常用的小表, 迁移小表是很快的. 业务恢复之后,再考虑那几张大表.)

等数据库起来之后, 业务也恢复正常了.

本来以为这样就结束了, 而且第二天还是周末啊.

第二天

既然你看到这里了, 那就说明真的有问题了.

用户反应, 前台很卡, 但是网络是没得问题的, 而且是周末, 用的人也不多啊. 为啥呢?

并没有新的程序发布, 也不是新的业务, 所以中间件的问排除了, 看数据库awr 发现有个sql执行时间非常长.

用恩墨的sql10.sql查看该sql的执行计划, 发现选择了一个错误的执行计划. 有个0.4秒的不走, 走那个几千秒的....

为啥呢? 查看该表的统计信息, 发现统计信息是3年前的.....

先解决问题把, 解决办法: 绑定执行计划 (绑定执行计划的脚本需要去MOS上下载)

现在来分析为什么统计信息会那么旧,oracle不会自动去收集统计信息吗? mysql都会啊, oracle肯定也会啊.

查询oracle收集统计信息的时间:

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

没毛病啊

周一到周五晚上10:00开始, 周末早上6点. 没毛病啊. 难道是表太大, 数据库太忙,没收集完统计信息旧到点了.

查看redo日志变化(能大概反应业务的情况), 然后化成图(用python画, matplotlib.pyplot 代码我就不放出来了, 有要的联系我就是)

SELECT TO_CHAR(first_time, 'MM/DD') DAY, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22, 
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23, 
COUNT(*) TOTAL 
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18 
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME) 
GROUP BY TO_CHAR(first_time, 'MM/DD') 
ORDER BY MIN(RN);

晚上比白天还忙....

然后问开发的晚上在干嘛? 他们说在跑JOB.

系统自动收集是很难了, 那就手动收集统计信息吧(早上5:00-7:00貌似闲一点,晚上22:00-0:00也闲)

统计常用的表, 然后写脚本自动收集统计信息.(我还专门写了个脚本自动收集统计信息呢)

收集统计信息可以用analyze 也可以调用 DBMS_STATS.GATHER_TABLE_STATS

我就用第二种了, 毕竟支持并行.

exec dbms_stats.gather_table_stats(ownname => 'DDCW',tabname => 'STUDENT',estimate_percent => 100,method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree  => 4,granularity => 'AUTO',cascade > TRUE);

也可以用脚本(有需要的可以联系我)

以前写的, 很久没用了....

后面再迁移了这个表空间的一些大表, 和开发的约定每个用户一个表空间, 自此这件事就算结束了.

回顾一下, 本来是表空间不足的, 但是又无法扩表空间了, 处理不及时, 导致数据库重启了, 数据库重启后, 执行计划旧重新生成了, 但是统计信息不准, 导致生成的执行计划有问题, 于是又导致第二天用户使用卡.....

为什么不用bigfile类型的表空间呢? 下一次分享下 bigfile类型的表空间的坑(也可以算上文件系统的坑吧).

坑踩多了也就没得那么多为什么了.

本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看本站版权声明

THE END