Re: PLSQL CPU consumption

  • From: "Anand Rao" <panandrao@xxxxxxxxx>
  • To: ade.turner@xxxxxxxxx
  • Date: Tue, 26 Sep 2006 10:18:32 +0530


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,


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?


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

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.




*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: