IDS Forum

Re: Auto Update Statistics

Posted By: Jonathan Leffler
Date: Friday, 3 October 2003, at 5:35 p.m.

First of all - I've got to eat my words, sort of.

My comment is accurate with respect to the server - there is no automatic
system in the server for doing update statistics.

However, in versions of ODBC prior to 3.82, the ODBC driver does automatic
update statistics in response to certain calls - SQLStatistics being one
that was mentioned. This feature has been disabled (fixed) in 3.82 in
response to the performance problem reports from a number of customers. If
I'm reading the data correctly, B162089 is fixed in 3.82.TC2 (and, by
inference, not in 3.82.TC1).

Thanks to the ODBC team for pointing out that I really don't know
everything - which, in relation to Windows, is not news to me at all - and
for allowing me to provide you with the correct information.


John Carlson asked:
How much overhead would that add to the system?

That would depend on how it was done, of course - not much would be my
intention, and it would be controllable. There are various ways in which
it could be managed. Some of the key information is how much the data has
changed since UPDATE STATISTICS was last run on the table. Under my ideal
scheme, we get back to the SE-style simplicity - UPDATE STATISTICS would
run an optimal set of UPDATE STATISTICS for the entire database. UPDATE
STATISTICS FOR TABLE x would run the optimal UPDATE STATISTICS for the
table x, etc. For reasons of backwards compatibility, it might be
necessary to add a keyword (OPTIMAL being a prime candidate) to the syntax.
The definition of 'optimal' is what gets hard. In the absence of better
heuristics, it should be the algorithm published in the performance guide
(and implemented by Art Kagel's dostats program). The tricky part is how
to allow a sysadmin define overrides for the default behaviour. And how to
organize scheduling...

Overriding default behaviour could be done by appropriately privileged
users (alter privs on the table, probably, or a new privilege) run a
variant of update statistics with the detailed information about what to do
(and/or when to do it) in place. Without having spent any time on syntax
"UPDATE STATISTICS OVERRIDE FOR TABLE x [STEP n] ...action
specification...". The override indicates that if there is no other
information in a system catalog table about update statistics for this
table, then the default operations should be placed in there. Once the
default steps exist, then the new action either replaces an existing step
(identified by STEP n) or is added to the end of the list of operations.
There might be some sort of spiel available to specify when the update
statistics should occur.

Scheduling:
The instance administrators should be able to configure (ONCONFIG
parameter) a daily window when automatic update statistics may be run at
all; the default window would be the whole day, of course.

For each table, the DBA or table owner should be able to say when the
statistics for the table should be updated - as a whole or in steps. It
would also be desirable to be able to specify change thresholds which would
trigger an automatic update statistics - say when the the total number of
changes since the last one is 100% of the rows in the table (with each
insert, delete or update being measured as a change - this information
would be stored mainly in shared memory and only occasionally written to
disk - perhaps at checkpoint time since it is only statistical info and
absolute precision is not critical to the operation of the database). Care
would have to be taken with absolute numbers vs percentages, especially on
small tables, or tables that happen to be empty, and so on. Ideally, the
language for scheduling should be able to handle schedules as complex as
after 22:00 on the last tuesday in july of every other leap year starting
in 2004. I doubt that it would be quite that flexible.

All of this is semi-informed hypothesizing - partly to get reactions.


--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"




|---------+----------------------------->
| | John Carlson |
| | <John_Carlson@whsm|
| | ithusa.com> |
| | |
| | 10/03/2003 10:20 |
| | AM |
|---------+----------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: Jonathan Leffler/Menlo Park/IBM@IBMUS, ids@iiug.org |
| cc: |
| Subject: RE: Auto Update Statistics [1988] |
>---------------------------------------------------------------------------------------------------------------------------------------------|




How much overhead would that add to the system?

-----Original Message-----
From: Jonathan Le.... [mailto:jleffler@us.ibm.com]
Sent: Friday, October 03, 2003 12:04 PM
To: ids@iiug.org
Subject: Re: Auto Update Statistics [1988]






There's no automatic UPDATE STATISTICS that I know of - much as I'd like
there to be one.

If I had my way, you'd never run them manually except if the system screwed
up. That's what autonomic computing is about.

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"




|---------+---------------------------->
| | "ADDAMS AU" |
| | <addamsau@pcihl.c|
| | om> |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 10/03/2003 02:16 |
| | AM |
|---------+---------------------------->

>---------------------------------------------------------------------------

--------------------------------------------------------------------|
|
|
| To: ids@iiug.org
|
| cc:
|
| Subject: Auto Update Statistics [1985]
|

>---------------------------------------------------------------------------

--------------------------------------------------------------------|




Hi,

I had found some "USTB" events in the Informix auditing log for a newly
purchased application use ODBC calls to access the database, but the vendor
said that they didn't include "UPDATE STATISTICS" statments in their
coding.
Can anyone have idea on this case? Is there "auto update statistics" option
in Informix database?


Best regards,
Addams




"CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
Retail. This email message and all attachments may contain legally
privileged and confidential information intended solely for the use of the
addressee. If you are not the intended recipient, you should immediately
stop reading this message and delete it from the system. Any unauthorized
reading, distribution, copying, or other use of this message or its
attachments is strictly prohibited. All personal messages express solely
the
sender's views and not those of WHSmith USA Travel Retail. This message may
not be copied or distributed without this disclaimer."

IDS Forum is maintained by Administrator with WebBBS 5.12.