Re: PLSQL CPU consumption

  • From: "Anand Rao" <panandrao@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • Date: Wed, 27 Sep 2006 11:05:07 +0530

i must have been on speed when i said that!

Mark, you are right. memory usage/consumption can be high with this
parameter. can sometimes lead to a ORA-4031 error too. it should not affect
cpu consumption.

though i have a feeling that keeping those child cursors pinned incurs some
cpu overhead. on the other hand, trying to pin them should consume more cpu!

my comment that cpu usage can be high was based on bug notes i read long
back that setting a value of true can cause high cpu consumption. there was
also a note somewhere on Metalink that has shown this behaviour. i just
can't find them now.

then there are all the versions and platforms where this behaviour is
visible (or not).

should not rely on old pages in memory, needs refreshing :)

thanks
anand

On 26/09/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote: 

 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




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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006