Re: Update statistics bug

In article <34B11569.5053@swbell.net>, Vickey Crouch <matcom2@swbell.net> writes >Hello, everyone! > >I have a very strange problem that I'm hoping someone can help me with. >I am running Informix on-line (both 7.12 and 7.23) on a Sun platform. I >have a variety of unrelated jobs (i.e. PB apps that run stored >procedures, ESQL/COBOL programs that insert data, etc,) that spawn >multiple threads and use up memory rapidly, but only periodically. This >is a relatively new problem, although the jobs themselves have been in >place for some time. If I run an 'update statistics' against the >database in question, the problem seems to go away SOMETIMES. I've >tried high, medium, low, etc., but so far haven't found any consistent >pattern. The output from onstat -g ses xxx looks shows several threads, >which look something like this: > >NAME STATUS >sqlexec cond wait(sm_read) >group_1. cond wait(await_MC1) >join_2.0 cond_wait(await_MC2) >join_2.1 cond_wait(await_MC2) >hjoin_3. cond_wait(await_MC3) >join_4.0 cond_wait(await_MC4) >join_4.1 cond_wait(await_MC4) >scan_5.0 cond_wait(await_MC5) >scan_6.0 cond_wait(await_MC6) > >Why are all of these different threads running? What do they mean? >What is, for example, MC1? An even bigger qestion is, is there some >documentation available that REALLY explains the onstat output? No! At this sort of level you need to ask Technical Support. I guess these threads are sqlexec = main session thread group_1. = threead used for group by join_ = threads used ofr joins hjoin_ = thread used for Dynamic Hash Joins If you have X Windows try running OnPerf and examining the session. In your ONCONFIG file make sure OPTCOMPIND=0 to avoid Dynamic Hash Join i.e. sequential scans of big tables... > >It looks like Informix is grabbing more memory every time an sql >statement is executed, even though the sql is either a stored procedure >or a prepared statement. > >I would really appreciate some help. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care