| |
My favorite Oracle tuning papers of IOUG
Collaborate 2006
The IOUG Collaborate 2006 conference was smashing success, being
bundled with the OAUG and QUEST user group conferences.
Attracting more than 5,000 attendees, this massive conference
offered something for everyone and had a virtual who's-who of Oracle
tuning experts offering a wide variety of highly-technical sessions.
As a Oracle tuning specialist, I was attracted to the Oracle
tuning papers and these were my favorites.
- Modern Performance Myths -
Craig Shallahamer
- Histograms - Myths and Facts -
Wolfgang Breitling
- Tuning Oracle at the Block Level -
Rich
Niemiec
- Welcome to my Nightmare - The Common Performance Errors in
Oracle Databases - Mike Ault
If I did not include your paper, I apologize in advance, as I was
unable to attend them all!
Modern Performance Myths -
Craig Shallahamer - I'm a
big fan of Craig and his whitepaper notes his short list of tuning
myths where he points out the shortcoming of wait event tuning and
session profiling approaches. This great papers supplements
the BC articles on
Busting the Oracle Myth Busters and
Oracle Myths Revisited.:
Myth #1. Decreasing wait event time will always
decrease Oracle response time.
Myth #2. Decreasing wait event time will always
decrease end-to-end response time.
Myth #3. Profiling sessions is the best way to
diagnose performance problems.
Myth #4. Focusing on where most of the time is
spent is always the best approach.
Craig advocates a holistic tuning method, some quite similar to
the method in my book "Oracle
Tuning: The Definitive Reference":
The
Holistic
Problem Isolation Method
(HPIM) provides a
very robust performance diagnosis analysis method. The key to
this method is identifying each subsystem’s bottleneck or area
of contention, and establishing and understanding the
relationships between subsystems.
Craig concludes about his holistic HPIM tuning method:
To optimize a database system one must
understand each subsystem: the database, the operating system,
and the application.
By discovering where each system is being
stressed or bottlenecked, observing the overlap, and re-focusing
the search, the problem possibilities can be narrowed very
quickly.
Unfortunately, most performance specialists tend
to focus on one of the three main subsystems mentioned above.
This results in an ill-defined problem that will translate into
a lop sided solution. In many cases, the solution, while
appearing to solve the problem from one subsystem’s viewpoint,
actually compounds the problem from other viewpoints resulting
in overall system performance degradation.
The key to making this method work is
discovering and exploiting the relationship between the three
subsystems. For example, if Oracle says there is a physical IO
issue, you should be able to find the offending high physical IO
SQL, and also uncover the IO bottleneck. The analysis does not
lie, so if something looks strange or does not correlate, it
forces you to dig deeper to get a better understanding of the
situation.
As with any system, once you make a change, the
system will adjust and the bottleneck could be very different.
Histograms - Myths and Facts -
Wolfgang Breitling
If you want to look under the covers of Oracle histograms,
Wolfgang is the man to see. His amazing whitepaper was
well-researched and offers a wealth of valuable insights into how
the Oracle CBO uses histograms to estimate the cardinality of
intermediate result sets (for choosing optimal indexes and optimal
table join order as noted in
this OTN paper). Breitling notes the performance benefits
of histograms on non-indexed columns (under certain circumstances)
and cautions against a one-size-fits-all solution:
Ok, so we have seen that a histogram on a
non-indexed column can be beneficial for the performance of a
SQL. But what about the reverse? Can a histogram on a column,
indexed or not, be detrimental to the performance of a SQL ?
I have always been convinced that
the answer to that is “Yes”
Wolfgang has boatloads of on-point test cases to illustrate his
points and offers some outstanding advice for using dbms_stats
for histogram collection (i.e. method_opt=>’size skewonly’):
Aside from the failure of
dbms_stats not pro-rating the histogram obtained through
sampling, it does not make sense to gather hostograms (sic) with
sampling in the first place. After all, histograms are only
gathered – or ought to – for columns with skewed data
distribution.
Sampling is liable to miss
infrequently occurring values and therefore skew the resulting
histogram.
He concludes by citing these great research papers:
1. Wolfgang Breitling
A Look
Under the Hood of CBO: The 10053 Event
2. Wolfgang Breitling
Using
DBMS_STATS in Access Path Optimization
3. Wolfgang Breitling
Tuning by
Cardinality Feedback - Method and Examples
4. Andrew Holdsworth, et al.
A
Practical Approach to Optimizer Statistics in 10g.
in Oracle Open World.
September 17-22, 2005. San Francisco.
Tuning Oracle at the Block Level -
Rich
Niemiec
Rich Niemiec is an exciting and dynamic speaker and this is a
very important Oracle tuning topic. In his paper, Rich notes
that AWR and STATSPACK elapsed-time reports are very valuable to
understanding block-level tuning:
The biggest problems can be
quickly found in the Statspack or AWR reports. One of the first
places that you can check is the Top 5 Wait Events. The Top 5
Wait Events section of statspack is probably the most revealing
section in the entire report when you are trying to quickly
eliminate bottlenecks on your system.
This section of the report shows the Top 5 Wait Events, the full
list of Wait Events, and the Background Wait Events. Identifying
major wait events will help you to target your tuning efforts to
the most burning issues on your system. If TIMED_STATISTICS is
true, then the events are ordered in time waited, if false, then
the events are ordered by the number of waits.
Niemiec also notes his short list for Oracle block-level tuning:
First, you want to review the basics and try
to tune everything without resorting to doing ANYTHING at the
block level. This includes the usually suspects (summarizing the
wait section about into a shorter list)
- Tune the SQL, of course… especially reads of full indexes
tables and others.
- Find the hot blocks… hot blocks can cause latching issues. Bad
SQL or bad indexes causes hot blocks (scanning through the same
large index). Improved in 10g (shared latches).
- Do you have enough freelists or can you use ASSM.
- Do you have initrans (ITL slots) set high enough for multiple
DML to the same block (pctfree not high enough to auto-generate
more ITL slots). Or do you have too many (each ITL costs 24
bytes!).
- Do you have a slow I/O subsystem or poor disk caching or not
enough paths and readers/writers are colliding.
- Are you on latest version so can use all the great new
features!
Welcome to my Nightmare - The Common
Performance Errors in Oracle Databases -
Mike Ault
I've been a Mike Ault fan for 15 years, and I'm always amazed at
his ability to quickly identify and correct performance bottlenecks.
This great paper relates to my book "Oracle
Silver Bullets" and show how a single change has have a dramatic
positive impact on Oracle performance:
I have come up with a list of 6 major areas
that the DBA or tuning expert needs to pay attention to in an
Oracle environment:
• Non-use of bind variables
• Improper Index usage
• Improper memory configuration
• Improper disk setup
• Improper initialization parameter usage
• Improper PGA setup
|
|
and include the URL for the page.
