IDS Forum

Re: UPDATE STATISTICS Performace Improvement

Posted By: Art Kagel
Date: Wednesday, 3 June 2009, at 11:55 a.m.

In Response To: Re: UPDATE STATISTICS Performace Improvement (KAMRAN HAQ)

If you run dostats with the browse options (-b/-B) it looks at each table's
partition header page (sysmaster:sysactptnhdr) for the actual number of rows
versus the number recorded during the last update statistics run in
systables. If those values differ by more than a specified percentage then
new distributions are generated for that table.

If you run dostats with the aging options (-a/-A) it looks at the age of
each table's data distributions (<database>:sysdistrib). If that date is
more than a specified number of days old then new distributions are
generated for that table.

What I normally do for my clients (and what I set up when I was at
Bloomberg) was to run dostats every night with browsing and aging options
enabled to 7 day aging and 15% browsing and on the weekend with no browsing
- 7 day aging only. That way all tables have fresh stats calculated at
least once a week and any table that has large numbers of rows inserted or
deleted will be updated midweek. You can do something similar or you can
just run dostats or use the built-in Auto Update Statistics feature so do
the same.

If you keep statistics on inserts, deletes, and updates (perhaps using a
sensor in the 11.50 scheduler) you can also update tables that have had a
significant number of updates or even offsetting inserts and deletes -
something that dostats can't detect.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, Jun 3, 2009 at 11:15 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:

> Thanks for such a nice and detailed answer. Here are answers what you
> asked.
>
> First what engine version are you running?
> IDS 11.5 FC4
>
> How much memory is on themachine?
> 16 GB (4 GB free usually)
>
> Do you have three or more independent filesystems that you can use for
> temporary sort-work files?
> Production servers are connected through SAN
>
> How can we identify what tables need update stats. Is there any sysmaster
> view?
>
> regards,
> Kamran
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c5a4406b996e046b73b0ff

Messages In This Thread

• UPDATE STATISTICS Performace Improvement
  ANEES AHMAD -- Thursday, 28 May 2009, at 8:20 a.m.
  • Re: UPDATE STATISTICS Performace Improvement
    Art Kagel -- Thursday, 28 May 2009, at 9:44 a.m.
  • Re: UPDATE STATISTICS Performace Improvement
    Khaled Bentebal -- Thursday, 28 May 2009, at 12:16 p.m.
  • Re: UPDATE STATISTICS Performace Improvement
    jak aka -- Friday, 29 May 2009, at 3:14 a.m.
    • Re: UPDATE STATISTICS Performace Improvement
      KAMRAN HAQ -- Tuesday, 2 June 2009, at 10:49 a.m.
      • Re: UPDATE STATISTICS Performace Improvement
        Art Kagel -- Tuesday, 2 June 2009, at 12:26 p.m.
        • Re: UPDATE STATISTICS Performace Improvement
          KAMRAN HAQ -- Wednesday, 3 June 2009, at 11:15 a.m.
          • Re: UPDATE STATISTICS Performace Improvement
            Art Kagel -- Wednesday, 3 June 2009, at 11:55 a.m.
        • RE: UPDATE STATISTICS Performace Improvement
          LARRY SORENSEN -- Tuesday, 9 June 2009, at 1:10 p.m.
          • Re: UPDATE STATISTICS Performace Improvement
            Richard Kofler -- Tuesday, 9 June 2009, at 1:26 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.