RE: PLSQL CPU consumption

Hi all,  

Thanks for the recommendations.  

For me, session_cached_cursors did resolve the issue.  

An interesting problem - it would be nice to know why the package call is so expensive for this small number of package procedures (and not others within the same package), especially given that they are already compiled. Without the server source code I guess I'll never know..  

Cheers

Adrian  

From: Anand Rao [mailto:panandrao_at_gmail.com] Sent: 26 September 2006 05:55
To: ade.turner_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: 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

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