High PGA memory usage fetching into Varrays

From: sideyt <sideyt_at_hotmail.com>
Date: Thu, 10 Jan 2008 07:43:52 -0800 (PST)
Message-ID: <7e18d02a-eec7-4a5d-9631-fda863543b8f@s19g2000prg.googlegroups.com>

  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. Received on Thu Jan 10 2008 - 09:43:52 CST

Original text of this message