Re: PLSQL CPU consumption
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- To: ade.turner@xxxxxxxxx
- Date: Tue, 26 Sep 2006 10:24:56 +0530
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@xxxxxxxxx> 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@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:
