Optimizer Hints¶
We have already mentioned the GATHER_PLAN_STATISTICS
hint, which can be used to obtain statistics about the execution plan during the execution of a statement.
It is especially helpful when you intend to diagnose performance issues with a particular statement.
It is definitely not meant to be used in production instances!
There is also a GATHER_OPTIMIZER_STATISTICS
, which Oracle lists under ‘Other hints’.
It can be used to collect bulk-load statistics for CTAS statements and INSERT INTO ... SELECT
statements that use a direct-path insert, which is accomplished with the APPEND
hint, but more on that later.
The opposite, NO_GATHER_OPTIMIZER_STATISTICS
is also provided.
The OPTIMIZER_FEATURES_ENABLE
hint can be used to temporarily disable certain (newer) optimizer feature after database upgrades.
This hint is typically employed as a short-term solution when a small subset of queries performs badly.
Valid parameter values are listed in the official documentation.