You can set shown below alter system set optimizer scope system altered

Chapter 8 ■ DynamiC Sampling anD CarDinality FeeDbaCk

130

Chapter 8 ■DynamiC Sampling anD CarDinality FeeDbaCk

3. If the optimizer finds that some statistics are missing, it may do some dynamic sampling. The amount, and even whether dynamic sampling is done, will depend on the value of optimizer_dynamic_sampling.

4. If no dynamic sampling is to be done the rest of the optimization process continues, and statistics are used where available.

Dynamic sampling is controlled by the value of optimizer_dynamic_sampling as already mentioned. It can be set at the system level or at the session level or by using hints in the SQL. These different options allow the behavior of dynamic sampling to be very carefully controlled and be set to behave in different ways for different SQL (with hints) and different sessions (log on triggers for example). Here are examples of all of these options. (I’ve set the value to 4. You can set any value from 0 to 10.) First set it at the session level.

SQL> alter session set optimizer_dynamic_sampling=4 ; Session altered.

131