Re: Smart upd_stats routine
> > ---- you 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. Don't run it on your static/reference tables > > > > 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. IMHO this is a know your data - you should know which tables are taking most of the activity, if you don't then the developers should > > Any ideas . . . or is this just another DBA | dream?? No, but I think comes back to knowing your data. -- Paul Watson # WF Software # If it was easy Tel ++44 1436 674729 # Everybody could do it Fax ++44 1436 678693 # www.wfsoftware.com/informix #
Paul Watson wrote: > > > > > ---- you 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. > > Don't run it on your static/reference tables . . . which are quite (relatively) small . . . hmmmmmm, might save a bit of 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. > > IMHO this is a know your data - you should know which tables are taking > most > of the activity, if you don't then the developers should > I do know, but I'm just looking for a systemic way, whether it's triggered by a certain increase in pages, rows, etc. I don't really want to hardcode the tables into a "create upd_stats" script. > > > Any ideas . . . or is this just another DBA | dream?? > > No, but I think comes back to knowing your data. Agreed, see above. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */
You might compare the number of rows reported in systables to the actual number reported in sysmaster:sysptnhdr and if it differs by some percentage then do the UPDATE STATISTICS otherwise skip it. Art S. Kagel "Carlson@WHSmith" wrote: > > Paul Watson wrote: > > > > > > > > ---- you 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. > > > > Don't run it on your static/reference tables > > . . . which are quite (relatively) small . . . hmmmmmm, might save a bit > of 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. > > > > IMHO this is a know your data - you should know which tables are taking > > most > > of the activity, if you don't then the developers should > > > > I do know, but I'm just looking for a systemic way, whether it's > triggered by a certain increase in pages, rows, etc. I don't really > want to hardcode the tables into a "create upd_stats" script. > > > > > Any ideas . . . or is this just another DBA | dream?? > > > > No, but I think comes back to knowing your data. > > Agreed, see above. > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */
"Art S. Kagel" wrote: > You might compare the number of rows reported in systables to the actual > number reported in sysmaster:sysptnhdr and if it differs by some percentage > then do the UPDATE STATISTICS otherwise skip it. Which number does optimizer uses: sysmaster:sysptnhdr.nrows or db:systables.nrows instead? > Art S. Kagel Leonid Vorontsov
The optimizer looks in systables that is why you have to run UPDATE STATISTICS LOW or HIGH without the DISTRIBUTIONS ONLY (MEDIUM does NOT update ALL of the values that LOW and HIGH do due to the nature of the sampling that it performs). Art S. Kagel Leonids.Voroncovs@dati.lv wrote: > > "Art S. Kagel" wrote: > > > You might compare the number of rows reported in systables to the actual > > number reported in sysmaster:sysptnhdr and if it differs by some percentage > > then do the UPDATE STATISTICS otherwise skip it. > > Which number does optimizer uses: > sysmaster:sysptnhdr.nrows > or db:systables.nrows instead? > > > Art S. Kagel > > Leonid Vorontsov