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.

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

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.

sideyt wrote:
What is your SGA size? How much memory in the box? what is the EXACT
error message - there are many relating to "out of memory". Sounds like
you have some tuning to do and/or upgrading the hardware to be able to
accommodate the workload it is being asked to do.

sideyt wrote:
What is your SGA size? How much memory in the box? what is the EXACT
error message - there are many relating to "out of memory". Sounds like
you have some tuning to do and/or upgrading the hardware to be able to
accommodate the workload it is being asked to do.

sideyt wrote:
What is your SGA size? How much memory in the box? what is the EXACT
error message - there are many relating to "out of memory". Sounds like
you have some tuning to do and/or upgrading the hardware to be able to
accommodate the workload it is being asked to do.

sideyt wrote:
What is your SGA size? How much memory in the box? what is the EXACT
error message - there are many relating to "out of memory". Sounds like
you have some tuning to do and/or upgrading the hardware to be able to
accommodate the workload it is being asked to do.

On Jan 10, 7:43*am, sideyt <sid... (AT) hotmail (DOT) com> wrote:
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.

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.

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

On Jan 10, 7:43*am, sideyt <sid... (AT) hotmail (DOT) com> wrote:
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.

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.

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

On Jan 10, 7:43*am, sideyt <sid... (AT) hotmail (DOT) com> wrote:
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.

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.

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

On Jan 10, 7:43*am, sideyt <sid... (AT) hotmail (DOT) com> wrote:
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.

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.

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