Tweaking _Shared_pool_reserved_min_alloc and ORA – 4031

Here comes one more article dedicated towards diagnosing ORA- 4031 and related parameters. While googling on ORA-4031 error or working on TAR with Oracle Support you will most likely hit this parameter “_Shared_pool_reserved_min_alloc “ and find a advice for altering this parameter to 4000 or so. And many times you go and implement it as this advice has come from Oracle support or you find it on one of your Favourite Oracle DBA’s site.
But how many times have you exactly gone ahead and tried to understand the validity of this suggestion.

Oracle Shared pool is managed by Heap Memory manager and is divided into memory chunks (Can visualize as blocks in tablespace) and the free memory chunks are managed by memory freelists. When a SQL statement is parsed in shared pool , it requires memory for storing execution plan and other internal structures. When the execution is complete the memory is freed and released back to the freelist. This repated use and release of memory causes fragmentation in shared pool.

Now algorithm for memory allocation is such that it will first try to get memory from the shared pool and then if the requested memory size is greater then _Shared_pool_reserved_min_alloc , then it will get the memory from Shared Pool Reserved area. By default this value is set to 4400 bytes. You can use below sql to find the current value for your database

col Parameter format a40
col “Instance Value” format a25
select a.ksppinm “Parameter”, b.ksppstvl “Value” from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like (‘%shared%’);

Suppose you get error like

ORA-04031: unable to allocate 4200 bytes of shared memory (“shared pool”,”unknown object”,”sga heap”,”state objects”)

Here we see failure size is 4200 bytes. In this case if you set _Shared_pool_reserved_min_alloc to 4100 then it will make memory available from Shared_pool_reserved_size and ORA -4031 will be avoided. But note that this is just to delay the ORA – 4031 error (Temporary Relief) and will not resolve the error. To actually resolve the issue you need to have a look at application and see if you are using Bind Variables so that you can reuse the sql statements and avoid shared pool fragmentation. You can read following discussion on AskTom Website to know more about Bind variables

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832

Note: – “_Shared_pool_reserved_min_alloc ” cannot be set below value of 4000

Recently while starting up database, I came across following error which was quite cryptic and took lot of time to resolve

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

This was contradicting the statement which I made above but I still tried to set it to less value but it errored out. On further investigation it turned out that there was blank space between M and amount of memory specified for one of memory parameters. i.e

DB_CACHE_SIZE=1200 M

Whereas it should have been

DB_CACHE_SIZE=1200M

Hope you see my post before wasting lot of time 🙂

Tags: databaseora-4031