High CPU_COUNT and increased granule size can cause ORA-0431 error.
Memory sizing depends on CPU_COUNT (No of processor groups).
Please use below formulas to calculate min buffer cache size
--Minimum Buffer Cache Size
10g : max(CPU_COUNT) * max(Granule size)
11g : max(4MB * CPU_COUNT)
Please note that If SGA_MAX_SIZE < 1GB then use Granule size = 4mb, SGA_MAX_SIZE > 1G then use Granule size = 8MB.
-- _PARALLEL_MIN_MESSAGE_POOL Size
If PARALLEL_AUTOMATIC_TUNING =TRUE then large pool is used for this area otherwise shared pool is used.
CPU_COUNT*PARALLEL_MAX_SERVERS*1.5*(OS msg bufferr size) OR CPU_COUNT*5*1.5*(OS message size)
-- Add extra 2MB per CPU_COUNT for shared pool.
Here is the example:-
Sun Solaris server has threaded CPUs. 2 physical CPUs has 8 cores, and each core has 8 threads, then Oracle evaluates CPU_COUNT = 2*8*8=128.
When SGA_MAX_SIZE=900MB,
Minimum Buffer Cache = CPU_COUNT *Granule size = 128*4M = 512MB
Shared Pool can use 338MB
When SGA_MAX_SIZE=1200MB,
Minimum Buffer Cache = CPU_COUNT *Granule size = 128*8M = 1024MB
Shared Pool can use 176 MB, so ORA-4031 occurs despite larger SGA_MAX_SIZE.
You need to manually tune CPU_COUNT parameter to resolve this error.
Please see the below link on CPU_COUNT
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams032.htm#sthref147
Thanks
Satishbabu Gunukula
http://www.oracleracexpert.com/
Subscribe to:
Post Comments (Atom)
Great and Useful Article.
ReplyDeleteJava Online Training
Java Course Online
Java EE course
Java Course in Chennai
Java Training in Chennai
Java Training Institutes in Chennai
Java Interview Questions
Java Interview Questions
شركة نقل اثاث بنجران
ReplyDeleteشركة نقل اثاث بخميس مشيط
شركة نقل اثاث بابها
Totally love it how you add meaning to the content and writing in the most easy way to everyone can understand. Great.Independence Day India Quotes
ReplyDelete