Re: PLSQL CPU consumption

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_at_il.proquest.com> 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_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
>

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