I understand ... but from what I understand the client tool can only set
this at the beginning ....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Art Kagel" <art.kagel@gmail.com>
To: ids@iiug.org
Date: 03/29/2012 10:54 AM
Subject: Re: Fw: Optimizer [26602]
Sent by: ids-bounces@iiug.org
No, you can switch between OPTIMIZATION LOW; and OPTIMIZATION HIGH;
between
queries. It would be nice if they would just add these are optimizer
directives though, like ALL_ROWS and FIRST_ROWS optimizer goals.
That's a hint Fernando!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. 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 Thu, Mar 29, 2012 at 10:43 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Art,
>
> yes .. and that does help this particular query ... the issue though is
> that this is only one part of a larger set of queries...and the
> optimization being set low has a negative impact on the entire stream
...
> and they can't turn that off and back on .... can only set it for the
> session ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 03/29/2012 10:24 AM
> Subject: Re: Fw: Optimizer [26600]
> Sent by: ids-bounces@iiug.org
>
> One thing Peter, have you tried running this query under SET
OPTIMIZATION
> LOW? You have 10 tables in there, I'm willing to bet that a significant
> portion of the runtime is optimizing the query since there are 3,628,800
> query plans that have to be examined and have cost calculated under the
> default HIGH optimization method versus only 54 query plans under LOW
> optimization.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. 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 Thu, Mar 29, 2012 at 10:06 AM, 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.
> >
> >
>
> --e89a8f3ba75f130a3204bc62783a
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93406612b710604bc62e2a6
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
