Tom,

Could you tell us the concept of Extents and Chunks in
shared pool. Is extent a contiguous space comprised of
multiple chunks by Forward coalescing mechanism of
adjacent free chunks.

Are the chunks connected by doubly linked list to previous
and next free chunk in the free list. Also if the memory is
managed in 4000 - 5000 bytes chunks, what sceanrio will
cause ORA 4031. According to the shared pool memory
management, Oracle can always allocate multiple chunks
if contiguous memory is not available

Thanks

Hi Tom,
I would like your comment on this. It's a metalink note DocId: 146559.1 "Diagnosing and Resolving Error ORA-04031"

"...
Shared Pool Fragmentation:
Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the shared pool requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chuck size is larger than the required size
- The space is contiguous
- The chuck is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation, the allocation of a piece of free space takes much more time generating slow performance in the database (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation) or ORA-04031 errors (when the database cannot find a contiguous piece of free memory).
...
The ORA-04031 is a result of lack of contiguous space in the shared pool reserved space...
..."

Doesn't this say that specific amount of FREE CONTIGUOUS space is needed to load an object into memory (?), which differs from "4k chunks" from the post above...

??

Thanks...

Ok!
Sorry, it was DocId 146599.1 with the title "Diagnosing and Resolving Error ORA-04031".
There, it says nothing about 4k chunks to manage memory (?), or am I getting this wrong?

Thanks!

Dear TOM,
I have heard about Oracle Clinical and want to know about it but unable to find any info about it. Please give some info about it or any link if you have. Thanks

Hi

Which version is this 4k chunks assignment of memory introduced? In one customer their production support has a rule, no packages bigger than 128k in size, if bigger split in two, they say this is because they used to get lots of ORA-04031 in Oracle 7...

Hi,
Does it mean that 9i shared pool cannot become fragmented because it uses something like LMT (onky in memory) and if I get ora-4031 in 9i it is caused by lack of space ?

hi tom ,

i am trying to create a table based on a view.
the view is based on xml document.
here is the view description.

CREATE OR REPLACE VIEW VW_MEPOutputData
AS
SELECT
  FileID                                                                                           FileID
, SUBSTR(extractvalue(value(MeasPoint), '/MeasPoint/Name'), 1, 255)   ||'('||
  SUBSTR(extractvalue(value(MeasPoint), '/MeasPoint/@id'), 1, 255)    ||')'                        LoadPoints
, SUBSTR(extractvalue(value(MeasPointStep),    '/MeasPointStep/@id'), 1, 255)                      LoadPointSteps
, SUBSTR(extractvalue(value(SetVar),           '/SetVar/ViewName'), 1, 255)                        SetVariable
, SUBSTR(extractvalue(value(VSV),              '/VarSetValue/@Step'), 1, 10)                      SetStep
, SUBSTR(extractvalue(value(VSV),              '/VarSetValue/VarSet/SetValue'), 1, 10)            SetValue
, SUBSTR(extractvalue(value(SetVar),           '/SetVar/SetState'), 1, 10)                        SetState
, SUBSTR(extractvalue(value(SetVar),           '/SetVar/VarDevice'), 1, 255)                       SetVarDevice
, SUBSTR(extractvalue(value(VSV),              '/VarSetValue/VarSet/SetTime'), 1, 10)             SetTime
, SUBSTR(extractvalue(value(MeasVar),          '/MeasVar/ViewName'), 1, 255)                       MeasVariable
, SUBSTR(extractvalue(value(VMV),              '/VarMeasValue/@Step'), 1, 10)                     MeasStep
, SUBSTR(extractvalue(value(VMV),              '/VarMeasValue/VarMeas/MeasValue'), 1, 10)         MeasValue
, SUBSTR(extractvalue(value(MeasVar),          '/MeasVar/MeasState'), 1, 10)                      MeasState
, SUBSTR(extractvalue(value(MeasVar),          '/MeasVar/MeasState'), 1, 255)                      MeasVarDevice
, SUBSTR(extractvalue(value(VMV),              '/VarMeasValue/VarMeas/MeasTime'), 1, 10)          MeasTime
FROM  TabL_FileContent f
, table(xmlsequence(extract(f.ResultData, '/MEPOutput/MEPOutputData/Sequences/Sequence'))) Sequence
, table(xmlsequence(extract(value(Sequence), '/Sequence/MeasPoint'))) MeasPoint
, table(xmlsequence(extract(value(MeasPoint),'/MeasPoint/MeasPointSteps'))) MeasPointSteps
, table(xmlsequence(extract(value(MeasPointSteps),'/MeasPointSteps/MeasPointStep'))) MeasPointStep
, table(xmlsequence(extract(value(MeasPointStep), '/MeasPointStep/SetPhase/SetVar'))) SetVar
, table(xmlsequence(extract(value(SetVar), '/SetVar/VarSetValues/VarSetValue'))) VSV
, table(xmlsequence(extract(value(MeasPointStep),'/MeasPointStep/MeasPhase/MeasVar'))) MeasVar
, table(xmlsequence(extract(value(MeasVar), '/MeasVar/VarMeasValues/VarMeasValue'))) VMV
/


when i try to create the table i get such an error.

SQL>  create table test_u as select * from vw_mepoutputdata where 1=2;
 create table test_u as select * from vw_mepoutputdata where 1=2
                                                               *
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 44 bytes
(callheap,temporary memory)

i increased the shared pool size and still get the error.

Total System Global Area 1074865868 bytes
Fixed Size                   456396 bytes
Variable Size            1048576000 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL> select value
  2  from v$parameter
  3  where name like 'shared_pool_size';

VALUE
---------------------------------------------
738197504

can you please help me. i have used substr becuase other wise it takes a default of varchar2(4000) for each column. 

Hi tom ,

i have logged a tar and seems like the problem is a bug.
here are the bug numbers given by Oracle support.

3132291/2806660

Tom,

In one of the above discussion, there is a ORA -04030 error related to process memory. If my understanding and memory is correct,

1. This happens because the process is not able to allocate PGA memory. Is it correct?
2. If my understanding on 1 is correct, then using PGA_AGGREGATE_TARGET should solve this problem because PGA_AGGREGATE_TARGET specificies the total of all PGA memory that an instance can use (that is the maximum PGA memory for the whole instance, rather than session wise). Correct?
3. Even if PGA_AGGREGATE_TARGET is set to a large value and still we get this error what should we look for as to why this error is happening?

Thanks

Hi,

So, am i correctlly understanded, that shared pool fragmentation is no longer concern starting from Oracle8?

Then, setting %reserved% init.ora parameters, pinning using DBMS_SHARED_POOL.KEEP is no longer meaningfull topic
for preventing ORA-4031?
for performance tuning?

And i should forget that stuff from Steve Adams?
</code> http://www.ixora.com.au/q+a/pool.htm#end <code>

Sincerely,

Hi Tom

I am on Oracle 8i (8.1.7.0)/Windows 2K Server/ RAID 5 dual processor server with 4gb RAM.Application is in D2K. Until recently, the database and application was working just about fine.There have been changes in the infrastructure of the network with the intro of thin clients using terminal services (RDP) and some problems with the network.

Since, the following errors have crept up.

ORA-04030 Out of Process Memory
Especially when running reports.

TNS-12500 Listener failed to start a dedicated server
Especially when using SQL-Loader and otherwise also.

FRM error giving a NO AUTHORIZATION message

Redo Space Wait Ratio 8.70

Chained Fetch Ratio 0.14
User tablespaces are LMTs

Parse to Execute Ratio 35.6

CPU Parse Overhead 100.00


I feel that a lot of the problems are not database related and I do not want to un-necessarily modify any parameters. Could you please give me some pointers to analyse, work on and remedy the problems?

K.Rajeev

Hi,
I am also facing the similar type of error:
ORA-04030: out of process memory when trying to allocate 917536 bytes (joxcx callheap,ioc_allocate ufree)
and ORA -12500 when trying to connect through toad or sqlplus.
I am working on Oracle 9i R2, Windows 2000 Advanced Server. Application is Java.
When the application servers were restarted, I was able to connect to DB. Is this related to the number of concurrent connections that hit the database?
Is there any restrictions on connections to the db?