OK, mine will be some criticism. First off I should mention that I am
biased from the get-go because I despise laundry lists. If you can put it
into a checklist, why not put it into a program or script and let the
computer do it. They are good at repetitive tasks.
Another reason I am against such lists is that they get outdated very fast,
but tend to still get followed, especially if their target is novice DBAs
who can not distinguish between truth and myth.
Some concrete beefs I have with your paper:
- you write that optimizer_index_caching and optimizer_index_cost_adj "must
be set". Everytime I hear or read absolutes like that, alarmbells go off
for me. I can give you examples, or create ones at the drop of a hat where
any lowering of those two values from their default will create disastrous
access paths.
- you write that "setting optimizer_max_parmutations=80000 indicates there
is no limit" and somewhere else that "columns with [predominant] null
values should be put at the end of a table to save space". Did or can you
prove that? I believe both statements are false, but have not spend any
time researching if I am right.
Enough of that. This is my 101 of tuning:
a) a performance problem must be raised and identified from a business
perspective - some business process is taking so long that it has a
negative, pecuniary impact on the business
b) together with that, a tuning goal must be identified in terms that are
measurable
c) armed with the concrete business process that needs tuning you start to
break it down into smaller components and at every step look for the
components that take the longest, either doing something, or waiting for
something. E.g. if the process consists of a sequence of SQL, you
concentrate on the sql that takes the longest and look at the sqltrace
(with wait stats) to identify where it spends its time. Or it could be that
it is not a single sql, but the fact the application (programmer) decided
he could do a nested loop faster than Oracle and fires off several hundred
or thousand sql for each employee, department, account, or whatever the
program is dealing with (don't laugh, I see it all the time).
The appropriate tuning measure depends on that analysis. Then you repeat
the process, always picking the "cream of the crop" until the identified
performance is reached. THEN STOP!!
At 06:15 AM 2/27/2004, you wrote:
Thanks for your good and bad opinions, but I would like to remember you please, the idea to give you the paper is to get ideas what more I can add there, what do you think should be in the paper (obviously briefly) and you can't see in there now. Or if you see some error, or something to improve.
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
