对一次drop分区引发性能问题的思考

2016-01-15 10:58:10

作者：付义，新炬网络高级技术专家。

某运营商**系统由于开发商运维人员使用drop分区进行历史数据清理引起了一次性能问题，庆幸的是操作时间是在晚上7点多，影响范围不大，持续时间80分钟。

运维人员的操作过程：

通过查询dba_tab_partitions确定需要清理的分区。

使用alter table *** drop partition ***;命令进行历史数据清理。

操作完成后，最终用户反馈该表涉及的查询模块效率变慢。经我们检查发现global index失效，随后我们做了重建索引以及收集统计信息操作，业务恢复正常。

事后，我们对该运维人员的目的进行了了解，他需要做临时需求，发现表空间不足，清理一个月的分区后发现空间释放未达到预期效果，又清理了一个月的分区。

现在我们模拟下当天发生的问题。

--生成测试表
create table fuyi_rpart (
    id number,
    titile varchar2(30),
    collect_dt date
)
partition by range (collect_dt) (
    partition p201506 values less than (to_date('20150701','yyyymmdd')),
    partition p201507 values less than (to_date('20150801','yyyymmdd')),
    partition p201508 values less than (to_date('20150901','yyyymmdd')),
    partition p201509 values less than (to_date('20151001','yyyymmdd')),
    partition p201510 values less than (to_date('20151101','yyyymmdd'))
);
--生成数据
begin
for i in 1..3000000 loop
insert into fuyi_rpart values(i,dbms_random.string('l',15),sysdate - dbms_random.value(1,150));
end loop;
end;
/
commit;
--创建global index
create index ind_rpart_id on fuyi_rpart2(id) nologging;

页面一般通过id进行查询数据，SQL如下：

select * from FUYI_RPART where id=:id and collect_id=:2

正确的执行计划：

在drop 分区前确认索引状态及大小：

SQL> select owner,index_name ,status from dba_indexes where index_name='IND_RPART_ID';
OWNER                      INDEX_NAME               STATUS
------------------------------ ------------------------------ --------
FY                        IND_RPART_ID             VALID    <<<索引有效
SQL> select owner,bytes/1024/1024 mb from dba_segments where segment_name='IND_RPART_ID' and segment_type='INDEX' AND owner='FY';
OWNER                             MB
------------------------------ ----------
FY                               72   <<< 索引大小72M

drop p201506分区：

alter table FUYI_RPART drop partition p201506;

检查索引状态及大小：

SQL> select owner,index_name ,status from dba_indexes where index_name='IND_RPART_ID';
OWNER                      INDEX_NAME               STATUS
------------------------------ ------------------------------ --------
FY                        IND_RPART_ID             UNUSABLE <<< 索引失效
SQL> select owner,bytes/1024/1024 mb from dba_segments where segment_name='IND_RPART_ID' and segment_type='INDEX' AND owner='FY';
OWNER                             MB
------------------------------ ----------
FY                               72    <<<索引大小未变化？

执行计划已经变为全表扫描：

至此，当日故障的简单模拟完成。通过模拟发现对存在global index的表进行drop partition会引起global index失效，可能导致大量全表扫描发生，最终引起整改数据库的性能下降。同时，我们发现drop partition后索引的大小未发生变化。那我们在drop partition同时进行update global indexes，索引大小是否发生变化呢？我们继续进行模拟测试。

重新生成测试表及数据的过程省略。

在drop 分区前确认索引状态及大小：
SQL> select owner,index_name ,status from dba_indexes where index_name='IND_RPART_ID';

OWNER                      INDEX_NAME               STATUS
------------------------------ ------------------------------ --------
FY                        IND_RPART_ID             VALID    <<<索引有效
SQL> select owner,bytes/1024/1024 mb from dba_segments where segment_name='IND_RPART_ID' and segment_type='INDEX' AND owner='FY';
OWNER                             MB
------------------------------ ----------
FY                               72   <<< 索引大小72M

drop partition p201507同时更新global indexes

SQL> alter table FUYI_RPART drop partition p201507 update global indexes;
Table altered.

删除分区后索引状态及大小：

truncate partition同时 update global indexes是否会发生变化呢？

SQL> select owner,index_name ,status from dba_indexes where index_name='IND_RPART_ID';

OWNER                      INDEX_NAME               STATUS
------------------------------ ------------------------------ --------
FY                        IND_RPART_ID             VALID    <<<索引有效
SQL> select owner,bytes/1024/1024 mb from dba_segments where segment_name='IND_RPART_ID' and segment_type='INDEX' AND owner='FY';

OWNER                             MB
------------------------------ ----------
FY                               72   <<< 索引大小前后未发生变化，仍未72M

初步判断update global indexes时候索引的大小并不会发生变化。让我们dump出索引块看下。

row#259[2113] flag: ---D--, lock: 2, len=18 <<<索引条目在状态位标示为D，已经删除
col 0; len 4; (4): c3 03 1c 24
col 1; len 10; (10): 00 01 55 68 01 00 11 38 00 28
row#260[2095] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 03 1c 25
col 1; len 10; (10): 00 01 55 6c 01 00 09 3b 00 9c
row#261[2077] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 03 1c 26
col 1; len 10; (10): 00 01 55 6c 01 00 09 3b 00 9d
row#262[2059] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 03 1c 27
col 1; len 10; (10): 00 01 55 6c 01 00 09 3b 00 9e
row#263[2023] flag: ---D--, lock: 2, len=18 <<<索引条目在状态位标示为D，已经删除
col 0; len 4; (4): c3 03 1c 29
col 1; len 10; (10): 00 01 55 68 01 00 11 38 00 29

由此可以确定，在update global indexes的时候仅更新了索引条目的状态，索引条目的数据并未减少，自然索引所占的空间不会发生变化。

那么如何才能让将索引使用的空间真正降低呢？

方法是对索引进行rebuild。
SQL> alter index IND_RPART_ID rebuild;
Index altered.
SQL> select owner,bytes/1024/1024 mb from dba_segments where segment_name='IND_RPART_ID' and segment_type='INDEX' AND owner='FY';
OWNER MB
------------------------------ ----------
FY 54 <<<rebuild后索引占用空间降低至54m

结论：

对分区表进行drop partition 或truncate partition 时，若该表存在global index则需要同时进行update global indexes操作，否则会出现global index失效，从而可能触发大量全表扫描，引起数据库整体性能下降。

使用update global indexes仅能保证全局索引有效，被清理数据对应的索引条目的标志位被更改为“D”，索引大小不会发生变化。进行此操作会产生大量IO，对性能存在一定影响，操作需要安排在非业务高峰期。若需要索引所占空间降低，需要进行rebuild操作。

需要通过drop partition 或truncate partition方式进行数据清理的表上尽量创建local index，数据的唯一性通过应用程序保证，而不是通过主键保证。

上一篇：Java项目构建新型利器——Gradle
下一篇：Latch Free、Library cache伪游标(pseudo cursor)之间的那些事