 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 7 - Tuning and Performance
Optimizer Dynamic
Sampling
The cost based optimizer uses statistics generated by the
dbms_stats procedure or the ANALYZE
statement as the situational data when creating its expert opinion
on which access path to the data is most optimal. These statistics
are stored in the data dictionary and describe the objects space
characteristics, data uniqueness and data distribution.
The cost-based optimizer is only as good as the
statistics it uses as input. Statistics collections should be run
on a regular basis to ensure that the statistics are current
(representative of the data being accessed). The optimizer is then
able to create a highly accurate access path that is based on the
least cost. If statistics are not available, the optimizer uses a
simple algorithm to calculate the statistics, which often leads to
“less than optimal” access paths. In other words, Oracle guesses
and it is usually not a very good guesser.
Oracle 9i Release 2 introduces optimizer
dynamic sampling to overcome the lack of accurate statistics on the
objects being accessed. The optimizer is now able to take a
sampling of the data during access path optimization.
Administrators are able to activate dynamic sampling and control the
size of the dynamic sample taken by using the
optimizer_dynamic_sampling dynamic initialization parameter as a throttle.
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
