Dynamic Sampling Challenges
Introduction
Dynamic sampling was introduced in Oracle 9.2. For the most part, that utility is invoked when SQL statements access tables that do not have any optimizer statistics built on them.
Basically, when dynamic sampling is invoked, Oracle will read some blocks from the underlying tables – at runtime – in order to generate some “temporary” statistics about those tables. Oracle will then use those temporary statistics, to generate an execution plan for the query in question.
It is certainly understandable that Oracle would invoke dynamic sampling, if one or more of the tables being accessed do not have any optimizer statistics – because otherwise, the optimizer would have no way of knowing what type of data it is dealing with.
Note, however, that dynamic sampling is not a “replacement” for gathering optimizer statistics! This is primarily due to the fact that by default, the number of blocks gathered by that mechanism is relatively small – as it must be, in order to minimize the time required. That small number of blocks means that sometimes the optimizer receives a very “skewed” sample of the data in the underlying tables – and that, in turn, can cause the optimizer to make extremely poor decisions about the execution plan to use, for any given query.
Dynamic sampling is especially inappropriate, when the query in question is relatively “complex”. For example, if a query is hundreds of lines long, and has numerous unions, outer joins, case statements, not in conditions, etc, then you certainly do not want to rely on dynamic sampling to give the optimizer accurate information about the underlying tables.
For example, take a look at the real-world output below. The query in question is quite complex – and the tables accessed by it do not have any optimizer statistics. The dynamic sampling that resulted caused the optimizer to choose a different execution plan for that query, almost every single day that it was run:
SQL> select unique timestamp, plan_hash_value
2 from DBA_HIST_SQL_PLAN
3 where sql_id = '80yc8uk4h65cd'
4 order by timestamp asc;
TIMESTAMP PLAN_HASH_VALUE
------
02/10/2011 11:34:31 3116961532
02/11/2011 08:49:42 838880956
02/12/2011 12:17:12 1176816174
02/15/2011 04:54:18 1439450237
02/16/2011 07:50:48 622407117
02/18/2011 06:34:40 2039555505
02/19/2011 14:54:16 219078873
02/23/2011 02:34:47 3091619021
02/24/2011 06:50:02 2919401131
02/25/2011 06:51:22 1864766766
Most of the execution plans above were relatively efficient; but one of them – the one from 2/16 – was wildly inefficient. That caused the run time of the query to be an order of magnitude longer on 2/16, than it was on any other day. That, in turn, caused great consternation among end users.
New Behavior in 11.2
In addition to the above information, it is important to note that Oracle 11.2 has changed some of the behavior of the dynamic sampling mechanism. Basically, in 11.2, dynamic sampling will sometimes be used, even if all of the underlying tables have optimizer statistics!
The overall rule is this: if a SQL statement uses any parallel processing, then Oracle will decide – at runtime – if dynamic sampling will be used for that statement. (If a SQL statement does not use any parallel processing, then Oracle will not make this decision.)
When Oracle makes a decision about using dynamic sampling, it considers a number of factors about the SQL statement in question. From what I have seen, the following items have the greatest influence on that decision:
- The sizes of the tables being accessed;
- The number of predicates that are specified;
- The complexity of the predicates;
- The presence of string literals in the predicates.
It is possible to determine if dynamic sampling is being used, on a current execution of a query, by running an explain plan on the query. If dynamic sampling is used, then a note will appear in the explain plan output, which states that fact. If dynamic sampling is not used, then the note will not appear at all.
Here are some examples of SQL statements. In some cases, Oracle 11.2 will run dynamic sampling on that SQL statement, but in other cases it will not.
In these examples, let’s assume the following:
- Table test_tab_1 has 67,108,864 rows, and occupies 102,400 blocks;
- Table test_tab_2 has 8,388,608 rows, and occupies 1,200,776 blocks.
- Table test_tab_3 has 524,288 rows, and occupies 2,100,448 blocks.
- All three tables have accurate optimizer statistics built on them.
Query 1:
select /*+ PARALLEL (a,4) */ * from test_user.test_tab_1 a
where col1 = :b1
and substr(col2,1,1) = 'a';"
- Dynamic sampling is used, at level 4
Query 2:
select /*+ PARALLEL (a,4) */ * from test_user.test_tab_2 a
where col1 = :b1
and substr(col2,1,1) = 'a';"
- Dynamic sampling is used, at level 6
Query 3:
select /*+ PARALLEL (a,4) */ * from test_user.test_tab_3 a
where col1 = :b1
and substr(col2,1,1) = 'a';"
- Dynamic sampling is used, at level 6
In all three of the above examples, all of the following conditions need to be true, in order for Oracle to decide to use dynamic sampling:
- Parallel query needs to be used;
- The tables in question need to be “large” – in terms of rows, or in terms of blocks;
- Two (or more) predicates need to be specified;
- At least one of the predicates needs to be “complex” – i.e., it needs to use a function call, an arithmetic expression, etc.
- At least one of the “complex” predicates needs to use a string literal.
If any of the above conditions are false, then Oracle will not use dynamic sampling. For example, if the string literal of ‘a’ in the above queries gets changed to a bind variable, then Oracle does not use dynamic sampling.
The optimizer_dynamic_sampling parameter
Note that the behavior of dynamic sampling is controlled by the optimizer_dynamic_sampling parameter. The valid “levels” for that parameter are 0 to 10. Level 0 completely “turns off” dynamic sampling. The levels from 1 to 10 basically determine when DS will be used, and how many blocks will be scanned by it. The default level is 2.
Note that if optimizer_dynamic_sampling is explicitly set to a non-default level (i.e., anything other than 2), then Oracle will honor that level. This is true, even in version 11.2. For example, if that parameter is set to level 0, then Oracle will never use DS – even in 11.2. Also, if that level is set to 1, then Oracle will only use DS on tables that do NOT have statistics – even in 11.2.
So, if there are significant problems with dynamic sampling getting used, on tables that have statistics, in an 11.2 database, then it may be worthwhile to test setting the optimizer_dynamic_sampling parameter to level 1.