Stored Procedures consuming Virtual Memory

[armsiee]

Hi,

IDS9.31HC5, HP-UX11i (PA-RISC) on an HP system container running under Itanium

This is part of a migration project and thus no chance of a systems upgrade.

I have a suite of stored procedures which are reading data out of one set of tables and transforming and manipulating the data for loading into a new set of tables in order to conform to a new DB Schema or around 25 entities

There are c. 100 procedures which will either do the reading / manipulating / or inserting for each of the rows processed. At low levels 1000-2000 rows I am seeing relatively good performance, however significant level of degradation kicks in past this point.

When the process is initially kicked off memory usage is at:

session #RSAM total used
id user tty pid hostname threads memory memory
153 dba 2 2705 dev 1 1404928 1327920

However after a couple of hours processing:

session #RSAM total used
id user tty pid hostname threads memory memory
153 dba 2 2705 dev 1 13398016 13025480

Finally ends up with an Assert error in the buffer manager.

I have tried updating stats for key tables and procedures at a 1000 row intervals to no avail.

Data per row is stored in temp tables which are cleared down as each row is processed as a single transaction with the data being committed or rolled back if it passes the internal validation requirements.

Any advice gratefully received.

[s***@t-online.de]

Unfortunatly not much...

you state that performance starts to slow down when more then 2000 rows are processed

What is OPTCOMPIND set to. Temp tables and indexes and optcompind =2 will do seq scans. set Explain will tell you.
If this is the problem you may try and set it to 0 and process more rows in one go may be workaround the problem.


You have to start debugging and try to pinpoint what is causing a leak.
may be onstat -g stm sid will give you a hint..

for what it is worth Informix does consume memory when dealing with spl and does not really like to release it.


Superboer

Post by armsiee
Hi,
IDS9.31HC5, HP-UX11i (PA-RISC) on an HP system container running under Itanium
This is part of a migration project and thus no chance of a systems upgrade.
I have a suite of stored procedures which are reading data out of one set of tables and transforming and manipulating the data for loading into a new set of tables in order to conform to a new DB Schema or around 25 entities
There are c. 100 procedures which will either do the reading / manipulating / or inserting for each of the rows processed. At low levels 1000-2000 rows I am seeing relatively good performance, however significant level of degradation kicks in past this point.
session #RSAM total used
id user tty pid hostname threads memory memory
153 dba 2 2705 dev 1 1404928 1327920
session #RSAM total used
id user tty pid hostname threads memory memory
153 dba 2 2705 dev 1 13398016 13025480
Finally ends up with an Assert error in the buffer manager.
I have tried updating stats for key tables and procedures at a 1000 row intervals to no avail.
Data per row is stored in temp tables which are cleared down as each row is processed as a single transaction with the data being committed or rolled back if it passes the internal validation requirements.
Any advice gratefully received.