Re: ORA-04031 with Collections and SGA Settings
- From: Sybrand Bakker <postbus@xxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Jun 2006 00:40:36 +0200
On Thu, 29 Jun 2006 21:04:35 GMT, "Dereck L. Dietz"
<dietzdl@xxxxxxxxxxxxx> wrote:
This is a two part question and I hope I've included enough information to
elicit responses.
I am working with a data warehouse using an Oracle 10g R1 database.
I attempted a quick test of collections to see if it would work and how much
of a time savings I could achieve. My steps for the test were as follows:
1. I used a BULK COLLECT to read 978,218 rows into collection #1.
2. I then created collection #2 from data directly in collection #1 and
from concatenations and conversions of collection #1 data.
3. I then used a FORALL to insert collection #2 into the final database
table.
4. I only was able to insert 450,561 rows before I ran out of memory and
received the ORA-04031 error stating the system was unable to allocate
225432 bytes of shared memory.
I ran this test twice. Once with deleting collection #1 after I had
finished creating collection #2 and once without deleting the collection.
Both times I received the ORA-04031 error.
I should also add that the rows in collection #1 are 449 bytes in size (for
a total size of about 422MB) and rows in collection #2 are 446 bytes in size
(for a total size of about 417MB).
When I checked how the SGA was configured these were the settings:
SHARED POOL: 752 MB
BUFFER CACHE: -1191182336 B
LARGE POOL: 208 MB
JAVA POOL: 64 MB
TOTAL SGA: 3988.108 MB
SGA MAX SIZE: 5904 MB
The database server is also configured as a shared server.
The questions I would like to ask are these:
1. I should be able to use Oracle collections to retrieve, modify and
save data, correct?
Yes, collections are however allocated in the *pga*, so in this case
(using shared server in the large pool)
The settings you specify here are too weird to be true.
2. Do the settings for the SGA raise any questions/comments/concerns? If
so, what would better settings be?
Look at the negative buffer cache alone!!
Something is utterly wrong.
Also a total SGA of almost 6 Gb looks ridiculously big, assuming it is
all going to the buffer cache.
Then of course you might have only 8 Gb of RAM and just swamp the
system by paging and faulting. So, if that is true, be prepared it
will melt down soon and/or raise it's white flag.
--
Sybrand Bakker, Senior Oracle DBA
.
- Follow-Ups:
- Re: ORA-04031 with Collections and SGA Settings
- From: Dereck L. Dietz
- Re: ORA-04031 with Collections and SGA Settings
- References:
- ORA-04031 with Collections and SGA Settings
- From: Dereck L. Dietz
- ORA-04031 with Collections and SGA Settings
- Prev by Date: Re: Where to get oracle patch 9.2.0.4 and 9.2.0.5
- Next by Date: Re: Where to get oracle patch 9.2.0.4 and 9.2.0.5
- Previous by thread: Re: ORA-04031 with Collections and SGA Settings
- Next by thread: Re: ORA-04031 with Collections and SGA Settings
- Index(es):
Relevant Pages
|
