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

--
http://www.freelists.org/webpage/oracle-l