Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> AW: Statspack ratios help

AW: Statspack ratios help

From: <F.Castillo_at_hzd.hessen.de>
Date: Thu, 8 Jun 2006 14:18:53 +0200
Message-ID: <325B075345858C44B357B09A7AF4A4BD027BA8E8@srvhzdexc095.hzd.hessen.de>


As Cary is THE profiling guy I'd suggest he's book  

"Optimizing Oracle Performance" from O'Reilly  

Felix
www.oraConsult.de <http://www.oraconsult.de/>    

-----Ursprüngliche Nachricht-----
Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Joseph Amalraj Gesendet: Donnerstag, 8. Juni 2006 13:51 An: cary.millsap_at_hotsos.com; terrysutton_at_usa.net; oracle-l_at_freelists.org Betreff: RE: Statspack ratios help

Can you suggest some books and other resources to learn about profiling.  

Thanks  

Joseph Amalraj

Cary Millsap <cary.millsap_at_hotsos.com> wrote:

> though the fact that almost all of your parses are soft is a good
        thing         

        Man, I was onboard 'til that last sentence. :)         

	I think the right statement about this is the same one Terry used in his
	first paragraph. Rephrasing slightly, I'd say, "It might be good. But it
	might not. You can't tell by looking only at this."
	
	To me (normally pessimistic), so many parse calls are a red flag
	indicating that the application is doing something stupid: either
	parsing inside a loop, or closing and reopening cursors too often, or
	doing the 2-tier thing of disconnecting and connecting instead of the
	3-tier thing of sharing Oracle sessions.
	
	I think Terry's point is that having a bunch of soft parses probably
	beats having the same number of hard parses, but my point is that what
	we're seeing here is probably way short of good enough.
	
	My favorite close-enough definition of "soft parse" is this: "How the
	Oracle kernel handles a parse call that the application should never
	have made in the first place." Think about it...
	
	One final point: I HATE doing analysis with wait-wait-don't-tell-me data
	like the basis of this thread. To Jonathan's point, you simply can't
	tell from data like this what the performance IMPACT of the change is
	going to be. If we were looking at profiles instead of Statspack data,
	then we'd be able to tell exactly how many seconds per business task
	this behavior is costing someone.
	
	That may sound self-serving because I'm a profiler company guy, but the
	factual basis behind this statement is WHY I have dedicated my career to
	becoming a profiler company guy.
	
	
	Cary Millsap
	Hotsos Enterprises, Ltd.
	http://www.hotsos.com
	Nullius in verba
	
	Hotsos Symposium 2007 / March 4-8 / Dallas
	Visit www.hotsos.com for curriculum and schedule details...
	
	
	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org
	[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terry Sutton
	Sent: Tuesday, June 06, 2006 8:04 PM
	To: oracle-l_at_freelists.org
	Subject: 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 -- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 08 2006 - 07:18:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US