1. 目标表NTL,经过分析后,数据发生行迁移的比例为17%,表和索引的如下表:
----------------------------------------------------------------------------------------------------- | 原始 | 索引重建后 | ----------------------------------------------------------------------------------------------------- |INDEX_NAME | HEIGHT |FRAGMENT(%)| PCT_USAGE(%)| SIZE(MB)| Times_Waited |SIZE(MB) |Times_Waited | ---------------|--------|-----------|-------------|---------|---------------|---------|-------------| |NTL | - | - | - | 18728 | 20814 | 18728 | 26920 | |IX_LTSHID | 3 | 0 | 19 | 61 | 328 | 7 | 176 | |IX_RETIME | 4 | 0.58 | 21 | 2837 | 5763 | 602 | 2108 | |IX_UM | 4 | 0.01 | 28 | 7241 | 45932 | 1974 | 34258 | |PK_ID | 4 | 0 | 32 | 7676 | 3037 | 7676 | 105 | |IX_BATCHID | 4 | 0 | 22 | 10916 | 16960 | 2377 | 9202 | |IX_OLD_IFU | 4 | 0.01 | 18 | 16974 | 37448 | 1885 | 14083 | |IX_TFIRSTUM | 4 | 0 | 18 | 19525 | 50776 | 2189 | 21128 | |IX_ORGTYAPT | 4 | 0 | 11 | 23071 | 57488 | 2450 | 23486 | |IX_LTCHID | 4 | 0 | 6 | 26126 | 73220 | 1444 | 30379 | |IX_APPTIM_STA | 4 | 0.04 | 7 | 26902 | 72926 | 1757 | 31671 | |IXLASTCON | 4 | 0 | 6 | 34270 | 80713 | 1953 | 34278 | |IX_UPDT | 4 | 0 | 4 | 45950 | 8284 | 1757 | 2324 | -----------------------------------------------------------------------------------------------------
目前的问题是,NTL经常发送高并发操作(Insert和Update),导致行迁移比例居高不下,索引膨胀速度惊人。
导致该表上的DML操作性能变差。
2. 以下分三种情况来对上述问题进行优化:
(1)删除不需要索引
(2)在线重定义
(3)重建索引及删除不需要索引
---------------------------------------------------------------------------------------------------------------- | | 原始 |删除不需要索引 |提升率(%)| 在线重定义 | 提升率(%)| 重建索引及删除不需要索引| 提升率(%) | ----------|-----------|---------------|---------|------------|-----------|-------------------------|------------ |删除行数 | 100,000 | 100,000 | - | 100,000 | - | 100,000 | - | |时间(s) | 2957.31 | 2227.37 | 132.77% | 290.55 | 1017.84% | 215.63 | 1371.47%| |逻辑读 | 4927006 | 4138866 | 119.04% | 4470292 | 110.22% | 3992786 | 123.40%| |物理读 | 606339 | 480715 | 126.13% | 254844 | 237.93% | 230276 | 263.31%| |Redo | 333437162 | 286663424 | 116.32% | 324855634 | 102.64% | 284658102 | 117.14%| ----------------------------------------------------------------------------------------------------------------
可以看到,在线重定义和重建索引,实质完成了性能提升的目的。
因为在线重定义包含了重建索引和重建表两个动作,该两个动作,到底哪一个性能提升率更高呢?
我们接下来分析一下看看。
3. 以下分三种情况进行对比分析,以明确行链接清理后的性能提升(response time)点在哪里:
情况1:原始情况
情况2:重建原表的所有索引(主键索引除外)之后
情况3:行链接清理(即为“行链接清理”+“重建索引”)之后
PS:情况2可视为情况1到情况3的一个对比中间情况,使性能提升点的确定更为准确。
------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | cpu(s) | elapsed(s)| disk | query | current | rows | top wait event | times waited | total waited(s)| waited(%) | time promotion(%)| ------------------------------------|----------------|-----------------------------------------------------------|----------------|-----------|------------------- |原始 | 226.97 | 3474.61 | 686428 | 80408 | 4562752 | 100000 | db file sequential read | 473760 | 3084.05 | 88.76% | - | |重建索引后 | 111.67 | 352.78 | 230137 | 40 | 4354050 | 100000 | db file sequential read | 230137 | 284.03 | 80.51% | 984.92% | |行链接清理后 | 91.7 | 152.25 | 212854 | 445 | 4225435 | 100000 | db file sequential read | 212851 | 89.38 | 58.71% | 231.71% | ------------------------------------------------------------------------------------------------------------------------------------------------------------------
分析:
(1)因为respons time=CPU time+wait time,CPU time关联逻辑读和物理读,wait time关联等待事件,故从此两方面分析。
(2)比较三种情况,从逻辑读(query+current)来看,下降并不明显,对性能提升影响不大;
比较三种情况,从物理读(disk)来看,“重建索引后”较“原始”有明显的下降,“行链接清理后”较“重建索引后”稍有下降,性能有所提升;
比较三种情况,CPU时间下降趋势基本可以反映逻辑读和物理读的下降趋势,不足以体现response time的下降比例。
(3)比较三种情况,从wait event来看,top等待事件为“db file sequential read”,该等待事件的总时间占response time的88.76%,80.51%,58.71%,是主要影响因素。
比较三种情况,该等待事件总时间的下降趋势,可以很好的对应response time的下降趋势,说明性能提升点就在于优化db file sequential read等待时间。
(4)进一步对trace文件中记录的db file sequential read事件分析,发现绝大部分是对索引块的等待,详见索引分析结果表。
对索引的分析可以看出,碎片率虽然不高,但是索引高度和PCT_USAGE都不理想,索引相对表的大小也很不理想。
比较情况1和情况2,索引由于重建,大小明显下降,结构更为规整,致使db file sequential read等待次数和时间下降,time promotion为984.92%,说明“重建索引”带来的性能提升为984.92%;
比较情况2和情况3,在情况2的基础上,time promotion为231.71%,说明“行链接清理”带来的性能提升为231.71%。
4. 总结
(1)以上案例其实是索引膨胀和行迁移两种问题共同作用的结果,必须明确哪个是根本原因,真相只有一个。
(2)分析表明索引的影响占了九成,行迁移只占了一成,故要解决索引的问题,重建即可。
(3)高并发操作对索引的膨胀影响很大,会导致索引问题,高并发的索引需要定期重建。
(4)行迁移只会影响索引扫描,对全表扫描没有影响,且修复行迁移风险成本较大,可以不做,尽量不要做。
no comment untill now