IDS Forum

Re: Misconceptions about Update Statistics

Posted By: Marcus Haarmann
Date: Thursday, 23 November 2017, at 8:27 a.m.

In Response To: Misconceptions about Update Statistics (Dirk Moolman [ MTN South Africa ])

Hi,

update statistics is a complex subject.
In general:
The query optimizer needs to know (roughly or nearly exact, you can parametrize this) about the data distributions
of the indexed columns to automatically choose the correct indexes for specific queries.
In case the statistics are not accurate, the optimizer might choose the wrong table to search for
specific criteria in the first place, while another table (with an indexed column) would be more "selective".

That means, the frequency of update statistics and the resolution for a specific table has to be individually guessed.
It very much depends on the total size of the table (a table with 200 records and two indexes has a different handling compared
to a table with 10000000 records and 7 indexes).

In case no statistics are present, the query optimizer only takes a look at the number of rows in a table
and if it finds an index for some selected columns which can be filtered with the query.
That kind of decision is relatively ok in most of the cases, but not always (e.g. when two relatively large tables
are involved in one query and both have indexes on some of the queried fields.
That might go into the wrong direction.

In case wrong statistics (outdated, number of rows has significantly increased since last statistic run)
are present, the query might run into a totally wrong guess. We encountered such situations where a query
ran a sequential scan over a very large table instead of choosing the existing index of a connected table.

dostats is a very nice mechanism which is able to do statistics in a good way, but it also has its parameters
you need to explore for your data situation.
The interesting part here is that the dostats program makes some internal decisions which in many cases are correct
and lead to accurate statistics.
Of course it is up to you how often you run the program.

The builtin mechanism of auto statistics via Scheduler is possibly a choice which you might take.
The main difference from my point of view is that dostats can be controlled in an easier way,
but also auto stats can leave you with a set of very good statistics.

As a result: it depends on your data which way is correct.

Last point: when doing a major release upgrade, dropping and re-creating of data distributions is recommended,
but I have been doing migrations of very huge databases without doing that because the existing statistics were
still working and simply dropping the data distributions would have taken many hours, not talking about re-creation.

Best regards,

Marcus Haarmann

Von: "Dirk Moolman [ MTN South Africa ]" <Dirk.Moolman@mtn.com>
An: "ids" <ids@iiug.org>
Gesendet: Donnerstag, 23. November 2017 13:45:06
Betreff: Misconceptions about Update Statistics [40252]

Has anyone here perhaps written an article about the Misconceptions of Update
Statistics ?

I am getting so tired of Developers telling me how Update Stats is the fix for
EVERYTHING.

Don't think the detail is necessary, but we run:
IDS 12.10.FC9W1

AIX 7

Regards
Dirk

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Misconceptions about Update Statistics
  Dirk Moolman [ MTN South Africa ] -- Thursday, 23 November 2017, at 7:45 a.m.
  • Re: Misconceptions about Update Statistics
    Alberto Romeu Pessonio Filho -- Thursday, 23 November 2017, at 8:05 a.m.
    • Re: Misconceptions about Update Statistics
      BENJAMIN THOMPSON -- Tuesday, 28 November 2017, at 4:10 a.m.
  • Re: Misconceptions about Update Statistics
    Marcus Haarmann -- Thursday, 23 November 2017, at 8:27 a.m.
  • Re: Misconceptions about Update Statistics
    Spokey Wheeler -- Thursday, 23 November 2017, at 8:52 a.m.
    • RE: Misconceptions about Update Statistics
      Dirk Moolman [ MTN South Africa ] -- Friday, 24 November 2017, at 7:23 a.m.
    • Re: Misconceptions about Update Statistics
      NEIL TRUBY -- Sunday, 3 December 2017, at 2:02 p.m.
  • Re: Misconceptions about Update Statistics
    BENJAMIN THOMPSON -- Tuesday, 28 November 2017, at 3:59 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.