Update Statistics
Answered: green (hollow confidence) — Lester and Art give ways to see whether update statistics is running and when it last ran (onstat -g sql, aus_cmd_comp, sysdistrib); Eric adds ustlowts and dostats. The asker thanked them and said they would review the material.
Advisory only.
Posted in 2020
Saradhi Motamarri asked how to tell whether update statistics is running and see its history. Lester Knutsen pointed to his webcast and suggested onstat -g sql | grep 'UPDATE STATIST'. Art Kagel posted a query joining systables and sysdistrib for oldest and latest constructed dates. Eric Vercelletto mentioned ustlowts in systables and Art's dostats utility. Saradhi thanked them.
Topics: General Discussion
Hi, Is there a way to identify whether 'Update Statistics' is running on a server and finding its history? What are the good practices to schedule update statistics? With the best regards and thanks, Saradhi Motamarri +61430022130 Sent from Yahoo Mail for iPhone #Informix
Hi,
The answer is it depends on how you are running Update Statistics, from AUS or from and SQL script or using Art's Dostats program. What are you trying to discover?
I did a Webcast a couple of months ago on this topic, the replay, and slides, are available on our website at:
Informix Webcast 2019 - Informix Update Statistics - Best Practices for Informix DBAs - Advanced DataTools
In the webcasts, I showed a number of scripts, one to tell when update statistics was run last on a database by table, the command history if run from AUS, and other examples.
To catch if it is running right now try onstat -g sql | grep "UPDATE STATIST"
If you are using AUS, to see completed commands do:
database sysadmin;
select * from aus_cmd_comp;If it says no rows found then you have not been using AUS.
Hi,
Is there a way to identify whether 'Update Statistics' is running on a server and finding its history?
What are the good practices to schedule update statistics?
With the best regards and thanks,
Saradhi Motamarri
, Tue March 24, 2020 08:58 PM
------------------------------
Lester Knutsen
lester@advancedatatools.com
Advanced DataTools Corporation
Voice: 703-256-0267 x102
Visit our Web page:
http://www.advancedatatools.com
------------------------------
Thank you so much Lester. Will review the material. With the best regards and thanks, Saradhi Motamarri +61430022130 Sent from Yahoo Mail for iPhone
Saradhi:
Lester's response and the presentation he mentioned are great sources and I recommend going through it all. For the benefit of anyone in a pinch to get a quick look when searching this forum down the road, here is a quick query to run in each database:
SELECT tabname, min(constructed) as oldest, max( constructed ) as latest
FROM systables as st, sysdistrib as sd
WHERE st.tabid = sd.tabid
GROUP BY 1
ORDER by 2 ASC;Add a filter for "AND tabid > 99 " to eliminate system catalog tables if you only want to look at user tables.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog:
http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Thank you very much Art..
Hi Saradhi, I am not aware of any built-in history of Update Statistics "process", but one indicator is the column ustlowts In systables, which tells the last date update stats low has been applied on this table. In systables, you will also see 'nrows' which is set by update statistics low, with the real count of rows at this time. IMO, IBM's AUS is a good start to have your statistics up to date, but Art kagel's 'do_stats' utility goes further than AUS, in a more accurate and efficient way. It is also handled by the Informix scheduler, so it has history. You can find do_stats in the IIUG Software Repository, along with bunch of excellent free utilities which I highly recommend you to take a look at. Here is where you'll have to take your browser https://sourceforge.net/projects/iiug-software-repository/ It is in DBAdmin-Tools / utilis2_ak Take time to browse the repository Take care Eric Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting Board of Directors, International Informix Users group IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org
Hi Eric, Thank you. Nice to see valuable information, digesting them.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g