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.
>

Adding to that: a shared pool of 752 Mb probably points to a '[parse once, execute once' approach. This usually result in ora-4030 errors. However you are not going to resolve those by bumping the shared pool, ever!!!

--
Sybrand Bakker, Senior Oracle DBA