Index Cost and Caching

There are a couple of optimiser parameters which are often used to make a database, session or query more OLTP, or more batch, in nature:

1. optimizer_index_caching - this parameter tells the optimiser what percentage of index blocks might be expected to be in the SGA (memory). So a higher value (0 to 100) would make Oracle more inclined to use an index access path over a full scan, and consequently this parameter is frequently raised above its default of 0 in OLTP databases.
2. optimizer_index_cost_adj - can be used to adjust the cost of index access paths to make them appear cheaper or more expensive. This parameter takes values between 1 and 10000 with a default of 100, so the parameter might be lowered in an OLTP environment to favour index access paths, and raised in data warehouse/batch environments to make index paths appear less favourable.

Batch Like

Consequently the hint below might be used in a large "batch like" query being run in an OLTP database, to inform the optimiser that it should be treated as a "large" or batch type query:

          /*+ OPT_PARAM('optimizer_index_cost_adj', 10000) OPT_PARAM('optimizer_index_caching', 0) */

When running ad hoc queries, the following can be used to change your current session (all subsequent session queries) to be more "batch like":

          ALTER SESSION SET "optimizer_index_cost_adj" = 10000;

          ALTER SESSION SET "optimizer_index_caching" = 0;

OLTP Like

Similarly the hint below might be used in an "OLTP like" query being run in a batch/warehouse database, to inform the optimiser that it should be treated as far as possible as an OLTP type query:

          /*+ OPT_PARAM('optimizer_index_cost_adj', 0) OPT_PARAM('optimizer_index_caching', 100) */

When running ad hoc queries, the following can be used to change your current session (all subsequent session queries) to be more "OLTP like":

          ALTER SESSION SET "optimizer_index_cost_adj" = 0;

          ALTER SESSION SET "optimizer_index_caching" = 100;