- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- ORA-04031 Error + Library Cache
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-04-2007 12:27 AM
тАО07-04-2007 12:27 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-04-2007 12:46 AM
тАО07-04-2007 12:46 AM
Re: ORA-04031 Error + Library Cache
alter system flush shared_pool;
This would clean it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-04-2007 01:08 AM
тАО07-04-2007 01:08 AM
Re: ORA-04031 Error + Library Cache
we are flushing the Shared Pool, but its not solving the problem.
This is due to fragmented memory.
Can we have some other solution?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-04-2007 08:19 PM
тАО07-04-2007 08:19 PM
Re: ORA-04031 Error + Library Cache
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-12-2007 09:15 AM
тАО07-12-2007 09:15 AM
SolutionThe 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-12-2007 03:38 PM
тАО07-12-2007 03:38 PM
Re: ORA-04031 Error + Library Cache
Thanks for your reply.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-12-2007 04:05 PM
тАО07-12-2007 04:05 PM
Re: ORA-04031 Error + Library Cache
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-12-2007 04:06 PM
тАО07-12-2007 04:06 PM
Re: ORA-04031 Error + Library Cache
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-12-2007 04:11 PM
тАО07-12-2007 04:11 PM
Re: ORA-04031 Error + Library Cache
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