Re: Slow procedure execution after re-optimised

In article <34D8F5A2.AAA@britannic.co.uk>, Paul Tonkin <tonkinp@britannic.co.uk> writes >We are running Online 7.23 on Dynix/ptx 4.1.3 and I have identified a >peculiar problem. We make heavy use of Stored Procedures and have a >performance problem with one in particular. The procedure queries a >couple of 2million row tables and takes about 2.5 minutes. If I run an >update statistics on the two tables the query comes down to 5 seconds.> >This might seem obvious at first. However if I then update statistics for >the procedure the time goes back to 2.5minutes. This can be reproduced >each time. The table statistics are obviously up to date but simply >re-optimising the procedure slows it down again! > >In addition the fast runtime reduces to sub-second if it is repeated >immeadiately - I assume because the data is now in buffers. The slow >runtime can be repeated endlessly and will not perform any quicker, as if >the data is either not in the buffers or online doesn't realise it is. > >These tests were performed on a 'quite' machine/instance with 62,500 >buffers and included some tests after online re-starts to flush the >buffers.OPTCOMPIND is 0 but other values don't seem to make any >difference. > Log it with Informix. Several people have reported problems with stored procedure optimization. It may be a bug. >Any ideas? >Thanks in advance, >Paul Tonkin DBA. -- 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