Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PLSQL CPU consumption

RE: PLSQL CPU consumption

From: Polarski, Bernard <>
Date: Tue, 26 Sep 2006 15:38:08 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D568713@mailserver1>

A guess : cursor_space_for_time trigger premature flush out of shared pool of high number of objects hence cpu activity, specialy in DB with sql without bind.

                The effect would be similiar to a small SGA.

B. Polarski

From: Bobak, Mark [] Sent: Tuesday, 26 September, 2006 3:06 PM To:; Cc:
Subject: RE: PLSQL CPU consumption

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: [] On Behalf Of Anand Rao Sent: Tuesday, September 26, 2006 12:49 AM To: Cc: 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 <> 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 [] Sent: 25 September 2006 20:33 To: Cc: 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 --
Received on Tue Sep 26 2006 - 08:38:08 CDT

Original text of this message