11.2.0.4 on AIX
We have an application that uses several databases. One of them is archiving database: the data gets loaded into a large partitioned table, kept for several months, and then truncated.
The database is I/O-bound, the data volume is growing, loading it takes longer and longer. We thought that adding memory to the db cache will help to improve performance of the load process.
Before the change that parameters were
memory_target = 25 GB
db_cache_size = 10 GB
shared_pool_size = 5 GB
Actual values for db cache size and shared pool size were close.
Parameter pga_aggregate_target wasn't set. From stats$pgastat we could see that 'total PGA inuse' and 'total PGA allocated' were 2 - 3 GB and 'maximum PGA allocated' was 5 GB.
Internally it calculated PGA target to 9 GB.
We started from testing the change in non-prod environment where the sizing is identical to production. AIX server memory was increased by 20 GB and we tried to restart the database
with new values of parameters:
memory_target = 45 GB (old value + 20 GB)
db_cache_size = 30 GB (old value + 20 GB)
shared_pool_size = 5 GB (unchanged)
pga_aggregate_target = 5 GB (new setting)
However it started producing large number of ORA-04031 errors immediately after the db restart, when the application was still down.
The instance was started, database mounted, but ALTER DATABASE OPEN didn't complete and I couldn't even run queries against V$ views.
Trace files for ORA-04031 errors indicates that almost all memory in the shared pool was free.
We restarted the database once again and got the same result (doing the same thing over and over again and expecting different result...).
So we reduced pga_aggregate_target to 1 GB and restarted it again. This time it was clean.
However 1 GB is too small for PGA, I am thinking about restarting it yet again with db_cache_size = 26 GB.
There is nothing special about this database: it doesn't use Java (DBMS_JAVA), it doesn't use streams, it doesn't use shared server aka MTS.
Question: why would it produce ORA-04031 errors when shared pool is 5 GB and almost all memory in the shared pool is free?
This is for Production database:
Memory Dynamic Components
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 10,752 0 SHRINK/IMM 03-Nov 06:06:27
PGA Target 8,960 0 STATIC
SGA Target 16,640 0 STATIC
java pool 128 0 SHRINK/DEF 18-Jun 04:42:00
large pool 128 0 SHRINK/DEF 21-Jul 12:47:42
shared pool 5,248 0 GROW/IMM 03-Nov 06:06:27
streams pool 128 0 SHRINK/DEF 27-Oct 17:55:42
-------------------------------------------------------------
Errors in alert.log (non-prod database) after the restart:
Completed: ALTER DATABASE MOUNT
Fri Nov 06 16:40:41 2015
ORA-04031 heap dump being written to trace file /ora01/oracle/diag/rdbms/xx/XX/trace/XX_ora_4653608.trc
Errors in file /ora01/oracle/diag/rdbms/xx/XX/trace/XX_ora_4653608.trc (incident=629125):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","ALTER DATABASE OPEN","SQLA","tmp")
Incident details in: /ora01/oracle/diag/rdbms/xx/XX/incident/incdir_629125/XX_ora_4653608_i629125.trc
Fri Nov 06 16:40:42 2015
Errors in file /ora01/oracle/diag/rdbms/xx/XX/trace/XX_mmon_2097578.trc (incident=629085):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","V$JAVA_POOL_ADVICE","KGLS^6ea2e546","kglHeapIniti
alize:temp")
Incident details in: /ora01/oracle/diag/rdbms/xx/XX/incident/incdir_629085/XX_mmon_2097578_i629085.trc
From trace file:
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"free memory " 942 MB 92%
"db_block_hash_buckets " 26 MB 2%
"ASH buffers " 10 MB 1%
"dbktb: trace buffer " 7488 KB 1%
"Checkpoint queue " 5250 KB 1%
"private strands " 4921 KB 0%
"event statistics per sess " 4238 KB 0%
"ksunfy : SSO free list " 4019 KB 0%
"dirty object counts array " 2624 KB 0%
"write state object " 2498 KB 0%
-----------------------------------------
free memory 942 MB
memory alloc. 82 MB
Sub total 1024 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"free memory " 935 MB 91%
"db_block_hash_buckets " 25 MB 2%
"ASH buffers " 12 MB 1%
"dbktb: trace buffer " 7488 KB 1%
"Checkpoint queue " 5250 KB 1%
"private strands " 4788 KB 0%
"event statistics per sess " 4225 KB 0%
"dbwriter coalesce buffer " 4100 KB 0%
"ksunfy : SSO free list " 4019 KB 0%
"dirty object counts array " 2624 KB 0%
-----------------------------------------
free memory 935 MB
memory alloc. 89 MB
Sub total 1024 MB
Only subpool 7 looks slightly different - note that TOTALS section:
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 7
----------------------------------------------
"free memory " 584 MB 76%
"FileOpenBlock " 91 MB 12%
"db_block_hash_buckets " 25 MB 3%
"ASH buffers " 10 MB 1%
"VIRTUAL CIRCUITS " 7758 KB 1%
"dbktb: trace buffer " 7488 KB 1%
"Checkpoint queue " 5314 KB 1%
"private strands " 4788 KB 1%
"event statistics per sess " 4225 KB 1%
"dbwriter coalesce buffer " 4100 KB 1%
-----------------------------------------
free memory 584 MB
memory alloc. 184 MB
Sub total 768 MB
TOTALS ---------------------------------------
Total free memory 6193 MB
Total memory alloc. 719 MB
Grand total 6912 MB