hi everyone - I am looking for some tips and advice on using memory_max_target and max_target parameters.we have two servers identical hardware,specs, configuration..etc. they are mirror images 12c databases, Windows 2008 R2. .on the production server we are still using the 11g parameter sizes (NO MEMORY),on the second server (Test) we are attempting to use MEMORY parameters for testing.
on Production sizes are:
sga_max_size big integer 9764339712
sga_target big integer 9764339712
pga_aggregate_limit big integer 2147483648
pga_aggregate_target big integer 401604608
db_cache_size big integer 3019898880
java_pool_size big integer 134217728
large_pool_size big integer 134217728
shared_pool_size big integer 1073741824
memory_max_target big integer 0
memory_target big integer 0
Test:
sga_max_size big integer 5776M
sga_target big integer 0
pga_aggregate_limit big integer 178848M
pga_aggregate_target big integer 0
db_cache_size big integer 0
java_pool_size big integer 0
large_pool_size big integer 0
shared_pool_size big integer 0
memory_max_target big integer 91632M
memory_target big integer 91632M
I understand the concept behind ASMM so wanted to test the results however performance degradation is quite substantial using Memory parameters, jobs that ran 4 minutes on Production
now run 40 minutes on Test.
I understand when SGA and PGA are zero value MEMORY assigns 60% SGA and 40% PGA however 60% of 9163 is way too small. should we scrap using MEMORY Parameters or how big would I have to size them so that 60% would be comparable to 11g 9764M. the 91632M was sized by Oracle during DBCA install.
hoping some here can share their knowledge/experiences using the Memory parameters and how to size them for better ASMM performance.
thanks in advance