In oracle9i R2, dynamic sampling query is introduced and it has ability to sample the table and collect the better statistics dynamically during the hard parse. This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled.
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled.
How do we set Dynamic sampling parameter? There are couple of ways, we can set the parameter.
1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level.
1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level.
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;
Session altered.
SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;
System altered.
SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;
Session altered.
SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;
System altered.
SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;
When does dynamic sampling is useful?
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.
Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10.
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.
Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10.
In Oracle9i R2, the default setting for dynamic sampling level is 1. At this setting, the optimizer will tend to use dynamic sampling only in some cases when a table is unanalyzed. This includes the case of global temporary tables without statistics but doesn’t ensure that dynamic sampling will be used against all unanalyzed tables. In Oracle Database 10g Release 1 and above, the default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics.
This entry was posted
on Thursday, November 3, 2011
at 7:56 AM
and is filed under
Performance Tuning
. You can follow any responses to this entry through the
comments feed
.