Informix

nested-group-icon.png

DB2

Expand all | Collapse all

AUTO_STAT_MODE - from where it calculates % changed

  • 1.  AUTO_STAT_MODE - from where it calculates % changed

    Posted 19 days ago
    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
    ------------------------------


  • 2.  RE: AUTO_STAT_MODE - from where it calculates % changed

    Posted 19 days ago
    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


    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.








  • 3.  RE: AUTO_STAT_MODE - from where it calculates % changed

    Posted 19 days ago
    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
    ------------------------------



  • 4.  RE: AUTO_STAT_MODE - from where it calculates % changed

    Posted 19 days ago
    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


    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.








  • 5.  RE: AUTO_STAT_MODE - from where it calculates % changed

    Posted 18 days ago
    Art,
    thank you for the explanation.

    ------------------------------
    Milan Rafaj
    ------------------------------



  • 6.  RE: AUTO_STAT_MODE - from where it calculates % changed

    Posted 9 days ago
    thanks for your suggestion

    ------------------------------
    MASHIVA TECHNOLOGIES PRIVATE LIMITED
    ------------------------------