Monday, April 11, 2011

Index rebuild and archival procedure

I administer one of the production databases that supports retail ordering application. We have an archival procedure that archives old orders. Last weekend, I rebuilt all of the indexes of 5 tables against which the delete statements in the archival procedure are identified most expensive. The total size of those indexes are reduced by 460GB after rebuild. After the rebuild, the archival of 350K orders took 1h40min compared to 2h30min typically before rebuild. The reason for the performance improvement as far as I can see are as follows:

(1) Decreasing the number of gets in the index range scan operation due to indexes are more compressed
(2) Increasing the chances of caching,  thus reducing the need to wait for db file sequential read.
For example, let's say, index entry 1 is in block 1 and entry 2 is in block 2 before rebuild, so Oracle needs to read block1 and 2 to the buffer cache to delete them; while after rebuild both entry 1 and entry 2 could be in block 1, so Oracle needs to read block 1 once and can work on two entries.

Among those 5 tables, the top-most expensive delete is the one from a table called ORDER_TRANS. There is an index called ORDER_TRANS_IX4 of this table is 16GB after rebuild as compared to 150GB before rebuild. Why is the index so sparse? The index is on the LAST_MODIFIED_DATE column of the table. This is a typical right-hand index, i.e. new index key entries are added to the right hand size of the index tree. The key value is monotonically increasing, like a sequence-based PK. If assuming a leaf block of the index can hold 360 index entries, 99.9% blocks (hold  <=180 entries) are  >50% empty; 97.5% blocks (hold <=90 entries) are >75% empty. (see appendix) .The reasons that why especially right-hand index can grow bigger are described in a Jonathan Lewis's blog post. In my particular case, I believe the problems of the archival procedure itself contribute to the sparsity of the index greatly.

The candidate orders that are eligible for archiving is based on certain business logic. However I found that there are very old orders. e.g. competed in 2004, 2005, that are not selected out for archiving. I confirmed with a lead developer who called those orders "fallout" that those orders should be updated so that they could become eligible for archiving long time ago. In addition, the orders are not selected based on the increasing order of its completion date or cancel date. We can imagine that if we really archive old orders based on their completion or cancel date, i.e, the older the earlier to be archived, we won't have that sparse index in the first place, because left-hand size leaf block can become empty much sooner and easily and thus reused.

In summary, it appears that rebuild index improves our archival procedure performance; however, we may want to  look it in the other way around. If the old orders are really archived based on common sense, we probably don't need to rebuild index at all.


Appendix - How the leaf blocks of ORDER_TRANS_IX4 are used based on JL's script. ( Sample 5% blocks)

ROWS_PER_BLOCK     BLOCKS     ROW_CT CUMULATIVE_BLOCKS
-------------- ---------- ---------- -----------------
             1      16232      16232             16232
             2      14696      29392             30928
             3      13739      41217             44667
             4      13840      55360             58507
             5      13316      66580             71823
             6      13859      83154             85682
             7      13787      96509             99469
             8      13842     110736            113311
             9      13937     125433            127248
            10      14127     141270            141375
            11      14244     156684            155619
            12      14528     174336            170147
            13      14566     189358            184713
            14      15005     210070            199718
            15      15150     227250            214868
            16      15492     247872            230360
            17      15868     269756            246228
            18      16015     288270            262243
            19      16026     304494            278269
            20      16217     324340            294486
            21      16106     338226            310592
            22      16121     354662            326713
            23      16257     373911            342970
            24      15860     380640            358830
            25      15912     397800            374742
            26      15920     413920            390662
            27      15859     428193            406521
            28      15462     432936            421983
            29      15288     443352            437271
            30      15095     452850            452366
            31      15346     475726            467712
            32      15000     480000            482712
            33      14832     489456            497544
            34      14719     500446            512263
            35      14147     495145            526410
            36      13925     501300            540335
            37      13671     505827            554006
            38      13452     511176            567458
            39      13278     517842            580736
            40      13109     524360            593845
            41      13040     534640            606885
            42      12801     537642            619686
            43      12338     530534            632024
            44      11986     527384            644010
            45      11732     527940            655742
            46      11123     511658            666865
            47      11126     522922            677991
            48      10896     523008            688887
            49      10546     516754            699433
            50      10178     508900            709611
            51       9644     491844            719255
            52       9593     498836            728848
            53       9208     488024            738056
            54       9009     486486            747065
            55       8421     463155            755486
            56       8393     470008            763879
            57       7992     455544            771871
            58       7651     443758            779522
            59       7329     432411            786851
            60       6941     416460            793792
            61       6662     406382            800454
            62       6526     404612            806980
            63       6320     398160            813300
            64       5864     375296            819164
            65       5687     369655            824851
            66       5334     352044            830185
            67       5182     347194            835367
            68       4953     336804            840320
            69       4728     326232            845048
            70       4379     306530            849427
            71       4219     299549            853646
            72       4124     296928            857770
            73       3769     275137            861539
            74       3737     276538            865276
            75       3553     266475            868829
            76       3268     248368            872097
            77       3102     238854            875199
            78       3010     234780            878209
            79       2860     225940            881069
            80       2608     208640            883677
            81       2552     206712            886229
            82       2408     197456            888637
            83       2238     185754            890875
            84       2236     187824            893111
            85       1989     169065            895100
            86       1883     161938            896983
            87       1737     151119            898720
            88       1705     150040            900425
            89       1627     144803            902052
            90       1479     133110            903531
            91       1447     131677            904978
            92       1314     120888            906292
            93       1316     122388            907608
            94       1207     113458            908815
            95       1258     119510            910073
            96       1065     102240            911138
            97       1049     101753            912187
            98        974      95452            913161
            99        927      91773            914088
           100        919      91900            915007
           101        877      88577            915884
           102        828      84456            916712
           103        794      81782            917506
           104        666      69264            918172
           105        635      66675            918807
           106        617      65402            919424
           107        549      58743            919973
           108        458      49464            920431
           109        475      51775            920906
           110        292      32120            921198
           111        243      26973            921441
           112        244      27328            921685
           113        201      22713            921886
           114        219      24966            922105
           115        169      19435            922274
           116        150      17400            922424
           117        167      19539            922591
           118        177      20886            922768
           119        128      15232            922896
           120        135      16200            923031
           121        140      16940            923171
           122        134      16348            923305
           123        130      15990            923435
           124         99      12276            923534
           125        107      13375            923641
           126        104      13104            923745
           127         84      10668            923829
           128        106      13568            923935
           129         86      11094            924021
           130         68       8840            924089
           131         97      12707            924186
           132         71       9372            924257
           133         71       9443            924328
           134         61       8174            924389
           135         72       9720            924461
           136         82      11152            924543
           137         48       6576            924591
           138         72       9936            924663
           139         64       8896            924727
           140         58       8120            924785
           141         41       5781            924826
           142         41       5822            924867
           143         35       5005            924902
           144         45       6480            924947
           145         39       5655            924986
           146         37       5402            925023
           147         32       4704            925055
           148         39       5772            925094
           149         26       3874            925120
           150         25       3750            925145
           151         38       5738            925183
           152         33       5016            925216
           153         34       5202            925250
           154         36       5544            925286
           155         33       5115            925319
           156         29       4524            925348
           157         33       5181            925381
           158         25       3950            925406
           159         22       3498            925428
           160         34       5440            925462
           161         22       3542            925484
           162         21       3402            925505
           163         21       3423            925526
           164         22       3608            925548
           165         21       3465            925569
           166         25       4150            925594
           167         19       3173            925613
           168         17       2856            925630
           169         16       2704            925646
           170         15       2550            925661
           171         17       2907            925678
           172          9       1548            925687
           173          9       1557            925696
           174         17       2958            925713
           175         13       2275            925726
           176         16       2816            925742
           177         13       2301            925755
           178         14       2492            925769
           179         10       1790            925779
           180          7       1260            925786
           181          7       1267            925793
           182          6       1092            925799
           183          9       1647            925808
           184          7       1288            925815
           185          7       1295            925822
           186          9       1674            925831
           187          6       1122            925837
           188          8       1504            925845
           189          5        945            925850
           190          7       1330            925857
           191          5        955            925862
           192          9       1728            925871
           193          6       1158            925877
           194          5        970            925882
           195          8       1560            925890
           196          8       1568            925898
           197          9       1773            925907
           198          6       1188            925913
           199          5        995            925918
           200          3        600            925921
           201          2        402            925923
           202          6       1212            925929
           203          5       1015            925934
           204          5       1020            925939
           205          4        820            925943
           206          5       1030            925948
           207          3        621            925951
           209          1        209            925952
           210          5       1050            925957
           211          3        633            925960
           212          3        636            925963
           213          3        639            925966
           214          4        856            925970
           215          3        645            925973
           216          1        216            925974
           217          2        434            925976
           218          4        872            925980
           219          3        657            925983
           220          2        440            925985
           221          2        442            925987
           222          2        444            925989
           223          4        892            925993
           224          2        448            925995
           225          2        450            925997
           226          2        452            925999
           227          2        454            926001
           228          1        228            926002
           229          4        916            926006
           230          5       1150            926011
           231          2        462            926013
           232          3        696            926016
           233          2        466            926018
           234          1        234            926019
           235          5       1175            926024
           236          3        708            926027
           237          3        711            926030
           239          3        717            926033
           240          4        960            926037
           241          4        964            926041
           242          1        242            926042
           243          2        486            926044
           244          5       1220            926049
           245          3        735            926052
           246          2        492            926054
           248          1        248            926055
           249          1        249            926056
           250          2        500            926058
           252          4       1008            926062
           255          4       1020            926066
           256          4       1024            926070
           257          2        514            926072
           258          1        258            926073
           259          4       1036            926077
           261          1        261            926078
           262          2        524            926080
           263          1        263            926081
           264          2        528            926083
           265          1        265            926084
           266          2        532            926086
           267          4       1068            926090
           268          2        536            926092
           269          2        538            926094
           271          1        271            926095
           272          1        272            926096
           273          3        819            926099
           275          2        550            926101
           277          1        277            926102
           278          1        278            926103
           279          1        279            926104
           280          2        560            926106
           281          1        281            926107
           283          1        283            926108
           284          3        852            926111
           285          1        285            926112
           286          3        858            926115
           288          2        576            926117
           289          1        289            926118
           290          1        290            926119
           291          2        582            926121
           292          3        876            926124
           293          3        879            926127
           294          1        294            926128
           295          1        295            926129
           296          1        296            926130
           297          2        594            926132
           298          2        596            926134
           299          1        299            926135
           301          1        301            926136
           302          4       1208            926140
           305          1        305            926141
           306          1        306            926142
           308          1        308            926143
           309          3        927            926146
           310          1        310            926147
           313          1        313            926148
           314          1        314            926149
           315          2        630            926151
           317          1        317            926152
           318          2        636            926154
           319          1        319            926155
           320          2        640            926157
           321          2        642            926159
           322          3        966            926162
           323          2        646            926164
           324          2        648            926166
           325          1        325            926167
           326          1        326            926168
           327          2        654            926170
           328          3        984            926173
           329          3        987            926176
           330          3        990            926179
           331          2        662            926181
           332          2        664            926183
           333          2        666            926185
           334          2        668            926187
           335          2        670            926189
           336          1        336            926190
           337          2        674            926192
           338          3       1014            926195
           340          3       1020            926198
           341          3       1023            926201
           342          1        342            926202
           343          3       1029            926205
           344          6       2064            926211
           345          4       1380            926215
           346          2        692            926217
           347          2        694            926219
           348          4       1392            926223
           349          4       1396            926227
           350          5       1750            926232
           351          3       1053            926235
           352          4       1408            926239
           354          3       1062            926242
           355          5       1775            926247
           356          5       1780            926252
           357          4       1428            926256
           358          6       2148            926262
           359          2        718            926264
           360          6       2160            926270
           361          9       3249            926279
           362          6       2172            926285
           363          5       1815            926290
           364          6       2184            926296
           365          9       3285            926305
           366         11       4026            926316
           367         43      15781            926359
               ---------- ----------
sum                926359   32433921