Re: PLSQL CPU consumption

forgot to add that session_cached_cursors could help. Also, 9205 and above use this parameter more than the open_cursors.

start with 200 and see if it helps. use the following query to find out if the session cache is indeed being used.

select
  'session_cached_cursors' parameter,
  lpad(value, 5) value,
  decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
  ( select

      max(s.value) used from v$statname n, v$sesstat s     where n.name = 'session cursor cache count' and       s.statistic# = n.statistic#),
  ( select value from v$parameter where name = 'session_cached_cursors') union all
select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used from v$statname n, v$sesstat s

    where n.name in ('opened cursors current', 'session cursor cache count') and

      s.statistic# = n.statistic#
    group by s.sid),
  ( select value from v$parameter
    where name = 'open_cursors')
/

after about 30-60 minutes of usage on your system, run this query. if it shows 95% or more usage, it is good. if it shows 100%, then you may try increasing the value in steps (for ex., 300).

cheers
anand

On 26/09/06, Anand Rao <panandrao_at_gmail.com> wrote:
>
> 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