Smart upd_stats routine

IDS 7.30.uc7 HPUX 10.20 Currently, my update statistics scripts update all tables and I run it every night, just in case <?>. However, the size ( and number ) of my tables is increasing, and update statistics runs quite a long time. Frankly, I'd like to run update statistics a bit smarter. Ultimately, I'd like to run against all tables once a week, and as needed between weekly runs. Only problem is that I haven't been able to accurately define when a table's distribution plan "needs" to be updated. I could always go for changes (actual or percentage) to number of rows or number of pages as a means, but that method doesn't seem smart enough. Any ideas . . . or is this just another DBA | dream?? TIA John Carlson Informix DBA WHSmith USA

Hi John ... hope you have been doing okay since I last worked on a case with you while a member of INFORMIX Tech Support :) In answer to your question, you need to determine what tables in your database may be static (not growing, relatively little changes). Those tables you would substitute HIGH for MEDIUM in the recommended procedure listed in the Performance Guide and as under the Syntax info for Update Statistics(U.S.). Once done, comment it out of your script(s) and just run U.S. on a periodic basis. That should eliminate most (if not all) of your lookup (catalog) tables. To make the determination, you could write a select to obtain the tabname and nrows from systables and compare the output from one with the output of another, both taken after running your current Update Statistics. Or use your knowledge (or that of the developers) of the tables to make this determination. Another thing you may want to consider is the use of PSORT_NPROCS, if you have multiple CPUs. Set the value to the total number of CPUs available (minimum of 2, maximum of 10) while running your Update Statistics scripts. This parameter is defined in SQL Reference Manual in the chapter for Environment Parameters. Notice I said scriptS above. Break down your single script and run four or five in parallel, serial by table. (This should save you more time). I normally recommend that your scripts be based on tables contained in the same hard drive; in that manner, your tables will not be competing against each other for I/Os. If you can, separate them even more by disk controller. Some people only run their U.S. when the row count has increased by a percentage; the larger the table, the smaller the percentage. I would tend to ignore this method if at all possible :) Take care and good luck. =============================================== Clifton M. Bean cmbean@msn.com SAP/Informix Database Administrator Informix Certified Database Specialist Informix 4GL-Certified Informix D4GL-Certified Tekmetrics Certified Informix DBA Tekmetrics Certified RDBMS Developer =============================================== Carlson@WHSmith <carlson1@bellsouth.net> wrote in message news:37BC536E.27A9AE15@bellsouth.net... > IDS 7.30.uc7 > HPUX 10.20 > > Currently, my update statistics scripts update all tables and I run it > every night, just in case <?>. However, the size ( and number ) of my > tables is increasing, and update statistics runs quite a long time. > > Frankly, I'd like to run update statistics a bit smarter. Ultimately, > I'd like to run against all tables once a week, and as needed between > weekly runs. Only problem is that I haven't been able to accurately > define when a table's distribution plan "needs" to be updated. I could > always go for changes (actual or percentage) to number of rows or number > of pages as a means, but that method doesn't seem smart enough. > > Any ideas . . . or is this just another DBA | dream?? > > TIA > > John Carlson > Informix DBA > WHSmith USA

Why not use Art Kagel's excellent dostats.ec utility from http://www.iiug.org??? Works great for me. allen "Carlson@WHSmith" wrote: > > IDS 7.30.uc7 > HPUX 10.20 > > Currently, my update statistics scripts update all tables and I run it > every night, just in case <?>. However, the size ( and number ) of my > tables is increasing, and update statistics runs quite a long time. > > Frankly, I'd like to run update statistics a bit smarter. Ultimately, > I'd like to run against all tables once a week, and as needed between > weekly runs. Only problem is that I haven't been able to accurately > define when a table's distribution plan "needs" to be updated. I could > always go for changes (actual or percentage) to number of rows or number > of pages as a means, but that method doesn't seem smart enough. > > Any ideas . . . or is this just another DBA | dream?? > > TIA > > John Carlson > Informix DBA > WHSmith USA

dostats is great . . . but it also will run for all tables for an entire database. I want a way to run update statistics only if necessary. John Carlson Informix DBA WHSmith USA allenj wrote: > > Why not use Art Kagel's excellent dostats.ec utility from > http://www.iiug.org??? > > Works great for me. > > allen > > "Carlson@WHSmith" wrote: > > > > IDS 7.30.uc7 > > HPUX 10.20 > > > > Currently, my update statistics scripts update all tables and I run it > > every night, just in case <?>. However, the size ( and number ) of my > > tables is increasing, and update statistics runs quite a long time. > > > > Frankly, I'd like to run update statistics a bit smarter. Ultimately, > > I'd like to run against all tables once a week, and as needed between > > weekly runs. Only problem is that I haven't been able to accurately > > define when a table's distribution plan "needs" to be updated. I could > > always go for changes (actual or percentage) to number of rows or number > > of pages as a means, but that method doesn't seem smart enough. > > > > Any ideas . . . or is this just another DBA | dream?? > > > > TIA > > > > John Carlson > > Informix DBA > > WHSmith USA

The only definitive way to know that your stats need updating is by comparing distributions in your table with those in sysdistrib ... a bit expensive, I would think. Since distributions change as a result of inserts, updates and deletes, one could extract insert, update and delete information from sysmaster:sysptprof (iswrites, isrewrites and isdeletes) and add some size indicator (systabinfo : ti_nrows or ti_npused) to create some sort of composite "Change Indicator". You'd probably want to cumulate sysptprof info. over time, initializing whenever you updated statistics. Finally, I'd want to factor in the amount of time since the last update stats was run. These 5 bits of info. could be used to create a list of tables each with a priority factor...which could be piped to your favorite "update stats" script in descending order. I actually did use a script based on the above some time back - I could send it to you privately, if you're interested. Rudy "Carlson@WHSmith" wrote: > IDS 7.30.uc7 > HPUX 10.20 > > Currently, my update statistics scripts update all tables and I run it > every night, just in case <?>. However, the size ( and number ) of my > tables is increasing, and update statistics runs quite a long time. > > Frankly, I'd like to run update statistics a bit smarter. Ultimately, > I'd like to run against all tables once a week, and as needed between > weekly runs. Only problem is that I haven't been able to accurately > define when a table's distribution plan "needs" to be updated. I could > always go for changes (actual or percentage) to number of rows or number > of pages as a means, but that method doesn't seem smart enough. > > Any ideas . . . or is this just another DBA | dream?? > > TIA > > John Carlson > Informix DBA > WHSmith USA

Sorry guys. This message was inadvertently sent with "informix" as the sender. No, I am not, in any way, affiliated to the company Informix, except in being an avid user of their products. My apologies. Rudy Informix wrote: > The only definitive way to know that your stats need updating is by > comparing distributions in your table with those in sysdistrib ... a bit > expensive, I would think. Since distributions change as a result of > inserts, updates and deletes, one could extract insert, update > and delete information from sysmaster:sysptprof (iswrites, isrewrites > and isdeletes) and add some size indicator (systabinfo : ti_nrows > or ti_npused) to create some sort of composite "Change Indicator". > > You'd probably want to cumulate sysptprof info. over time, > initializing whenever you updated statistics. Finally, I'd want to > factor in the amount of time since the last update stats was run. > > These 5 bits of info. could be used to create a list of tables each > with a priority factor...which could be piped to your favorite "update > stats" script in descending order. > > I actually did use a script based on the above some time back - > I could send it to you privately, if you're interested. > > Rudy