Alter statistics to trick the optimizer?

IDS 10.00.fc8 OS AIX 5.x Situation: Statistics updated low every night. Table is empty at time statistics are run every night. I have no control over SQL or schedule of the application tasks. Mass load occurs on table at time of end users choice. Automated task begins on interval to process data in table. Since table at stat time was empty a nested loop join is selected by optimizer. Has very poor performance. If stats can be updated after load and before task a dynamic hash join is selected and performance is good. Since I don't know when the load occurs I can't update stats directly after. I also can not alter the SQL to put in an optimizer hint. So I want to alter the appropriate system tables after my nightly update statistics run to set the stats so a dynamic hash join is selected. I have not been successful in my testing. I have performed the following test to no avail. 1. Loaded the data in test. 2. update statistics 3. verified SQL explain is a hash join 4. capture values in systables, sysindexes, and syscolumns 5. removed data from user table 6. update statistics 7. verified SQL explain is a nested loop join 8. cleared statement cache "onmode -e flush". I don't think this is needed but..... 9. set nrows, npused in systables, colmin, and colmax in syscolumns, and nunique and clust in sysindexes to the values there were at when hash join was selected. And it always still selects the nested loop join. What am I missing? Other solutions? Thanks Rogers

Best thing to do? - Run the update stats on that one table once a week with the table full (so after the load and perhaps after the application has run for the day). - Change the nightly run to not do the entire database but each table individually except for this one problematic table. - I assume that the number of rows and the relative key values aren't significantly different from one day to the next, so doing the stats AFTER the load and before the table is emptied so you have the stats ready for the next day's run should work just fine. I do have a question: Why are you only running LOW stats? That is hamstringing the optimizer to using the older costing algorithms from OnLine 5.xx. You should be running the recommended mix of LOW, MEDIUM, and HIGH commands as recommended in the Performance Guide. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 9, 2009 at 5:31 PM, Rogers Patterson < rogers.patterson@arkansas.gov> wrote: > IDS 10.00.fc8 > OS AIX 5.x > > Situation: > > Statistics updated low every night. Table is empty at time statistics are > run > every night. > I have no control over SQL or schedule of the application tasks. > Mass load occurs on table at time of end users choice. > Automated task begins on interval to process data in table. > Since table at stat time was empty a nested loop join is selected by > optimizer. Has very poor performance. > If stats can be updated after load and before task a dynamic hash join is > selected and performance is good. > > Since I don't know when the load occurs I can't update stats directly > after. I > also can not alter the SQL to put in an optimizer hint. So I want to alter > the > appropriate system tables after my nightly update statistics run to set the > stats so a dynamic hash join is selected. I have not been successful in my > testing. > > I have performed the following test to no avail. > > 1. Loaded the data in test. > 2. update statistics > 3. verified SQL explain is a hash join > 4. capture values in systables, sysindexes, and syscolumns > 5. removed data from user table > 6. update statistics > 7. verified SQL explain is a nested loop join > 8. cleared statement cache "onmode -e flush". I don't think this is needed > but..... > 9. set nrows, npused in systables, colmin, and colmax in syscolumns, and > nunique and clust in sysindexes to the values there were at when hash join > was > selected. > > And it always still selects the nested loop join. > > What am I missing? Other solutions? > > Thanks > > Rogers > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5983231549b046bf17e85

Thanks Art. The table never has data in it outside of prime processing hours. So, I was looking for a way to make the optimizer think there is data in the table. I guess the solution I will implement will be in a single transaction, lock the table, save any data that might be there, load bogus data, update stats, delete, reload, commit. But I'd prefer to not to play with the data like that. Also, just want to know why my method of updating the system tables is not working...... We do use the high and medium status on the very few tables in our system that have any significant volume of data (and even those would be considered by most shops to be small tables). Our challenge is the number of databases and database objects we manage. So we have to go with a very much "cookie cutter" approach. We have had issues in previous versions with the number of our objects and memory cache. We performed better with low stats on 99% of the tables than we did with recommended method. But of course as luck would have it, only 1% of our customers use this process that is having the problem. So I was looking for a quick way to deal with them as an exception. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, June 09, 2009 5:01 PM To: ids@iiug.org Subject: Re: Alter statistics to trick the optimizer? [15985] Best thing to do? - Run the update stats on that one table once a week with the table full (so after the load and perhaps after the application has run for the day). - Change the nightly run to not do the entire database but each table individually except for this one problematic table. - I assume that the number of rows and the relative key values aren't significantly different from one day to the next, so doing the stats AFTER the load and before the table is emptied so you have the stats ready for the next day's run should work just fine. I do have a question: Why are you only running LOW stats? That is hamstringing the optimizer to using the older costing algorithms from OnLine 5.xx. You should be running the recommended mix of LOW, MEDIUM, and HIGH commands as recommended in the Performance Guide. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 9, 2009 at 5:31 PM, Rogers Patterson < rogers.patterson@arkansas.gov> wrote: > IDS 10.00.fc8 > OS AIX 5.x > > Situation: > > Statistics updated low every night. Table is empty at time statistics are > run > every night. > I have no control over SQL or schedule of the application tasks. > Mass load occurs on table at time of end users choice. > Automated task begins on interval to process data in table. > Since table at stat time was empty a nested loop join is selected by > optimizer. Has very poor performance. > If stats can be updated after load and before task a dynamic hash join is > selected and performance is good. > > Since I don't know when the load occurs I can't update stats directly > after. I > also can not alter the SQL to put in an optimizer hint. So I want to alter > the > appropriate system tables after my nightly update statistics run to set the > stats so a dynamic hash join is selected. I have not been successful in my > testing. > > I have performed the following test to no avail. > > 1. Loaded the data in test. > 2. update statistics > 3. verified SQL explain is a hash join > 4. capture values in systables, sysindexes, and syscolumns > 5. removed data from user table > 6. update statistics > 7. verified SQL explain is a nested loop join > 8. cleared statement cache "onmode -e flush". I don't think this is needed > but..... > 9. set nrows, npused in systables, colmin, and colmax in syscolumns, and > nunique and clust in sysindexes to the values there were at when hash join > was > selected. > > And it always still selects the nested loop join. > > What am I missing? Other solutions? > > Thanks > > Rogers > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5983231549b046bf17e85 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.