Hello All,

There's been an on going discussion about what to do when an upgrade
results in real or perceived optimizer related issues. This discussion
belongs in this newsgroup so more people can comment,

Eric
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Kevin,

As ever thank you for your remarks. I think that we have all accepted
that
optimizer can behave differently under different conditions and
fortunately
we all have developed work arounds about it.

The points I was trying to make were the case for having parallel
optimizer
engines (which our competitors are also caving for it as well) and the
need
to be able to run a tool within ASE to check the health of the stored
statistics.

Mich

-----Original Message-----
From: owner-sybase-product-future@isug.com
[mailto:owner-sybase-product-future@isug.com] On Behalf Of Kevin
Sherlock
Sent: 17 September 2004 21:33
To: sybase-product-future@isug.com
Subject: Re: Case for having parallel optimizer engines

Mich,
With all due respect, I think you're taking this way too far. As has
been
mentioned before, statistics don't change during upgrades from 11.9.2
on.
There may be some optimizer changes between releases, but that's why
you're
encouraged to test new releases before going live.

Most every performance case that comes and goes, usually involves some
very
customer/data specific circumstances, and your's is probably no
exception.

The mere fact that you took action on generating a different set of
statistics to solve your performance problem proves almost nothing.
There
are so many other factors involved in optimization. Perhaps your cache
sizes changed during the upgrade?

I would have thought your book would cover such things.

Kevin Sherlock
Staff Information Systems Analyst
DEX Media
kevin.sherlock@dexmedia.com
kevin.sherlock@teamsybase.com
----- Original Message -----
From: Mich Talebzadeh
Sent: 9/17/2004 12:50:14 PM
To: sybase-product-future@isug.com
Subject: Case for having parallel optimizer engines

> Eric,

>
> Thank you for your insight. I think through experience there is a
case for
> having Parallel Optimizer engines (POE) with ASE. However, it is
pretty
> obvious that POE will require a major release; I suppose something
post
> Galaxy to care for it.
>
> There is also a case for ASE manuals to include recommending backing
up of
> statistics for a given database pre upgrade given the problematic
issues
> with statistics post upgrade. I have never performed the following
but I
> will be interested to see if anyone has done a simple UNIX diff on
> statistics outputs from optdiag pre and post upgrade.
>
> I have also come across the following case where the output from
optdiag
was
> successful but feeding the same optdiag back (the same ASE version,
the
same
> database) resulted in the following error:
> ---------------------------------------------------------------------
> Server Message: XXXX - Msg 0, Level 10, State 1:
> Configuration option changed. The SQL Server need not be rebooted
since
the
> option is dynamic.
> Server Message: XXXX - Msg 0, Level 10, State 1:
> Changing the value of 'allow updates to system tables' does not
increase
the
> amount of memory Adaptive Server uses.
>
> At step 2 of the histogram for 'dbo.index_highs_lows.year_ago', the
step
> value is not increasing.
> Server Message: XXXX - Msg 0, Level 10, State 1:
> Configuration option changed. The SQL Server need not be rebooted
since
the
> option is dynamic.
> Server Message: XXXX - Msg 0, Level 10, State 1:
> Changing the value of 'allow updates to system tables' does not
increase
the
> amount of memory Adaptive Server uses.
>
> Optdiag failed. Check for error messages above.
>
> If the location of the problem is not clear, consider rerunning
Optdiag
> in input mode with trace flag 6 to help locate the problem.
>
> ---------------------------------------------------------------------
>
> I had to modify the input file manually to make optdiag successful.
As a
> matter of interest, Sybase Tech Support are still considering this
error.
>
> Perhaps we also need to have a tool like dbcc checks to ensure that
stats
> stored in a given database are current and valid (for examples
identifying
> errors like above) without the need to use optdiag and I am not
talking
> about DERIVED_STAT function etc.
>
> Mich
>
> -----Original Message-----
> From: Eric Miner [mailto:eminer1254@yahoo.com]
> Sent: 17 September 2004 04:14
> To: Mich Talebzadeh; Joe Woodhouse
> Cc: Sethu; sybase-product-future@isug.com
> Subject: Re: Re : Re: Re : Re: Fun with Sybase optimizer behavior,
case
for
> having different optimizer engines
>
> Hi Mich,
>
> First I think Sethu's right, this discussion would be
> better one over in
> news://sybase.public.ase.performance+tuning many more
> set of eyes will see it there.
>
> I'm not going to go into this in detail right now as
> it's late here. But here goes...
>
> There's no reason to delete column level stats, there
> never has been a reason. If you saw changes to query
> plans (for better or worse) after deleting column
> stats you're seeing the affect of 'brute' force stats
> tuning. If you want to replace stats either run update
> stats, read in an edited optdiag file, or use an
> optdiag file as a backup of the stats.
>
> Between 11.9.2 and 12.5.x there were no charges to how
> the column level stats are used in estimating the cost
> of a given query plan (there may have been bugs
> though).
>
> >It is a showstopper and this forum
> >discusses these matters as well. It is as simple as
> >that. We need to address this perceived weakness.
>
> Mich, to be fair here you do have to agree that
> whenever moving between major versions some tuning may
> be necessary. Also, it's standard practice to do at
> least a little testing before putting a new version
> into production.
>
> Bottom-line: Knowing what I do about the internal
> workings of the optimizer in these two version I
> strongly suspect your situation was caused by the need
> to do a little tuning of the stats. I've worked with
> many, many users who ran into situations like that you
> describe and a bit of tuning took care of the issue.
>
> As for your idea of having parallel
> optimizers....interestingly I was involved in many
> discussions on this subject while with Sybase. It's
> not impossible, but there are major technical hurtles
> to get overcome.
>
> Later,
>
> Eric
> --- Mich Talebzadeh <mich@peridale.co.uk> wrote:
>
> >
> > Joe,
> >
> > Again thanks. Your point:
> >
> > "I make it my standard practice to optdiag out all
> > the
> > stats before the upgrade, then delete all stats and
> > re-update them following. This seems to fix the
> > majority
> > of problems, and should there seem to be a stats
> > issue,
> > we can simply reload the saved pre-upgrade stats to
> > confirm
> > or deny."
> >
> > Actually I did exactly the same thing. However
> > following delete stats we got a mixed bag
> > performance, so I put back the stats.
> >
> > Again it begs the question why do we need to do the
> > delete stats in most cases (irrelevant for smaller
> > table anyway), if the optimizer behavior is expected
> > to remain the same? I stand to be corrected but
> > neither ASE upgrades or documentation recommed
> > deleting statistics. We of course do it because it
> > make sense through experience.
> >
> >
> >
> >
>
--------------------------------------------------------------------------
> > 16 Sep 2004 13:23
> > Joe Woodhouse
> > <joe.woodhouse@primadonnaconsulting.com>
> >
>
--------------------------------------------------------------------------
> > >Mich,
> > >
> > >> Well through my experience the
> > >> optimizer behavior post upgrade can change. I am
> > not sure
> > >> this is strictly the optimizer or other factors.
> > However
> > >> what is important is the end result.
> > >
> > >Absolutely. I've also seen my share of post-upgrade
> > regressions.
> > >Most have been explainable - consider that many
> > upgrades can
> > >involve bcps or other migration methods that
> > effectively
> > >defragment data, which alters row and page counts.
> > >
> > >And yes, sometimes the regressions are due to
> > optimiser bugs.
> > >
> > >
> > >> The absract query plans might be wonderful things
> > for a
> > >> laboratory and when you have all the time and
> > resources to do
> > >> all sort of tests in your test environment before
> > go live. In
> > >> real life when you have to deliver and work in
> > production and
> > >> you have a limited time scale these things fall
> > apart.
> > >
> > >AQPs don't have to be managed on a query by query
> > basis.
> > >To ensure the optimiser uses the same query plans,
> > we'd
> > >run AQPs in "capture" mode for, say, a week or two
> > before
> > >the upgrade, and ensure all code paths are
> > exercised.
> > >
> > >As part of the upgrade, we turn off "capture" mode
> > and
> > >enable "enforcement" mode instead. Voila. All
> > queries
> > >matching those captured pre-upgrade are now run
> > with
> > >exactly the same query plan. While this doesn't
> > guarantee
> > >no regressions, it does at least eliminate a lot of
> > >possible causes.
> > >
> > >That being said I've never yet seen the need for
> > this -
> > >most upgrade regressions in my experience (>99%)
> > have
> > >been explainable and generally fixable.
> > >
> > >
> > >> The problem with performance is that it is
> > generally a
> > >> deployment issue and you need to find and fix
> > these
> > >> bottlenecks quickly. Keeping optimizer to behave
> > as per pre
> > >> upgrade release can save a lot of hassle.
> > >
> > >I don't think I'm disagreeing with this, but
> > multiple
> > >optimisers per ASE version and separate optimiser
> > engines
> > >introduce many more moving parts. I think I'd fear
> > >regressions introduced by such a major reworking of
> > some
> > >basic ASE paradigms a lot more than a simple
> > upgrade!
> > >
> > >I make it my standard practice to optdiag out all
> > the
> > >stats before the upgrade, then delete all stats and
> > >re-update them following. This seems to fix the
> > majority
> > >of problems, and should there seem to be a stats
> > issue,
> > >we can simply reload the saved pre-upgrade stats to
> > confirm
> > >or deny.
> > >
> > >On a vaguely related note, many optimiser changes
> > end up
> > >having a boot-time traceflag associated with them
> > to back
> > >them out.
> > >
> > >One futures request I'd like to see would be more
> > of these,
> > >and having them documented more visibly. It would
> > ultimately
> > >save a lot of Sybase CS&S and Engineering effort,
> > since
> > >clients could build their own test cases to prove
> > the
> > >optimiser change was causing their bug...
> > >
> > >Regards,
> > >
> > >Joe Woodhouse
> > >Prima Donna Consulting Pty Ltd
> > >
> > --
> > To subscribe : complete the form at
> > http://www.isug.com/sybase-product-future
> > To unsubscribe: send body "unsubscribe
> > sybase-product-future <email address>"
> > to majordomo@isug.com.
> >
>
>
> --
> To subscribe : complete the form at
http://www.isug.com/sybase-product-future
> To unsubscribe: send body "unsubscribe sybase-product-future <email
address>"
> to majordomo@isug.com.

--
To subscribe : complete the form at
http://www.isug.com/sybase-product-future
To unsubscribe: send body "unsubscribe sybase-product-future <email
address>"
to majordomo@isug.com.


--
To subscribe : complete the form at
http://www.isug.com/sybase-product-future
To unsubscribe: send body "unsubscribe sybase-product-future <email
address>"
to majordomo@isug.com.