Performance (Update Statistics) in v10
After migrating from Informix v5 to 10.00.UC6 on AIX, a 4GL report that used to run in under 10 minutes took hours, yet ran in 7 minutes if UPDATE STATISTICS was run immediately beforehand (nightly stats were already in place). Posters suggested volatile tables, stored procedures/temp tables, and IBM's recommended stats script. Art Kagel gave the likely explanation: the nightly FIFO purge/load makes distributions stale, so the optimizer misjudges date-key filters. Fixes offered: run UPDATE STATISTICS LOW ... DROP DISTRIBUTIONS before the report (reverting to v5-style optimization), or run the full recommended stats suite (e.g. dostats) just before the report. No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a very strange situation occurring. I just performed a migration from v5 to v10 via multiple unload/loads into the new instance. Practically everything seems to be running well insofar as order entry is concerned. I am running update statistics (low, medium, high on indexes) as they should be - currently on a nightly basis. However, I am experiencing an issue with report (4ge) that take upwards of several hours to run on v10 when, on the prior v5, they took less than 10 minutes. Now the strange - if I run a new update statistics before running the report, it takes 7 minutes. 10.00.UC6, AIX 5.2 Thanks in advance for your assistance. Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics 1431 Greenway Drive #700 Irving, Texas 75038 Main (972) 812-1411 x244 Toll Free (800) 834-8807 x244 Fax (469) 417-0665
On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 > > Thanks in advance for your assistance. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > 1431 Greenway Drive #700 > Irving, Texas 75038 > > Main (972) 812-1411 x244 > > Toll Free (800) 834-8807 x244 > > Fax (469) 417-0665 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Clifton V5 (and V7) were quite happy to just accept there was data in a table and took a 'best guess' at the index to use based on the query. V10 (and V9) absolutely demand low stats to be run (to count the rows in each table) and like medium nand high stats to really point the optimiser in the right direction. However the stats you are running should be OK. Are the reports using stored procedure that are not included in you nightly stats but are in the pre-report stats? Are any of the tables access in your reports highly volitile? or are any dropped and re-created just before the report is run.? Is there a massive temp table created in the session just before the report taht needs stats run against it? Have you extracted the SQL out of the report and run it separately to get a query plan (or switched query plan on during the (sorry, forgot this was V10 and you can do that))? Keith
Answers to Keith's questions ... 1. No stored procedures nor triggers are used within this instance. 2. Tables in use are the main tables for this instance. These tables contain 60 days worth of data. Each morning, a fifo run takes the earliest information in some of these tables and moves them to a different database. During the day, new data for the current day is moved in. 3. There is one temporary table; at end of report execution, it may have a total of 15 rows. 4. Query plan gives me the same results before and after the update statistics. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Thursday, May 24, 2007 9:36 AM To: ids@iiug.org Subject: Re: Performance (Update Statistics) in v10 [9229] On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 > > Thanks in advance for your assistance. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > 1431 Greenway Drive #700 > Irving, Texas 75038 > > Main (972) 812-1411 x244 > > Toll Free (800) 834-8807 x244 > > Fax (469) 417-0665 > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > Clifton V5 (and V7) were quite happy to just accept there was data in a table and took a 'best guess' at the index to use based on the query. V10 (and V9) absolutely demand low stats to be run (to count the rows in each table) and like medium nand high stats to really point the optimiser in the right direction. However the stats you are running should be OK. Are the reports using stored procedure that are not included in you nightly stats but are in the pre-report stats? Are any of the tables access in your reports highly volitile? or are any dropped and re-created just before the report is run.? Is there a massive temp table created in the session just before the report taht needs stats run against it? Have you extracted the SQL out of the report and run it separately to get a query plan (or switched query plan on during the (sorry, forgot this was V10 and you can do that))? Keith
Clifton, Complementing what I wrote before, run the script to update statistics, daily and if is possible after you move the table to a different database. To be sure that you script to update statistics are done in the best way, use the script available in this site: http://www-1.ibm.com/support/docview.wss?uid=swg21137764 Take care. Miguel Carbone Informix Specialist Grato, Saluti, Best Regards, Mit Freundlichen Grüssen, Meilleures Salutation Miguel Carbone Miguel Carbone miguel@mcsoftware.com.br +55 11 2295 1933 +55 11 9634 7103 Clifton, If you made significant changes in you database before running the report, ( load significant amount of data for example) is recommended that after that you run the update statistics again. To be sure that you script to update statistics are done in the best way, use the script available in this site: http://www-1.ibm.com/support/docview.wss?uid=swg21137764 Take Care -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Clifton Bean Sent: quinta-feira, 24 de maio de 2007 11:50 To: ids@iiug.org Subject: RE: Performance (Update Statistics) in v10 [9230] Answers to Keith's questions ... 1. No stored procedures nor triggers are used within this instance. 2. Tables in use are the main tables for this instance. These tables contain 60 days worth of data. Each morning, a fifo run takes the earliest information in some of these tables and moves them to a different database. During the day, new data for the current day is moved in. 3. There is one temporary table; at end of report execution, it may have a total of 15 rows. 4. Query plan gives me the same results before and after the update statistics. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Thursday, May 24, 2007 9:36 AM To: ids@iiug.org Subject: Re: Performance (Update Statistics) in v10 [9229] On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 > > Thanks in advance for your assistance. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > 1431 Greenway Drive #700 > Irving, Texas 75038 > > Main (972) 812-1411 x244 > > Toll Free (800) 834-8807 x244 > > Fax (469) 417-0665 > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > Clifton V5 (and V7) were quite happy to just accept there was data in a table and took a 'best guess' at the index to use based on the query. V10 (and V9) absolutely demand low stats to be run (to count the rows in each table) and like medium nand high stats to really point the optimiser in the right direction. However the stats you are running should be OK. Are the reports using stored procedure that are not included in you nightly stats but are in the pre-report stats? Are any of the tables access in your reports highly volitile? or are any dropped and re-created just before the report is run.? Is there a massive temp table created in the session just before the report taht needs stats run against it? Have you extracted the SQL out of the report and run it separately to get a query plan (or switched query plan on during the (sorry, forgot this was V10 and you can do that))? Keith **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Clifton, If you made significant changes in you database before running the report, ( load significant amount of data for example) is recommended that after that you run the update statistics again. To be sure that you script to update statistics are done in the best way, use the script available in this site: http://www-1.ibm.com/support/docview.wss?uid=swg21137764 Take Care Miguel Grato, Saluti, Best Regards, Mit Freundlichen Grüssen, Meilleures Salutation Miguel Carbone Miguel Carbone miguel@mcsoftware.com.br +55 11 2295 1933 +55 11 9634 7103 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: quinta-feira, 24 de maio de 2007 11:36 To: ids@iiug.org Subject: Re: Performance (Update Statistics) in v10 [9229] On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 > > Thanks in advance for your assistance. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > 1431 Greenway Drive #700 > Irving, Texas 75038 > > Main (972) 812-1411 x244 > > Toll Free (800) 834-8807 x244 > > Fax (469) 417-0665 > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > Clifton V5 (and V7) were quite happy to just accept there was data in a table and took a 'best guess' at the index to use based on the query. V10 (and V9) absolutely demand low stats to be run (to count the rows in each table) and like medium nand high stats to really point the optimiser in the right direction. However the stats you are running should be OK. Are the reports using stored procedure that are not included in you nightly stats but are in the pre-report stats? Are any of the tables access in your reports highly volitile? or are any dropped and re-created just before the report is run.? Is there a massive temp table created in the session just before the report taht needs stats run against it? Have you extracted the SQL out of the report and run it separately to get a query plan (or switched query plan on during the (sorry, forgot this was V10 and you can do that))? Keith **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Keith Simmons > Sent: Thursday, May 24, 2007 7:36 AM > To: ids@iiug.org > Subject: Re: Performance (Update Statistics) in v10 [9229] > > On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > > I have a very strange situation occurring. > > > > I just performed a migration from v5 to v10 via multiple > unload/loads into > > the new instance. Practically everything seems to be > running well insofar > > as order entry is concerned. I am running update statistics > (low, medium, > > high on indexes) as they should be - currently on a nightly basis. > > > > However, I am experiencing an issue with report (4ge) that > take upwards of > > several hours to run on v10 when, on the prior v5, they > took less than 10 > > minutes. Now the strange - if I run a new update statistics > before running > > the report, it takes 7 minutes. > > > > 10.00.UC6, AIX 5.2 > > > > Thanks in advance for your assistance. > > > > Take care. > > > > Clifton M. Bean > > Informix DBA / AIX System Admin > > Currency Technics & Metrics > > 1431 Greenway Drive #700 > > Irving, Texas 75038 > > > > Main (972) 812-1411 x244 > > > > Toll Free (800) 834-8807 x244 > > > > Fax (469) 417-0665 > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > Clifton > > V5 (and V7) were quite happy to just accept there was data in a table > and took a 'best guess' at the index to use based on the query. V10 > (and V9) absolutely demand low stats to be run (to count the rows in > each table) and like medium nand high stats to really point the > optimiser in the right direction. > However the stats you are running should be OK. Are the reports using > stored procedure that are not included in you nightly stats > but are in > the pre-report stats? Are any of the tables access in your reports > highly volitile? or are any dropped and re-created just before the > report is run.? Is there a massive temp table created in the session > just before the report taht needs stats run against it? Have you > extracted the SQL out of the report and run it separately to get a > query plan (or switched query plan on during the (sorry, forgot this > was V10 and you can do that))? > > Keith > Our IBM support folks tell us that a "HIGH" accomplishes the same things as a "LOW" followed by a "HIGH...DISTRIBUTIONS ONLY". In my testing, with the "LOW" on a non-index column, and the "HIGH" on all columns involved in any index (i.e., not just leading) -- in my testing I have seen that the "HIGH" only runs quite a bit longer than the "LOW" + "HIGH...DISTRIBUTIONS ONLY". Strangely, there seems to be more of a system performance impact with the LOW/HIGH combo than with the HIGH only. Also, batch testing following each updt stats run indicates that the batch runs are slightly shorter after the HIGH only vs. after the LOW/HIGH combo. These results are just the opposite of what I would have expected. BTW, we have been using Art's dostats up to this point. However, because of some production "locking" issues related to the forced re-compile of SP's, we have had to look at reducing the number of updt stats statements, in order to reduce the number of possible locking situations. Thoughts??? Thanks, Paul M.
From: "Clifton Bean" <cbean@ctm.com> To: <ids@iiug.org> Sent: Thursday, May 24, 2007 9:48 AM Subject: Performance (Update Statistics) in v10 [9228] >I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 I know your situation, and I'm extremely curious as to why you won't use the update stats scripts that Informix provided for version 9. There is no significant difference in the storage strategies between v9 and v10, so the updates should run in good order, just as they've always done. Wayne B. Houseknecht whouseknecht@hotmail.com
IB the problem is the overnight removal of old data and new day's addition of new data. I'd guess that the query(ies) in the 4ge include the keys column(s) that are specific to newer data in their filter or join columns. When you run the report without updating stats immediately before the report the stats say there are N rows from the oldest day(s) that no longer exist in actuality and that there are no rows for today which there definitely are now. Here are your choices, and it's one we all had to deal with after upgrading from 5.xx to 7/9/10. Just before the report runs, either: - UPDATE STATISTICS LOW ... DROP DISTRIBUTIONS; for the tables referenced in the report -or- - run the full suite of update stats as recommeded in the Performance Guide or the tech paper mentioned earlier or using my dostats utility. The first option will drop the modern data distribution information used by the v10 optimizer and force it to revert to OL5 optimizer behavior which should perform just as it used to. The second option will, as you've already seen, provide additional data for the optimizer to use to do an even better job of optimizing the query than OL5 could. In 5.xx it just wasn't as critical that the stats be up-to-date as all that OL5 used was the row count and for each column in a key the second highest and second lowest value the column contained. IDS 10 using the more detailed distributions can make better decisions than 5 could, but only if the stats are recent and accurate enough. Art S. Kagel ----- Original Message ----- From: Clifton Bean <ids@iiug.org> At: 5/24 10:50:23 Answers to Keith's questions ... 1. No stored procedures nor triggers are used within this instance. 2. Tables in use are the main tables for this instance. These tables contain 60 days worth of data. Each morning, a fifo run takes the earliest information in some of these tables and moves them to a different database. During the day, new data for the current day is moved in. 3. There is one temporary table; at end of report execution, it may have a total of 15 rows. 4. Query plan gives me the same results before and after the update statistics. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Thursday, May 24, 2007 9:36 AM To: ids@iiug.org Subject: Re: Performance (Update Statistics) in v10 [9229] On 24/05/07, Clifton Bean <cbean@ctm.com> wrote: > I have a very strange situation occurring. > > I just performed a migration from v5 to v10 via multiple unload/loads into > the new instance. Practically everything seems to be running well insofar > as order entry is concerned. I am running update statistics (low, medium, > high on indexes) as they should be - currently on a nightly basis. > > However, I am experiencing an issue with report (4ge) that take upwards of > several hours to run on v10 when, on the prior v5, they took less than 10 > minutes. Now the strange - if I run a new update statistics before running > the report, it takes 7 minutes. > > 10.00.UC6, AIX 5.2 > > Thanks in advance for your assistance. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > 1431 Greenway Drive #700 > Irving, Texas 75038 > > Main (972) 812-1411 x244 > > Toll Free (800) 834-8807 x244 > > Fax (469) 417-0665 > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > Clifton V5 (and V7) were quite happy to just accept there was data in a table and took a 'best guess' at the index to use based on the query. V10 (and V9) absolutely demand low stats to be run (to count the rows in each table) and like medium nand high stats to really point the optimiser in the right direction. However the stats you are running should be OK. Are the reports using stored procedure that are not included in you nightly stats but are in the pre-report stats? Are any of the tables access in your reports highly volitile? or are any dropped and re-created just before the report is run.? Is there a massive temp table created in the session just before the report taht needs stats run against it? Have you extracted the SQL out of the report and run it separately to get a query plan (or switched query plan on during the (sorry, forgot this was V10 and you can do that))? Keith ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.