We're using Oracle 10g and 11g on Windows. We have a client-server app
written in PowerBuilder. We do logic on the PBuilder side and with PL/
SQL.

We have a process that will loop through many records (about a
thousand). For each record, it must "process it" meaning do additional
queries across various tables with it, etc. Once done with a record,
it is considered done with that record and goes to the next.

We notice as that each record is processed, the memory consumption of
the oracle database oracle.exe keeps increasing. Eventually it crashes
the session by either disconnecting the session or giving something
similar to an Ora-04031 error (usually the 04031 error itself).

My guess is that the as the record is processed, it consumes memory to
do it. But then it never releases it before going to the next record.
Is there a way to force it to do this?

I know this is a vague description of a scenario, but i'm hoping it's
all that is needed. I hope the solution doesn't involve re-writing our
code or increasing the amount of memory on the machine running the
server. From what I see, it can process one record fine (actually,
more than one, the effects don't bog down the oracle db machine until
it hits the 300th record or so). I just want the memory to "start
fresh" with each record or so. But it just seems to keep consuming
more and more.

Something we notice... If we simply disconnect the sqlca object (the
transaction object the PowerBuilder application uses to connect to the
database) and then simply re-connect, say, every 100 records or so...
the problem goes away. We simply disconnect, re-connect, and pick up
at the point where we left off. This shows me the memory gets flushed
every time the session is disconnected.

This is the effect that I want... for the memory to be flushed every
so many records, so it can continue looping through each record in the
resultset as if it were doing the first one each time. I understand
there may be a performance impact as it flushes the memory for each
record (or every hundred or so), but I'm willing to sacrifice that to
keep it from running out of memory altogether.

Luch wrote:

Dump PowerBuilder - it even tries to use it's own locking
mechanism. Oracle is quite good at that, and does not
require locks on reads.

What you are witnessing is a PB thing.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...