| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Execution Plan Tuning - A cautionary tale
Remember back in school when you did your science projects that started with
a section
Assumptions:
and then you listed your assumptions and did the experiment prescribed by the text books and (oh so) occasionally reproduced the results that you were supposed to?
A similar approach might be useful in dealing with database tuning issues.
You have a complex query that works reasonably well in packaged product A version 2.59.3 on oracle 8.1.6.1.0. You buy packaged product A version 2.60.4 and (following best practice) install it on 8i release 3.
said query looks a bit like
select <col list>
from taba a,tabb btab1, tabb btab2, tabc agrdfe54
where
<25 join conditions>
;
and uses hash joins nested loops in list iterators etc.
It performs terribly on the new platform. So you analyze the tables. then you create histograms. then because you *do* have access to the sql you hint the statement to use the access path (hash joins index access etc) that it did before. Then you get depressed and create a stored outline in an earlier version knocked up from a technet download and move it across. then you release plan stability isn't supported in standard edition.
Then you put it on hold a bit and go on to a performance issue with a new piece of functionality. Its relatively trivial and just requires a concatenated index. So you create it.
*then and only then* do you think to yourself. "hang on a mo those columns were in my where clause this morning". You discover that the optimiser steadfastly refuses to use indexes that don't actually exist.
lesson to learn. before you dive into the optimiser and performance tuning. do check that database objects you know are useful haven't been lost in an upgrade. don't assume but check (or at least list your assumptions like you did in school).
grrrrrrrrrrr
--
Niall Litchfield
Oracle DBA
Audit Commission UK
--
Niall Litchfield
Oracle DBA
Audit Commission UK
 |
 |