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='*';