Re: Your opinion please :) keep in the goal

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 27 Feb 2004 10:12:26 -0700
Message-Id: <6.0.3.0.2.20040227094047.02b84998@pop.centrexcc.com>

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.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 27 2004 - 11:11:59 CST