Analyzing ORA-4031 In Oracle Database 19c

 

Possible Reason :

1) Too many Child Cursors 

2)  In memory  enabled 

3)  Undersized Sga 

4)  Pinned objects  in shared pool 

5)  Memory Fragmentation 

6) Too many hard parsing ,  

 

Possible Solution : 

1) Use  bind variables 

2)   Set enough  Sga 

References : 

1) https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C 

2) Troubleshooting: High Version Count Issues (Doc ID 296377.1)

#####################################

Logs to Collect 

#####################################

1) Tfa 

2) Using customized  sql 

2)  Using  srdc_db_ora4031.sql  retrieved from 2232371.1

Use Tfa 

$TFA_HOME/bin/tfactl diagcollect -srdc ora4031

Use your  customized  sql 

spool /tmp/memory_status.txt <<<<<< Call out the database name

set linesize 200

col VALUE for a50

set pagesize 100

set pagesize 100

col Parameter for a50

col "Session Value" for a50

col "Instance Value" for a50

column component format a25

column Initial format 99,999,999,999

column Final format 99,999,999,999

column Started format A25

select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx and a.ksppinm in

('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');

select * from v$sgastat where pool like '%shared%' order by bytes;

select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;

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;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;

select * from v$pga_target_advice;

spool off;