A sample table
scan retrieves a random sample of data of
whatever size you choose. The sample can be from
a simple table or a complex SELECT statement
such as a statement involving multiple joins and
complex views.
To peek inside dynamic sampling we can run some
simple SQL queries. The following SQL statement
uses a sample block and sample rows scan on the
customer table. (Note that there are 50,000 rows
in this table.) The first statement shows a
sample block scan and the last SQL statement
shows a sample row scan.
select
count(*)
from
customer
sample block(20);
COUNT(*)
----------
12268
select
pol_no,
sales_id,
sum_assured,
premium
from
customer
sample (0.02) ;
POL_NO SALES_ID SUM_ASSURED PREMIUM
---------- ---------- ----------- ---------- --
2895 10 2525 2
3176 10 2525 2
9228 10 2525 2
11294 11 2535 4
19846 11 2535 4
25547 12 2545 6
29583 12 2545 6
40042 13 2555 8
47331 14 2565 10
45283 14 2565 10
10 rows selected.
Just as we can sample our data with SQL queries,
the Oracle10g cost-based optimizer can sample
the data prior to formulating the execution
plan. For example, we can use the new
dynamic_sampling SQL hint to sample rows from
the table at execution time:
select /*+
dynamic_sampling(customer 4) */
pol_no,
sales_id,
sum_assured,
premium
from
customer;
POL_NO SALES_ID SUM_ASSURED PREMIUM
---------- ---------- ----------- ---------- --
2895 10 2525 2
3176 10 2525 2
9228 10 2525 2
11294 11 2535 4
19846 11 2535 4
25547 12 2545 6
29583 12 2545 6
40042 13 2555 8
47331 14 2565 10
45283 14 2565 10
10 rows selected.
Dynamic sampling addresses an innate problem in
SQL and this issue is common to all relational
databases. Estimating the optimal join order
involves guessing the sequence that results in
the smallest amount of intermediate row-sets,
and it is impossible to collect every possible
combination of WHERE clauses with a priori
statistics.
Dynamic sampling is a godsend for databases that
have large n-way table joins that execute
frequently. By sampling a tiny sub-set of the
data the Oracle 10g CBO gleans clues as to the
fastest table join order.
As we have noted, dynamic_sampling
does not take
a long time to execute, but it can be an
unnecessary overhead for all Oracle10g
databases. Dynamic sampling is just another
example of Oracle’s commitment to making
Oracle10g an intelligent, self-optimizing
database.
***********************
The above text is an excerpt
from forthcoming book “Oracle
Tuning: The Definitive Reference” by Donald K. Burleson.
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
