Yes,
I think that we are talking about different things:
- I don't feel that we are abusing the queue theory by borrowing terms like
service time and wait time.
Actually when I did the YAPP method, it was back in 1996 on a project that
involved Tuxedo and the
programmers on the project wanted more processes. I had to convince them to
do it with less and I
could do that with the service and wait time model (calculating the
response time in the Oracle server for sessions). And it really works well.
- The problem with tuning by hit ratios and tuning by counting (like number of
buffer gets and number of physical I/O from
v$sqlarea) is that we ignore the cost or the time they take. We assume that
each Logical I/O or buffer get is the same
cost. Which is not true. So the statement with a 1000 LIO could be more
expensive than the statement with a 1200 LIO.
The same is true for the Physical I/O. Not each physical I/O has the same
cost or response time. Again the statement with
1000 I/Os may be more expensive than the 1200 I/Os, because one is going
after different disks. I have run tests that show LIO for the same
statement to be all most twice as slow (depending on some settings, but
the SQL is the same, same plan)
- If you now take the service time (which is CPU) and that part is 80 percent
of the total response time, we can tell management
that a 50 percent faster CPU will make roughly a 40 percent difference.
That is not to say that is the right approach, because
the opposite may also happen (20 percent CPU and 80 percent wait, 50 percent
faster will only make a 10 percent improvement).
I have seen customers with response time problems that consisted for over 80
percent of I/O problems (I/O too slow). They needed a
50 percent improvement but couldn't fix the I/O. So they wanted to find 50
percent some where else. That didn't happen ofcourse
and they had to fix the I/O problem. Now that really helped management to
understand where the priorities were: Yeeh, this is not
a database problem but a disk array problem.
- I believe that 80-90 percent of all Oracle applications out there in the
field are highly inefficient. And that doesn't mean that they
don't use bind variables or that they do many logical I/Os. And that the
only way to fix them is faster CPU's (open to flames here ;-)) or do
some serious redesigning.
- Oracle provides many interesting statistics, but most of them only count. Now
in Oracle 9i they have added some long overdue response time or
timing statistics. But still it is lacking very important information. For
example, how can we tell what a SQL statement waited for a particular
session between 2 AM and 3 AM (without 10046 tracing) or for all sessions ?
If the session performs a business function, what resources did the session
use in that period for that business function ? That information is hard to
come by, or with very high overhead with the traditional Oracle tools.
- probably the most important point. The database doesn't decide WHAT SQL to
execute. The database decides HOW SQL should be executed. The application
decides WHAT to execute. Many fast SQL statements can still result in a
slow business function, because do we need all those functions ? I have another
favorite formula for that: Amount * Cost => total cost. So either reduce
the cost or reduce the amount of SQL statements. Showed this formula to
bunch of people at an Oracle user group in the netherlands. 2 days later, I
got an email from someone saying that they concentrated on the amount instead of
cost
They reduced the batch job time from 2 hours to 10 minutes without tuning
the SQL statement, but tuned the function.
- The response time model gives the end-user perspective (without actually
having to go to the coffee machine, unless you want coffee ;-)), but talking to
them
is very valuable. That doesn't mean they are right ;-)
Anjo.
Stephane Faroult wrote:
> Anjo Kolk wrote:
> >
> > Stephane.
> >
> > The SQL statement is the right level, believe it or not. Basically the most
> > expensive SQL statements (resource wise) will float to the top that way.
> >
> > Anji,
> >
>
> I disagree, with a strong feeling of not talking about the same thing.
> My favorite method for finding the most expensive SQL statements is
> rather to check buffer gets at regular intervals, but here of course is
> a question of personal taste. But I meet more and more (business)
> processes in which, without being top-notch, SQL statements do not look
> terribly bad. Rewrite everything, and it roars. I am not sure that
> digging deep in this case inside trace files is the most effective.
> Having a talk round the coffee-machine with end-users also helps. And
> you always have that terrible SQL statement which runs at 2 am and about
> which nobody cares as long as the maintenance window is large enough.
> What I question is the need to abuse queue theory when, let's put it
> clearly, the problem is awful code written by beginners under the
> leadership of people too often unable to reread what has been written by
> their 'subordinates'. And I have strong doubts about how easily you will
> 'sell' it to a management who better understands that a faster processor
> (or an additional processor) may make things run faster - even if we all
> know that it is far from being always true. How much simpler for a
> 'decision taker' than purchasing days of consulting for a result which
> may, and usually will, be much more efficient, but for which quantifying
> (even wrongly) results is much more delicate.
>
> End of rant ;-).
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anjo Kolk
INET: anjo_at_oraperf.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun May 05 2002 - 19:08:21 CDT
