Re: ORA-04031 with Collections and SGA Settings

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:jfl8a2psfme3drpcpqk8v4a9qqlkv3hrce_at_4ax.com...
> On Thu, 29 Jun 2006 21:04:35 GMT, "Dereck L. Dietz"
> <dietzdl_at_ameritech.net> 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)
> >
> >
> >2. Do the settings for the SGA raise any questions/comments/concerns?
If
> >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? Received on Thu Jun 29 2006 - 18:02:33 CDT