ORA-04031 Error + Library Cache

[ManojMac]

Hi,

We are getting the ORA-04031 error while running our application on Database.

The exact error we get is:
Unable to allocate 4216 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","library cache")

As we found from the various sources, this is due to fragmented SGA, or insufficient Shared Memory.

Our init.ora Parameter for Shared Pool are:

shared_pool_reserved_size=52428800
shared_pool_size=524288000
java_pool_size=262144000

We are executing the SELECT query which return 8,00,000 rows. We are also having one other query which executed 8,00,000 times.

Is this SELECT Statement can cause this error?

As we are getting the Notification related to Library cache, is it related to fragmented Library Cache?

Please help us in resolving this issue.

Thanks in Advance.

[Jannik]

you could flush the shared pool in the weekend :-)
alter system flush shared_pool;

This would clean it.

[ManojMac]

Hi Jannik,

we are flushing the Shared Pool, but its not solving the problem.

This is due to fragmented memory.

Can we have some other solution?

[Jannik]

This should solv your problem???
These two errors are mainly caused by shared pool allocation error, or process creation error. See below SHARED_POOL ERROR (ORA-04030) and PROCESS CREATION/ALLOCATION ERROR (ORA-04031).

Sometimes Oracle send this error message not because the SHARED_POOL is full (or too small), but simply because it is too fragmented, so Oracle cannot allocate the needed memory, since it cannot find non fragmented memory area.

You can solve this problem simply executing a shared pool memory flush. In this way, you clear this memory area; it means it is defragmented.

This links tells even more:
http://ale870.pbwiki.com/ResolveORA-04031

Allsow how to detect when the flush is needed.

Oracle Corporation recommends that you set the SHMMAX parameter value to the amount of available physical memory on the system. Doing this ensures that the entire shared memory for a single Oracle instance is assigned to one shared memory segment and your instance needs only one protection key. To display the list of active shared memory segments on the system, enter the following command:

$ ipcs -m

If the Oracle server creates more than six segments for the instance, increase the value of the SHMMAX kernel parameter.

[Ben Dehner · Accepted solution]

A single select statement -- whether executed 8,000,000 times or returning 8,000,000 rows -- will not cause your problems. Because each of these are a single object that is stored in the shared pool library cache. (Assuming you're using parameterized SQL, that is ...)

The shared pool servers two basic functions: the library cache and session data. (It actually does a lot more, but I don't want to go there.) How much memory it needs for these things really depends on two things: 1) your application design, and 2) the number of sessions that you have.

The dependence on sessions should be obvious. As for the application design, if your application uses a lot of PL/SQL, or your sessions open a lot of cursors, then there are going to be a lot of objects in the library cache. And, while the system flushes out the shared pools with a LRU algorithm, if particular PL/SQL procedure is being executed, it can't get flushed out. In addition, if the application does not use sharable SQL, then there are just more objects that need to be in the shared pool library cache.

At the end of the day, your base problem is that the shared pool needs to be larger. Flushing the shared pool might temporarily alleviate the symptoms, but as procedures get executed and sessions connect, it will just get fragmented again. Other posters have discussed the relevent kernel settings for having a large shared pool.

[ManojMac]

HI Ben,
Thanks for your reply.

[Yogeeraj_1]

Hi,

It could also be related to the fact that your application is not using Bind Variables.

Simply speaking, you will never have a good hit ratio if everyone submits UNIQUE sql. Your SHARED POOL will never be used right and you will be using excessive CPU (90% of the time it takes to process "insert into mytable values ( 'a' )" is parsing. If you use "insert into mytable values ( :x )", and bind the value of 'a' -- then the next person that runs that insert will benefit from your work and run that much faster.

hope this helps!
kind regards
yogeeraj

[skt_skt]

Please asign some points to people who gave the suggetions.

[Yogeeraj_1]

hi again,

Below a script quote from Tom Kyte which describes how to query the statements that are identical in the shared pool.


create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/


The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed. These statements -- and more importantly their counts -- are the potential bottlenecks. In
addition to causing the contention, they will be HUGE cpu consumers.



Hope this helps too!
kind regards
yogeeraj