Table cast and memory consumption

From: Luc Gyselinck <Luc.Gyselinck_at_nospampandora.be>
Date: Fri, 05 Sep 2003 21:39:53 GMT
Message-ID: <Jo76b.2941$8J3.66626@phobos.telenet-ops.be>

Hi,

We are running 8.1.7.4 on Sun Solaris.

We have the following function :

CREATE TYPE quantities AS OBJECT
( IssuedQty NUMBER,
  LostQty NUMBER,
  CountedQty NUMBER
)
/

CREATE TYPE quantities_t AS TABLE OF quantities
/

CREATE OR REPLACE FUNCTION getQties(key IN NUMBER) RETURN quantities_t IS
  r quantities_t := quantities_t(null);
BEGIN
  r.extend;

• call the functions to retrieve the quantities for the given key r(1) := quantities(getIssuedQty(key),getLostQty(key),getCountedQty(key)); return r; END;
  /
  

CREATE TABLE t
( key number,
  ....
)
/

Now, I used all the above stuff in a query:

SELECT

  t.key,
  q.IssuedQty,
  q.CountedQty,
  q.LostQty

FROM
  t, table(cast(getQties(key) as quantities_t)) q WHERE

• some filter clauses on the table t q.IssuedQty > q.CountedQty
  /
  

The functions to calculate each quantity are somehow very complex.

I used this approach to be sure the functions are only called once for each row found in the driving table and as last filter in the where clause. This reduced the estimated time of the query (see longops) from 2 hours to 20 minutes.

The only problem is that the PGA memory consumption starts low and is ever increasing while the query is running, until the process ends (with an ORA-3113 error), while other users on the database reported ORA-4030 (memory exhausted) errors. It looks like there is a memory leak at each call to the table(cast(...)) expression, but I can not find any hints or bug reports at METALINK. vmstats shows a decreasing free memory stat.

Is there something wrong with my construction or am I hitting a BUG?

Any ideas appreciated. Received on Fri Sep 05 2003 - 16:39:53 CDT