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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
