IDS Forum

Re: The Scheduler and Update Statistics

Posted By: John Miller iii
Date: Monday, 12 October 2009, at 12:50 p.m.

In Response To: The Scheduler and Update Statistics (Clifton Bean)

Clifton:

Few comments on AUS:

1. AUS has two modes set by the parameter AUS_AUTO_RULES

If this is set to 0 then it will repeat only the update stats command
previously run. The system catalogs

have been updated to track allot of new information about the
running of update stats including the

confidence and resolution.

If this mode is set to 1 then it will repeat the exact commands (i.e.
same as mode 0), but it will ensure

that the minimum documented recommendations are met. If the
user/DBA has specifies higher or

the same statistics than the documentation requires AUS will
follow the users request. If the

user omits or is lower than what is recommended then it will
automatically add or upgrade the

command to the documented amount.

2. If you have modified the confidences or resolution, AUS will pick this
up and build the commands with

the confidence and resolution. In fact every command will list the
resolution and confidence. If

you use the new sample_size parameter then it will be added also.

EXAMPLE:

UPDATE STATISTICS HIGH FOR TABLE sysutils:syscolumns ( tabid,
extended_id ) RESOLUTION 0.500 DISTRIBUTIONS ONLY;

UPDATE STATISTICS MEDIUM FOR TABLE sysutils:syscolumns (colno)
RESOLUTION 2.000 0.950 DISTRIBUTIONS ONLY;

UPDATE STATISTICS LOW FOR TABLE sysutils:syscolumns

3. AUS does not do procedures, but a recent change has improved the
compilation of stored procedures. Now;

when you compile stored procedures, the are compiled in a separate
transaction. So you do not have.

to wait for the users transaction to commit before see the
re-optimized version. More importantly locks

are not held for possibly long period of time because IDS waits for
the users transaction to commit.

John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 10/12/2009 08:37:05 AM:

> [image removed]
>
> The Scheduler and Update Statistics [17481]
>
> Clifton Bean
>
> to:
>
> ids
>
> 10/12/2009 08:39 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> IDS 11.5.FC5, AIX 5.3
>
> I am about to start my investigation into the new AUS subsystem with11.50
and
> had a few questions I would like to ask.
>
> Is anyone currently using the AUS facility to generate and run update
> statistics? If yes, does the statistic scripts generated by this tool
follow
> the same guideslines as noted within the performance manual and/as noted
on
> the following webpage:
>
> http://www-01.ibm.com/support/docview.wss?uid=swg21137764
>
> If you are using it, have you modified its scripts to add RESOLUTION and
> CONFIDENCE information?
>
> If you are not using it, please let me know why.
>
> I just started at my current contract last week and do not currentlyhave
the
> ability to review data within the sysadmin database; however, I do know
the
> client is still using command line scripts to perform update statistics
at
> this time.
>
> Thanks in advance.
> Clifton
> _________________________________________________________________
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
> http://clk.atdmt.com/GBL/go/171222985/direct/01/
>
>
>
*******************************************************************************

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

Messages In This Thread

• The Scheduler and Update Statistics
  Clifton Bean -- Monday, 12 October 2009, at 11:37 a.m.
  • Re: The Scheduler and Update Statistics
    Art Kagel -- Monday, 12 October 2009, at 12:28 p.m.
  • Re: The Scheduler and Update Statistics
    John Miller iii -- Monday, 12 October 2009, at 12:50 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.