 |
IDS Forum
Re: Fw: Optimizer
Posted By: Art Kagel
Date: Thursday, 29 March 2012, at 11:43 a.m.
In Response To: Re: Fw: Optimizer (Peter_Logan@spartanstores.com)
Don't know the reporting tool you are using, but couldn't you just include
the statement in the stream of SQL statements? LIke:
"set optimization low; <big query>; set optimization high;"
Also, are you completely certain that the data distributions on the two
tables that are being directly affected by the week id (so week_to_year_lkp
and efin_wk_line_cls) are current and sufficiently detailed? Have you
tried increasing the resolution for the HIGH on the
week_to_year_lkp.fiscal_week_id, efin_wk_line_cls.fiscal_week_id, and
week_to_year_lkp.fiscal_wty_id columns beyond the default 2.5%?
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:57 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8f247d81ca04bc639641
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
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.
|
|
