IDS Forum

Re: Auto Update Stats

Posted By: Nita Dembla
Date: Wednesday, 26 June 2013, at 3:30 p.m.

In Response To: Auto Update Stats (ANIL GOYAL)

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.

=

Messages In This Thread

• Auto Update Stats
  ANIL GOYAL -- Wednesday, 26 June 2013, at 10:26 a.m.
  • Re: Auto Update Stats
    Nita Dembla -- Wednesday, 26 June 2013, at 3:30 p.m.
    • Re: Auto Update Stats
      ANIL GOYAL -- Thursday, 27 June 2013, at 2:28 a.m.
      • Re: Auto Update Stats
        Nita Dembla -- Sunday, 30 June 2013, at 10:33 a.m.
        • Re: Auto Update Stats
          ANIL GOYAL -- Tuesday, 2 July 2013, at 1:24 a.m.
  • Re: Auto Update Stats
    BENJAMIN THOMPSON -- Wednesday, 3 July 2013, at 9:09 a.m.
    • Re: Auto Update Stats
      Art Kagel -- Wednesday, 3 July 2013, at 9:46 a.m.
    • Re: Auto Update Stats
      BENJAMIN THOMPSON -- Thursday, 4 July 2013, at 7:21 a.m.
      • Re: Auto Update Stats
        Nita Dembla -- Monday, 8 July 2013, at 6:03 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.