 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 8 -
Miscellaneous Oracle Information
The following sections provide information on a wide range of
topics. Information provided includes general hints and tips to
simplify administrative tasks and information that, although
important, didn’t fit into any other section.
The Foot Rule of Thumb
The Foot Rule of Thumb is that there are no
rules of thumb! Don’t listen to industry pundits about their rules.
Make your own rules. How do you do that? Test! Experiment! Learn!
Afraid that performance will suffer if you place 6 columns in an
index to obtain index-only access? Build the index, monitor on-line
transaction performance and find out. The index 95/5 rule (don’t
build indexes on queries that retrieve more than 5% of the rows) is
a high-level recommendation. It doesn’t ring true in all cases.
Every environment is different and there are no two workload mixes
that are the same. I have seen queries return much higher
percentages of rows through indexes and the queries performed
flawlessly. Remember rules of thumb are just that, general
recommendations.
Try different column combinations, multiple
index usage, clustering changes, etc. When prototyping complex joins
between several tables, build a set of indexes that favors one
access path and test the queries in question. Drop the first set of
indexes and create indexes that favor another access path (such as
two different tables being joined first or a different join method
being used) and test again. Build the different permutations of
indexes that influence the optimizer to join a different set of
tables first or allow different join types to be performed. Test
each different access path and keep a performance history of each
change that is made. The above hint may seem to be a time consuming
process, but until you learn access paths this is the best approach
to take. I have spent hundreds of hours working with access paths
and if I could impart one sentence of wisdom, it would be the
following one: What path works well in one situation may not be the
best choice for another situation that almost exactly duplicates the
first!
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
|
|
