Re: Optimizer misbehaving

In article <6fepb0$h77$1@news.xmission.com>, Steve Romankiw <sromankiw@execrisk.com> writes > >We are experiencing some "very strange" behavior with our optimizer. We >are running ODS v7.23UC1 on Solaris 2.5.1.. > >Each night, we run our optimizing routines (from a 4gl app). The >routines that we use are based upon Informix's recommendation. For >example, > >UPDATE STATISTICS MEDIUM FOR TABLE claimant DISTRIBUTIONS ONLY ; >UPDATE STATISTICS HIGH FOR TABLE claimant >(claimant_name,claim_no,claimant_id);> >Over the last week and a half (or so), we have seen a performance get >real bad. In our development environment, we noticed that the execution >plan was different than production/test. Hum!! Verified that indexes >existed in both environments. > Can you show us the execution plans, how many rows per table and how unique are any indexes? >Here's is where is gets interesting. We ran MEDIUM FOR TABLE..DIST ONLY >in both test and development environments and each returned the same >execution plan and also acceptable performance. Running the HIGH for >each column against each environment once again changed the execution >plan. We also tried fiddling around with OPTCOMPIND. No change in >performance. > >Have you experienced strange behavior with the HIGH option? For your >update statistics, do you follow informix's recommendation? If you have>had to alter there recommendation, what did you do? > >Thanks in advance... >-- >----------------------------------------------------------------- >Steve Romankiw + >Executive Risk Inc. + >DBA + email: sromankiw@execrisk.com >82 Hopmeadow Street + work: (860) 408-2474 >Simsbury,CT 06070 + fax: (860) 408-2139 >----------------------------------------------------------------- > > -- 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