 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
 |
Donald K. Burleson Oracle Tips |
When to invoke Oracle Dynamic Sampling
The main objective of dynamic sampling is to create more accurate selectivity and cardinality estimates, which help the CBO generate faster execution plans. Dynamic sampling is normally used to estimate single-table predicate selectivity when collected statistics cannot be used or are likely to lead to significant errors in estimation. It is also used to estimate table cardinality for tables without current statistics (Global temporary tables) or for tables that radically change between dbms_stats executions.
Dynamic sampling is great for global temporary tables (GTT) and highly volatile tables that radically change data characteristics. Dynamic sampling is also good for decision support systems (DSS) that have large n-way table joins with extremely complex Boolean predicates (i.e. giant WHERE clauses).
Warning - Dynamic sampling is not for everyone!
When dynamic_sampling was first introduced in Oracle9i I was used primarily for data warehouse system with complex queries. Because it is enabled by default in Oracle10g you may want to turn off dynamic_sampling to remove unnecessary overhead if any of the following are true:
·
You have an online transaction processing (OLTP) database with
small, single-table queries.
·
Your queries are not frequently re-executed (as determined by
the executions column in v$sql and executions_delta in
dba_hist_sqlstat).
· Your multi-table joins have simple WHERE clause predicates with single-column values and no built-in or mathematical functions.
Dynamic sampling is ideal whenever a query is going to execute multiple times because the sample time is small compared to the overall query execution time.
By sampling data from the table at runtime, Oracle10g can quickly evaluate complex WHERE clause predicates and determine the selectivity of each predicate, using this information to determine the optimal table join order. Let’s use the Oracle SQL sample clause to see how this works.
****************************************
The above text is an excerpt from my book “Oracle Tuning: The Definitive Reference” by Donald K. Burleson
|
|
Copyright © 1996 - 2009 by
Burleson Enterprises, Inc. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.