RE: PLSQL CPU consumption

  • From: Adrian <ade.turner@xxxxxxxxx>
  • To: "'Anand Rao'" <panandrao@xxxxxxxxx>
  • Date: Tue, 26 Sep 2006 19:46:08 +0100

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@xxxxxxxxx] 
Sent: 26 September 2006 05:55
To: ade.turner@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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



Other related posts: