Re: ORA-04031 with Collections and SGA Settings - Part II

On Sat, 01 Jul 2006 20:01:57 GMT, "Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote:

>After my first post I took another look at the memory settings for the
>Oracle instance where I work. This time using the web version of the
>Enterprise Manager and came up with these settings:
>
>-- SGA --
>
>Shared Pool 752MB
>Buffer Cache 2960MB
>Large Pool 208MB
>Java Pool 64MB
>Other (MB) 54
>Total SGA 4038MB
>
>Max SGA 5904MB
>
>-- PGA --
>
>Aggregate PGA Target 205MB
>Current PGA Allocated 50555Kb
>Maximum PGA Allocated 6613600
>
>Cache Hit% 49.85
>
>---------------------
>
>Are these settings normal or is there something off in them that would
>explain why I consistently ran out of memory when trying to use a collection
>of about only 972,000 records?
>
>Any assistance/comments would be appreciated.
>
>Thanks.
>

A question that can't be answered completely without knowing two things:
What 'cache hit ratio' is being displayed? How much physical RAM is present in the server.

Other than that, these setting should scare the heck out of anyone. Buffer cache of almost 3G is ridiculous. Other memory: probably the log_buffer of 54 Mb???? However decided to set those values doesn't have a clue about Oracle, and never read the Concepts Manual.

I would however assume setting up a collection of 972000 records is yet another misuse of Oracle, attempting to work around other problems, which are a result of plain incompetency. I do not know how much memory a record theoretically would use, b ut you should be able to make an estimate as to how much PGA is required. However, it looks like you are trying to set up a sort of buffer cache outside the SGA. That is something which isn't the purpose of a collection.

--
Sybrand Bakker, Senior Oracle DBA