ORA-4031 Analysis and Diagnosis

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/02/24
-- # Name: ORA-4031 Analysis and Diagnosis
--  ################################################
-- #
-- # Compatible: Oracle 8i 9i 10g 11g
-- #
-- ################################################
-- #
-- # Steps to find and solve fragmentation issues
-- # in the shared pool after ORA-4031 raised
-- #
-- ################################################
 

"ORA-4031 - Unable to allocate bytes of shared memory"
ORA-4031 is a very common error that many dba's face in their day to day activities. This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to  fragmentation of the shared pool. 


- Increase Shared_Pool if too small (and therefore SGA_TARGET)


- _shared_pool_reserved_pct = 10 (default parameter value = 5%,  10% is better and advised by Oracle)
alter system set "_shared_pool_reserved_pct"= 10 scope = spfile;


- begin traces:
alter system set events '4031 trace name errorstack level 3: 4031 trace name HEAPDUMP level 536870914 ';


- Stop traces:
alter system set events '4031 trace name HEAPDUMP off ';


- Trace analysis:
Search query / module has asked for the memory


- Run SGAStat query every 30 minutes to check the evolution of dispatch of memory:

SELECT *
FROM v $ sgastat
WHERE name IN ('free memory', 'db_block_buffers', 'log_buffer'
'dictionary cache', 'sql area', 'library cache');



- Search queries that are not shared:
	Cf script: "unshared_queries.sql" (Tuning/SQL)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.