Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 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 s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 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 Tips by Burleson Consulting

 

 
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

 

 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.