Re: High PGA memory usage fetching into Varrays

On Jan 10, 7:43 am, sideyt <sid..._at_hotmail.com> wrote:
> 1)  What are the real bytes per value (RAM) associated with each
> element of a Varray of NUMBER? (Of INTEGER, of PLS_INTEGER, of
> VARCHAR2(20) etc).We are using arrays with more than 1 million
> elements and often get PGA usage in the gigabytes.
>
> 2) What else (besides sorting) contributes to high PGA usage? We often
> do bulk collect (with row limits) of SDO_geometries and this seems to
> easily generate out of memory errors unless we use small row limits
> like 500.

Have support help you with heap dumps to really see what is going on. Also google for what Jonathan Lewis has written on pga_aggregate_target. You may need to be handling the pga sizing manually simply because of what you do. If the memory issue is too big, you may want to _decrease_ sort area size to force more passes. Remember you can do things differently per session, you may want to futz with things for your specific app. Metalink Note:223730.1 is a pretty good primer.

>
> 3) Are there any strategies (besides
> dbms_session.free_unused_user_memory ) to free memory and keep PGA
> memory usage lower?

From metalink Note:284951.1 (please ignore what it says about hit ratios):

select
name,
to_char(decode( unit,'bytes', value/1024/1024, value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit from
v$pgastat
/

Then check out mult-pass usage, either through OEM or google for how to check it.

If you are doing indexed access before or between your in-memory stuff, that free_unused_user_memory procedure may simply be what you need.

>
> Our environment is 64 bit Blade servers running Linux: Oracle Database
> 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
> I would prefer real answers to these questions - not avoidance answers
> like "don't use Varrays".  We are performing spatial algorithms in
> core - and are using PL/SQL successfully except for this
> limitation.Thank you.

If you are using RAC, there are mysterious unpublished bugs about PGA memory leaks in 10.2.0.3.0. Who knows if they apply to non-RAC? You might consider pushing support for a fix if you can demonstrate a problem in the latest patch set.

Note:304215.1 is kind of interesting.

jg

--
@home.com is bogus.
One poll's prediction of who would win in New Hampshire:
78% Ron Paul
8% John McCain
6% Mike Huckabee
5% Mitt Romney
2% Rudy Giuliani
1% Fred Thompson