Re: Performance Overview

s.aswani_at_iname.com wrote:
>
> Here we go again, I had asked :
> > Has anyone managed to get near or above 95%.
> ===========================================
> Under the E-commerce environment (appl server, web server and DB there are 2 atleast kind of users you support as a DBA.
> Thanks for some of you have remarked,
> but not answered the above, please do so.
> Over the IE you can get better or worse response times than over the sqlnet. Its the response over the sqlnet I was talking about, is bad.
> >I have a good configuration of init paras 8k block, optimized share pool, sort area, log buufer, no waits ..............
> I want to merely confirm that its not the OEM 2.1 or the new version of B/Estats.
> My customers technical managers here in UK are well informed. Not only they expect IE users to be happy but also inhouse users as well. But why not they pay me and they expect me to leave some performance overview charts are also near 95% as in the past if not 99%, among other things. Thats why they have been giving me work since last 15 years as an Oracle consultant in USA, Europe and Japan.
> Wish you all my Oracle DBAs good day.
> Sunder Aswani.
> There is so much to be learned, so I listen, even when you flame. - By Mahatma Gandhi -
>
> ---------------------------------------------------

Hi Sundar,

I don't remember anything in your original note about sqlnet performance or OEM 2.1. All I remember is that performance was good, but you are not happy with the hit ratio. (I could be wrong though!) As far as I am aware, there is no problem with the new STATSPACK in OEM 2.x or the way it is calculated, although I could be wrong again!

The reason why I jumped in to answer is to make you (and the list) aware that a high hit ratio is NOT necessarily good. Jared has already pointed out some of the reasons it could not be good. I did point out that you should look at V$SYSTEM_EVENT and that will show up what the bottleneck could be. So what does it say?

I have cut-and-pasted a portion of the article by Mogens Nørgaard, Oracle Denmark that I obtained from Cary Millsap's site previously. It is now a 'protected' document and hence I am not legally able to post the whole.

This debunks the Hit ratio theory and is possibly a good article for your technical managers to read. Have them take a look at 'http://www.hotsos.com'. They will recognize the owner (Cary Millsap) as the architect of Oracle's OFA, if that will lend any credibility to this. (FWIW, the Anjo Kolk named below is the original author (or co-author) of the Wait Events paper that has now become a separate appendix in the Oracle 8i Reference Manual.)

This technique is relatively new (1999?) and has not yet achieved adequate momentum behind it. I hope this discussion contributes to that momentum.

• Quote ---- Almost every Oracle textbook author prescribes a tuning method based on analysis of key ratios. For about two decades, there was virtually no other way to tune the database. Now there is a better way, which my staff and a few performance specialists around the world use to diagnose and repair performance problems tens to hundreds of times more quickly than we ever could with ratio-based techniques. Once you learn how to use the Oracle wait interface, you will probably come to regard the ratio-based tuning techniques prescribed by so many authors and executed by so many database administrators as ridiculously inadequate. Oracle Corporation included the wait interface in Release 7.0.12 to enable exact measurements of how much time each Oracle kernel function consumed. With the birth of this interface, Oracle became capable of telling you where its bottleneck is (for a given system as a whole or on a given session). The wait interface offers overwhelming advantages over ratio-based tuning techniques.

•••• The wait interface offers a methodical, procedural approach to performance
optimization. Ratio-based tuning offers no way to prioritize which apparent
bottleneck to attack first. With ratio-based tuning, there is no clear method
or decision tree, just ratio after ratio until something you try finally helps.
Example: Kolk, Yamaguchi, and Viscusi define an Oracle optimization method that exploits Oracle’s
wait interface [Kolk et al. (1999)]. The key differentiator of this method is its reliability in
identifying the specific optimization effort technique that would provide the largest possible percentage
of overall performance improvement. Such a focused assault is not possible with ratio-based
tuning approaches.
•••• The wait interface produces straightforward performance data that are
not susceptible to ratio fallacies. Ratios mislead. By concealing a denominator, a
ratio conceals information that is often relevant. Example: A system’s database buffer cache hit ratio is a statistic that many database administrators
use as a measure of system health: many believe that the higher this ratio, the better the performance.
However, there is no consistent correlation between database buffer cache hit ratio and
a system’s state of optimization.
Many systems with very high database buffer cache hit ratios are very poorly optimized. They
have high cache hit ratios because they make too many logical I/O calls. They make too many
logical I/O calls because they execute inefficient SQL that reads the same blocks over and over
again. Yet, it is possible for a system with a very high cache hit ratio to be well tuned.
•••• The wait interface allows you to estimate the response-time impact of
removing a specific bottleneck. Because there is no consistent correlation between
ratios and system performance, tuning by ratio manipulation produces results
that are extremely difficult to forecast. Example: The response time for a particularly slow transaction is approximately 16.5 seconds.
The wait interface shows its Oracle service time to be 3.25 seconds, and its Oracle wait time to be
12.82 seconds. Further analysis of wait interface data reveals that almost all of the wait time is
consumed by db file scattered read events. Reducing the wait time by 95% will reduce the total response time by 0.95 ×12.82 = 12.18 seconds. The resulting transaction, at 16.5 – 12.18 = 4.32 seconds, will be
almost four times faster than before. None of the information required to compute this forecast is
available in ratio-based optimization approaches.

• End quote ---

"From a DBA who has been there and done that" and whose bacon has been saved many a time by just looking at V$SYSTEM_EVENT.

John Kanagaraj Received on Fri Oct 27 2000 - 13:27:20 CDT