Databases Reference
In-Depth Information
There's more...
Oracle optimizer needs up-to-date statistics to choose the best execution plan for our queries.
From Oracle Database 9
i
9iR2 onward, Dynamic Sampling was introduced. But, from 11
g
R2,
the optimizer automatically detects whether dynamic sampling should be used and
determines the appropriate sampling level. This allows us to have a better execution plan
than in previous database releases, even if we don't have statistics on the objects interested
in our query, as in our example.
However, relying on Dynamic Sampling in production databases is not a good idea. We can
use Dynamic Sampling when we have out-of-date statistics, and we can also use a hint to
inform the optimizer that we want to use this feature, as follows:
SELECT /*+ DYNAMIC_SAMPLING (CUSTOMERS 8) */ …
In this example, we have asked the optimizer to use Dynamic Sampling on the
CUSTOMERS
table, using a parameter of 8—the scale ranges from 0 (don't use) to 10 (use "aggressive"
sampling).
The greatest drawback to using Dynamic Sampling is that it
is only an estimate of the table contents based on a random
selected sample block of rows; computed statistics are much
more precise, resulting in an optimal execution plan.
The default level for Dynamic Sampling is set by the
OPTIMIZER_DYNAMIC_SAMPLING
parameter, which defaults to 2, as we can see in the screenshot for step 8 in the Note section
(
level
=
2
). With this level, the optimizer will use Dynamic Sampling to analyze a small
number of blocks, only for tables without statistics.
Lock table statistics for load or highly volatile tables
We have seen that, with Oracle Database 10
g
, the automatic statistics gathering feature was
introduced; however, we may prefer not to collect statistics on some tables, due to the highly
volatile data stored in them, or because they are load tables. For example, in a table used to
load data, we could collect statistics when the table is almost empty or when only one specific
kind of data is present in the table.
In such situations, we can inform the database to lock statistics on the table, using the
following
DMBS_STATS
procedure to lock the
TABLE_NAME
table of schema
SH
:
DBMS_STATS.LOCK_TABLE_STATS('SH', 'TABLE_NAME');
