Re: ORA-04031 with Collections and SGA Settings
- From: "Dereck L. Dietz" <dietzdl@xxxxxxxxxxxxx>
- Date: Thu, 29 Jun 2006 23:02:33 GMT
"Sybrand Bakker" <postbus@xxxxxxxxxxxxxxxxx> wrote in message
news:jfl8a2psfme3drpcpqk8v4a9qqlkv3hrce@xxxxxxxxxx
On Thu, 29 Jun 2006 21:04:35 GMT, "Dereck L. Dietz"to
<dietzdl@xxxxxxxxxxxxx> wrote:
This is a two part question and I hope I've included enough information
muchelicit 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
follows:of a time savings I could achieve. My steps for the test were as
and
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
databasefrom concatenations and conversions of collection #1 data.
3. I then used a FORALL to insert collection #2 into the final
andtable.
4. I only was able to insert 450,561 rows before I ran out of memory
(forreceived 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
sizea total size of about 422MB) and rows in collection #2 are 446 bytes in
If(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)
2. Do the settings for the SGA raise any questions/comments/concerns?
so, what would better settings be?The settings you specify here are too weird to be true.
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
Sybrand - thank you for confirming what I have suspected for a while.
Unfortunately I'm a contractor there so I can't just come out and say it's
hosed. It's an uphill battle just to get them to consider using external
tables much less getting away from their "standard" of using tables to store
results of queries to be used to create even more tables.
Would you happen to have any suggestions - even generalized ones - for the
SGA settings or should I also get the PGA settings and post those too?
.
- References:
- ORA-04031 with Collections and SGA Settings
- From: Dereck L. Dietz
- Re: ORA-04031 with Collections and SGA Settings
- From: Sybrand Bakker
- 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: Which Doc to Read - 10g or 9i ?
- Previous by thread: Re: ORA-04031 with Collections and SGA Settings
- Next by thread: How to recover any SYSDBA user?
- Index(es):
Relevant Pages
|
