Use Oracle dynamic sampling to improve database optimizer performance

To help an optimizer gather statistics more effectively, Oracle dynamic sampling comes to the rescue, according to database expert Matthew Morris.

Oracle dynamic sampling can improve the database optimizer's decisions. It's been around since Oracle 9i Release 2, but, despite that, the intent of dynamic sampling is still often misunderstood.

The Oracle optimizer makes decisions based on the statistics stored in the data dictionary for the objects referenced by a given query. Without valid statistics, the optimizer often makes poor choices when picking an execution plan. One of the primary functions of Oracle dynamic sampling is to provide a means for the optimizer to handle this situation.

The fact that this feature can dynamically gather statistics has led many to the mistaken impression that dynamic sampling eliminates the need for stored statistics. This is simply not the case. First, the statistics gathered by dynamic sampling are not as comprehensive as those gathered by the DBMS_STATS package. Second, the act of gathering them imposes a performance overhead on the SQL operation. Oracle dynamic sampling is meant to complement regular statistics rather than replace them.

During the generation of an execution plan, if the optimizer encounters a table with missing statistics, a limited set of statistics can be gathered dynamically as part of the parse operation. Note, however, that the optimizer will not perform dynamic sampling on remote tables or external tables. Even if statistics exist on all of the objects referenced by a query, the existence of complex predicates can cause the optimizer to perform dynamic sampling. If the optimizer determines that dynamic sampling should be used, Oracle will issue recursive SQL to scan a small random sample of table blocks.

The OPTIMIZER_DYNAMIC_SAMPLING level determines the conditions that will trigger Oracle dynamic sampling in addition to the size of the sample used. As a general rule, Oracle recommends leaving the default setting of this parameter at the system level -- altering it only at the session level when required. In most cases, altering the parameter at the system level is unlikely to benefit all SQL statements in the database.

Here are the criteria levels for OPTIMIZER_DYNAMIC_SAMPLING:

  • 0 -- Do not use dynamic sampling.
  • 1 -- Use dynamic sampling with a 32 block sample size for all tables that have not been analyzed, but only if the unanalyzed table has more than 32 blocks, no indexes and is not partitioned.
  • 2 -- Use dynamic sampling with a 64 block sample size if at least one table in the statement has no statistics. This is the default.
  • 3 -- Use dynamic sampling with a 64 block sample size if the statement meets level 2 criteria or has one or more expressions used in the WHERE clause predicates.
  • 4 -- Use dynamic sampling with a 64 block sample size if the statement meets level 3 criteria or uses complex predicates (an OR or AND operator between multiple predicates on the same table).
  • 5-9 -- Use dynamic sampling with a 128, 256, 512, 1024 or 2048 block sample size, respectively, if the statement meets level 4 criteria.
  • 10 -- Use dynamic sampling of all blocks for all statements.

In an Oracle database that is acting primarily in a data warehouse role with a small number of long-running queries, increasing the OPTIMIZER_DYNAMIC_SAMPLING level for the database to three or four may cause an improvement in the overall performance of queries. That's because the overhead imposed by dynamic sampling will be offset by better execution plans. However, in OLTP environments that have a large number of fast-running queries, the increased parse-time overhead may reduce the overall performance. Leaving the level at the default of two is generally advisable in that environment. In either case, understanding how dynamic sampling works is an important part of the SQL tuning process. The DYNAMIC_SAMPLING hint can also be used to invoke dynamic sampling for a single statement. As with hints in general, this is primarily of use in testing rather than something that should be widely used in a production environment.

When used for SQL statements that have complex predicates, Oracle dynamic sampling provides more accurate estimates for predicate selectivity. This in turn helps the optimizer create better execution plans. Dynamic sampling can supplement several statistics, including table and index block counts, table cardinalities and join column statistics.

A significant factor in determining whether or not the optimizer will use dynamic sampling for a given statement is whether it is being executed serially or in parallel. Serial operations generally use fewer system resources and have shorter execution times than parallel statements. The overhead imposed by gathering statistics dynamically may have a negative impact on their performance rather than a positive one. Allocating additional resources to the creation of an execution plan for a parallel query has a greater chance of reducing the overall time to complete the SQL operation. The optimizer will always honor the OPTIMIZER_DYNAMIC_SAMPLING level for serial operations. However, for parallel operations, if the level is set to the default and the SQL statement has complex predicates, the optimizer may choose to invoke dynamic sampling. If the OPTIMIZER_DYNAMIC_SAMPLING level is set to a nondefault level, Oracle honors the logic assigned to that level even if the statement is run in parallel. In either case, the database will perform dynamic sampling when existing statistics are missing, unusable or insufficient (unless dynamic sampling has been turned off entirely).

Not all queries can benefit from Oracle dynamic sampling. Queries with very fast execution times or ad-hoc queries that will not be repeated are not good candidates. Here are the situations that can benefit most from dynamic sampling:

  • A query with complex predicates that is generating a poor execution plan.
  • Queries with execution times that are significantly longer than the time required to perform dynamic sampling.
  • Queries that will be repeatedly executed on the database.

Dig Deeper on Oracle database performance problems and tuning