Thursday, November 17, 2022

Rebuild index candidates

Recent reminder on Oracle on line course "Understanding Explain Plans & Index Utilization" by Dan Hotka and problems that I faced in real professional life, reminded me to write some thoughts about index and usage in Oracle databases.
As we all known, some developers/DBAs have bad habit to "tune" SQL's by rebuilding indexes just because someone said it is good to have rebuild indexes. But how to know when I need to rebuild index?
First things first: Do not rebuild Oracle indexes! … Unless you have to. If you are even considering rebuilding indexes on an autonomous manner, please stop now, and first spend some time reading some of the many things Richard Foote has to say on his well-recommended blog and what is my proposal for such situations.
This is mainly as online rebuild use a lot of resources and it is far away from "normal" DDL operation in database....so run it always with some precautions and test on NON prod databases.

The solution

My solution against some others exposed all around Internet is that it expose parameters which can cut interesting parts to focus. Also it return all needed information like index size and estimated savings.
Script is totally aligned on database statistics, so bad index stat -> bad script results.
My 32kB size script named reb_ind_cand.sql is posted here.
You have to run script with 4 parameters:
sta reb_ind_cand [OWNER] (INDEX_NAME) [I/IP/ALL] [PERC_EST_SAVING]
where parameters in [] are fixed and parameter in () is used with like parameter. Script result looks like:
SQL> @reb_ind_cand.sql XXXXX % ALL 5

REPORT_DATE
-------------------
2022-11-17T06-38-04

ORACLE_VERSION
--------------
            19
PDB: SOME_DB

OWNER.INDEX_NAME                                    SAVING %         CURRENT SIZE SAV. STAT%  CURRENT SIZE (STAT) ESTIMATED SIZE (STAT)
------------------------------------------------- ---------- -------------------- ---------- -------------------- ---------------------
XXXXX.AH_AUDIT_IX2                                     0.0 %               2.0 MB    -41.4 %               1.4 MB                2.0 MB
XXXXX.DWH_WAIT_AH_LOCALDATE_IX2                       22.7 %              22.0 MB     16.4 %              20.3 MB               17.0 MB
XXXXX.DWH_WAIT_AH_NAME_IX1                            38.9 %              72.0 MB     32.2 %              64.9 MB               44.0 MB
XXXXX.NK_AHG_NAME                                     94.1 %              34.0 MB     50.3 %               4.0 MB                2.0 MB
XXXXX.NK_AH_ALIAS                                      6.1 %           6,271.8 MB      4.9 %           6,188.3 MB            5,888.0 MB
XXXXX.NK_AH_ARCHIVE                                   61.2 %           9,562.4 MB     57.8 %           8,802.5 MB            3,712.0 MB
XXXXX.NK_AH_ARCHIVE1                                  55.5 %           5,901.8 MB     50.0 %           5,251.9 MB            2,624.0 MB
XXXXX.NK_AH_ARCHIVE2                                  52.3 %           5,497.6 MB     47.7 %           5,019.9 MB            2,624.0 MB
XXXXX.NK_AH_ATYP                                       0.8 %           5,936.4 MB     -1.8 %           5,782.4 MB            5,888.0 MB
XXXXX.NK_AH_CMTFLAG                                    5.9 %           2,787.2 MB      2.1 %           2,681.3 MB            2,624.0 MB
XXXXX.NK_AH_DATE1                                      7.0 %           4,130.3 MB      1.6 %           3,901.5 MB            3,840.0 MB
XXXXX.NK_AH_DATE2                                      6.9 %           4,123.0 MB      3.8 %           3,992.5 MB            3,840.0 MB
XXXXX.NK_AH_DATE4_1                                   63.2 %           8,700.9 MB     58.9 %           7,786.3 MB            3,200.0 MB
XXXXX.NK_AH_DATE4_2                                   61.7 %           8,348.9 MB     60.6 %           8,131.3 MB            3,200.0 MB
XXXXX.NK_AH_DELFLAG                                   59.1 %           6,103.3 MB     54.3 %           5,459.6 MB            2,496.0 MB
XXXXX.NK_AH_EXTCOND                                    2.0 %           7,901.2 MB     -0.7 %           7,690.0 MB            7,744.0 MB
XXXXX.NK_AH_IDNR_BEARB                                 2.4 %           5,376.0 MB     -0.8 %           5,208.5 MB            5,248.0 MB
XXXXX.NK_AH_NAME                                       4.0 %           8,463.9 MB      3.1 %           8,392.1 MB            8,128.0 MB
XXXXX.NK_AH_NAME2                                      2.3 %           8,315.5 MB     -4.9 %           7,748.4 MB            8,128.0 MB
XXXXX.NK_AH_NAME3                                      0.0 %           8,899.0 MB     -0.9 %           8,812.5 MB            8,896.0 MB
XXXXX.NK_AH_OH_IDNR                                    8.5 %           3,077.8 MB      3.7 %           2,923.5 MB            2,816.0 MB
XXXXX.NK_AH_PARENTACT                                 12.4 %           2,994.3 MB     10.3 %           2,926.5 MB            2,624.0 MB
XXXXX.NK_AH_PARENTHIR                                 50.7 %           5,586.9 MB     46.2 %           5,114.0 MB            2,752.0 MB
XXXXX.NK_AH_PARENTPRC                                 56.1 %           5,541.7 MB     50.4 %           4,903.8 MB            2,432.0 MB
XXXXX.NK_AH_QUEUE                                      3.8 %           5,120.0 MB      0.6 %           4,958.9 MB            4,928.0 MB
XXXXX.NK_AH_REFNR                                     62.2 %           6,089.9 MB     61.1 %           5,930.0 MB            2,304.0 MB
XXXXX.NK_AH_REORG                                      2.0 %           5,486.8 MB     -0.9 %           5,328.0 MB            5,376.0 MB
XXXXX.NK_AH_RUN_EVNTID                                50.0 %           7,169.4 MB     43.6 %           6,353.5 MB            3,584.0 MB
XXXXX.NK_AH_TIMESTAMP4                                 6.9 %           3,504.9 MB      3.8 %           3,394.3 MB            3,264.0 MB
XXXXX.NK_AH_TOPNR                                     59.9 %           5,742.4 MB     58.3 %           5,526.3 MB            2,304.0 MB
XXXXX.NK_AH_USR                                       51.2 %           6,298.5 MB     45.8 %           5,663.3 MB            3,072.0 MB
XXXXX.NK_AJPP_OBJECT                                  46.1 %           5,940.6 MB     45.5 %           5,876.9 MB            3,200.0 MB
XXXXX.NK_AJPP_REFNR                                   52.5 %           2,691.9 MB     50.8 %           2,602.3 MB            1,280.0 MB
XXXXX.NK_AJPP_TASKIDNR                                60.8 %           3,264.3 MB     56.9 %           2,966.9 MB            1,280.0 MB
XXXXX.NK_EH_CHILDNR                                   90.9 %              33.0 MB     89.8 %              29.5 MB                3.0 MB
XXXXX.NK_EH_EVENTID                                   97.7 %              88.0 MB     97.3 %              73.4 MB                2.0 MB
XXXXX.NK_EH_HOST                                      81.8 %              11.0 MB     72.3 %               7.2 MB                2.0 MB
XXXXX.NK_EH_NAME                                      66.7 %              24.0 MB     52.6 %              16.9 MB                8.0 MB
XXXXX.NK_EH_NEXTCHECK                                 92.9 %              28.0 MB     90.9 %              22.1 MB                2.0 MB
XXXXX.NK_EH_OH_IDNR                                   78.6 %              14.0 MB     76.2 %              12.6 MB                3.0 MB
XXXXX.NK_EH_OTYPE                                     96.9 %              64.0 MB     96.5 %              56.4 MB                2.0 MB
XXXXX.NK_EH_PARENTACT                                 94.6 %              37.0 MB     93.6 %              31.2 MB                2.0 MB
XXXXX.NK_EH_PARENTHIR                                 92.9 %              28.0 MB     91.7 %              24.1 MB                2.0 MB
XXXXX.NK_EH_PARENTPRC                                 92.9 %              28.0 MB     92.4 %              26.4 MB                2.0 MB
XXXXX.NK_EH_QUEUE                                     88.5 %              26.0 MB     87.4 %              23.9 MB                3.0 MB
XXXXX.NK_EH_REFNR                                     50.0 %               4.0 MB     26.6 %               2.7 MB                2.0 MB
XXXXX.NK_EH_STARTTYPE                                 81.8 %              11.0 MB     77.4 %               8.8 MB                2.0 MB
XXXXX.NK_EH_STATUS                                    77.8 %               9.0 MB     70.5 %               6.8 MB                2.0 MB
XXXXX.NK_EH_TOPNR                                     87.5 %              16.0 MB     85.7 %              13.9 MB                2.0 MB
XXXXX.NK_EJPP_OBJECT                                  86.2 %              29.0 MB     85.5 %              27.6 MB                4.0 MB
XXXXX.NK_EJPP_OTYPE                                   96.5 %              57.0 MB     96.3 %              53.5 MB                2.0 MB
XXXXX.NK_EJPP_REFNR                                   33.3 %               3.0 MB      8.2 %               2.2 MB                2.0 MB
XXXXX.NK_EJPP_STATUS                                  66.7 %               6.0 MB     56.2 %               4.6 MB                2.0 MB
XXXXX.NK_EJPP_TASKIDNR                                95.8 %              48.0 MB     95.7 %              46.7 MB                2.0 MB
XXXXX.NK_EJPP_TASKIDNR2                               92.6 %              27.0 MB     91.7 %              24.1 MB                2.0 MB
XXXXX.NK_EJPP_TIMEOK                                  50.0 %               4.0 MB     21.7 %               2.6 MB                2.0 MB
XXXXX.NK_EY_SYNCNAME                                  88.2 %              17.0 MB     87.2 %              15.7 MB                2.0 MB
XXXXX.NK_EY_SYNCNAME2                                 92.3 %              26.0 MB     91.7 %              24.1 MB                2.0 MB
XXXXX.NK_FE_FH_IDNR                                   12.5 %               8.0 MB      2.3 %               7.2 MB                7.0 MB
XXXXX.NK_JPP_ALIAS                                    40.0 %               5.0 MB     29.7 %               4.3 MB                3.0 MB
XXXXX.NK_JPP_OBJECT                                   40.0 %              10.0 MB     31.6 %               8.8 MB                6.0 MB
XXXXX.NK_JPP_PARENTALIAS                            -275.0 %               4.0 MB   -401.3 %               3.0 MB               15.0 MB
XXXXX.NK_MELD_LOEKZ                                   89.3 %           4,354.1 MB     88.9 %           4,198.0 MB              464.0 MB
XXXXX.NK_MELD_REORG                                   77.0 %           3,264.6 MB     75.1 %           3,022.0 MB              752.0 MB
XXXXX.NK_MELD_SOURCE                                  47.3 %           1,093.7 MB     36.3 %             904.1 MB              576.0 MB
XXXXX.NK_MELD_TIMESTAMP                               78.9 %           3,265.2 MB     76.8 %           2,970.4 MB              688.0 MB
XXXXX.NK_MELD_USR                                     44.0 %           1,157.1 MB     34.0 %             981.8 MB              648.0 MB
XXXXX.NK_OFS_F                                        20.0 %               5.0 MB      4.1 %               4.2 MB                4.0 MB
XXXXX.NK_OFS_MODDATE                                  14.3 %               7.0 MB      0.9 %               6.1 MB                6.0 MB
XXXXX.NK_OFS_O                                        20.0 %               5.0 MB     -3.4 %               3.9 MB                4.0 MB
XXXXX.NK_OH_CRDATE                                    25.0 %               8.0 MB     17.4 %               7.3 MB                6.0 MB
XXXXX.NK_OH_DELFLAG                                   33.3 %              12.0 MB     30.5 %              11.5 MB                8.0 MB
XXXXX.NK_OH_MODDATE                                   76.7 %              30.0 MB     76.0 %              29.2 MB                7.0 MB
XXXXX.NK_OH_MODDATE2                                  87.8 %              49.0 MB     87.6 %              48.3 MB                6.0 MB
XXXXX.NK_OH_MRTEXECUTE                                14.3 %               7.0 MB      0.3 %               6.0 MB                6.0 MB
XXXXX.NK_OH_OPENSESS                                   0.0 %               6.0 MB    -19.6 %               5.0 MB                6.0 MB
XXXXX.NK_OH_OTYPE                                     24.0 %              25.0 MB     21.5 %              24.2 MB               19.0 MB
XXXXX.NK_OH_OTYPE2                                     0.0 %               8.0 MB     -4.1 %               7.7 MB                8.0 MB
XXXXX.NK_OH_REFIDNR                                   70.8 %              24.0 MB     42.3 %              12.1 MB                7.0 MB
XXXXX.NK_OH_REORG                                     72.2 %              36.0 MB     71.4 %              34.9 MB               10.0 MB
XXXXX.NK_OH_SEARCH                                    26.9 %              26.0 MB     24.5 %              25.2 MB               19.0 MB
XXXXX.NK_OH_SEARCH2                                   28.6 %              21.0 MB     25.0 %              20.0 MB               15.0 MB
XXXXX.NK_OH_SEARCH3                                   82.8 %              58.0 MB     82.2 %              56.0 MB               10.0 MB
XXXXX.NK_OH_VERSIONINGID                            -200.0 %               2.0 MB   -333.9 %               1.4 MB                6.0 MB
XXXXX.NK_OT_SEARCH_NC                               -130.0 %              80.0 MB   -154.6 %              72.3 MB              184.0 MB
XXXXX.NK_OVW_VVALUE                                   77.8 %             864.0 MB     43.7 %             340.9 MB              192.0 MB
XXXXX.NK_OY_SYNCNAME                                  50.0 %               4.0 MB     37.6 %               3.2 MB                2.0 MB
XXXXX.NK_RH_AH_IDNR                                   75.9 %           4,775.0 MB     75.5 %           4,700.4 MB            1,152.0 MB
XXXXX.NK_RH_ARCHIVE1                                  66.8 %           4,242.6 MB     62.7 %           3,773.6 MB            1,408.0 MB
XXXXX.NK_RH_ARCHIVE2                                  73.0 %           5,924.0 MB     72.2 %           5,758.2 MB            1,600.0 MB
XXXXX.NK_RH_DELFLAG                                   68.6 %           4,079.4 MB     64.3 %           3,585.2 MB            1,280.0 MB
XXXXX.NK_RH_TIMESTAMP4                                76.5 %           4,912.4 MB     76.0 %           4,809.4 MB            1,152.0 MB
XXXXX.NK_TEMP1_KEY1                                    0.0 %               0.0 MB      0.0 %               0.0 MB                0.0 MB
XXXXX.NK_TEMP1_KEY3                                    0.0 %               0.0 MB      0.0 %               0.0 MB                0.0 MB
XXXXX.NK_XAO_GETCHLOG                                 44.4 %              72.0 MB     43.2 %              70.5 MB               40.0 MB
XXXXX.NK_XAO_REORG                                    79.8 %             104.0 MB     77.1 %              91.8 MB               21.0 MB
XXXXX.PK_ABLOB                                        69.0 %           1,008.0 MB     46.3 %             581.4 MB              312.0 MB
XXXXX.PK_ACV                                          25.0 %              44.0 MB     14.8 %              38.8 MB               33.0 MB
XXXXX.PK_AFC                                          94.3 %              88.0 MB     66.5 %              14.9 MB                5.0 MB
XXXXX.PK_AH                                           55.4 %           5,164.0 MB     52.2 %           4,816.9 MB            2,304.0 MB
XXXXX.PK_AHG                                          95.1 %              41.0 MB     45.3 %               3.7 MB                2.0 MB
XXXXX.PK_AHGH                                         95.7 %              46.0 MB     51.2 %               4.1 MB                2.0 MB
XXXXX.PK_AH_HIST_10_AH_IDNR                           10.0 %           1,280.0 MB     -1.2 %           1,138.6 MB            1,152.0 MB
XXXXX.PK_AH_HIST_1_AH_IDNR                            11.4 %             280.0 MB      5.7 %             262.9 MB              248.0 MB
XXXXX.PK_AH_HIST_50_AH_IDNR                           32.5 %              80.0 MB     29.0 %              76.0 MB               54.0 MB
XXXXX.PK_AH_HIST_900_AH_IDNR                          25.0 %               4.0 MB     11.5 %               3.4 MB                3.0 MB
XXXXX.PK_AH_HIST_ADX_AH_IDNR                           7.5 %             320.0 MB      3.2 %             305.7 MB              296.0 MB
XXXXX.PK_AH_HIST_MBBUP_AH_IDNR                       -14.3 %               7.0 MB    -36.9 %               5.8 MB                8.0 MB
XXXXX.PK_AH_HIST_MB_AH_IDNR                           28.6 %             112.0 MB     17.8 %              97.3 MB               80.0 MB
XXXXX.PK_AJPFV                                        44.1 %             272.0 MB     34.0 %             230.2 MB              152.0 MB
XXXXX.PK_AJPOV                                        73.1 %           4,283.1 MB     45.3 %           2,106.3 MB            1,152.0 MB
XXXXX.PK_AJPP                                         52.4 %           2,690.9 MB     51.2 %           2,623.2 MB            1,280.0 MB
XXXXX.PK_AJPPA                                        52.4 %           2,823.0 MB     49.5 %           2,661.0 MB            1,344.0 MB
XXXXX.PK_AJPPC                                        39.1 %              46.0 MB     36.7 %              44.2 MB               28.0 MB
XXXXX.PK_AJPPF                                        56.7 %             240.0 MB     52.4 %             218.6 MB              104.0 MB
XXXXX.PK_AJPPO                                        72.0 %           3,885.4 MB     40.0 %           1,812.6 MB            1,088.0 MB
XXXXX.PK_AJPPV                                        85.0 %           3,781.4 MB     42.3 %             983.9 MB              568.0 MB
XXXXX.PK_APD                                          60.0 %              10.0 MB     51.5 %               8.3 MB                4.0 MB
XXXXX.PK_APPF                                         90.0 %              50.0 MB     58.0 %              11.9 MB                5.0 MB
XXXXX.PK_APUD                                         76.5 %           4,365.8 MB     45.4 %           1,874.5 MB            1,024.0 MB
XXXXX.PK_AV                                            0.3 %           8,988.3 MB     -3.1 %           8,688.9 MB            8,960.0 MB
XXXXX.PK_BH                                           27.3 %             264.0 MB     20.5 %             241.6 MB              192.0 MB
XXXXX.PK_BT                                           36.8 %             304.0 MB     30.4 %             275.7 MB              192.0 MB
XXXXX.PK_DWH_WAIT                                     10.5 %              19.0 MB      4.7 %              17.8 MB               17.0 MB
XXXXX.PK_EH                                           98.2 %             112.0 MB     97.9 %              93.3 MB                2.0 MB
XXXXX.PK_EJ                                           97.2 %              36.0 MB     96.5 %              28.6 MB                1.0 MB
XXXXX.PK_EJPOV                                        90.0 %              40.0 MB     67.0 %              12.1 MB                4.0 MB
XXXXX.PK_EJPP                                         92.3 %              26.0 MB     91.4 %              23.2 MB                2.0 MB
XXXXX.PK_EJPPA                                        88.9 %              27.0 MB     87.7 %              24.3 MB                3.0 MB
XXXXX.PK_EJPPO                                        88.6 %              35.0 MB     68.4 %              12.7 MB                4.0 MB
XXXXX.PK_EJPPV                                        89.5 %              38.0 MB     60.3 %              10.1 MB                4.0 MB
XXXXX.PK_EPUD                                         94.4 %              72.0 MB     87.8 %              32.9 MB                4.0 MB
XXXXX.PK_ESTP                                         95.2 %              21.0 MB     93.9 %              16.4 MB                1.0 MB
XXXXX.PK_EV                                           93.8 %             192.0 MB     92.7 %             165.2 MB               12.0 MB
XXXXX.PK_EY                                           94.1 %              17.0 MB     93.8 %              16.2 MB                1.0 MB
XXXXX.PK_FE                                           12.5 %               8.0 MB     -3.2 %               6.8 MB                7.0 MB
XXXXX.PK_FIFO                                         50.0 %               2.0 MB      4.5 %               1.0 MB                1.0 MB
XXXXX.PK_JPOV                                         42.9 %               7.0 MB     28.7 %               5.6 MB                4.0 MB
XXXXX.PK_JPP                                          25.0 %               4.0 MB     13.3 %               3.5 MB                3.0 MB
XXXXX.PK_JPPA                                         20.0 %               5.0 MB      3.2 %               4.1 MB                4.0 MB
XXXXX.PK_JPPO                                         42.9 %               7.0 MB     22.0 %               5.1 MB                4.0 MB
XXXXX.PK_JPPV                                         40.0 %               5.0 MB     16.3 %               3.6 MB                3.0 MB
XXXXX.PK_MELD                                         87.9 %           3,845.5 MB     87.4 %           3,675.7 MB              464.0 MB
XXXXX.PK_MQMEM                                        97.7 %              44.0 MB     82.9 %               5.8 MB                1.0 MB
XXXXX.PK_MSGTX                                         0.0 %               2.0 MB    -82.9 %               1.1 MB                2.0 MB
XXXXX.PK_ODOC                                         50.0 %               2.0 MB     26.0 %               1.4 MB                1.0 MB
XXXXX.PK_OFS                                           0.0 %               4.0 MB    -27.4 %               3.1 MB                4.0 MB
XXXXX.PK_OH                                            0.0 %               6.0 MB     -4.5 %               5.7 MB                6.0 MB
XXXXX.PK_OKD                                          33.3 %               9.0 MB     24.1 %               7.9 MB                6.0 MB
XXXXX.PK_OT                                           11.1 %              27.0 MB      5.4 %              25.4 MB               24.0 MB
XXXXX.PK_OVD                                           0.0 %               2.0 MB    -41.4 %               1.4 MB                2.0 MB
XXXXX.PK_OVW                                          69.3 %             600.0 MB     33.3 %             275.7 MB              184.0 MB
XXXXX.PK_OX                                           25.0 %               8.0 MB     13.5 %               6.9 MB                6.0 MB
XXXXX.PK_OY                                           50.0 %               2.0 MB      5.9 %               1.1 MB                1.0 MB
XXXXX.PK_RH                                           77.2 %           4,352.6 MB     76.8 %           4,275.4 MB              992.0 MB
XXXXX.PK_RT                                           -4.6 %          11,200.0 MB     -7.3 %          10,914.4 MB           11,712.0 MB
XXXXX.PK_XAO                                          77.5 %              80.0 MB     76.2 %              75.5 MB               18.0 MB
XXXXX.UK_OH_NAME                                      28.6 %              21.0 MB     25.5 %              20.1 MB               15.0 MB
XXXXX.UK_OKB_KEY                                      50.0 %               2.0 MB     30.1 %               1.4 MB                1.0 MB
XXXXX.UK_RH_TYP                                       66.5 %           3,625.2 MB     64.0 %           3,374.8 MB            1,216.0 MB

Elapsed: 00:00:21.56

reb_ind_cand [OWNER] (INDEX_NAME) [I/IP/ALL] [PERC_EST_SAVING]

reb_ind_cand HR % ALL 10

SQL>

The Final

Hope this helps someone, especially young and non experienced developers/DBA. Also for all of them might be interested to read my previous blog post from 2010.

Cheers!

No comments:

Post a Comment