Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    26

    Unanswered: Inconsistent runstats

    Hello, Any suggestions? Thanks

    runstats on table eaa.acct_ph with distribution on all columns and
    detailed indexes all
    SQL2314W Some statistics are in an inconsistent state. The newly collected
    "TABLE" statistics are inconsistent with the existing "INDEX" statistics.
    SQLSTATE=01650

    I have made sure that the backup completes before the runstats begin.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    SQL2314W  Some statistics are in an inconsistent state. The newly
          collected "<object1>" statistics are inconsistent with the
          existing "<object2>" statistics.
    
    Explanation:
    
    The query optimizer uses statistics to determine the best access plan
    for a query. When inconsistent statistics are present, the optimizer
    performs access plan evaluation with information that is less accurate
    then if a consistent set of statistics was available. This might result
    in an access plan that is not optimal.
    
    Possible causes of this warning include the following.
    
    1. Issuing RUNSTATS on the table only may result in a situation where
       the table level statistics are inconsistent with the already existing
       index level statistics. Likewise, issuing RUNSTATS for indexes only
       or during index creation may leave table level statistics in an
       inconsistent state. For example, if index level statistics are
       collected on a particular table and later a significant number of
       rows is deleted from this table, issuing RUNSTATS on the table only
       may end up with the table cardinality less than FIRSTKEYCARD which is
       an inconsistent state.
    2. If a RUNSTATS is issued with the ALLOW WRITE ACCESS option when many
       inserts, updates, or deletes are occurring concurrently, then
       inconsistencies may occur because of table changes between the time
       that table and index statistics are collected.
    3. If a RUNSTATS is issued with the TABLESAMPLE option, then an
       excessively low sample size may cause statistics to be inaccurately
       extrapolated, resulting in inconsistencies. The chance of inaccurate
       extrapolation is higher for SYSTEM sampling than for BERNOULLI
       sampling.
    
    User response:
    
    Address the cause of the inconsistency and then collect statistics again
    to resolve the inconsistency.
    
    1. Issue a RUNSTATS to collect both table level and index level
       statistics.
    2. Collect statistics when inserts, updates, and deletes are minimal or
       not occurring at all. Or, if concurrent inserts, updates, and deletes
       are not essential, issue a RUNSTATS with the ALLOW READ ACCESS
       option.
    3. Increase the sample size, or, if RUNSTATS was issued with TABLESAMPLE
       SYSTEM, use the TABLESAMPLE BERNOULLI option instead.
    
    sqlcode: 2314
    
    sqlstate: 01650
    Read number 2 closely.

    Andy

  3. #3
    Join Date
    Nov 2013
    Posts
    26
    Thanks Andy. Will look into it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •