Dynamic Sampling in Oracle

OPTIMIZER_DYNAMIC_SAMPLING is an Oracle database initialization parameter that controls the level of dynamic sampling used by the query optimizer when generating execution plans. The query optimizer is responsible for generating the most efficient execution plan for a SQL query by choosing the best execution path based on available statistics, indexes, and other factors.

Dynamic sampling is a feature that allows the optimizer to gather additional statistics on tables or indexes at runtime if it determines that the existing statistics are insufficient for generating an optimal execution plan. The OPTIMIZER_DYNAMIC_SAMPLING parameter specifies the level of dynamic sampling to be used, with valid values ranging from 0 to 10.

 0: Disable dynamic sampling
 1: Enable basic dynamic sampling
 2: Enable moderate dynamic sampling (default)
 3-10: Enable higher levels of dynamic sampling

It's important to note that increasing the level of dynamic sampling can increase the amount of time required to generate execution plans, so it's generally recommended to use the default value unless you have a specific need for more aggressive sampling. Additionally, dynamic sampling can be disabled for individual queries using the NO_DYNAMIC_SAMPLING hint.
 
Disable dynamic sampling at session level:
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

Disable dynamic sampling in db level:
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0 scope=both sid='*';