ORA-04031 with Collections and SGA Settings
- From: "Dereck L. Dietz" <dietzdl@xxxxxxxxxxxxx>
- Date: Thu, 29 Jun 2006 21:04:35 GMT
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?
2. Do the settings for the SGA raise any questions/comments/concerns? If
so, what would better settings be?
.
- Follow-Ups:
- Re: ORA-04031 with Collections and SGA Settings
- From: Sybrand Bakker
- Re: ORA-04031 with Collections and SGA Settings
- From: stephen O'D
- Re: ORA-04031 with Collections and SGA Settings
- Prev by Date: Re: trigger is firing in wrong schema
- Next by Date: Re: How to modifty the first chr of a field
- Previous by thread: How to modifty the first chr of a field
- Next by thread: Re: ORA-04031 with Collections and SGA Settings
- Index(es):
Relevant Pages
|
