what kind of update statistics

I have read the relevent pages in the online informix doc on update statistics but not sure which to run each night. Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 so i dont think we have to worry about distributions as no data fragmentation. With online 5 we ran plain and simple "update statistics". When I first imported the databases into the new version, I ran : "update statistics high for table "dba".agent_companies (agcompid_ref,status_ref); for each table once. Now I only run "update statistics" each night. Should i be doing "update statistics high/low/medium" or what ?

Get ready for an avalanche of angry messages explaining why you should not run a database on Raid 5 (and rightly so). -- Bashar Chalabi CTL, London Tam McLaughlin <tamm@scotlegal.com> wrote in message news:869b5k$717$1@uranium.btinternet.com... > I have read the relevent pages in the online informix doc on update > statistics but > not sure which to run each night. > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 > so i dont think we have to worry about distributions as no data > fragmentation. > With online 5 we ran plain and simple "update statistics". When I first > imported the > databases into the new version, I ran : > > "update statistics high for table "dba".agent_companies > (agcompid_ref,status_ref); > > for each table once. > > Now I only run "update statistics" each night. > Should i be doing "update statistics high/low/medium" or what ? > >

Tam McLaughlin wrote: > > I have read the relevent pages in the online informix doc on update > statistics but > not sure which to run each night. > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 > so i dont think we have to worry about distributions as no data > fragmentation. Is a query using the 'correct' index still quicker than using a wrong index or no index at all? > With online 5 we ran plain and simple "update statistics". When I first > imported the > databases into the new version, I ran : > > "update statistics high for table "dba".agent_companies > (agcompid_ref,status_ref); > > for each table once. > > Now I only run "update statistics" each night. > Should i be doing "update statistics high/low/medium" or what ? I'd recommend the following as a start: update statistics medium at the table level. update statistics high for all columns heading an index. The release notes should have a bit more information. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Tam McLaughlin wrote: > > I have read the relevent pages in the online informix doc on update > statistics but > not sure which to run each night. > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 No to disappoint Bashar, I MUST point out that RAID5 and databases are a poor combination. RAID5 incurs a 50% penalty on all writes. You will do MUCH better with hardware RAID 3 or 4 or, best of all, RAID 10. See my previous posts (search the IIUG Archives of CDI for 'RAID10') for the real problems with RAID5, the write penalty is not the worst. > so i dont think we have to worry about distributions as no data > fragmentation. > With online 5 we ran plain and simple "update statistics". When I first > imported the > databases into the new version, I ran : > > "update statistics high for table "dba".agent_companies > (agcompid_ref,status_ref); > > for each table once. > > Now I only run "update statistics" each night. > Should i be doing "update statistics high/low/medium" or what ? You will find the recommended suite of commands to run documented in the Performance Guide or you can get my dostats.ec utility or one of the several shell/awk/perl scripts any of which implement the recommended protocols. (Dostats.ec is contained in the package utils2_ak in the IIUG Software Repository.) You do not have to run the stats nightly as you did for OL5.xx. Since 5.xx only had the meagar stats that LOW generates to work with it was critical that they be up-to-date. IDS 7/8/9 use data distributions which are far less sensitive to changes in the data. The rule of thumb is if the number rows increases or decreases significantly (say 25%) rerun LOW (or HIGH without the DISTRIBUTIONS ONLY clause). If the relative importance of individual key values increases (ie suddenly your least active customer becomes your most active) rerun the MEDIUM and HIGH as recommended in the suite. Generally if things change I just run: dostats -d database -t table and don't worry about it. Art S. Kagel

Thanks all for the info. Although we have RAID 5, we upgraded the OS and from Online 5 to IDS on 3 servers and did not want to stretch ourselves too much by reconfiguring the RAID array at the same time. However, we have plans to add another RAID controller and more disks ASAP and implement some kind of mirroring at the hardware level (cant remember the RAID level - possibly 10) Tam McLaughlin wrote: > I have read the relevent pages in the online informix doc on update > statistics but > not sure which to run each night. > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 > so i dont think we have to worry about distributions as no data > fragmentation. > With online 5 we ran plain and simple "update statistics". When I first > imported the > databases into the new version, I ran : > > "update statistics high for table "dba".agent_companies > (agcompid_ref,status_ref); > > for each table once. > > Now I only run "update statistics" each night. > Should i be doing "update statistics high/low/medium" or what ?

"Art S. Kagel" schrieb: > Tam McLaughlin wrote: > > > > I have read the relevent pages in the online informix doc on update > > statistics but > > not sure which to run each night. > > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 > > No to disappoint Bashar, I MUST point out that RAID5 and databases are > a poor combination. RAID5 incurs a 50% penalty on all writes. You > will do MUCH better with hardware RAID 3 or 4 or, best of all, RAID 10. For understanding only, RAID10 is the combination of RAID 0/1 ? Dirk > > See my previous posts (search the IIUG Archives of CDI for 'RAID10') > for the real problems with RAID5, the write penalty is not the worst. > > > so i dont think we have to worry about distributions as no data > > fragmentation. > > With online 5 we ran plain and simple "update statistics". When I first > > imported the > > databases into the new version, I ran : > > > > "update statistics high for table "dba".agent_companies > > (agcompid_ref,status_ref); > > > > for each table once. > > > > Now I only run "update statistics" each night. > > Should i be doing "update statistics high/low/medium" or what ? > > You will find the recommended suite of commands to run documented in > the Performance Guide or you can get my dostats.ec utility or one of > the several shell/awk/perl scripts any of which implement the > recommended protocols. (Dostats.ec is contained in the package > utils2_ak in the IIUG Software Repository.) You do not have to run the > stats nightly as you did for OL5.xx. Since 5.xx only had the meagar > stats that LOW generates to work with it was critical that they be > up-to-date. IDS 7/8/9 use data distributions which are far less > sensitive to changes in the data. The rule of thumb is if the number > rows increases or decreases significantly (say 25%) rerun LOW (or HIGH > without the DISTRIBUTIONS ONLY clause). If the relative importance of > individual key values increases (ie suddenly your least active customer > becomes your most active) rerun the MEDIUM and HIGH as recommended in > the suite. Generally if things change I just run: > dostats -d database -t table > and don't worry about it. > > Art S. Kagel

Dirk Niemeier wrote: > "Art S. Kagel" schrieb: > > > Tam McLaughlin wrote: > > > > > > I have read the relevent pages in the online informix doc on update > > > statistics but > > > not sure which to run each night. > > > Our system is an OLTP one and IDS 7.30.UC2 on SCO unixware 7 we use RAID 5 > > > > No to disappoint Bashar, I MUST point out that RAID5 and databases are > > a poor combination. RAID5 incurs a 50% penalty on all writes. You > > will do MUCH better with hardware RAID 3 or 4 or, best of all, RAID 10. > > For understanding only, RAID10 is the combination of RAID 0/1 ? > Dirk Yes, RAID10 is a stripe set formed of N mirrored pair, as opposed to RAID01 which would be a mirrored pair of two stripe sets. RAID10 is preferred over RAID01 for integrity, recovery, and maintenance reasons, performance of two should be nearly identical. Several vendors implement RAID10 in hardware/firmware on their controllers and arrays. Art S. Kagel > > > > > > See my previous posts (search the IIUG Archives of CDI for 'RAID10') > > for the real problems with RAID5, the write penalty is not the worst. > > > > > so i dont think we have to worry about distributions as no data > > > fragmentation. > > > With online 5 we ran plain and simple "update statistics". When I first > > > imported the > > > databases into the new version, I ran : > > > > > > "update statistics high for table "dba".agent_companies > > > (agcompid_ref,status_ref); > > > > > > for each table once. > > > > > > Now I only run "update statistics" each night. > > > Should i be doing "update statistics high/low/medium" or what ? > > > > You will find the recommended suite of commands to run documented in > > the Performance Guide or you can get my dostats.ec utility or one of > > the several shell/awk/perl scripts any of which implement the > > recommended protocols. (Dostats.ec is contained in the package > > utils2_ak in the IIUG Software Repository.) You do not have to run the > > stats nightly as you did for OL5.xx. Since 5.xx only had the meagar > > stats that LOW generates to work with it was critical that they be > > up-to-date. IDS 7/8/9 use data distributions which are far less > > sensitive to changes in the data. The rule of thumb is if the number > > rows increases or decreases significantly (say 25%) rerun LOW (or HIGH > > without the DISTRIBUTIONS ONLY clause). If the relative importance of > > individual key values increases (ie suddenly your least active customer > > becomes your most active) rerun the MEDIUM and HIGH as recommended in > > the suite. Generally if things change I just run: > > dostats -d database -t table > > and don't worry about it. > > > > Art S. Kagel