Re: ORA-04031 with Collections and SGA Settings

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