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>
