[Update statistics & sysdistrib]

Hi, I am supporting IDS 7.24UC4 on HP-UX 10.20. It is recently upgraded from 7.23. After upgrade, some queries are slower than previous version because of changed query path. So, I 'update statistics high' for some table, but nothing changes. I looked up the syscolumns table, and 'dbschema -d dbname -t tabname -hd tabname'. And I found the table has null value in syscolumns(colmax,colmin) and the distribution information was totally incorrect despite of "update statistics high". Could you please help me? Thanks in advance..

Heung-Soon, it is considered poor net etiquette to post the identical message three times under different subjects to the same newsgroup within so short a period (1 hour). Hold back, we'll get to you. Those stats are not used with as much import as they were in OL5.xx, the distributions are far more important, but you can update them with UPDATE STATISTICS LOW. Just do not include the DROP DISTRIBUTIONS clause. What do you mean the distribution information is all wrong? Try running the recommended set of stats per the release notes and performance guide: 1) UPDATE STATISTICS MEDIUM on the whole table at the table level (not database) 2) UPDATE STATISTICS HIGH on the first column of each index 3) If you have several composite indexes that begin with the same subset of columns UPDATE STATISTICS HIGH on the first column of each index that is different from the others. (1, 2, & 3 can be done with the DISTRIBUTIONS ONLY clause to run faster since 4 below will take care of the lower level stats) 4) UPDATE STATISTICS LOW on the entire column list of each index. (If you have any single column indexes you can skip 4) by NOT including the DISTRIBUTIONS ONLY clause when you do the HIGH for that column. My dostats.ec utility, included in the package utils2_ak submitted to the IIUG Software Repository, implements this scheme as described and will save you much typing. Art S. Kagel "Yang, Heung-Soon" wrote: > > Hi, > > I am supporting IDS 7.24UC4 on HP-UX 10.20. > It is recently upgraded from 7.23. > After upgrade, some queries are slower than previous version because of > changed query path. So, I 'update statistics high' for some table, but > nothing changes. > I looked up the syscolumns table, and 'dbschema -d dbname -t tabname -hd > tabname'. > And I found the table has null value in syscolumns(colmax,colmin) and > the distribution information was totally incorrect despite of "update > statistics high". > > Could you please help me? Thanks in advance..

Thank you for your apply. First of all, I'm sorry for reposting. I did it because of miss-spelling, and my poor english. I have used your dostats.ec(It was incredible). But the colmax, colmin for the table was not updated at all despite of indexes this time. And when I 'dbschema -hd' for that table's information seemed poor. The columns has date values. When I do 'select count(*) from wh_order_cont where dt_order="1997-12-03";', It returns about 1000. But 'dbschema -hd' says that the column has 2976141 "1997-12-03" values. The table has about 3000000 rows. I don't know why --; I recommended to my client IDS 7.31, but the application was coded with Forte. A Forte engineer told me, there must be ESQL/C 7.2x. So we couldn't use optimizer hint.(Some queries are using inappropriate indexes after upgrade from 7.23 to 7.24) Thank you for reading this mail.. "Art S. Kagel" wrote: > Heung-Soon, it is considered poor net etiquette to post the identical message > three times under different subjects to the same newsgroup within so short a > period (1 hour). Hold back, we'll get to you. > > Those stats are not used with as much import as they were in OL5.xx, the > distributions are far more important, but you can update them with UPDATE > STATISTICS LOW. Just do not include the DROP DISTRIBUTIONS clause. > > What do you mean the distribution information is all wrong? > > Try running the recommended set of stats per the release notes and performance > guide: > > 1) UPDATE STATISTICS MEDIUM on the whole table at the table level (not database) > 2) UPDATE STATISTICS HIGH on the first column of each index > 3) If you have several composite indexes that begin with the same subset of > columns UPDATE STATISTICS HIGH on the first column of each index that is > different from the others. > (1, 2, & 3 can be done with the DISTRIBUTIONS ONLY clause to run faster since > 4 below will take care of the lower level stats) > 4) UPDATE STATISTICS LOW on the entire column list of each index. > (If you have any single column indexes you can skip 4) by NOT including the > DISTRIBUTIONS ONLY clause when you do the HIGH for that column. > > My dostats.ec utility, included in the package utils2_ak submitted to the IIUG > Software Repository, implements this scheme as described and will save you much > typing. > > Art S. Kagel > > "Yang, Heung-Soon" wrote: > > > > Hi, > > > > I am supporting IDS 7.24UC4 on HP-UX 10.20. > > It is recently upgraded from 7.23. > > After upgrade, some queries are slower than previous version because of > > changed query path. So, I 'update statistics high' for some table, but > > nothing changes. > > I looked up the syscolumns table, and 'dbschema -d dbname -t tabname -hd > > tabname'. > > And I found the table has null value in syscolumns(colmax,colmin) and > > the distribution information was totally incorrect despite of "update > > statistics high". > > > > Could you please help me? Thanks in advance..

Oh, there are only 2 bins for the column I specified, when I 'dbschema -hd'. I 'update statistics high' with default resolution (0.5). "Yang, Heung-Soon" wrote: > Thank you for your apply. First of all, I'm sorry for reposting. I did it because of > > miss-spelling, and my poor english. > > I have used your dostats.ec(It was incredible). But the colmax, colmin for the table > > was not updated at all despite of indexes this time. > And when I 'dbschema -hd' for that table's information seemed poor. > The columns has date values. > When I do 'select count(*) from wh_order_cont where dt_order="1997-12-03";', It > returns about 1000. But 'dbschema -hd' says that the column has 2976141 "1997-12-03" > > values. The table has about 3000000 rows. I don't know why --; > > I recommended to my client IDS 7.31, but the application was coded with Forte. A > Forte engineer told me, there must be ESQL/C 7.2x. So we couldn't use optimizer > hint.(Some queries are using inappropriate indexes after upgrade from 7.23 to 7.24) > > Thank you for reading this mail.. > > "Art S. Kagel" wrote: > > > Heung-Soon, it is considered poor net etiquette to post the identical message > > three times under different subjects to the same newsgroup within so short a > > period (1 hour). Hold back, we'll get to you. > > > > Those stats are not used with as much import as they were in OL5.xx, the > > distributions are far more important, but you can update them with UPDATE > > STATISTICS LOW. Just do not include the DROP DISTRIBUTIONS clause. > > > > What do you mean the distribution information is all wrong? > > > > Try running the recommended set of stats per the release notes and performance > > guide: > > > > 1) UPDATE STATISTICS MEDIUM on the whole table at the table level (not database) > > 2) UPDATE STATISTICS HIGH on the first column of each index > > 3) If you have several composite indexes that begin with the same subset of > > columns UPDATE STATISTICS HIGH on the first column of each index that is > > different from the others. > > (1, 2, & 3 can be done with the DISTRIBUTIONS ONLY clause to run faster since > > 4 below will take care of the lower level stats) > > 4) UPDATE STATISTICS LOW on the entire column list of each index. > > (If you have any single column indexes you can skip 4) by NOT including the > > DISTRIBUTIONS ONLY clause when you do the HIGH for that column. > > > > My dostats.ec utility, included in the package utils2_ak submitted to the IIUG > > Software Repository, implements this scheme as described and will save you much > > typing. > > > > Art S. Kagel > > > > "Yang, Heung-Soon" wrote: > > > > > > Hi, > > > > > > I am supporting IDS 7.24UC4 on HP-UX 10.20. > > > It is recently upgraded from 7.23. > > > After upgrade, some queries are slower than previous version because of > > > changed query path. So, I 'update statistics high' for some table, but > > > nothing changes. > > > I looked up the syscolumns table, and 'dbschema -d dbname -t tabname -hd > > > tabname'. > > > And I found the table has null value in syscolumns(colmax,colmin) and > > > the distribution information was totally incorrect despite of "update > > > statistics high". > > > > > > Could you please help me? Thanks in advance..

After upgrade from 7.24.UC4 to 7.24.UC8, the poor distribution problem has solved. The 'dbschema -hd' displays correct distribution of the table. "Yang, Heung-Soon" wrote: > Thank you for your apply. First of all, I'm sorry for reposting. I did it because of > > miss-spelling, and my poor english. > > I have used your dostats.ec(It was incredible). But the colmax, colmin for the table > > was not updated at all despite of indexes this time. > And when I 'dbschema -hd' for that table's information seemed poor. > The columns has date values. > When I do 'select count(*) from wh_order_cont where dt_order="1997-12-03";', It > returns about 1000. But 'dbschema -hd' says that the column has 2976141 "1997-12-03" > > values. The table has about 3000000 rows. I don't know why --; > > I recommended to my client IDS 7.31, but the application was coded with Forte. A > Forte engineer told me, there must be ESQL/C 7.2x. So we couldn't use optimizer > hint.(Some queries are using inappropriate indexes after upgrade from 7.23 to 7.24) > > Thank you for reading this mail.. > > "Art S. Kagel" wrote: > > > Heung-Soon, it is considered poor net etiquette to post the identical message > > three times under different subjects to the same newsgroup within so short a > > period (1 hour). Hold back, we'll get to you. > > > > Those stats are not used with as much import as they were in OL5.xx, the > > distributions are far more important, but you can update them with UPDATE > > STATISTICS LOW. Just do not include the DROP DISTRIBUTIONS clause. > > > > What do you mean the distribution information is all wrong? > > > > Try running the recommended set of stats per the release notes and performance > > guide: > > > > 1) UPDATE STATISTICS MEDIUM on the whole table at the table level (not database) > > 2) UPDATE STATISTICS HIGH on the first column of each index > > 3) If you have several composite indexes that begin with the same subset of > > columns UPDATE STATISTICS HIGH on the first column of each index that is > > different from the others. > > (1, 2, & 3 can be done with the DISTRIBUTIONS ONLY clause to run faster since > > 4 below will take care of the lower level stats) > > 4) UPDATE STATISTICS LOW on the entire column list of each index. > > (If you have any single column indexes you can skip 4) by NOT including the > > DISTRIBUTIONS ONLY clause when you do the HIGH for that column. > > > > My dostats.ec utility, included in the package utils2_ak submitted to the IIUG > > Software Repository, implements this scheme as described and will save you much > > typing. > > > > Art S. Kagel > > > > "Yang, Heung-Soon" wrote: > > > > > > Hi, > > > > > > I am supporting IDS 7.24UC4 on HP-UX 10.20. > > > It is recently upgraded from 7.23. > > > After upgrade, some queries are slower than previous version because of > > > changed query path. So, I 'update statistics high' for some table, but > > > nothing changes. > > > I looked up the syscolumns table, and 'dbschema -d dbname -t tabname -hd > > > tabname'. > > > And I found the table has null value in syscolumns(colmax,colmin) and > > > the distribution information was totally incorrect despite of "update > > > statistics high". > > > > > > Could you please help me? Thanks in advance..