High PGA memory usage fetching into Varrays
Date: Thu, 10 Jan 2008 07:43:52 -0800 (PST)
- 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.
- 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.
- 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