Background
Since Oracle 11 - Memory management is done via setting one parameter: memory_max_target
By default setting, memory_max_target effect the whole SGA memory size.
Oracle divides that memory between the various memory areas: Shared Pool, Java Pool, Large Object Pool.
How to see current memory
set linesize 120
show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- --------
hi_shared_memory_address integer 0
memory_max_target big integer 500M
memory_target big integer 500M
shared_memory_address integer 0
OR
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
shared_memory_address 0
shared_pool_reserved_size 10276044
shared_pool_size 0
memory_max_target 524288000
memory_target 524288000
java_pool_size 0
large_pool_size 0
OR
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE name like '%memo%'
Resolution
Several options, as the reasons for ORA-04031 may vary.
A. - Increase Oracle Memory
To increase the memory, need to reset memory_target and restart Oracle:
1. ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
2. STARTUP [FORCE]
3. ALTER SYSTEM SET MEMORY_TARGET = nM;
By Example:
SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=2000M scope=spfile;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size 2254824 bytes
Variable Size 1962936344 bytes
Database Buffers 117440512 bytes
Redo Buffers 5148672 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET MEMORY_TARGET=2000M;
System altered.
How to Flush Shared Pool
Option A - ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
Option B - (from Oracle 11) - Flush only single object from Shared Pool.
DBMS_SHARED_POOL.PURGE(address||','||hash_value, 'C');
Where address and hash_value are retrieved from V$SQL.
'C' - Stands for Cursor.
See complete reference for DBMS_SHARED_POOL package: Oracle Reference.
C. - Restart server.
On Win 2003 server, from time to time server experiance memory leak issues, becomming extremely slow and unresponsive.
Reboot to Win server resolved the issue.
Evidences:
Alert.log:
Sun Mar 19 03:04:55 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_6056.trc (incident=67969):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:05:12 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_6056.trc (incident=67970):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:05:49 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_5740.trc (incident=67969):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:06:17 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_5740.trc (incident=67970):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:07:15 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_2628.trc (incident=67969):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:07:32 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_2628.trc (incident=67970):
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Sun Mar 19 03:10:08 2017
DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
Sun Mar 19 03:19:51 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_j001_6056.trc:
ORA-12012: error on auto execute of job 11285
ORA-04031: unable to allocate 3936 bytes of shared memory ("shared pool","DELETE FROM SYS.SCHEDULER$_J...","sga heap(1,0)","kglsim object batch")
ORA-06512: at "SYS.DBMS_ISCHED", line 566
ORA-06512: at "SYS.DBMS_SCHEDULER", line
...
...
There are many incident files.
Sun Mar 19 03:55:07 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_mmon_400.trc (incident=70073):
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: d:\software\oracle\diag\rdbms\igt\igt\incident\incdir_70073\igt_mmon_400_i70073.trc
Sun Mar 19 03:55:14 2017
Trace dumping is performing id=[cdmp_20170319035514]
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_mmon_400.trc (incident=70074):
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
bytes (,)
Incident details in: d:\software\oracle\diag\rdbms\igt\igt\incident\incdir_70074\igt_mmon_400_i70074.trc
Sun Mar 19 03:55:17 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_5732.trc (incident=68171):
ORA-04030: out of process memory when trying to allocate 32784 bytes (pga heap,kgh stack)
Incident details in: d:\software\oracle\diag\rdbms\igt\igt\incident\incdir_68171\igt_ora_5732_i68171.trc
Sun Mar 19 03:55:18 2017
Starting background process CJQ0
Sun Mar 19 05:11:09 2017
Stopping background process CJQ0
Sun Mar 19 05:15:33 2017
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_mmon_400.trc (incident=70075):
ORA-04030: out of process memory when trying to allocate 16396 bytes (QERHJ hash-joi,QERHJ Bit vector)
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
bytes (,)
Incident details in: d:\software\oracle\diag\rdbms\igt\igt\incident\incdir_70075\igt_mmon_400_i70075.trc
Sun Mar 19 05:15:37 2017
Trace dumping is performing id=[cdmp_20170319051537]
Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_mmon_400.trc (incident=70076):
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 16396 bytes (QERHJ hash-joi,QERHJ Bit vector)
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
bytes (,)
Appendix
A. Oracle 11 Memory Architecture
B. Using Automatic Memory Management
C. Memory Target Wiki
No comments:
Post a Comment