RE: PLSQL CPU consumption

  • From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 26 Sep 2006 15:38:08 +0200

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 [mailto:Mark.Bobak@xxxxxxxxxxxxxxx] 
Sent: Tuesday, 26 September, 2006 3:06 PM
To: panandrao@xxxxxxxxx; ade.turner@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Anand Rao
Sent: Tuesday, September 26, 2006 12:49 AM
To: ade.turner@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx] 
        Sent: 25 September 2006 20:33
        To: ade.turner@xxxxxxxxx
        Cc: oracle-l@xxxxxxxxxxxxx
        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 

Other related posts: