Problem with delete

Hi! ~200 Process delete records from the same table and bolb. We use cursor for delete. We have remark: Informix allocates many 16Mbyte share memory segment while there is on the winchester free spaces. If one Process end, the allocated share memory segment will be not free. Have you any idea, why makes Informix this? Could it be the Problem, that the cursor was not free? When the process ends, does it make the cursor free? If not: When will be the cursor free? with best regards Gabor.

These are additional Virtual Segments. Informix does not free them because it assumes that they will be needed again and allocating shared memory is expensive and hangs the server for several seconds while all the VPs connect to the new segment. If you know the segments will not be needed for a while, in effect this delete was a periodic thing and not constant, then run onmode -F to ask the engine to remove any virtual segments it is no longer using. However, of this job runs frequently consider folding the space into the initial virtual segment by adding their combined size to the SHMVIRTSIZE onconfig parameter. You did not specify platform but if you run in HP this is a neccessity as the processor can only deal with four shared memory segments at a time. Accessing more than 4 requires the 4 special purpose shared memory base address registers to thrash which severely degrades performance of the server. Art S. Kagel Papp Gabor wrote: > > Hi! > > ~200 Process delete records from the same table and bolb. > We use cursor for delete. > We have remark: > Informix allocates many 16Mbyte share memory segment > while there is on the winchester free spaces. > If one Process end, the allocated share memory segment will be not free. > > Have you any idea, why makes Informix this? > > Could it be the Problem, that the cursor was not free? > When the process ends, does it make the cursor free? > If not: When will be the cursor free? > > with best regards > > Gabor.

In addition to what Art said: You may also want to increase the size of SHMADD when increasing the size of SHMVIRTSIZE. It is sometimes suggested you make SHMADD from 20 - 25 percent the size of SHMVIRTSIZE as to create fewer non-primary Virtual segments when additional memory is requested. Some will add the onmode -F in a script file to be executed by cron every hour in an attempt to rid the engine of extra segments. I wish I would have kept a copy of Reece's White Paper dealing with SHMVIRTSIZE. I don't remember if it was proprietary or not. (Until recently, I worked for INFORMIX.) It basically stated that all ports excluding HP start experiencing degradation upon the addition of the third Virtual segment; HP started experiencing the problem with the second segment. This is another reason you should make SHMVIRTSIZE and SHMADD higher than the defaults. I would take the total of all of your Virtual segments (from the onstat -g seg command), divide it by 1024 to get Kbytes and then add an additional 20 percent as a growth and safety margin and use that for SHMVIRTSIZE. Take 25 percent of that for SHMADD. Whle we are discussing Shared Memory segments, you may want to check your Machine-Specific release notes and see if you can set the ONCONFIG configuration parameter RESIDENT to 1. If your shared memory is paging, this will lock down the RESIDENT portion to not page. Under 7.3, you can also set the variable to -1 to prevent all segments from paging in and out of memory. To test whether your instance may use this parameter (if available), you can use the onmode -r command to set RESIDENT to 1, use onmode -n to turn if off. Take care. Clifton Bean PS. Cursors are not freed in applications unless you specifically do it using the FREE command. Terminating the application will FREE the memory as well. This only sets the memory free within the Virtual segment, it does not release the Virtual segment itself. CB Art S. Kagel <kagel@bloomberg.net> wrote in message news:37A1D2CB.476A40DA@bloomberg.net... > These are additional Virtual Segments. Informix does not free them because > it assumes that they will be needed again and allocating shared memory is > expensive and hangs the server for several seconds while all the VPs connect > to the new segment. If you know the segments will not be needed for a while, > in effect this delete was a periodic thing and not constant, then run onmode -F> to ask the engine to remove any virtual segments it is no longer using. > However, of this job runs frequently consider folding the space into the > initial virtual segment by adding their combined size to the SHMVIRTSIZE > onconfig parameter. You did not specify platform but if you run in HP this is > a neccessity as the processor can only deal with four shared memory segments > at a time. Accessing more than 4 requires the 4 special purpose shared memory > base address registers to thrash which severely degrades performance of the > server. > > Art S. Kagel > > Papp Gabor wrote: > > > > Hi! > > > > ~200 Process delete records from the same table and bolb. > > We use cursor for delete. > > We have remark: > > Informix allocates many 16Mbyte share memory segment > > while there is on the winchester free spaces. > > If one Process end, the allocated share memory segment will be not free. > > > > Have you any idea, why makes Informix this? > > > > Could it be the Problem, that the cursor was not free? > > When the process ends, does it make the cursor free? > > If not: When will be the cursor free? > > > > with best regards > > > > Gabor.