Update Stats for procedure uses too much Virt memory.

After running update statistics for tables in a database, we run update statistics for procedure to update the SP's, using thefollowing: set isolation to dirty read; set pdqpriority 0; update statistics for procedure;commit; There are about 400 SP's and some are complex calling other SP's. This uses a lot of Virtual shared memory and will add shared memory segments which are freed after this process runs. Why does running update stats on SP's use so much V memory? What can be done to keep this from using so much memory? Machine and Version info: Linux version 2.6.9-42.ELlargesmp (bhcompile@ls20- bc1-13.build.redhat.com) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-2)) SHMVIRTSIZE 12582912 onstat -g seg before running update stats. IBM Informix Dynamic Server Version 11.10.FC2 -- On-Line -- Up 56 days 16:41:22 -- 56058360 Kbytes Segment Summary: id key addr size ovhd class blkused blkfree 8093696 525d4801 44000000 44518858752 522136952 R* 10868859 3 8126465 525d4802 aa187e000 12884901888 150996880 V 386888 2758840 Total: - - 57403760640 - - 11255747 2758843 (* segment locked in memory) While update stats for procedures is running: 01:00:17 Dynamically allocated new virtual shared memory segment (size 2097152KB) 01:00:17 Memory sizes:resident:43475448 KB, virtual:14680064 KB,SHMTOTAL:62914560 KB 01:00:21 Dynamically allocated new virtual shared memory segment (size 2097152KB) 01:00:21 Memory sizes:resident:43475448 KB, virtual:16777216 KB,SHMTOTAL:62914560 KB 01:00:25 Dynamically allocated new virtual shared memory segment (size 2097152KB) 01:00:25 Memory sizes:resident:43475448 KB, virtual:18874368 KB,SHMTOTAL:62914560 KB 01:00:29 Size of resident + virtual segments 43475448KB + 20971520KB> 62914560KB 01:00:29 total allowed by configuration parameter SHMTOTAL SHMTOTAL is capped at 62914560 Only env change during the update stats run is: export DBUPSPACE=0:50:1 export PSORT_NPROCS=20 Thanks

Get my dostats utility. It will recompile each routine individually which tends to take up much less memory that doing them all in a single statement as you have below. Dostats is contained in the package utils2_ak which you can download from the IIUG Software Repository. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 23, 2009 at 8:03 AM, Roy Mercer <roy.mercer@gmail.com> wrote: > After running update statistics for tables in a database, we run > update statistics for procedure to update the SP's, using the> following: > > set isolation to dirty read; > set pdqpriority 0; > update statistics for procedure;> commit; > > There are about 400 SP's and some are complex calling other SP's. > > > This uses a lot of Virtual shared memory and will add shared memory > segments which are freed after this process runs. > > Why does running update stats on SP's use so much V memory? > What can be done to keep this from using so much memory? > > > Machine and Version info: > Linux version 2.6.9-42.ELlargesmp (bhcompile@ls20- > bc1-13.build.redhat.com) (gcc version 3.4.6 20060404 (Red Hat > 3.4.6-2)) > > SHMVIRTSIZE 12582912> > onstat -g seg before running update stats. > IBM Informix Dynamic Server Version 11.10.FC2 -- On-Line -- Up 56 days > 16:41:22 -- 56058360 Kbytes> > Segment Summary: > id key addr size ovhd class > blkused blkfree > 8093696 525d4801 44000000 44518858752 522136952 R* > 10868859 3 > 8126465 525d4802 aa187e000 12884901888 150996880 V > 386888 2758840 > Total: - - 57403760640 - - > 11255747 2758843 > > (* segment locked in memory) > > > While update stats for procedures is running: > 01:00:17 Dynamically allocated new virtual shared memory segment > (size 2097152KB) > 01:00:17 Memory sizes:resident:43475448 KB, virtual:14680064 KB,> SHMTOTAL:62914560 KB > 01:00:21 Dynamically allocated new virtual shared memory segment > (size 2097152KB) > 01:00:21 Memory sizes:resident:43475448 KB, virtual:16777216 KB,> SHMTOTAL:62914560 KB > 01:00:25 Dynamically allocated new virtual shared memory segment > (size 2097152KB) > 01:00:25 Memory sizes:resident:43475448 KB, virtual:18874368 KB,> SHMTOTAL:62914560 KB > 01:00:29 Size of resident + virtual segments 43475448KB + 20971520KB> > 62914560KB > 01:00:29 total allowed by configuration parameter SHMTOTAL> > SHMTOTAL is capped at 62914560> > > > Only env change during the update stats run is: > export DBUPSPACE=0:50:1 > export PSORT_NPROCS=20 > > Thanks > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >