Ora-04031 Unable To Allocate 316 Bytes Of Shared Memory

HELLO ORACLE GURU'S

I AM FACING A STRANGE PROBLEM I MY DATABASE I HAVE 2 VIEWS :
PENDING_ANX_IV_POST_VIEW & PENDING_ANX_V_POST_VIEW.
WHEN EVER I USE TO RUN THESE VIEWS THEY BOTH GIVE SAM ERROR :

ORA-04031: unable to allocate 316 bytes of shared memory
("large pool","unknown object","cursor work he","kdiixs1 : kdipc")

HOWEVER WHEN I VIEW SHARED POOL AND LARGE POOL THE STATISTICS IS :


SGA CACHE STATISTICS
********************
SQL Cache Hit rate = 99.73
Dict Cache Hit rate = 92.03
Buffer Cache Hit rate = 99.93
Redo Log space requests = 1
Total SGA = 292.69

INIT.ORA SETTING
****************
Shared Pool Size = 149380352 Bytes
DB Block Buffer = 47202 Blocks
Log Buffer = 32768 Bytes



Main SGA Areas NAME SUM(BYTES)
-------------------- -------------------- ----------------
DB Buffer Cache db_block_buffers 386,678,784
Shared Pool shared pool 158,447,628
Large Pool large pool 40,405,560
Redo Log Buffer log_buffer 65,536
Fixed SGA fixed_sga 65,484
----------------
585,662,992


Shared Pool Areas NAME SUM(BYTES)
-------------------- -------------------- ----------------
Shared Pool free memory 133,477,124
Shared Pool miscellaneous 14,100,744
Shared Pool sql area 5,939,240
Shared Pool library cache 4,290,964
Shared Pool dictionary cache 639,556
----------------
sum 158,447,628




I AM ATTACHING SOURCE CODE OF BOTH VIEWS. WHEN I USE TO RUN THESE VIEWS BY MAKING PART OF IT. THEY RUN SUCCESFULLY. I AM NOT GETTING WHAT IS THE PROBLEM.

PLEASE HELP WHAT IS THE REASON.

REGARDS

GURPREET SINGH SETHI

What do you mean by "RUN A VIEW"??

It is probably because the SGA has become fragmented to the point
that there is not enough contiguous space for the object to be loaded.
As objects are aged out of the SGA, they leave "holes". Objects have to have contigous space in order to be loaded into SGA... You need to
1. reboot the database in order to clear the problem (flushing shared pool doesn't do it)
2. Look at the procs, functions, etc that are loaded into the SGA, their size and the number of executions and the reloads of them ... Find the problem ones and PIN them in SGA...

To determine what large PL/SQL objects are currently loaded in the shared pool
and are not marked 'kept' and therefore may cause a problem, execute the following:


select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where sharable_mem > 100
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;


--- Objects that have been reloaded ---

select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where loads > 1
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;


HTH
Gregg

Increase your large pool. If that does not fix your problem, set this event in your init.ora:

event = "4031 trace name errorstack level 3"

It will create a dump file when the error occurs next. Send the dump to oracle support.

Your large pool is not large enough.