Hi All,
I am using 11GR2 on linux box. few days back , we hit the ora-4031 and we reported the issue to MOS, they then suggested to bump up the value of shared_pool_size to 3G, which is currently 1G.
Here , we are using sga_target and sga_max_size to 30G and also when I checked the current shared pool size, it's shows around 6G.
Since I am using the Automatic memory memory management along with the shared_pool_size parameter set to 1G. So, Do I really need to bump up the shared_pool_size as per MOS. doesn't this simply
means that , irrespective of shared_pool_size being set , oracle will ignore this value and size the shared pool according to the load in the database.
I would really appreciate , if you could offer your expert opinion here.
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ --------------------------------- ----------------
sga_max_size big integer 30G
sga_target big integer 30G
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ --------------------------------- ----------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 338899763
shared_pool_size big integer 1G
shared_server_sessions integer
shared_servers integer 0
SQL> select sum(bytes)/1024/1024 mbytes
from v$sgastat where pool = 'shared pool'; 2
MBYTES
- ---------
6464
Errors in file /db/prod01/oracle/admin/diag/rdbms/prod01/prod01/trace/prod01_m001_19998.trc:
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^281","kglseshtTable")
Fri Aug 16 12:49:19 2013
Process m001 died, see its trace file