Optimizer parameters tuning

Optimizer parameters tuning:
Most of the optimizer parameter are set to a default value and these parameters effect most of the queries plan. If we set these parameters we can improve the SQL performance a lot.
• optimizer_index_cost_adj - This is an important CBO parameter because it adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more like that the CBO will use an available index.

• optimizer_index_caching - This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching effects the CBOs decision to use an index for a table join (nested loops), or to favor a full-table scan.