Re: Statspack ratios help

Sandeep,

You've gotten some comments which may be confusing. I'll have to disagree with the concept that "a 100% buffer cache hit ratio is bad". That's not true. A 100% BCHR is not bad. Nor is it good. That's why wise folk tell you not to use ratios to judge/improve performance. The ratio is mostly irrelevant.

What you want to look at is the section of Statspack that is half a page below these ratios-- the Wait Event and CPU usage statistics. Is your system spending a large amount of time waiting? If it's not waiting, is the CPU usage high (relative to the number of CPUs you have). If the answer to these questions is no, then you don't have a system-wide problem. But that doesn't mean that you might not have problems with individual processes, which may not show up in system-wide statistics. Are your users complaining? Is your application sufficiently fast? If something is running slowly, you want to concentrate on that process (which is where you use 10046 traces and such, as someone mentioned).

But the ratios below don't tell you what you need to know (though the fact that almost all of your parses are soft is a good thing).

--Terry

> Hi,
>
> I am running OLTP system on Oracle 10.2 and J2EE weblogic.
>
> I am getting some ratios as:
>
> Load Profile Per Second Per Transaction
> ~~~~~~~~~~~~ --------------- ---------------
> Redo size: 156,162.18 3,020.55
> Logical reads: 26,407.64 510.79
> Block changes: 904.27 17.49
> Physical reads: 0.39 0.01
> Physical writes: 34.01 0.66
> User calls: 5,863.32 113.41
> Parses: 981.47 18.98
> Hard parses: 0.01 0.00
> Sorts: 16.97 0.33
> Logons: 5.54 0.11
> Executes: 1,085.95 21.00
> Transactions: 51.70
>
>
> Rollback per transaction %: 44.17
> Buffer Nowait %: 100.00 Redo NoWait %: 99.97
> Buffer Hit %: 100.00 In-memory Sort %: 100.00
> Library Hit %: 100.00 Soft Parse %: 100.00
> Execute to Parse %: 9.62 Latch Hit %: 99.88
> Parse CPU to Parse Elapsd %: 69.06 % Non-Parse CPU: 91.60
>
> With 100% soft parse, execute to parse ratio is so low. Is it bad, how
> I can I improve it?
>
> I see rollback per transaction as 44.17. We are using Hibernate that
> generates database mapping and produces most of the SQLs. How can I
> invetigate further? But I doubt if application is doing some big time
> rollbacks.
>
> Thanks for help
>
> Sandeep
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l