Hi,
I'm working with an Oracle Database 11g Release 11.1.0.6.0 - 64bit Production With the Real Application Clusters option.
I'm experiencing some performance issue, in particular a lot of library cache pin concurency wait events.
Browsing the web I found out that that events is strongly connected to the sga size and the session cached cursor parameter.
Is that true?
Investigating more deeply I found some interesting query to size how many session cursor cache count I've:
select max(value) from v$sesstat where STATISTIC# in (select STATISTIC# from v$statname where name='session cursor cache count');
NODE1: 788
NODE 2: 21147
select cache/tot*100 "Session cursor cache%" from
(select value tot from v$sysstat where name='parse count (total)'),
( select value cache from sys.v_$sysstat where name = 'session cursor cache hits' );
NODE1: 1204,8
NODE2: 2306,8
Furthermore here there are sga usage statistics:
select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot ;
NODE1:
used_mb: 5122
free_mb: 532
total_mb: 5657
NODE2:
used_mb: 4723
free_mb: 239
total_mb: 4962
(
I've divided write and read on my RAC using 2 different services: write intensive sessions on node2 and read intensive sessions on node1.
This division worked perfectly for months till the number of sessions and volumes of data increased a lot.
)
This are my involved parameter:
memory_max_target 12G
memory_target 8G
sga_max_size 4932M
open_cursors 1000
session_cached_cursors 500
I was thinknig to increase memory_target up to 12gb and session_cached_cursors up to 1000.
Question 1: do you have other suggestions?
Question 2: do I have to restart the database to let the change take effect?
Thanks in advance,
Samuel Rabini