AUTO_STAT_MODE - from where it calculates % changed
Milan Rafaj asked where AUTO_STAT_MODE gets the percentage changed. Art Kagel explained it compares the nupdates, ndeletes and ninserts values stored in sysdistrib or sysfragdist with the counters in the table's partition header (sysptnhdr). They are persisted on disk, so they stay comparable after a restart, though a crash may leave counts slightly off.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, I would like to know from what source update statistics calculates if should run or not if AUTO_STAT_MODE is set to one based on STATCHANGE? Is it also dependent on sysadmin mon_table_profile as AUS or uses some other source? Thanks a lot. ------------------------------ Milan Rafaj ------------------------------ #Informix
MIlan: In sysdistrib and sysfragdist in each database, the row for the distribution contains three columns: nupdates FLOAT, ndeletes FLOAT, ninserts FLOAT They record the status of the table at the time that this particular data distribution was created. On the partition page for each table, which can be seen in the sysmaster table sysptnhdr, there are three corresponding columns: ninserts BIGINT, nupdates BIGINT, ndeletes BIGINT, So, when you run UPDATE STATISTICS against a column in a table under the effect of AUTO_STAT_MODE the columns in the existing sysdistrib or sysfragdist record are compared to the table's current state as represented in the partition header page's corresponding columns through a non-trivial calculation to determine a percent change for the table since the column's distributions were last created at the desired level. 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.
Art, thank you, it was exactly I wanted to know. Just one more question - if engine is restarted since recent update statistics, are values in sysptnhdr still comparable with corresponding sysdistrib/sysfragdist or does some other algorithm apply? ------------------------------ Milan Rafaj ------------------------------
Mian: Yes. Those values are stored on disk and are persistent. The sysptnhdr pseudo table is a window into the shared memory image of partition header pages on disk that have been read into memory. The pages are kept in sync live when data is modified (there are several dynamic fields in that record) and are flushed at IDLE Write and Checkpoint time when modified. So, at worst, after a crash, it might not reflect those counts perfectly, but certainly good enough to have auto stats work predictably. 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.
Art, thank you for the explanation. ------------------------------ Milan Rafaj ------------------------------
thanks for your suggesti o n . ------------------------------ MASHIVA TECHNOLOGIES PRIVATE LIMITED ------------------------------