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)行迁移只会影响索引扫描,对全表扫描没有影响,且修复行迁移风险成本较大,可以不做,尽量不要做。

Trackback

no comment untill now

Add your comment now

切换到手机版