Step by step analyzing ORA-04031 unable to allocate x bytes of shared memory error in oracle
In alert log the error is ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”unknown object”,”KGLH0^e481b9fe”,”kglHeapInitialize:temp”)
If you get ORA-04031 error on shared pool and can not logon your database you must restart your instance. I will try to explain why you get ORA-04031 error and show you the solitions step by step.
1-) One of the reason is fragmentation of shared pool. If your shared pool is fragmented then you can flush your shared pool or restart your instance.
alter system flush shared_pool;
2-) Sometimes Shared pool need to grow. Then you should increase your shared pool.
alter system set shared_pool_size=32G;
3.1-) Sql that coming with Literals and use same plan_hash_value and do not use bind variables
select * from my_table where my_column=’variable1′;
select * from my_table where my_column=’variable2′;
select * from my_table where my_column=’variable44′;
3.2-) Similar sql statement that coming same plan_hash_value using bind variable.
select * from my_table where my_column in (:b1,:b2);
select * from my_table where my_column in (:b1);
select * from my_table where my_column in (:b1,:b2,:b3);
- Script to find how much memory used by similar sql or sql that coming literals.
select inst_id,plan_hash_value,round(sum(S.SHARABLE_MEM)/(1024*1024)) as Memory_MB,count(1) from gv$sql s
group by inst_id,plan_hash_value
order by 3 desc
INST_ID PLAN_HASH_VALUE MEMORY_MB COUNT(1)
---------- --------------- ---------- ----------
1 0 5222 26414
2 0 4598 22007
2 1316043113 208 2234
1 1316043113 200 2151
2 3095531958 112 2239
1 3095531958 108 2151
NOTE: You can ignore plan_hash_value=0 but some specific case such as mine it should be considered with sql_id together. plan_hash_value=0 may be an insert statement, plsql blok,procedure call, sql statement with dblink etc.
4-) Too many child of sql and high version count ( script to find why that sqls have high version count is at the bottom). If you want to flush/purge sql from shared pool that has high child and version count , you can click the link.
- Script to find sql child count.
select inst_id,sql_id,round(sum(S.SHARABLE_MEM)/(1024*1024),1) as Memory_MB,count(1) as CHILDCOUNT
from gv$sql s
group by inst_id,sql_id
order by 3 desc;
INST_ID SQL_ID MEMORY_MB CHILDCOUNT
---------- ------------- ---------- ----------
1 7yj8ubju3vc57 3936,2 5725
2 7yj8ubju3vc57 3372,4 4878
1 7y3md7fy66cgx 41,3 31
1 f6jp03faawzs2 38,2 43
2 7txvv1msuyyu9 37,4 42
2 f6jp03faawzs2 36,8 46
- Script to find sql version count. (script to find why that sqls have high version count is at the bottom)
select
inst_id,sql_id,version_count
from
gv$sqlarea
order by version_count desc
INST_ID SQL_ID VERSION_COUNT
---------- ------------- -------------
2 7yj8ubju3vc57 783
1 7yj8ubju3vc57 608
2 04c5k4phtxm7a 349
2 dyjzd44h1t0r1 83
2 6raq7sb0yp6su 79
1 6raq7sb0yp6su 79
- Sql plan hash value list that has high version count. This script can be used on specific case. If versioncount > count there might be a problem.
set lines 500
select * from (
select
plan_hash_value,sum(version_count) as versioncount ,count(1)
from
v$sqlarea
group by plan_hash_value
order by sum(version_count) desc
) where rownum<11
;
PLAN_HASH_VALUE VERSIONCOUNT COUNT(1)
--------------- ------------ ----------
0 18895 4918
564703301 2835 2834
3095531958 2239 2239
1316043113 2234 2234
1788691278 609 125
989574962 465 465
1404353691 457 457
Analyzing shared pool and component
- Shared pool size by instance
select inst_id,round(sum(S.SHARABLE_MEM)/(1024*1024),1) as Memory_MB
from gv$sql s
group by inst_id
order by 2 desc;
INST_ID MEMORY_MB
---------- ----------
1 9852,6
2 7861,9
- Shared pool component size
select *
from gv$sgastat
where pool=’shared pool’
–and name=’free memory’
and inst_id=1
order by bytes desc;
INST_ID POOL NAME BYTES
---------- ------------ -------------------------- ----------
1 shared pool free memory 1060714008
1 shared pool KGLHD 229558520
1 shared pool SQLA 221058248
1 shared pool KQR L PO 191218792
1 shared pool ges resource 134750712
1 shared pool KGLH0 110085888
1 shared pool ASH buffers 104857600
1 shared pool ASM extent pointer array 90770352
1 shared pool KQR X PO 84272960
1 shared pool kkslLoadParentOnLock:lite 83647224
1 shared pool init_heap_kfsg 74839336
1 shared pool NUMA pool 0 73181032
1 shared pool event statistics per sess 60393984
1 shared pool KKSSP 56685344
1 shared pool dbktb: trace buffer 49152000
1 shared pool PRTDS 39276760
1 shared pool ges big msg buffers 31869448
1 shared pool KGLS 28888312
1 shared pool kglhdusr 19822008
- Historical shared pool free memory in each instance. We can use this query to compare when shared pool grow fast.
select HS.BEGIN_INTERVAL_TIME,ss.*
from DBA_HIST_SGASTAT ss ,dba_hist_snapshot hs
where pool=’shared pool’ and name=’free memory’
and SS.SNAP_ID=HS.SNAP_ID
and SS.INSTANCE_NUMBER=HS.INSTANCE_NUMBER
and ss.instance_number=1
–and HS.BEGIN_INTERVAL_TIME between to_date(’17-09-2019 13:00:00′,’dd-mm-yyyy hh24:mi:ss’) and to_date(’17-09-2019 15:30:00′,’dd-mm-yyyy hh24:mi:ss’)
order by ss.snap_id desc;
BEGIN_INTERVAL_TIME NAME POOL BYTES -------------------------- ---------------------------- ---------- 17/09/2019 15:15:52,808 free memory shared pool 1062792200 17/09/2019 15:00:27,628 free memory shared pool 1065576904 17/09/2019 14:46:00,338 free memory shared pool 1070302504 17/09/2019 14:30:31,065 free memory shared pool 1082045840 17/09/2019 14:15:11,962 free memory shared pool 1094113208 17/09/2019 14:00:21,753 free memory shared pool 1101234304 17/09/2019 13:45:32,422 free memory shared pool 1110825320 17/09/2019 13:30:15,446 free memory shared pool 1119258664 17/09/2019 13:15:45,254 free memory shared pool 1131666016 17/09/2019 13:00:11,987 free memory shared pool 1140481080
select HS.BEGIN_INTERVAL_TIME,ss.*
from DBA_HIST_SGASTAT ss ,dba_hist_snapshot hs
where pool=’shared pool’ and name=’free memory’
and SS.SNAP_ID=HS.SNAP_ID
and SS.INSTANCE_NUMBER=HS.INSTANCE_NUMBER
and ss.instance_number=2
–and HS.BEGIN_INTERVAL_TIME between to_date(’17-09-2019 13:00:00′,’dd-mm-yyyy hh24:mi:ss’) and to_date(’17-09-2019 15:30:00′,’dd-mm-yyyy hh24:mi:ss’)
order by ss.snap_id desc;
BEGIN_INTERVAL_TIME NAME POOL BYTES ------------------------------------------------------- ---------- 17/09/2019 15:15:52,735 free memory shared pool 1006936880 17/09/2019 15:00:27,675 free memory shared pool 1023507584 17/09/2019 14:46:00,372 free memory shared pool 1036382744 17/09/2019 14:30:31,037 free memory shared pool 1049451160 17/09/2019 14:15:12,002 free memory shared pool 1065164048 17/09/2019 14:00:21,703 free memory shared pool 1072838016 17/09/2019 13:45:32,494 free memory shared pool 1083137664 17/09/2019 13:30:15,419 free memory shared pool 1093108792 17/09/2019 13:15:45,217 free memory shared pool 1105843808 17/09/2019 13:00:11,963 free memory shared pool 1119044688
- Script to find the reasons why sql has high version count.
select version_count,a.sql_id,hash_value,parsing_schema_name,reason,sql_text from (
select
address,sql_id,”
||decode(max( UNBOUND_CURSOR),’Y’, ‘ UNBOUND_CURSOR’)
||decode(max( SQL_TYPE_MISMATCH),’Y’, ‘ SQL_TYPE_MISMATCH’)
||decode(max( OPTIMIZER_MISMATCH),’Y’, ‘ OPTIMIZER_MISMATCH’)
||decode(max( OUTLINE_MISMATCH),’Y’, ‘ OUTLINE_MISMATCH’)
||decode(max( STATS_ROW_MISMATCH),’Y’, ‘ STATS_ROW_MISMATCH’)
||decode(max( LITERAL_MISMATCH),’Y’, ‘ LITERAL_MISMATCH’)
–||decode(max( SEC_DEPTH_MISMATCH),’Y’, ‘ SEC_DEPTH_MISMATCH’)
||decode(max( EXPLAIN_PLAN_CURSOR),’Y’, ‘ EXPLAIN_PLAN_CURSOR’)
||decode(max( BUFFERED_DML_MISMATCH),’Y’, ‘ BUFFERED_DML_MISMATCH’)
||decode(max( PDML_ENV_MISMATCH),’Y’, ‘ PDML_ENV_MISMATCH’)
||decode(max( INST_DRTLD_MISMATCH),’Y’, ‘ INST_DRTLD_MISMATCH’)
||decode(max( SLAVE_QC_MISMATCH),’Y’, ‘ SLAVE_QC_MISMATCH’)
||decode(max( TYPECHECK_MISMATCH),’Y’, ‘ TYPECHECK_MISMATCH’)
||decode(max( AUTH_CHECK_MISMATCH),’Y’, ‘ AUTH_CHECK_MISMATCH’)
||decode(max( BIND_MISMATCH),’Y’, ‘ BIND_MISMATCH’)
||decode(max( DESCRIBE_MISMATCH),’Y’, ‘ DESCRIBE_MISMATCH’)
||decode(max( LANGUAGE_MISMATCH),’Y’, ‘ LANGUAGE_MISMATCH’)
||decode(max( TRANSLATION_MISMATCH),’Y’, ‘ TRANSLATION_MISMATCH’)
–||decode(max( ROW_LEVEL_SEC_MISMATCH),’Y’, ‘ ROW_LEVEL_SEC_MISMATCH’)
||decode(max( INSUFF_PRIVS),’Y’, ‘ INSUFF_PRIVS’)
||decode(max( INSUFF_PRIVS_REM),’Y’, ‘ INSUFF_PRIVS_REM’)
||decode(max( REMOTE_TRANS_MISMATCH),’Y’, ‘ REMOTE_TRANS_MISMATCH’)
||decode(max( LOGMINER_SESSION_MISMATCH),’Y’, ‘ LOGMINER_SESSION_MISMATCH’)
||decode(max( INCOMP_LTRL_MISMATCH),’Y’, ‘ INCOMP_LTRL_MISMATCH’)
||decode(max( OVERLAP_TIME_MISMATCH),’Y’, ‘ OVERLAP_TIME_MISMATCH’)
–||decode(max( SQL_REDIRECT_MISMATCH),’Y’, ‘ SQL_REDIRECT_MISMATCH’)
||decode(max( MV_QUERY_GEN_MISMATCH),’Y’, ‘ MV_QUERY_GEN_MISMATCH’)
||decode(max( USER_BIND_PEEK_MISMATCH),’Y’, ‘ USER_BIND_PEEK_MISMATCH’)
||decode(max( TYPCHK_DEP_MISMATCH),’Y’, ‘ TYPCHK_DEP_MISMATCH’)
||decode(max( NO_TRIGGER_MISMATCH),’Y’, ‘ NO_TRIGGER_MISMATCH’)
||decode(max( FLASHBACK_CURSOR),’Y’, ‘ FLASHBACK_CURSOR’)
||decode(max( ANYDATA_TRANSFORMATION),’Y’, ‘ ANYDATA_TRANSFORMATION’)
–||decode(max( INCOMPLETE_CURSOR),’Y’, ‘ INCOMPLETE_CURSOR’)
||decode(max( TOP_LEVEL_RPI_CURSOR),’Y’, ‘ TOP_LEVEL_RPI_CURSOR’)
||decode(max( DIFFERENT_LONG_LENGTH),’Y’, ‘ DIFFERENT_LONG_LENGTH’)
||decode(max( LOGICAL_STANDBY_APPLY),’Y’, ‘ LOGICAL_STANDBY_APPLY’)
||decode(max( DIFF_CALL_DURN),’Y’, ‘ DIFF_CALL_DURN’)
||decode(max( BIND_UACS_DIFF),’Y’, ‘ BIND_UACS_DIFF’)
||decode(max( PLSQL_CMP_SWITCHS_DIFF),’Y’, ‘ PLSQL_CMP_SWITCHS_DIFF’)
||decode(max( CURSOR_PARTS_MISMATCH),’Y’, ‘ CURSOR_PARTS_MISMATCH’)
||decode(max( STB_OBJECT_MISMATCH),’Y’, ‘ STB_OBJECT_MISMATCH’)
–||decode(max( ROW_SHIP_MISMATCH),’Y’, ‘ ROW_SHIP_MISMATCH’)
||decode(max( PQ_SLAVE_MISMATCH),’Y’, ‘ PQ_SLAVE_MISMATCH’)
||decode(max( TOP_LEVEL_DDL_MISMATCH),’Y’, ‘ TOP_LEVEL_DDL_MISMATCH’)
||decode(max( MULTI_PX_MISMATCH),’Y’, ‘ MULTI_PX_MISMATCH’)
||decode(max( BIND_PEEKED_PQ_MISMATCH),’Y’, ‘ BIND_PEEKED_PQ_MISMATCH’)
||decode(max( MV_REWRITE_MISMATCH),’Y’, ‘ MV_REWRITE_MISMATCH’)
||decode(max( ROLL_INVALID_MISMATCH),’Y’, ‘ ROLL_INVALID_MISMATCH’)
||decode(max( OPTIMIZER_MODE_MISMATCH),’Y’, ‘ OPTIMIZER_MODE_MISMATCH’)
||decode(max( PX_MISMATCH),’Y’, ‘ PX_MISMATCH’)
||decode(max( MV_STALEOBJ_MISMATCH),’Y’, ‘ MV_STALEOBJ_MISMATCH’)
||decode(max( FLASHBACK_TABLE_MISMATCH),’Y’, ‘ FLASHBACK_TABLE_MISMATCH’)
||decode(max( LITREP_COMP_MISMATCH),’Y’, ‘ LITREP_COMP_MISMATCH’)
reason
from
v$sql_shared_cursor –where sql_id=’1s4cu90p8sdab’
group by
address,sql_id
) a join v$sqlarea using(address) where version_count>10
order by version_count desc,address
;
I need some pointers on how to diagnose and fix this problem. I don’t know if this is a simple server setup problem or an application design problem (or both).
Once or twice every few months this Oracle XE database reports ORA-4031 errors. It doesn’t point to any particular part of the sga consistently. A recent example is:
ORA-04031: unable to allocate 8208 bytes of shared memory ("large pool","unknown object","sort subheap","sort key")
When this error comes up, if the user keeps refreshing, clicking on different links, they’ll generally get more of these kinds of errors at different times, then soon they’ll get «404 not found» page errors.
Restarting the database usually resolves the problem for a while, then a month or so later it comes up again, but rarely at the same location in the program (i.e. it doesn’t seem linked to any particular portion of code) (the above example error was raised from an Apex page which was sorting 5000+ rows from a table).
I’ve tried increasing sga_max_size from 140M to 256M and hope this will help things. Of course, I won’t know if this has helped since I had to restart the database to change the setting 
I’m running Oracle XE 10.2.0.1.0 on a Oracle Enterprise Linux 5 box with 512MB of RAM. The server only runs the database, Oracle Apex (v3.1.2) and Apache web server. I installed it with pretty much all default parameters and it’s been running quite well for a year or so. Most issues I’ve been able to resolve myself by tuning the application code; it’s not intensively used and isn’t a business critical system.
These are some current settings I think may be relevant:
pga_aggregate_target 41,943,040
sga_max_size 268,435,456
sga_target 146,800,640
shared_pool_reserved_size 5,452,595
shared_pool_size 104,857,600
If it’s any help here’s the current SGA sizes:
Total System Global Area 268435456 bytes
Fixed Size 1258392 bytes
Variable Size 251661416 bytes
Database Buffers 12582912 bytes
Redo Buffers 2932736 bytes
Problem
Oracle Error occurs when attempting to run a report against an Oracle datasource via SQL*Net.
Symptom
Error Message — Cognos 8 (English):
- RQP-DEF-0177 An error occurred while performing operation ‘sqlOpenResult’ status=’-28′.
UDA-SQL-0114 The cursor supplied to the operation «sqlOpenResult» is inactive.
UDA-SQL-0107 A general exception has occurred during the operation «open result».
ORA-04031: unable to allocate 81744 bytes of shared memory («shared pool»,»with «ARBEITSPLATZGRUPPEN» a…»,»Typecheck heap»,»qry_text : qcpisqt»)
RSV-SRV-0042 Trace back:
Error Message — Cognos 8 (German):
- RQP-DEF-0177 Fehler beim Ausführen der Operation ‘sqlOpenResult’ Status=’-28′.
UDA-SQL-0114 Der für Operation «sqlOpenResult» angegebene Cursor ist nicht aktiv.
UDA-SQL-0107 Allgemeiner Ausnahmefehler während der Operation «open result».
ORA-04031: unable to allocate 81744 bytes of shared memory («shared pool»,»with «ARBEITSPLATZGRUPPEN» a…»,»Typecheck heap»,»qry_text : qcpisqt»)
RSV-SRV-0042 Zurückverfolgen: . . .
Error Message — Impromptu (English):
- Error -9
DMS-E-GENERAL, A general exception has occurred during operation prepare request
ORA-04031 unable to allocate bytes of shared memory
Cause
SHARED_POOL_SIZE is too small.
Environment
Applies to all products using a Oracle database.
Resolving The Problem
This error is being returned from the Oracle database. Increase the SHARED_POOL_SIZE on the Database.
Steps:
The following is an extract from Oracle help:
ORA-04031-Unable to allocate num bytes of shared memory num, num, num
Cause: More shared memory is needed than was allocated in the operating system process. SGA private memory has been exhausted
Action: Either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value set for the SHARED_POOL_SIZE initialization parameter.
Please contact your DBA for further information on this error message.
Related Information
[{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Impromptu»,»Platform»:[{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»7.4;7.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}},{«Product»:{«code»:»SSEP7J»,»label»:»Cognos Business Intelligence»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud u0026 Data Platform»},»Component»:»Cognos Connection»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.0;8.1;8.2;8.3;8.4″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}},{«Product»:{«code»:»SSEP7J»,»label»:»Cognos Business Intelligence»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud u0026 Data Platform»},»Component»:»Report Studio»,»Platform»:[{«code»:»PF016″,»label»:»Linux»},{«code»:»PF033″,»label»:»Windows»},{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»»,»label»:»HP Itanium»},{«code»:»PF027″,»label»:»Solaris»}],»Version»:»8.0;8.1;8.2;8.3;8.4″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}},{«Product»:{«code»:»SUNSET»,»label»:»PRODUCT REMOVED»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud u0026 Data Platform»},»Component»:»ReportNet»,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»},{«code»:»PF016″,»label»:»Linux»}],»Version»:»1.1″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}},{«Product»:{«code»:»SSEP7J»,»label»:»Cognos Business Intelligence»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud u0026 Data Platform»},»Component»:» «,»Platform»:[{«code»:»PF016″,»label»:»Linux»},{«code»:»PF033″,»label»:»Windows»},{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»»,»label»:»HP Itanium»},{«code»:»PF027″,»label»:»Solaris»}],»Version»:»8.0;8.1;8.2;8.3;8.4″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}},{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:null,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»»,»label»:»HP-UX on PA-RISC»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»},{«code»:»PF030″,»label»:»TRU64 UNIX»}],»Version»:»7.4;7.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
Causes of ORA-04031
Found ORA-04031 in the alert log.
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","unknown object","sga heap","library cache")
Several kinds of memory problem can result in ORA-04031. As you can see, the above error complained specifically about the shared pool. Therefore, your database could be in one of the following situations:
Small shared pool size
If your database had survived through the most critical pressure ever, the size should be sufficient already, just leave it unchanged. Otherwise, you can raise the shared pool related parameters, such as MEMORY_TARGET (under AMM), SGA_TARGET (under ASMM), or SHARED_POOL_SIZE manually to solve ORA-04031.
You may refer to Oracle 19c documentation for more details about memory management:
- AMM: Enabling Automatic Memory Management
- ASMM: Enabling Automatic Shared Memory Management
- Manually: Using Manual Shared Memory Management
Fragmented shared pool
This could be the most common consequences of high library cache during peak hours. There’re too many small cursors generated and allocated scatteringly in the shared pool. In this post, I will focus on the solutions to this type of ORA-04031.
Solutions to ORA-04031
Flushing shared pool.
In urgent situations, you can flush shared pool to remove all unused cursors and make the database functional, you will have a peace time for several hours before next ORA-04031. Yes, just several hours, because the root cause is still there.
This action does no harm to your database, but it may take a while to complete. Please don’t interrupt it, and just be patient with it.
SQL> alter system flush shared_pool;
System altered.
Flushing shared pool can ease your problem, but it’s not the real cure to this repeated ORA-04031. You have to find out the root cause. And, the root cause usually points to badly written SQL statements or PL/SQL.
Setting CURSOR_SHARING parameter to any other value beside EXACT.
For example, SIMILAR can make similar statements in literals to share the same cursor in order to reduce the usage of shared pool.
The available values of Oracle 12.1 or later releases are:
CURSOR_SHARING = { EXACT | FORCE }
The available values before Oracle 12.1 are:
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
This solution could save some space of shared pool, but transactions might be a little slower than ever. So, it’s a trade-off. Besides, Oracle also warns that Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix of ORA-04031.
Using bind variables in SQL statements.
When Oracle executes a badly written application which does not use bind variables, slightly different statements in literals may cause enormous amount of small cursors, this makes shared pool tight and fragmented.
Using bind variables by rewriting SQL statements makes your applications (e.g. Stored procedures, Java objects) flexible and dynamic to do millions of execution with the same parsed SQLs, which forces the millions of executions to share one or little cursors and optimizes the shared pool more contiguous. This could be the real cure to ORA-04031.
For example, a parsed SQL statement with bind variables looks like this:
SQL_TEXT
-------------------------------------------------------
SELECT * FROM hr.data_table WHERE sn = :b1 and bn = :b2
In the above statement, :b1 and :b2 represent two compiled bind variables. For more information about binding variables in PL/SQL, please refer to: PL/SQL Dynamic SQL
Many DBAs are unsure how to respond to this error, here is how I usually troubleshoot it.
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^a31a067e","kglHeapInitialize:temp")
Immediate workaround: Flush the shared pool
In some cases a flush of the Shared Pool will temporarily fix the condition. However, this may not be very effective in the case of a “KGH: NO ACCESS” memory buildup. Also, your system will suffer temporary performance degradation in the library cache including a spike in hard parsing.
alter system flush shared_pool
Troubleshooting: Use the MOS ORA-04031 troubleshooting tool
Run adrci to get the first occurence of the ORA-04031 and upload that trace file in to the troubleshooting tool.
oracle@aix092:/app/oracle/orcl/admin/diag/rdbms/orcl/ORCL/trace
$adrci
ADRCI: Release 11.2.0.4.0 - Production on Sat Sep 29 12:15:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ADR base = "/app/oracle/orcl/admin"
adrci> SHOW INCIDENT
ADR Home = /app/oracle/orcl/admin/diag/rdbms/orcl/ORCL:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
460698 ORA 4031 2018-09-28 15:48:03.990000 -04:00
463090 ORA 4031 2018-09-28 15:48:04.023000 -04:00
463242 ORA 4031 2018-09-28 22:06:19.094000 -04:00
460386 ORA 4031 2018-09-28 22:06:20.004000 -04:00
4 rows fetched
Fri Sep 28 15:48:03 2018
ORA-04031 heap dump being written to trace file /app/oracle/orcl/admin/diag/rdbms/orcl/ORCL/trace/ORCL_e004_32637012.trc
Fri Sep 28 15:48:04 2018
Errors in file /app/oracle/orcl/admin/diag/rdbms/orcl/ORCL/trace/ORCL_ora_31457498.trc (incident=463090):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^a31a067e","kglHeapInitialize:temp")
Read the output, see if it make sense for your system
Primary Issue:
SGA Auto Tuner Stressed Out
“KGH: NO ACCESS” is an indicator of the amount of memory resizing being done between the shared pool and the buffer cache when ASMM or AMM is enabled.
Shortage in SGA free memory and a high frequency of SGA memory resizing requests overburdens the Autotuner with high frequency of resizing operations. This results in the exhuastion of free memory to store the administrative data for managing incomplete resize operations, thus the occurrence of ORA-04031.
This issue could occur if in “Memory Utilization of All Subpools” of your trace file, “KGH: NO ACCESS” is the top memory consumer.
Recommended Solution
1) Clear the problem by restarting the database.
The auto memory manager will attempt to resize the dynamic SGA components frequently which leads to a “buildup” of the ‘KGH: NO ACCESS’. This “buildup” can only be freed immediately by restarting the instance.
NOTE: An outage of the database will need to be scheduled and in cases of RAC this could affect many nodes. A flush of the Shared Pool will not affect these memory allocations.
2) Research known bugs associated with auto-tuning issues
In some cases the “buildup” of these allocation can be due to bugs that cause the frequent resizes of memory in the SGA. Refer to the notes below for actions to take on appropriate database releases.
* 10g – Document 801787.1 NOTE:801787.1 – Common Cause for ORA-4031 in 10gR2, Excess “KGH: NO ACCESS” Memory Allocation
* 11g – Document 1127833.1 NOTE:1127833.1 – ORA-04031 in 11g & 11gR2, Excess “KGH: NO ACCESS” Memory Allocation
3) If your problem does not match known issues, consider these steps to prevent the “buildup” of ‘KGH: NO ACCESS’:
Tune the Auto-Tuner:
a. Increase SGA_TARGET or MEMORY_TARGET by 15% and monitor resize operations using the view V$SGA_RESIZE_OPS. See Document 781630.1 NOTE:781630.1 – How to configure ASMM (Auto Memory) on 10g and 11g – Best Practices
and
b. Set minimum values for the Shared Pool and Database Buffer Cache. Setting minimum values ensures those component cannot be shrunk below the defined level and may reduce resize operations.
A rule of thumb for setting minimum settings is to use values of 60% of typical memory usage for the various auto-tuned components in the SGA. For example, if you see the Shared Pool grows and shrinks but typically using about 800M, you would set the explicit setting for SHARED_POOL_SIZE to 480M.
NOTE: Changing the setting for SGA_TARGET or MEMORY_TARGET will require a database outage. The exception is if SGA_MAX_SIZE is larger than SGA_TARGET or MEMORY_MAX_TARGET is larger than MEMORY_TARGET. Setting minimum values for the various auto-tuned components can adjusted without an outage of the database.
or
Disable Auto-tuning:
a. Configure the database to use manual memory management. Since ‘KGH: NO ACCESS’ only occurs when auto memory management is enabled, disabling this functionality will prevent these memory allocation. Disable auto memory management by setting SGA_TARGET=0 and additionally on 11g MEMORY_TARGET=0
References:
Document 430473.1 NOTE:430473.1 – ORA-4031 Common Analysis/Diagnostic Scripts [Video]
Document 396940.1 NOTE:396940.1 – Troubleshooting and Diagnosing ORA-4031 Error [Video]
SGA_TARGET and 11.2.0.4
I’ve had this issue in many of my EE 11.2.0.4 databases. The root cause was a bug in the frequent resizing of the shared pool and the solution was to manually set the size of the shared pool and disable the automatic sizing of the SGA, and restart the instance:
-- -- Manually Set Memory Pools in the SGA -- Numbers are based on AWR memory advisor -- ALTER SYSTEM SET DB_CACHE_SIZE=10G SCOPE=SPFILE; ALTER SYSTEM SET SHARED_POOL_SIZE=5g SCOPE=SPFILE; ALTER SYSTEM SET STREAMS_POOL_SIZE=1G SCOPE=SPFILE; ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE=350M SCOPE=SPFILE; ALTER SYSTEM SET JAVA_POOL_SIZE=100M SCOPE=SPFILE; ALTER SYSTEM SET LARGE_POOL_SIZE=100M SCOPE=SPFILE; -- -- Disable Auto SGA and Memory Tuning -- ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE; ALTER SYSTEM SET SGA_MAX_SIZE=0 SCOPE=SPFILE; ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE; ALTER SYSTEM SET MEMORY_MAX_SIZE=0 SCOPE=SPFILE; -- -- Set PGA if not already done -- ALTER SYSTEM SET PGA_AGGREGATE_TARGET=5G SCOPE=SPFILE;
Published by PL/SQL DBA
Oracle DBA and PL/SQL developer specializing in Database Tuning, PL/SQL, Licenses, GoldenGate, Shell Scripting, OID
View all posts by PL/SQL DBA
The answer above is a little old.
alter system flush shared_pool;
May alleviate the problem right away but not deal with the root cause.
the cursor_sharing parameter now takes 2 values:
FORCE or EXACT.
With EXACT, your query will have to match exactly for a cursor (sql with execution plan ) to be reused, with «FORCE», all values in your queries are changed to bind variables. This is really handy when your application doesn’t use bind variables. The database will do that for you.
If you are already using cursor_sharing=FORCE. Then you need to start considering adjusting your memory allocationand depending on the memory parameters your system use, you can obtain an idea of where you should set that value with the following queries:
When SGA_TARGET and SGA_MAX__SIZE /PGA_TARGET and PGA_MAX_SIZE are used (they should match):
select * from v$sga_target_advice;
select * from v$pga_target_advice;
It would look like this:
In my case this is a pre prod system, it won’t look this good for long:
This is the SGA_TARGET_ADVICE:
The row where SGA_SIZE_FACTOR is 1, is the current setting.
In a system where memory is undersized, the increase in SGA_SIZE and SGA_SIZE_FACTOR should show a dramatic decrease in ESTD_DB_TIME and ESTD_DB_TIME_FACTOR. Increase the memory to a point where increasing again doesn’t make such a big change to the estd_db_time anymore.
In my case here, with my current load, I could easily reduce the size of SGA_TARGET to 80G without suffering a big hit. However reducing it to 57G and less would produce more and more dramatic performance issues.
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
5760 .0625 482104 .5257 50950730 76032 18176 0
11520 .125 482104 .5257 50950730 76032 18176 0
17280 .1875 482104 .5257 50950730 76032 18176 0
23040 .25 482104 .5257 50950730 76032 18176 0
28800 .3125 1560028 1.7011 174592866 6912 19456 0
34560 .375 1374046 1.4983 138703172 13824 18176 0
40320 .4375 1105895 1.2059 87207269 20736 18176 0
46080 .5 1028769 1.1218 72319466 27648 17664 0
51840 .5625 1000157 1.0906 66607889 34560 16896 0
57600 .625 980623 1.0693 62628637 41472 16128 0
63360 .6875 976129 1.0644 62628637 41472 21248 0
69120 .75 961456 1.0484 59805967 48384 21248 0
74880 .8125 945683 1.0312 56626641 55296 19456 0
80640 .875 933852 1.0183 54359334 62208 19456 0
86400 .9375 923765 1.0073 51867843 71424 16640 0
92160 1 917070 1 50950730 76032 18176 0
97920 1.0625 910467 .9928 49534300 82944 17408 0
103680 1.125 903131 .9848 47914066 89856 16640 0
109440 1.1875 896528 .9776 46385545 96768 15872 0
115200 1.25 891575 .9722 46385545 96768 21248 0
120960 1.3125 886990 .9672 45361435 103680 19456 0
126720 1.375 884331 .9643 44851928 110592 19456 0
132480 1.4375 880937 .9606 44194663 117504 19456 0
138240 1.5 875252 .9544 42915800 124416 18176 0
144000 1.5625 870116 .9488 41901880 131328 17920 0
149760 1.625 867915 .9464 41901880 131328 23040 0
155520 1.6875 867181 .9456 41769408 138240 23040 0
161280 1.75 866723 .9451 41769408 138240 28416 0
167040 1.8125 866631 .945 41769408 138240 32000 0
172800 1.875 866631 .945 41769408 138240 32000 0
178560 1.9375 866631 .945 41769408 138240 32000 0
184320 2 866631 .945 41769408 145152 32000 0
32 rows selected.
When MEMORY_TARGET is used:
select * from v$memory_target_advice;
The answer above is a little old.
alter system flush shared_pool;
May alleviate the problem right away but not deal with the root cause.
the cursor_sharing parameter now takes 2 values:
FORCE or EXACT.
With EXACT, your query will have to match exactly for a cursor (sql with execution plan ) to be reused, with «FORCE», all values in your queries are changed to bind variables. This is really handy when your application doesn’t use bind variables. The database will do that for you.
If you are already using cursor_sharing=FORCE. Then you need to start considering adjusting your memory allocationand depending on the memory parameters your system use, you can obtain an idea of where you should set that value with the following queries:
When SGA_TARGET and SGA_MAX__SIZE /PGA_TARGET and PGA_MAX_SIZE are used (they should match):
select * from v$sga_target_advice;
select * from v$pga_target_advice;
It would look like this:
In my case this is a pre prod system, it won’t look this good for long:
This is the SGA_TARGET_ADVICE:
The row where SGA_SIZE_FACTOR is 1, is the current setting.
In a system where memory is undersized, the increase in SGA_SIZE and SGA_SIZE_FACTOR should show a dramatic decrease in ESTD_DB_TIME and ESTD_DB_TIME_FACTOR. Increase the memory to a point where increasing again doesn’t make such a big change to the estd_db_time anymore.
In my case here, with my current load, I could easily reduce the size of SGA_TARGET to 80G without suffering a big hit. However reducing it to 57G and less would produce more and more dramatic performance issues.
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
5760 .0625 482104 .5257 50950730 76032 18176 0
11520 .125 482104 .5257 50950730 76032 18176 0
17280 .1875 482104 .5257 50950730 76032 18176 0
23040 .25 482104 .5257 50950730 76032 18176 0
28800 .3125 1560028 1.7011 174592866 6912 19456 0
34560 .375 1374046 1.4983 138703172 13824 18176 0
40320 .4375 1105895 1.2059 87207269 20736 18176 0
46080 .5 1028769 1.1218 72319466 27648 17664 0
51840 .5625 1000157 1.0906 66607889 34560 16896 0
57600 .625 980623 1.0693 62628637 41472 16128 0
63360 .6875 976129 1.0644 62628637 41472 21248 0
69120 .75 961456 1.0484 59805967 48384 21248 0
74880 .8125 945683 1.0312 56626641 55296 19456 0
80640 .875 933852 1.0183 54359334 62208 19456 0
86400 .9375 923765 1.0073 51867843 71424 16640 0
92160 1 917070 1 50950730 76032 18176 0
97920 1.0625 910467 .9928 49534300 82944 17408 0
103680 1.125 903131 .9848 47914066 89856 16640 0
109440 1.1875 896528 .9776 46385545 96768 15872 0
115200 1.25 891575 .9722 46385545 96768 21248 0
120960 1.3125 886990 .9672 45361435 103680 19456 0
126720 1.375 884331 .9643 44851928 110592 19456 0
132480 1.4375 880937 .9606 44194663 117504 19456 0
138240 1.5 875252 .9544 42915800 124416 18176 0
144000 1.5625 870116 .9488 41901880 131328 17920 0
149760 1.625 867915 .9464 41901880 131328 23040 0
155520 1.6875 867181 .9456 41769408 138240 23040 0
161280 1.75 866723 .9451 41769408 138240 28416 0
167040 1.8125 866631 .945 41769408 138240 32000 0
172800 1.875 866631 .945 41769408 138240 32000 0
178560 1.9375 866631 .945 41769408 138240 32000 0
184320 2 866631 .945 41769408 145152 32000 0
32 rows selected.
When MEMORY_TARGET is used:
select * from v$memory_target_advice;