| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Your opinion please :) keep in the goal
I tried to send this twice on Thursday, but it appears that I am incompetent at email..
Hi Comments interspersed
> 1) About the set of checklist, I agree, but the goal is to
> introduce to
> tunning, in the market there are several excellent tunning
> books, (including
> the documentation where is almost all), but people don't read them.
> I decided something short, a checklist explaining very very
> briefly what you
> can tune, the most important, the first steps, for newbies,
This is lifted straight from the introduction to Cary and Jeff's most excellent opus 'Optimizing Oracle Performance'
<quote>
I submit for your consideration the following hypothesis
If you find that Oracle Performance Tuning is really difficult then the chances are excellent that you're doing it wrong
Now, here's the scary part
You're doing it wrong because you've been taught to do it that way
</quote>
The wrong way to do tuning of course being to build a checklist of metrics. The right way is to focus your attention on the process or in rare instances processes that are causing your end-users the hassle. It is the end-users experience that you are trying to improve. If newbies start by thinking that tuning can be accomplished by checklists and database wide parameter changes then there is a high probability that their tuning efforts will fail. there is also the problem that even if they do succeed they may not know why and so be unable to repeat the success at a later date.
> For example, about statistics, the explanation is very short
> 1.1 Statistics
> The optimizer works based on statistics, if they are old, or
> inaccurate you'
> ll get a wrong execution plan.
>
> You must recalculate statistics every time an important
> change had happened
> in it:
>
> * Periodically, based on normal changes in a database
Actually I think this is open to debate. Certainly I think that the frequency is not time dependent but volume dependent
> * After importing a big amount of data
What is a big amount of data? We found that we had out of date statistics after adding 13 rows to a table and the end-user experience was bad. Equally we have loaded 750000 rows and not had any degradation in performance with the 'out of date' stats.
> * When distinct values in primary columns change
I assume that you mean when the distribution of data changes?
> Don't execute DBMS_STATS on SYS schema.
open to debate in 9.2, even more so as I understand it in 10. Who knows what it will be like in 10.2/11
> Because their database
> are small and
> comparing with sql server it takes a similar amount of time
> to process, they
> don't see it as a problem or a mistake.
If no-one is complaining then it is probably a mistake to reanalyze.
> 2)About the optimizer_index_caching I read a lot, because we
> have a problem,
> we solved setting this parameter, about this parameter I say:
> "This parameter sets the possibility to find blocks accessed
> through an
> index in memory.
>
> Default value is 0, 0 means that no one block accessed
> through index will be
> found in memory THIS IS FALSE. A value of 90% is advisable"
>
> Could you giveme an example how could I explain it better.
well *why* is a value of 90 advisable? There is a slight problem with the English in the first sentence as well. In English you are saying that this parameter *determines* the chances of a block being found in memory. This might be better rewritten as 'This parameter is an estimate of the likelihood of finding any given index block in memory rather than on disc'.
> 3) About
> "but pays no attention to individual business processes (or indeed
> development modules)."
Could you giveme an example what for example I could add
Well of course the text book on this has already been written. What I have in mind is explaining that your tuning goals should be determined by your business requirements, a made up example might be. You have an overnight batch job that runs in 4 hours. Because your company has just opened an office in Aukland you now only have a 2 hour window. The process is now not acceptably efficient because it will not finish in its allocated window.
Or you might illustrate the interactive wait interface approach by using an example like. Your 3rd party call centre application has run smoothly for months, Today however the users are ringing your phone off the hook complaining about the length of time it takes to bring up the call details screen.
I'd strongly recommend cary and jeff's book on optimizing response time which would be tailor made for the first scenario, and Oracle Performance Tuning 101 on the use of the wait interface to diagnose problems like the second. You could of course use extended trace for both scenarios, but i'd certainly at least start with v$session_wait etc for scenario 2.
cheers
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805
> -----Original Message-----
> From: jreyes_at_dazasoftware.com
> Sent: 27 February 2004 17:15
> To: jreyes_at_dazasoftware.com; oracle-l_at_freelists.org
> Subject: Re: Your opinion please :) keep in the goal
>
>
> Thanks Wolfgang
> ----- Original Message -----
> From: "Wolfgang Breitling" <breitliw_at_centrexcc.com>
> To: <oracle-l_at_freelists.org>
> Sent: Friday, February 27, 2004 1:12 PM
> Subject: Re: Your opinion please :) keep in the goal
>
>
> > 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
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
>
-- 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 Mon Mar 01 2004 - 03:17:11 CST
 |
 |