IDS Forum

RE: Performance (Update Statistics) in v10

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Tuesday, 29 May 2007, at 1:17 p.m.

In Response To: RE: Performance (Update Statistics) in v10 (Clifton Bean)

IB the problem is the overnight removal of old data and new day's addition of
new data. I'd guess that the query(ies) in the 4ge include the keys column(s)
that are specific to newer data in their filter or join columns. When you run
the report without updating stats immediately before the report the stats say
there are N rows from the oldest day(s) that no longer exist in actuality and
that there are no rows for today which there definitely are now. Here are your
choices, and it's one we all had to deal with after upgrading from 5.xx to
7/9/10. Just before the report runs, either:

- UPDATE STATISTICS LOW ... DROP DISTRIBUTIONS; for the tables referenced in the
report -or-
- run the full suite of update stats as recommeded in the Performance Guide or
the tech paper mentioned earlier or using my dostats utility.

The first option will drop the modern data distribution information used by the
v10 optimizer and force it to revert to OL5 optimizer behavior which should
perform just as it used to. The second option will, as you've already seen,
provide additional data for the optimizer to use to do an even better job of
optimizing the query than OL5 could.

In 5.xx it just wasn't as critical that the stats be up-to-date as all that OL5
used was the row count and for each column in a key the second highest and
second lowest value the column contained. IDS 10 using the more detailed
distributions can make better decisions than 5 could, but only if the stats are
recent and accurate enough.

Art S. Kagel

----- Original Message -----
From: Clifton Bean <ids@iiug.org>
At: 5/24 10:50:23

Answers to Keith's questions ...

1. No stored procedures nor triggers are used within this instance.

2. Tables in use are the main tables for this instance. These tables
contain 60 days worth of data. Each morning, a fifo run takes the earliest
information in some of these tables and moves them to a different database.
During the day, new data for the current day is moved in.

3. There is one temporary table; at end of report execution, it may have a
total of 15 rows.

4. Query plan gives me the same results before and after the update
statistics.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith
Simmons
Sent: Thursday, May 24, 2007 9:36 AM
To: ids@iiug.org
Subject: Re: Performance (Update Statistics) in v10 [9229]

On 24/05/07, Clifton Bean <cbean@ctm.com> wrote:
> I have a very strange situation occurring.
>
> I just performed a migration from v5 to v10 via multiple unload/loads into

> the new instance. Practically everything seems to be running well insofar
> as order entry is concerned. I am running update statistics (low, medium,
> high on indexes) as they should be - currently on a nightly basis.
>
> However, I am experiencing an issue with report (4ge) that take upwards of

> several hours to run on v10 when, on the prior v5, they took less than 10
> minutes. Now the strange - if I run a new update statistics before running

> the report, it takes 7 minutes.
>
> 10.00.UC6, AIX 5.2
>
> Thanks in advance for your assistance.
>
> Take care.
>
> Clifton M. Bean
> Informix DBA / AIX System Admin
> Currency Technics & Metrics
> 1431 Greenway Drive #700
> Irving, Texas 75038
>
> Main (972) 812-1411 x244
>
> Toll Free (800) 834-8807 x244
>
> Fax (469) 417-0665
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Clifton

V5 (and V7) were quite happy to just accept there was data in a table
and took a 'best guess' at the index to use based on the query. V10
(and V9) absolutely demand low stats to be run (to count the rows in
each table) and like medium nand high stats to really point the
optimiser in the right direction.
However the stats you are running should be OK. Are the reports using
stored procedure that are not included in you nightly stats but are in
the pre-report stats? Are any of the tables access in your reports
highly volitile? or are any dropped and re-created just before the
report is run.? Is there a massive temp table created in the session
just before the report taht needs stats run against it? Have you
extracted the SQL out of the report and run it separately to get a
query plan (or switched query plan on during the (sorry, forgot this
was V10 and you can do that))?

Keith

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

Messages In This Thread

• Performance (Update Statistics) in v10
  Clifton Bean -- Thursday, 24 May 2007, at 9:48 a.m.
  • Re: Performance (Update Statistics) in v10
    Keith Simmons -- Thursday, 24 May 2007, at 10:35 a.m.
    • RE: Performance (Update Statistics) in v10
      Clifton Bean -- Thursday, 24 May 2007, at 10:49 a.m.
      • RE: Performance (Update Statistics) in v10
        Miguel Carbone -- Thursday, 24 May 2007, at 11:08 a.m.
      • RE: Performance (Update Statistics) in v10
        ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 29 May 2007, at 1:17 p.m.
    • RE: Performance (Update Statistics) in v10
      Miguel Carbone -- Thursday, 24 May 2007, at 11:17 a.m.
    • RE: Performance (Update Statistics) in v10
      mosserp@wellsfargo.com -- Thursday, 24 May 2007, at 3:02 p.m.
  • Re: Performance (Update Statistics) in v10
    Wayne B. Houseknecht -- Friday, 25 May 2007, at 2:54 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.