Re: Slow procedure execution after re-optimised

Have you used "set explain on" to see what execution path its choosing? The output may give some indication. cheers denzil In article <34D8F5A2.AAA@britannic.co.uk>, tonkinp@britannic.co.uk says... > 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. > > Any ideas? > Thanks in advance, > Paul Tonkin DBA. > -- > ------------------------------------------ > The views expressed here are mine and not > those of my employer, Britannic Assurance > ------------------------------------------ >