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
;
Ali KAPAR's DATABASE BLOG 