Misconceptions about Update Statistics
Dirk asked whether anyone had written an article debunking the idea that UPDATE STATISTICS fixes everything, as his developers treat it as a cure-all (IDS 12.10.FC9W1 on AIX 7). Replies explained why the optimizer needs reasonably current distributions, how stale/missing stats can cause bad plans and sequential scans, and compared dostats, custom scripts and the built-in AUS scheduler. Ben Thompson pointed to two blog posts (on AUS experience and when stored-procedure plans get refreshed), and noted you needn't drop distributions before UPDATE STATISTICS HIGH, that UPDATE STATISTICS FOR PROCEDURE is usually unnecessary, that PDQ often slows stats collection, and that stale-data issues on fast-growing tables are improved in 12.10 (and 11.70.FC7W3+ via SQL_DEF_CTRL 0x2). No single fix — the thread is advice and references rather than a resolved problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
Hi Dirk! I noticed using IDS versions after v10.00 that the optimizer demands more accurate statistics information from tables that grows significantly every day. Based on my perception, I tried to use dostats (that improved performance) but wasn't enough. So I developed a statistics collection system with the following rules: - Drop statistics of a columns followed by collecting high mode statistics for the same column for every indexed columns - After that, update statistics for procedure - This routine repeats for every database of the instance. So I developed a system with database execution queues, and set 2 queues running in parallel with a properly PDQ for each queue, based on the relevance of each database. After deploy this system on the company's production environment, our instances runs very fast, decreasing I/O and CPU usage, running very stable. Let me know your thoughts about it and if you need some help in order to implement it. Regards, Alberto. Em 23 de nov de 2017 10:45 AM, "Dirk Moolman [ MTN South Africa ]" < Dirk.Moolman@mtn.com> escreveu: 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.
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.
Have you tried running UPDATE STATISTICS? > On 23 Nov 2017, at 12:45, Dirk Moolman [ MTN South Africa ] <Dirk.Moolman@mtn.com> wrote: > > 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. >
hahaha -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Spokey Wheeler Sent: Thursday, 23 November 2017 3:52 PM To: ids@iiug.org Subject: Re: Misconceptions about Update Statistics [40255] Have you tried running UPDATE STATISTICS? > On 23 Nov 2017, at 12:45, Dirk Moolman [ MTN South Africa ] <Dirk.Moolman@mtn.com> wrote: > > 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. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Well, kind of yes: Experience with Auto Update Statistics (AUS) https://informixdba.wordpress.com/2015/04/30/experience-with-auto-update-statist ics-aus/ The article doesn't just discuss AUS, much of what is in it is relevant no matter what method you use as they are all fundamentally the same (AUS, dostats, your custom script etc.). When do my stored procedure execution plans get updated? https://informixdba.wordpress.com/2017/06/23/when-do-my-stored-procedure-executi on-plans-get-updated/ Ben.
- Drop statistics of a columns followed by collecting high mode statistics
for the same column for every indexed columns
- After that, update statistics for procedure
- This routine repeats for every database of the instance. So I developed a
system with database execution queues, and set 2 queues running in parallel
with a properly PDQ for each queue, based on the relevance of each database.
A few things I'd say about this strategy:
It isn't necessary to drop a distribution before collecting them again: you
can just do UPDATE STATISTICS HIGH. Dropping leaves you with no distribution
during this time which might be ok on a system when you can take regular
downtime but would otherwise be risky.
Update statistics for procedure isn't usually necessary because the query planwill get updated the first time it's called if any of the tables referenced in
the procedure have had their statistics updated.
PDQ for update statistics is usually slower but the two queues idea is good.
You mention:
"I noticed using IDS versions after v10.00 that the optimizer demands more
accurate statistics information from tables that grows significantly every
day."
Is this essentially because you have incrementing ID fields or date or
date/time fields indicating the time of the record and you query for records
added after you last updated statistics? If so, this is much improved in 12.10
and later 11.70 versions (11.70.FC7W3+ I think...) can implement the same
feature if undocumented onconfig parameter SQL_DEF_CTRL is set to 0x2.
Ben.
Sarcasm (or is this irony?) is the lowest form of wit. (Although the highest form of intelligence, obvs ;-)).