IDS Forum

Re: Fw: Optimizer

Posted By: Fernando Nunes
Date: Thursday, 29 March 2012, at 1:49 p.m.

In Response To: Re: Fw: Optimizer (Peter_Logan@spartanstores.com)

Hmmmm... How do you have the EXT_DIRECTIVES parameter in onconfig?
Regards.

On Thu, Mar 29, 2012 at 3:06 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:

> Ok .. here is the really strange thing ...
>
> If I do away with the function ... and put in place the fiscal week id ...
> then up until week 36 one query plan is used ... and then from week 37 on,
> a different query plan is used ... The first plan runs in a minute...the
> second several minutes ... when I put the directive on the query and
> force it to use the order of the tables that was chosen by the optimizer
> it always runs in a minute ...
>
> I'm trying to see if an external directive will work for this one .. just
> to give them that as a choice .. but I can't seem to get that working ...
> I can save the external directive, but when I execute the sql, the
> explain doesn't show the directive being used ... and I'm using the exact
> sql to create the directive as well as execute the sql ... wondering if
> the function in the sql is messing that up ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Fernando Nunes" <domusonline@gmail.com>
> To: ids@iiug.org
> Date: 03/29/2012 09:52 AM
> Subject: Re: Fw: Optimizer [26597]
> Sent by: ids-bounces@iiug.org
>
> Hmmm.... Yes, I think I can understand the point, although tables schema
> would help.
> I believe the problem is a well known issue (notice I use "issue" instead
> of "bug"). And recent versions have been seeing changes in that area.
>
> Would I be correct to assume the bad query plan is using a date/datetime
> index? This could be one source of the problem, but in your case it's
> probably another issue.
> You're asking the optimizer to calculate a query plan based on the unknown
>
> result of a user function. As such the optimizer must consider/estimate a
> selectivity. Too low will make that index non attractive, and too high
> makes it too attractive. And the dramatic issue is that it has no way to
> make a good estimate.
>
> I believe that recent versions (11.50.FC9 and maybe 11.70.FC2/3) may have
> change the way this selectivity is calculated. This solved many customer's
>
> problems and probably caused problems to many others...
>
> I'm not in developing but I believe 11.70.FC4 may have changed this. And
> eventually some improvements may appear in the future. Would it be too
> hard
> for you to try that query on 11.70.FC4?
>
> Of course, if you had the chance to avoid the use of the function and give
>
> it the value the problem should not happen.
> If you're sure that the query should NEVER use that index (on the column
> you're comparing with the function result), you can use some sort of
> operation on it. For example:
>
> my_column + 0 UNITS DAY = ( SELECT WEEKID(...) FROM systables WHERE tabid
> =
> 0)
>
> This will avoid the use of any index on that column and will not change
> the
> result set... hmmmm... But again, if that's a query generated from a tool
> this may not be feasible.
>
> P.S.: Above I avoided using the term "bug" because this is a nasty
> situation... We must consider some value for selectivity, but we have no
> idea about the possible or probable result of the function. So basically
> we're blind... I can't imagine a good solution (that will fit all the
> scenarios) for this.
>
> Regards.
>
> On Thu, Mar 29, 2012 at 1:27 PM, Peter_Logan@spartanstores.com <
> Peter_Logan@spartanstores.com> wrote:
>
> > Thanks for the reply ... Yes, the auto stats updating has been disabled
> > ... and I am aware of the external optimizer directives ...
> >
> > Let me try to sum this up a little differently ... when the date was say
>
> > 9/15/2011 .... one query plan was followed ... the one that works ...
> now
> > that the date is 3/28/2012 ... the optimizer is choosing a different
> path
> > ... so, what it seems to me is that the optimizer must be evaluating
> that
> > part of the query .. and then figuring out the query plan from there ...
> I
> > could understand that if this was a constant, but this isn't ... the
> > select weekid(today, -1) from systables where tabid = 1 is a procedure
> > that brings back the fiscal week id .. ie 201252 ... does that make a
> > little ore sense ... I'm trying to figure out if the optimizer is making
>
> > that evaluation .. then coming up with the plan.... and if so, how to
> keep
> > it from doing that ....
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From: "Fernando Nunes" <domusonline@gmail.com>
> > To: ids@iiug.org
> > Date: 03/28/2012 05:11 PM
> > Subject: Re: Fw: Optimizer [26592]
> > Sent by: ids-bounces@iiug.org
> >
> > I'm afraid I didn't understand your point correctly. You mention the
> > select
> > from systables but I didn't understand how it's relevant for the
> problem.
> > Are you saying that query presented works fine but if you use the select
>
> > from systables instead of:
> >
> > and a12.fiscal_week_id in (201253)
> >
> > It will break the query plan?
> >
> > In any case a few warnings and maybe some useful information:
> >
> > - 11.7 alreay includes the AUTO_STAT_MODE parameter. This activates or
> > deactivates the optimization that says to the engine that "if the table
> > doesn't change too much don't bother to update it's statistics". I had a
>
> > recent situation where the default threshold was causing some stalled
> > statistics and it made a difference on the query plans. And the
> > probability
> > that this happens with queries involving time/dates is higher than with
> > other types of filters (because these fields many times tend to be
> > populated with constantly increasing values, so statistically something
> > that makes the filter "column > today - some_time" look good will in
> time
> > lead to bad choices)
> >
> > - You can activate optimizer directives for any query outside the
> > applications by using the External Directives feature. I will not go
> into
> > details, because it could take a while, but if you're not aware of this
> > and
> > need further information, please tell. In any case I'm against the
> > principle of using directives to solve query plans problems... after all
>
> > we
> > have an optimizer... we're not Oracle :)
> >
> > So, my advise:
> > - Verify that the distributions are really up to date (disable
> > AUTO_STAT_MODE) and run them (it's dynamic and I believe there's a way
> to
> > control it inside the session (either with SET ENVIRONMENT or with a
> > environment variable)
> > - If you never used the external optimizer directives, please
> investigate
> > them. Sometimes you can get away with them to quickly solve a
> performance
> > problem. But my advise is to always investigate this kind of problems
> and
> > find a definitive solution
> > - You could of course re-post your problem in a manner I can understand,
>
> > or
> > wait until someone smarter to answer you :)
> >
> > Regards.
> >
> > On Wed, Mar 28, 2012 at 9:00 PM, Peter_Logan@spartanstores.com <
> > Peter_Logan@spartanstores.com> wrote:
> >
> > > Aix 6.1
> > > IDS 11.70.fc3
> > >
> > > The below query was executing ok, and then recently it started taking
> a
> > > lot longer... In looking at the query plans, the order of the tables
> > > changed a few weeks ago... Amount of data and stats in the tables
> > haven't
> > > changed dramatically ...
> > >
> > > The question I have, is ... Is the optimizer smart enough to execute
> the
> >
> > > (select weekid(today, -1) from systables where tabid = 1 ... I can
> > > duplicate if I take that select out and hard code the fiscal week id
> ...
> > > runs about a minute .. then runs about 4 minutes. The query plan
> changes
> >
> > > ... If I do run the query with the directive ... then I can
> consistently
> >
> > > get it to run around a minute .. The problem is that this is a Micro
> > > Strategies report, so that optimizer directive can't be included ....
> > > Any help would be appreciated ...
> > >
> > > Peter Logan
> > > Senior Database Administrator
> > > Phone: 616/878-8309
> > > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/28/2012 03:53
> PM
> > > -----
> > >
> > > From: plisad0 (spartan02) <plisad0@spartanstores.com>
> > > To: Peter_Logan@spartanstores.com
> > > Date: 03/28/2012 03:52 PM
> > > Subject: Optimizer
> > >
> > > -- select --+ORDERED
> > > select
> > > a18.mdse_grp_key mdse_grp_key,
> > >
> > > a12.fiscal_week_id fiscal_week_id,
> > >
> > > a13.catgy_manager_key catgy_manager_key,
> > >
> > > (sum(a11.total_sales_amt) * 0.001) WJXBFS1,
> > >
> > > sum(a11.ext_profit_amt) WJXBFS2,
> > >
> > > sum(a11.total_sales_amt) WJXBFS3,
> > >
> > > sum(a11.ext_profit_amt) WJXBFS4
> > > from
> > >
> > > week_to_year_lkp a12,
> > >
> > > channel a16,
> > >
> > > chain a15,
> > >
> > > line a14,
> > > efin_wk_line_cls a11,
> > >
> > > mdse_class_manager a13,
> > >
> > > mdse_class a17,
> > >
> > > mdse_category a18,
> > >
> > > mdse_group a19,
> > >
> > > department a110,
> > >
> > > business_dept a111
> > > where a11.fiscal_week_id = a12.fiscal_wty_id and
> > >
> > > a11.mdse_class_key = a13.mdse_class_key and
> > >
> > > a11.sales_line_id = a14.sales_line_id and
> > >
> > > a14.sales_chain_id = a15.sales_chain_id and
> > >
> > > a15.sales_channel_id = a16.sales_channel_id and
> > >
> > > a11.mdse_class_key = a17.mdse_class_key and
> > >
> > > a17.mdse_catgy_key = a18.mdse_catgy_key and
> > >
> > > a18.mdse_grp_key = a19.mdse_grp_key and
> > >
> > > a19.dept_key = a110.dept_key and
> > >
> > > a110.bus_dept_key = a111.bus_dept_key
> > >
> > > and (a16.enterprise_id in (18)
> > >
> > > and a13.catgy_manager_key in (70)
> > >
> > > and a110.dept_grp_key not in (525, 9)
> > >
> > > and a14.format_type_id in ('SUPERMKT ')
> > >
> > > and a12.fiscal_week_id in (201253)
> > >
> > > and a111.dept_grp_type_key in (1))
> > > group by a18.mdse_grp_key,
> > >
> > > a12.fiscal_week_id,
> > >
> > > a13.catgy_manager_key
> > > into temp ZZMD03 with no log
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
> *******************************************************************************
>
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --0023544710d08e6fef04bc540bf4
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074d934d0d33704bc6205a4
>
>
>
> *******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--0023544710d051976304bc655976

Messages In This Thread

• Fw: Optimizer
  Peter_Logan@spartanstores.com -- Wednesday, 28 March 2012, at 3:59 p.m.
  • Re: Fw: Optimizer
    Fernando Nunes -- Wednesday, 28 March 2012, at 5:11 p.m.
    • Re: Fw: Optimizer
      Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 8:27 a.m.
      • Re: Fw: Optimizer
        Fernando Nunes -- Thursday, 29 March 2012, at 9:51 a.m.
        • Re: Fw: Optimizer
          Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:06 a.m.
          • Re: Fw: Optimizer
            Art Kagel -- Thursday, 29 March 2012, at 10:23 a.m.
            • Re: Fw: Optimizer
              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:43 a.m.
              • Re: Fw: Optimizer
                Art Kagel -- Thursday, 29 March 2012, at 10:53 a.m.
                • Re: Fw: Optimizer
                  Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:57 a.m.
                  • Re: Fw: Optimizer
                    Art Kagel -- Thursday, 29 March 2012, at 11:43 a.m.
                    • Re: Fw: Optimizer
                      Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 11:50 a.m.
                      • Re: Fw: Optimizer
                        Art Kagel -- Thursday, 29 March 2012, at 12:42 p.m.
                        • Re: Fw: Optimizer
                          Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 1:24 p.m.
                          • Re: Fw: Optimizer
                            Art Kagel -- Thursday, 29 March 2012, at 1:59 p.m.
                            • Re: Fw: Optimizer
                              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 2:04 p.m.
                      • Re: Fw: Optimizer
                        Fernando Nunes -- Thursday, 29 March 2012, at 1:52 p.m.
          • Re: Fw: Optimizer
            Fernando Nunes -- Thursday, 29 March 2012, at 1:49 p.m.
            • Re: Fw: Optimizer
              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 2:02 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.