Re: High PGA memory usage fetching into Varrays
Date: Mon, 14 Jan 2008 14:02:41 -0800 (PST)
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):
to_char(decode( unit,'bytes', value/1024/1024, value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit from
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.
-- @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 ThompsonReceived on Mon Jan 14 2008 - 16:02:41 CST