Sample Table Scans and Oracle Dynamic Sampling

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Sample Table Scans and Oracle Dynamic Sampling

Oracle Database Tips by Donald Burleson

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_1002_oracle_tuning_definitive_reference_2nd_ed.htm

��