Auto Update Stats
A user wanted to enable Auto Update Statistics (AUS) in an 11.70 instance and asked which sysadmin scheduler tasks need tk_enable='t', how to schedule runs more often, and how STATCHANGE relates to AUS. IBM's Nita Dembla answered: below 11.70xC6 you must enable Auto Update Statistics Evaluation, Refresh and mon_table_profile; from 11.70xC6 up mon_table_profile can stay disabled (Evaluator uses partition counters) and AUS_CHANGE is deprecated. For two-hourly runs set tk_frequency to '0 2:00:00' and set tk_monday..tk_friday to 't', since Refresher defaults to weekends. STATCHANGE/AUTO_STAT_MODE are separate 'smarter statistics' settings that make an update statistics a no-op unless the table changed by that percentage (override per table with ALTER TABLE, or use FORCE). Ben Thompson added a blog link, noted a defect where updating tk_enable directly doesn't fire the next-execution-time trigger, and suggested STATCHANGE 0 globally with per-table values.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Team
We are working on implementation of Auto Update statistics (AUS) in our
product using Informix database.
For that we are doing the below changes in onconfig file
AUTO_STAT_MODE 1
STATCHANGE 10
USTLOW_SAMPLE 0
As we know Auto Update Statistics (AUS) maintenance system uses a combination
of Scheduler sensors, tasks, thresholds, and tables to evaluate and update
statistics.
we have 'tk_enable' column set to 'false' for mon_table_profile, Auto Update
Statistics Evaluation, Auto Update Statistics Refresh tables in sysadmin
database.
So do I need to make this coulmn tk_enable 'true' for any of the mention
tables to implement AUS in our product or AUS will work fine with this default
values as well i.e. tk_enable='F'.
Currently we have a sysagent task that update stats once per day and we are
able to see the update statistics in aus_cmd_comp table as well for that
particular time period (even though tk_enable is set to 'F' as mentioned
above) because we are calling the Evaluator and Refresh Externally. We want to
disable this sysagent task and implement AUS.
What if I make tk_enable true only for Auto Update Statistics Evaluation &
Auto Update Statistics Refresh and not for mon_profile_table because
mon_profile_table is used by Evaluator. Since Evaluator gathers information
from systable, sysdistrib, syscolumns, and sysindices tables in the sysmaster
database as well so what happen if I make tk_enable false for
mon_profile_table. what is the impact of that?
One more question. STATCHANGE of 10% means whenever any table stats are
changed by 10%, the AUS will run instantly or AUS will always run at scheduled
time set in Auto Update Statistics Evaluation and determines which tables need
updates based on the expiration policies set by STATCHANGE.
Please suggest on this
Thanks
Anil
On any 11.xx version lower than 11.70xC6, you would need to set tk_enab=
le
to "t" for all the following tasks:
Auto Update Statistics Evaluation
Auto Update Statistics Refresh
mon_table_profile
AUS Evaluator depends on the information collected by mon_table_profile=
to
identify the how much the tables have changed over a period of time. AU=
S
Evaluator uses AUS_CHANGE parameter to decide if a table qualifies for
statistics rebuild. AUS Refresher executes the update statistics on tab=
les
identified by the Evaluator.
You can configure when to run the AUS tasks by updating ph_task table
manually or using OAT (Open Admin Tool) graphical interface.
On version 11.70xC6 and higher, you can disable "mon_table_profile" as =
AUS
Evaluator has been revised to use partition counters. Also AUS_CHANGE h=
as
been deprecated.
AUTO_STAT_MODE and STATCHANGE are not directly tied to AUS feature. The=
y
are part of Smarter Statistics and Fragment Level Statistics features.
STATCHANGE of 10% means that a table needs a change of atleast 10% befo=
re
any update statistics on it will be run. The server automatically
determines this and makes a update statistics command a "no-op" if
STATCHANGE percentage is not met. You can append a "FORCE" keyword to t=
he
update statistics command to ignore the STATCHANGE comparison.
You can learn more about this feature from my developerWorks article - =
Take
advantage of fragment-level statistics and smarter statistics in IBM
Informix
Regards,
Nita Dembla, PMP=AE
IBM Informix Development
nita@us.ibm.com
=
"ANIL GOYAL" =
<dabwali312@yahoo =
.co.in> =
To
Sent by: ids@iiug.org, =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Auto Update Stats [30669] =
06/26/2013 10:26 =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
Hi Team
We are working on implementation of Auto Update statistics (AUS) in our=
product using Informix database.
For that we are doing the below changes in onconfig file
AUTO_STAT_MODE 1
STATCHANGE 10
USTLOW_SAMPLE 0
As we know Auto Update Statistics (AUS) maintenance system uses a
combination
of Scheduler sensors, tasks, thresholds, and tables to evaluate and upd=
ate
statistics.
we have 'tk_enable' column set to 'false' for mon_table_profile, Auto
Update
Statistics Evaluation, Auto Update Statistics Refresh tables in sysadmi=
n
database.
So do I need to make this coulmn tk_enable 'true' for any of the mentio=
n
tables to implement AUS in our product or AUS will work fine with this
default
values as well i.e. tk_enable=3D'F'.
Currently we have a sysagent task that update stats once per day and we=
are
able to see the update statistics in aus_cmd_comp table as well for tha=
t
particular time period (even though tk_enable is set to 'F' as mentione=
d
above) because we are calling the Evaluator and Refresh Externally. We =
want
to
disable this sysagent task and implement AUS.
What if I make tk_enable true only for Auto Update Statistics Evaluatio=
n &
Auto Update Statistics Refresh and not for mon_profile_table because
mon_profile_table is used by Evaluator. Since Evaluator gathers informa=
tion
from systable, sysdistrib, syscolumns, and sysindices tables in the
sysmaster
database as well so what happen if I make tk_enable false for
mon_profile_table. what is the impact of that?
One more question. STATCHANGE of 10% means whenever any table stats are=
changed by 10%, the AUS will run instantly or AUS will always run at
scheduled
time set in Auto Update Statistics Evaluation and determines which tabl=
es
need
updates based on the expiration policies set by STATCHANGE.
Please suggest on this
Thanks
Anil
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
A very good article on update databse stats. Thanks for sharing it. we are working on informix version 11.70.UC7X3. As we know Evaluator gathers information not only from mon_table_profile but from systable, sysdistrib, syscolumns, and sysindices tables in the sysmaster database as well so what happen if I make tk_enable='f' for mon_profile_table and 't' for Evaluator and Refresh. what is the impact on performance? We want to implement Smarter Statistics. We have a requirement in our product that we want to run auto update statistics task multiple times a day. I think there is one parameter tk_frequency in tables Auto Update Statistics Evaluation, Auto Update Statistics Refresh and mon_table_profile . Right now its value is set to '1 00:00:00.00000'. Now if i want to run this task, suppose every two hours, then what value of tk_frequency i need to set. Please suggest on this Thanks Anil
Since you are on 11.70UC7, you can disable "mon_table_profile" task, it= will not have any ill-effects on either the Evaluator or performance. You if want to run the AUS tasks every couple of hours, you should set tk_frequency to "0 2:00:00". By default, Refresher is enabled only on Saturday and Sunday. You will= need to set the columns tk_monday, tk_tuesday,.., tk_friday to "t" so i= t runs everyday. Smarter statistics is enabled by default. You can tweak the STATCHANGE = to cater your specific requirements. Regards, Nita Dembla, PMP=AE IBM Informix Development nita@us.ibm.com = "ANIL GOYAL" = <dabwali312@yahoo = .co.in> = To Sent by: ids@iiug.org, = ids-bounces@iiug. = cc org = Subj= ect Re: Auto Update Stats [30679] = 06/27/2013 02:28 = AM = = = Please respond to = ids@iiug.org = = = A very good article on update databse stats. Thanks for sharing it. we are working on informix version 11.70.UC7X3. As we know Evaluator gathers information not only from mon_table_profil= e but from systable, sysdistrib, syscolumns, and sysindices tables in the sysmaster database as well so what happen if I make tk_enable=3D'f' for mon_profile_table and 't' for Evaluator and Refresh. what is the impact on performance? We want to implement Smarter Statistics. We have a requirement in our product that we want to run auto update statistics task multiple times a day. I think there is one parameter tk_frequency in tables Auto Update Statistics Evaluation, Auto Update Statistics Refresh and mon_table_profile . Right now its value is set to '1 00:00:00.00000'. Now if i want to run = this task, suppose every two hours, then what value of tk_frequency i need t= o set. Please suggest on this Thanks Anil ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Hi,
I have one more query.
I have the below entries in onconfig file for AUS
AUTO_STAT_MODE 1
STATCHANGE 10
USTLOW_SAMPLE 0
Now all my tasks(mon_table_profile,Evaluator and Refresh) running fine at
scheduled time.
Now when I remove the above entries in onconfig file, still all my tasks are
running at scheduled time.
I read in informix doc that in order to enable AUS, it is mandatory to enter
above params in onconfig file. So i want to know even in the absence of those
params in onconfig file, AUS is working fine.
Please comment on this
Thanks
Anil
Hi Anil,
You might want to read my article on working with auto update stats:
http://informixdba.wordpress.com/2013/05/31/working-with-auto-update-stats/
I would add a couple of things:
1. If you just change the tk_enable column in ph_task using dbaccess or
similar, this does not fire the trigger that sets the next execution time of
the task. There is a defect I raised with support about this:
IC92632: ENABLE TASK IN PH_TASK DOES NOT FIRE THE TRIGGER
ph_task_trig_update_exec_time AUTOMATICALLY.
This is simply because the trigger does not operate on that column. It works
on many of the others, like the ones for each day of the week.
2. I think the default STATCHANGE value of 10 is potentially risky. It takes
no account of the age of your stats and if you're querying on date/time fields
with distributions, aged stats can be an issue even with a small volume of
change.
My recommendation is to set this to 0 in 'onconfig' whilst still having
AUTO_STAT_MODE on so it only applies to static tables and use 'ALTER TABLE X
STATCHANGE Y' to set a custom value on any table where you want to implement a
different policy.
Ben.
Ben:
Love your blog post on AUS! Well written and well thought out.
On the AUS -vs- my tools debate, let me add the following points:
- If you like to set up distributions levels differently for different
tables due to load patterns etc., you have two options with my tools to
maintain those (and you can combine the two):
1. Use the dostats' -i@filepath option to pass lists of tables to
include (or -x@file for tables to exclude) for default processing and
either process those tables with custom dostats options (it is fully
configurable) or maintain those tables using other scripts, even ones
produced as in 2 below
2. Use the myschema -u option to generate a script of update
statistics commands to replicate the existing distributions and stats
levels.
- I would mention that dostats has the ability to schedule itself into
the task scheduler to replace AUS's evaluator and refresh tasks and that
its aging (-a) and browsing (-b) options can use the AUS thresholds
(--aus-thresholds) to configure its operations so you can use OAT to
configure cron or task scheduler runs' behavior.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, 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.
On Wed, Jul 3, 2013 at 9:09 AM, BENJAMIN THOMPSON <
benjamin.thompson@bskyb.com> wrote:
> Hi Anil,
>
> You might want to read my article on working with auto update stats:
> http://informixdba.wordpress.com/2013/05/31/working-with-auto-update-stats/
>
> I would add a couple of things:
>
> 1. If you just change the tk_enable column in ph_task using dbaccess or
> similar, this does not fire the trigger that sets the next execution time
> of
> the task. There is a defect I raised with support about this:
>
> IC92632: ENABLE TASK IN PH_TASK DOES NOT FIRE THE TRIGGER
> ph_task_trig_update_exec_time AUTOMATICALLY.
>
> This is simply because the trigger does not operate on that column. It
> works
> on many of the others, like the ones for each day of the week.
>
> 2. I think the default STATCHANGE value of 10 is potentially risky. It
> takes
> no account of the age of your stats and if you're querying on date/time
> fields
> with distributions, aged stats can be an issue even with a small volume of
> change.
>
> My recommendation is to set this to 0 in 'onconfig' whilst still having
> AUTO_STAT_MODE on so it only applies to static tables and use 'ALTER TABLE
> X
> STATCHANGE Y' to set a custom value on any table where you want to
> implement a
> different policy.
>
> Ben.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01493380997fdd04e09baf24
To follow-up on my own post, Anil has asked me by email what the relationship
is between STATCHANGE in the onconfig file and the AUS_CHANGE parameter of
auto update stats. I don't think the Informix manuals make this very clear.
My understanding is - and I'd appreciate it if someone could confirm or
correct it - the following:
STATCHANGE in the onconfig file takes effect when AUTO_STAT_MODE is set to 1.
It means that for ALL 'update statistics' commands run - whether by AUS, a
third-party tool or through dbaccess - that if the percentage of changed rows
does not exceed STATCHANGE the 'update statistics' command will not do
anything and complete immediately. Confusingly it still returns the result
'statistics updated' though.
The value of STATCHANGE can be overridden on a per-table basis using ALTER
TABLE.
The value of AUS_CHANGE in sysadmin:ph_threshold just applies to AUS and I
think it stops the evaluator flagging tables with insufficient change for any
statistics updates.
I think that if AUS_CHANGE is set lower than STATCHANGE in onconfig (with
AUTO_STAT_MODE on) the evaluator will flag some tables for refresh but when
they come to be run the 'update statistics' command will do nothing.
Hi Benjamin,
Your description of differentiation between STATCHANGE and AUS_CHANGE i=
s
right.
One important point to note is that in 12.10xC1, AUS now supports all t=
ypes
of databases - ANSI, logging and non-logging. Additionally AUS_CHANGE h=
as
been deprecated. The AUS evaluator now prioritizes the order of tables
based on the % of change.
AUS now relies on the server to automatically use STATCHANGE and determ=
ine
if the update statistics issued by AUS refresher should be run or be a
NO-OP. This allows users to apply specific change threshold at table,
database and server levels.
Regards,
Nita Dembla, PMP=AE
IBM Informix Development
Office: 404-238-4109 (T/L: 623-7266)
nita@us.ibm.com
=
"BENJAMIN =
THOMPSON" =
<benjamin.thompso =
To
n@bskyb.com> ids@iiug.org, =
Sent by: =
cc
ids-bounces@iiug. =
org Subj=
ect
Re: Auto Update Stats [30759] =
=
07/04/2013 07:21 =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
To follow-up on my own post, Anil has asked me by email what the
relationship
is between STATCHANGE in the onconfig file and the AUS_CHANGE parameter=
of
auto update stats. I don't think the Informix manuals make this very cl=
ear.
My understanding is - and I'd appreciate it if someone could confirm or=
correct it - the following:
STATCHANGE in the onconfig file takes effect when AUTO_STAT_MODE is set=
to
1.
It means that for ALL 'update statistics' commands run - whether by AUS=
, a
third-party tool or through dbaccess - that if the percentage of change=
d
rows
does not exceed STATCHANGE the 'update statistics' command will not do
anything and complete immediately. Confusingly it still returns the res=
ult
'statistics updated' though.
The value of STATCHANGE can be overridden on a per-table basis using AL=
TER
TABLE.
The value of AUS_CHANGE in sysadmin:ph_threshold just applies to AUS an=
d I
think it stops the evaluator flagging tables with insufficient change f=
or
any
statistics updates.
I think that if AUS_CHANGE is set lower than STATCHANGE in onconfig (wi=
th
AUTO_STAT_MODE on) the evaluator will flag some tables for refresh but =
when
they come to be run the 'update statistics' command will do nothing.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=