RE: Response time analysis and TKPROF

Anjo, you mentioned third party tool using instead of 10046 event trace files. What is the tool and how it works - if you have this info of cource.

Alex Hillman

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Anjo Kolk
> Sent: Sunday, May 05, 2002 8:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Response time analysis and TKPROF
>
>
> Yes,
>
> I think that we are talking about different things:
>
> 1) 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.
>
> 2) 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)
>
> 3) 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.
>
> 4) 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.
>
> 5) 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.
>
> 6) 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.
>
> 7) 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alex Hillman
  INET: ahillman_at_erols.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).