Database Tables - Expert Oracle Database Architecture - page 419

Dynamic sampling is the optimizer's ability, when hard parsing a query, to scan segments in the database (sample
them) to collect statistics useful in optimizing that particular query. It is akin to doing a miniature gather statistics
command during a hard parse. In Oracle 10 g and above, dynamic sampling will work out of the box, because the
default setting has been increased from 1 to 2; at level 2, the optimizer will dynamically sample any unanalyzed object
referenced in a query processed by the optimizer prior to evaluating the query plan. In 9 i Release 2, the setting of 1
would cause dynamic sampling to be used much less often. We can use an ALTER SESSION|SYSTEM command in Oracle9 i
Release 2 to make it behave the way Oracle 10 g does by default, or we can use the dynamic sampling hint as follows:

ops$tkyte@ORA9IR2> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0;
Table created.
ops$tkyte@ORA9IR2> insert into gtt select * from scott.emp;
14 rows created.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ first_rows */ * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=8168 Bytes...
1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=8168 Bytes=710616)
ops$tkyte@ORA9IR2> select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=14 Bytes=1218)
1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=14 Bytes=1218)
ops$tkyte@ORA9IR2> set autotrace off

Here, we set the dynamic sampling to level 2 for the table GTT in this query. Left to itself, the optimizer guessed
8,168 rows would be returned from the table GTT .

■ the 8,168 default value is actually a function of your default block size. In a database with a 4Kb block size, the
number of estimated rows would be smaller; with 16Kb blocks, it would be larger.

Note

Using dynamic sampling, the estimated cardinality will be much closer to reality (which leads to better query
plans overall). Using the level 2 setting, the optimizer quickly scans the table to come up with more-realistic estimates
of the true size of this table. In Oracle 10 g and higher, we should find this to be less of a problem, because the defaults
will cause dynamic sampling to take place:

EODA@ORA11GR2> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0;
Table created.