Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:
Rule Based Optimizer (RBO) – This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
Cost Based Optimizer (CBO) – This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
An oracle CBO will have a knock on effect if an oracle init parameter optimizer_index_cost_adj is set to a wrong value. I came across this issue while working with a media client using SAP CRM/BW applications on top of oracle database layer. The total database size was in excess of 4 tera Bytes.
I have picked up a worst performing SQL for analysis here. A view “”VBAP_VAPMA” is based on VBAP and VAPMA tables, VBAP listed in top wait segments consistently. I could see optimizer_index_cost_adj is favouring index scans even if they are...
To view and download this full PLR article, you must be logged in. Registration is completely free. Once you create your account, you will be able to browse, search & downlod from our PLR articles database of over "1,57,897+" on 1,000's of niches and 200+ categories without paying a penny. Click here to signup...
