 |
|
Oracle 10g
Dynamic sampling
Oracle Tips by Burleson Consulting |
Enabling Dynamic Sampling
The main objective of dynamic sampling is to create more accurate
selectivity and cardinality estimates, which in
turn helps 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 statistics or for tables whose
statistics are too out of date to trust.
The optimizer_dynamic_sampling
initialization parameter
controls the number of blocks read by the dynamic sampling query.
The parameter can be set to a value from zero to ten. In 10g, the
default for this parameter is set to two, automatically enabling
dynamic sampling. The
optimizer_features_enable parameter will turn OFF
dynamic sampling if it is set to a version earlier than 9.2.0.
A value of zero means dynamic sampling will not be performed.
Increasing the value of the parameter results in more aggressive
dynamic sampling in terms of both the type of tables sampled,
analyzed, or unanalyzed, and the amount of I/O spent on sampling.
According to Oracle Corporation, Oracle will sample 32 random blocks
"The number of blocks sampled is the default number of dynamic
sampling blocks (32)."
There is also a new dynamic sampling hint,
dynamic_sampling
(tablename level), where
tablename is the name of
the table to be dynamically sampled and
level is the same setting
from zero to ten. The default value for the
level is two, which will
only sample tables that have not been analyzed with
dbms_stats.
select /*+ dynamic_sampling (customer 4) */
customer_name, . . . .
The following are the level descriptions. The higher the level, the
deeper the sampling. The sampling levels are cumulative and each
level contains all of the sampling of the prior level:
§
Level 1: Samples tables that appear in join or
subquery conditions that have no indexes and have more blocks than
32, the default for dynamic sampling.
§
Level 2 (default): Samples all unanalyzed tables that
have more than 32 blocks.
§
Level 3: Samples tables using a single column that
applies selectivity to the table being sampled.
§
Level 4: Samples tables using two or more columns that
apply selectivity to the table being sampled.
§
Level 5: Doubles the dynamic sample size and samples
64 blocks on tables.
§
Level 6: Quadruples the dynamic sample size and
samples 128 blocks on tables.
§
Level 7: Samples 256 blocks on tables.
§
Level 8: Samples 1,024 blocks on tables.
§
Level 9: Samples 4,096 blocks on tables.
§
Level 10: Samples all of the block in the tables.
|
|
Dynamic sampling is not for everyone!
|
When dynamic_sampling was first introduced in
Oracle9i, it was used primarily for data warehouse system with
complex queries. Because it is enabled by default in Oracle10g, the
DBA may want to turn off
dynamic_sampling to remove unnecessary overhead if any of the
following are true:
§
The system utilizes an online transaction processing
(OLTP) database with small, single-table queries.
§
Queries are not frequently re-executed as determined
by the executions column
in v$sql and
executions_delta in
dba_hist_sqlstat
.
§
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. The following section will introduce
the Oracle SQL SAMPLE clause and show how it works.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
