Discussion:
Stored Procedures consuming Virtual Memory
(too old to reply)
armsiee
2015-07-20 11:38:43 UTC
Permalink
Raw Message
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
2015-07-20 19:26:05 UTC
Permalink
Raw Message
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.
Loading...