RE: PLSQL CPU consumption

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 26 Sep 2006 09:05:46 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF2709AB4FC4@AABO-EXCHANGE02.bos.il.pqe>

Um, how is cursor_space_for_time "known to be CPU bound"? It will certainly cause higher memory usage, but how does it affect CPU consumption?

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." --Robert A. Heinlein

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anand Rao Sent: Tuesday, September 26, 2006 12:49 AM To: ade.turner_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: PLSQL CPU consumption

Hi,

i would try disabling cursor_space_for_time. it is known to be CPU bound. not very sure how much of that is affecting you. your wait event suggests libary cache issues. i am no good with ref cursors, so i can't really comment on that.

could be that there are large no. of copies of the same statement or that your packages / sql are getting invalidated from inside another proc. needs more diagnosis for sure.

just try,

cursor_space_for_time=false

and bounce your instance.

your next step is to drill down into V$SQL, V$SQLAREA and all those packages. do you use a lot of SQL from inside those packages?

cheers
anand

On 26/09/06, Adrian <ade.turner_at_gmail.com> wrote:

Hi David,

Sorry, I'm not onsite (UK based) so cant give you exact info, but pretty much

Latch Free(librarycache) 50%

	CPU Time,                                45%

	sequential read(much lower)       5%

	 

	The latch frees only appear under CPU starvation. Under normal

load its 90% to CPU Time.

Tkprof output does not seem to show the considerable cpu time attributed by v$sqlarea to the package call.

Cheers

Adrian

	From: David Sharples [mailto:davidsharples_at_gmail.com] 
	Sent: 25 September 2006 20:33
	To: ade.turner_at_gmail.com
	Cc: oracle-l_at_freelists.org
	Subject: Re: PLSQL CPU consumption

	 

	what are you biggest wait  / timed events

--

All I need to make a comedy is a park, a policeman and a pretty girl - Charlie Chaplin

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

Received on Tue Sep 26 2006 - 08:05:46 CDT