Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Poor query plans after upgrade from ASE 11 to 15

15 posts in Performance and Tuning Last posting was on 2008-03-07 14:50:46.0Z
John Flynn Posted on 2008-02-07 00:00:02.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Poor query plans after upgrade from ASE 11 to 15
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47aa4a02$1@forums-1-dub>
Date: 6 Feb 2008 16:00:02 -0800
X-Trace: forums-1-dub 1202342402 10.22.241.152 (6 Feb 2008 16:00:02 -0800)
X-Original-Trace: 6 Feb 2008 16:00:02 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10641
Article PK: 89255

I upgraded a 30Gb database from ASE 11.9.2.6 to 15.0.2 EBF 14328. I am aware
of the general concept that existing well-running queries may perform
differently so you should test them all out, and I have no problem with
that. I am testing out my application now, and 99+% of the queries run just
as well as before. In general this migration has been amazingly smooth!

But I have found a handful of queries that I can't figure out. Some of these
are very simple joins, with seemingly good indexes, update statistics run
periodically, no major index skewing, no funny SARGs, IOW no big red flags
that I am aware of. Yet the optimizer might choose a plan that takes
5,000,000 IOs and several minutes, when I can force the "right" index and
get it to take 10,000 IOs, near-instantaneously. I can easily rewrite most
of these queries to perform well again, whether that's by permanently
forcing an index, or using forceplan, or breaking the join into two smaller
queries connected by an intermediate temporary table, etc. But it nags at me
that the seemingly-simple query doesn't work like it used to.

Anything obvious I might be missing? I know I can graduate to the esoteric
optdiag stuff and abstract query plans and all that, and that would be good
stuff for me to learn. But before I do that, any other hints? Is there some
SQL construct that causes a well-known optimizer glitch in ASE15? Maybe some
particular characteristic of a table that ASE11 handled okay but ASE15 needs
some help with? I might be willing to upload the specifics of one of these
queries if anyone thinks that info would help, but I thought I'd get some
general impressions first.

Thanks.
- John.


"Mark A. Parsons" <iron_horse Posted on 2008-02-07 00:31:45.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub>
In-Reply-To: <47aa4a02$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47aa5171$1@forums-1-dub>
Date: 6 Feb 2008 16:31:45 -0800
X-Trace: forums-1-dub 1202344305 10.22.241.152 (6 Feb 2008 16:31:45 -0800)
X-Original-Trace: 6 Feb 2008 16:31:45 -0800, vip152.sybase.com
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10642
Article PK: 89257

Some general suggestions ...

- run 'update index statistics' instead of 'update statistics'; the 15 optimizer is mucho more picky about the
availability of stats on non-leading columns in the indexes

- you may have to run 'update index stats' more often than you're used to running; again, the optimizer is a bit more
picky about accuracy of stats

- prior to running 'update index statistics' the first time you may want to run 'delete statistics' to insure all old
stats are removed from the system; if someone had run stats on an individual column in the past, and those stats aren't
overwritten by 'update index stats', then the optimizer could have problems generating a valid query plan

[NOTE: Alternatively check for 'old' stats and then run 'delete statistics' where appropriate. Either way, you should
only need to run 'delete statistics' that first time.]

- test different optimization goals (eg, allrows_oltp vs allrows_mix); the downside to changing this at the dataserver
level (sp_configure) is that you may trash the query plans on queries that currently perform ok as is; suggest changing
the optimization goal at the command line ('set plan optgoal <optgoal>') or in an AQP (<normal_query> plan '(use optgoal
<optgoal>)'); ideally the optimizer should find the best query plan while running under allrows_mix, but this is not
always the case with the current optimizer

- I've got a handful of queries with one client that will only compile properly if the optimization goal (usually
allrows_oltp) is defined at the query level via an AQP; there's a bug in the optimizer where the optgoal is treated
differently at the dataserver/command-line level vs at the query level

- check for ansi compliance in your queries, as well as proper/appropriate join clauses; the new optimizer is less
forgiving of sloppy SQL; I'd say about 35% of the problematic queries at a current client are due to poor SQL coding
methods ... methods which were kinda-sorta handled by the old pre-15.x optimizer, but are now less likely to compile
properly with the new 15.x optimizer

- one co-worker at a current client has found that explicitly stating transitive join clauses can help a query; it seems
that the optimizer doesn't always make the connection that if a=b and b=c then you can assume a=c ... so add 'a=c' to
help the optimizer

- you may also be running into a new (known?) bug with the optimizer; so you may want to open a case with Sybase
TechSupport; yeah, there are still quite a few bugs in 15.0.2 ESD #2

John Flynn wrote:
> I upgraded a 30Gb database from ASE 11.9.2.6 to 15.0.2 EBF 14328. I am aware
> of the general concept that existing well-running queries may perform
> differently so you should test them all out, and I have no problem with
> that. I am testing out my application now, and 99+% of the queries run just
> as well as before. In general this migration has been amazingly smooth!
>
> But I have found a handful of queries that I can't figure out. Some of these
> are very simple joins, with seemingly good indexes, update statistics run
> periodically, no major index skewing, no funny SARGs, IOW no big red flags
> that I am aware of. Yet the optimizer might choose a plan that takes
> 5,000,000 IOs and several minutes, when I can force the "right" index and
> get it to take 10,000 IOs, near-instantaneously. I can easily rewrite most
> of these queries to perform well again, whether that's by permanently
> forcing an index, or using forceplan, or breaking the join into two smaller
> queries connected by an intermediate temporary table, etc. But it nags at me
> that the seemingly-simple query doesn't work like it used to.
>
> Anything obvious I might be missing? I know I can graduate to the esoteric
> optdiag stuff and abstract query plans and all that, and that would be good
> stuff for me to learn. But before I do that, any other hints? Is there some
> SQL construct that causes a well-known optimizer glitch in ASE15? Maybe some
> particular characteristic of a table that ASE11 handled okay but ASE15 needs
> some help with? I might be willing to upload the specifics of one of these
> queries if anyone thinks that info would help, but I thought I'd get some
> general impressions first.
>
> Thanks.
> - John.
>
>


John Flynn Posted on 2008-02-07 15:47:53.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47aa4a02$1@forums-1-dub> <47aa5171$1@forums-1-dub>
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
Lines: 9
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ab2829@forums-1-dub>
Date: 7 Feb 2008 07:47:53 -0800
X-Trace: forums-1-dub 1202399273 10.22.241.152 (7 Feb 2008 07:47:53 -0800)
X-Original-Trace: 7 Feb 2008 07:47:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10643
Article PK: 89258


Mark A. Parsons wrote:
> Some general suggestions ...

Thank you so much. That's exactly the kind of info I was looking for right
now. That gives me a lot to go on.

- John.


Jeff Tallman Posted on 2008-02-07 22:18:43.0Z
From: Jeff Tallman <tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub> <47aa5171$1@forums-1-dub>
In-Reply-To: <47aa5171$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ab83c3@forums-1-dub>
Date: 7 Feb 2008 14:18:43 -0800
X-Trace: forums-1-dub 1202422723 10.22.241.152 (7 Feb 2008 14:18:43 -0800)
X-Original-Trace: 7 Feb 2008 14:18:43 -0800, vip152.sybase.com
Lines: 167
X-Authenticated-User: workspace
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10645
Article PK: 89266

In addition to (and to explain) the below, one of the most common causes
is that when the optimizer doesn't have good statistics on something, it
may think that sorting it and doing merge operation is best - hence the
millions of IOs that are different. Two places I have seen this are
joins and distincts() with group by's.

With respect to the first, we often see this in queries involving 2 or
more tables that are joined on multiple keys. As often in DB design,
the leading key is less distinctive, but is the only one we have stats
on using update statistics. As a consequence, the optimizer using the
magic numbers (i.e. 10%, 25% and 33%) for estimating the other keys of
the join columns - especially when the join key values are not known at
query time (i.e. a different column is the access method or another
third table is the outermost). The use of the magic numbers really
distorts the number of rows expected in return which can make nested
loop joins appear prohibitively expensive as compared to a very
expensive merge join - so it takes the very expensive route vs. the what
it thinks is prohibitively expensive route...hence update index
statistics resolves most of this. Sometimes this is easily spotted if
you have a show plan and you see the merge join and it specifies it is
using 1 key and you know that the join involves 2-3 columns or more.

One shining example of the above that happens unexpectedly is non-ANSI
compliant group by's - for instance:

select col1, col2, col3, sum(col4)
from table
group by col1, col2

ASE always kinda did a double access here - even the 11.x and 12.x
showplans revealed this. The first would do the aggregation and then
the second was effectively a re-join to pick up the extended column
(col3). If there isn't an index on col1, col2 (i.e. just col1) or if
the index on col1, col2 only had stats for col1, the second access can
get real ugly real quick. In some cases this is easily fixed as adding
col2 to the index or updating index stats resolves it. However,
consider the case of:

select t1.col1, t2.col1, t1.col2, sum(t1.col3)
from table t1, table t2
where t1.key1=t2.key1
group by t1.col1, t2.col2

...kinda tough to have an index on a join ....and so the second access
may get really messed up. Enforcing ANSI and not allowing the extended
columns resolves the issue, but then the result set is different from
what the application expects....and you either need to do the query in
two steps (add col to group by and insert into temp table and then
re-aggregate the temp table), use derived table syntax...or play around
with a having clause or similar to make the second access behave ideally.

In other cases, I have seen queries like:

select col1, col2, sum(col3), count(distinct col4)
....
group by col1, col2

take longer as ASE 15 may decide to use distinct_sorted as it has no
idea of the cardinality of col4 - and adding an index or statistics on
col4 or adding an index/statistics on col1, col2, col4 can help
considerably. Note that this is not as common as the join situation
described above as I have also seen ASE 15 do queries similar to the
above and just blow through the query with speeds that ASE 12.5 would
just gawk at.

My point is that a lot of the query degradations people see are due to
sorting operations in ASE....and that while disabling the sorting
operation is always a possibility (using a login trigger and exporting
the setting), there may be other things you can do that are repeatable
and help more than disabling options - and are easier to manage trying
to use an AQP for a whole bunch of queries.

....the bug mentioned below wrt to optimizer goals being treated
differently is being addressed....some of it was addressed in ESD #2 -
other parts are forthcoming.

Mark A. Parsons wrote:
> Some general suggestions ...
>
> - run 'update index statistics' instead of 'update statistics'; the 15
> optimizer is mucho more picky about the availability of stats on
> non-leading columns in the indexes
>
> - you may have to run 'update index stats' more often than you're used
> to running; again, the optimizer is a bit more picky about accuracy of
> stats
>
> - prior to running 'update index statistics' the first time you may want
> to run 'delete statistics' to insure all old stats are removed from the
> system; if someone had run stats on an individual column in the past,
> and those stats aren't overwritten by 'update index stats', then the
> optimizer could have problems generating a valid query plan
>
> [NOTE: Alternatively check for 'old' stats and then run 'delete
> statistics' where appropriate. Either way, you should only need to run
> 'delete statistics' that first time.]
>
> - test different optimization goals (eg, allrows_oltp vs allrows_mix);
> the downside to changing this at the dataserver level (sp_configure) is
> that you may trash the query plans on queries that currently perform ok
> as is; suggest changing the optimization goal at the command line ('set
> plan optgoal <optgoal>') or in an AQP (<normal_query> plan '(use optgoal
> <optgoal>)'); ideally the optimizer should find the best query plan
> while running under allrows_mix, but this is not always the case with
> the current optimizer
>
> - I've got a handful of queries with one client that will only compile
> properly if the optimization goal (usually allrows_oltp) is defined at
> the query level via an AQP; there's a bug in the optimizer where the
> optgoal is treated differently at the dataserver/command-line level vs
> at the query level
>
> - check for ansi compliance in your queries, as well as
> proper/appropriate join clauses; the new optimizer is less forgiving of
> sloppy SQL; I'd say about 35% of the problematic queries at a current
> client are due to poor SQL coding methods ... methods which were
> kinda-sorta handled by the old pre-15.x optimizer, but are now less
> likely to compile properly with the new 15.x optimizer
>
> - one co-worker at a current client has found that explicitly stating
> transitive join clauses can help a query; it seems that the optimizer
> doesn't always make the connection that if a=b and b=c then you can
> assume a=c ... so add 'a=c' to help the optimizer
>
> - you may also be running into a new (known?) bug with the optimizer; so
> you may want to open a case with Sybase TechSupport; yeah, there are
> still quite a few bugs in 15.0.2 ESD #2
>
>
>
>
> John Flynn wrote:
>> I upgraded a 30Gb database from ASE 11.9.2.6 to 15.0.2 EBF 14328. I am
>> aware of the general concept that existing well-running queries may
>> perform differently so you should test them all out, and I have no
>> problem with that. I am testing out my application now, and 99+% of
>> the queries run just as well as before. In general this migration has
>> been amazingly smooth!
>>
>> But I have found a handful of queries that I can't figure out. Some of
>> these are very simple joins, with seemingly good indexes, update
>> statistics run periodically, no major index skewing, no funny SARGs,
>> IOW no big red flags that I am aware of. Yet the optimizer might
>> choose a plan that takes 5,000,000 IOs and several minutes, when I can
>> force the "right" index and get it to take 10,000 IOs,
>> near-instantaneously. I can easily rewrite most of these queries to
>> perform well again, whether that's by permanently forcing an index, or
>> using forceplan, or breaking the join into two smaller queries
>> connected by an intermediate temporary table, etc. But it nags at me
>> that the seemingly-simple query doesn't work like it used to.
>>
>> Anything obvious I might be missing? I know I can graduate to the
>> esoteric optdiag stuff and abstract query plans and all that, and that
>> would be good stuff for me to learn. But before I do that, any other
>> hints? Is there some SQL construct that causes a well-known optimizer
>> glitch in ASE15? Maybe some particular characteristic of a table that
>> ASE11 handled okay but ASE15 needs some help with? I might be willing
>> to upload the specifics of one of these queries if anyone thinks that
>> info would help, but I thought I'd get some general impressions first.
>>
>> Thanks.
>> - John.
>>
>>


Sherlock, Kevin Posted on 2008-02-07 22:44:35.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47aa4a02$1@forums-1-dub> <47aa5171$1@forums-1-dub> <47ab83c3@forums-1-dub>
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
Lines: 55
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ab89d3@forums-1-dub>
Date: 7 Feb 2008 14:44:35 -0800
X-Trace: forums-1-dub 1202424275 10.22.241.152 (7 Feb 2008 14:44:35 -0800)
X-Original-Trace: 7 Feb 2008 14:44:35 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10646
Article PK: 89263

questions below regarding join selectivity estimates:

"Jeff Tallman" <tallman@sybase.com> wrote in message
news:47ab83c3@forums-1-dub...
>
> In addition to (and to explain) the below, one of the most common causes
> is that when the optimizer doesn't have good statistics on something, it
> may think that sorting it and doing merge operation is best - hence the
> millions of IOs that are different. Two places I have seen this are joins
> and distincts() with group by's.
>
> With respect to the first, we often see this in queries involving 2 or
> more tables that are joined on multiple keys. As often in DB design, the
> leading key is less distinctive, but is the only one we have stats on
> using update statistics. As a consequence, the optimizer using the magic
> numbers (i.e. 10%, 25% and 33%) for estimating the other keys of the join
> columns - especially when the join key values are not known at query time
> (i.e. a different column is the access method or another third table is
> the outermost).

Well, when it comes to join costing, I was under the impression that "column
group" densities (which you get without usign "update index statistics")
were used for join costing. Update index stats only additionaly adds the
histograms (and individual column densities) for the inner index columns
which would be useful for SARG's, but that doesn't apply to join costing
(unless there are SARGS on the join columns in addition to the join
criteria).

So if "column group" densities are not used for multi-key join costing, what
are they used for?

> The use of the magic numbers really distorts the number of rows expected
> in return which can make nested loop joins appear prohibitively expensive
> as compared to a very expensive merge join - so it takes the very
> expensive route vs. the what it thinks is prohibitively expensive
> route...hence update index statistics resolves most of this.

Again, why would additional histograms, and separate column densities affect
the multi-key join costing?

I can understand the if you have say a 3 column key, and have individual
"total densities" for each column (via update index stats), you might
estimate the 3 column join selectivity by multiplying the 3 individual
densities, but then that may or may not be calculated the same way for the
same "column group" density. However, in the absence of each individual
"total density" stat, wouldn't the column group's "total density" (available
via normal update stats) come close enough to avoid the "magic number"
method estimates?

> Sometimes this is easily spotted if you have a show plan and you see the
> merge join and it specifies it is using 1 key and you know that the join
> involves 2-3 columns or more.


Jeff Tallman Posted on 2008-02-09 00:48:37.0Z
From: Jeff Tallman <tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub> <47aa5171$1@forums-1-dub> <47ab83c3@forums-1-dub> <47ab89d3@forums-1-dub>
In-Reply-To: <47ab89d3@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47acf865$1@forums-1-dub>
Date: 8 Feb 2008 16:48:37 -0800
X-Trace: forums-1-dub 1202518117 10.22.241.152 (8 Feb 2008 16:48:37 -0800)
X-Original-Trace: 8 Feb 2008 16:48:37 -0800, vip152.sybase.com
Lines: 76
X-Authenticated-User: workspace
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10652
Article PK: 89265

Wellll....can't comment on the *supposed* to part - I only know what
happened from the 'set option show' output.....and if you think about
it, we have the group stats (if just using update stats - as you pointed
out) - and if optimizer only used that, then updating index stats would
have zero benefit......I was a tad surprised when I saw it myself - but
then I had a long hard think and it wasn't so surprising after that.
And it has been validated at nearly every ASE 15 upgrade since as out of
control merge joins became well behaved NLJ's after adding col stats on
2-n cols. Some day when I get a free moment (hmmm.....2010 looks good)
I may ask the optimizer team about the wherefores and what-nots.

...however, we all need to remember that the ASE 15 optimizer is a
different model from 12.5.x - so the costing/etc. rules that were used
in 12.5 may be different in some cases...and our understanding of those
rules may need to be updated.....almost kinda funny - despite all the
functionality we wish ASE had - it is hard just to keep up with the
functionality it does have.....either that or my brain is leaking.

Sherlock, Kevin wrote:
> questions below regarding join selectivity estimates:
>
> "Jeff Tallman" <tallman@sybase.com> wrote in message
> news:47ab83c3@forums-1-dub...
>> In addition to (and to explain) the below, one of the most common causes
>> is that when the optimizer doesn't have good statistics on something, it
>> may think that sorting it and doing merge operation is best - hence the
>> millions of IOs that are different. Two places I have seen this are joins
>> and distincts() with group by's.
>>
>> With respect to the first, we often see this in queries involving 2 or
>> more tables that are joined on multiple keys. As often in DB design, the
>> leading key is less distinctive, but is the only one we have stats on
>> using update statistics. As a consequence, the optimizer using the magic
>> numbers (i.e. 10%, 25% and 33%) for estimating the other keys of the join
>> columns - especially when the join key values are not known at query time
>> (i.e. a different column is the access method or another third table is
>> the outermost).
>
> Well, when it comes to join costing, I was under the impression that "column
> group" densities (which you get without usign "update index statistics")
> were used for join costing. Update index stats only additionaly adds the
> histograms (and individual column densities) for the inner index columns
> which would be useful for SARG's, but that doesn't apply to join costing
> (unless there are SARGS on the join columns in addition to the join
> criteria).
>
> So if "column group" densities are not used for multi-key join costing, what
> are they used for?
>
>> The use of the magic numbers really distorts the number of rows expected
>> in return which can make nested loop joins appear prohibitively expensive
>> as compared to a very expensive merge join - so it takes the very
>> expensive route vs. the what it thinks is prohibitively expensive
>> route...hence update index statistics resolves most of this.
>
> Again, why would additional histograms, and separate column densities affect
> the multi-key join costing?
>
> I can understand the if you have say a 3 column key, and have individual
> "total densities" for each column (via update index stats), you might
> estimate the 3 column join selectivity by multiplying the 3 individual
> densities, but then that may or may not be calculated the same way for the
> same "column group" density. However, in the absence of each individual
> "total density" stat, wouldn't the column group's "total density" (available
> via normal update stats) come close enough to avoid the "magic number"
> method estimates?
>
>> Sometimes this is easily spotted if you have a show plan and you see the
>> merge join and it specifies it is using 1 key and you know that the join
>> involves 2-3 columns or more.
>
>
>


tartampion Posted on 2008-02-11 14:16:53.0Z
Sender: 5034.47b05574.1804289383@sybase.com
From: Tartampion
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47b058d5.506a.1681692777@sybase.com>
References: <47acf865$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Feb 2008 06:16:53 -0800
X-Trace: forums-1-dub 1202739413 10.22.241.41 (11 Feb 2008 06:16:53 -0800)
X-Original-Trace: 11 Feb 2008 06:16:53 -0800, 10.22.241.41
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10653
Article PK: 89268

I have observed similar anomalies in Sybase 15.It is really
hard to believe that ASE15( even 15.02 EDS2) can be used for
production purposes, onc can not use it before all the
enormous bugs are fixed by Sybase. Sorry for the unpleasant
comment.
tartampion

> Wellll....can't comment on the *supposed* to part - I only
> know what happened from the 'set option show'
> output.....and if you think about it, we have the group
> stats (if just using update stats - as you pointed out) -
> and if optimizer only used that, then updating index stats
> would have zero benefit......I was a tad surprised when I
> saw it myself - but then I had a long hard think and it
> wasn't so surprising after that. And it has been
> validated at nearly every ASE 15 upgrade since as out of
> control merge joins became well behaved NLJ's after adding
> col stats on 2-n cols. Some day when I get a free
> moment (hmmm.....2010 looks good) I may ask the optimizer
> team about the wherefores and what-nots.
>
> ...however, we all need to remember that the ASE 15
> optimizer is a different model from 12.5.x - so the
> costing/etc. rules that were used in 12.5 may be
> different in some cases...and our understanding of those
> rules may need to be updated.....almost kinda funny -
> despite all the functionality we wish ASE had - it is
> hard just to keep up with the functionality it does
> have.....either that or my brain is leaking.
>
>
>
> Sherlock, Kevin wrote:
> > questions below regarding join selectivity estimates:
> >
> > "Jeff Tallman" <tallman@sybase.com> wrote in message
> > news:47ab83c3@forums-1-dub...
> >> In addition to (and to explain) the below, one of the
> most common causes >> is that when the optimizer doesn't
> have good statistics on something, it >> may think that
> sorting it and doing merge operation is best - hence the
> >> millions of IOs that are different. Two places I have
> seen this are joins >> and distincts() with group by's.
> >>
> >> With respect to the first, we often see this in queries
> involving 2 or >> more tables that are joined on multiple
> keys. As often in DB design, the >> leading key is less
> distinctive, but is the only one we have stats on >>
> using update statistics. As a consequence, the optimizer
> using the magic >> numbers (i.e. 10%, 25% and 33%) for
> estimating the other keys of the join >> columns -
> especially when the join key values are not known at query
> time >> (i.e. a different column is the access method or
> another third table is >> the outermost).
> >
> > Well, when it comes to join costing, I was under the
> > impression that "column group" densities (which you get
> > without usign "update index statistics") were used for
> join costing. Update index stats only additionaly adds
> > the histograms (and individual column densities) for
> > the inner index columns which would be useful for
> > SARG's, but that doesn't apply to join costing (unless
> there are SARGS on the join columns in addition to the
> > join criteria).
> >
> > So if "column group" densities are not used for
> > multi-key join costing, what are they used for?
> >
> >> The use of the magic numbers really distorts the number
> of rows expected >> in return which can make nested loop
> joins appear prohibitively expensive >> as compared to a
> very expensive merge join - so it takes the very >>
> expensive route vs. the what it thinks is prohibitively
> expensive >> route...hence update index statistics
> > resolves most of this.
> > Again, why would additional histograms, and separate
> > column densities affect the multi-key join costing?
> >
> > I can understand the if you have say a 3 column key, and
> > have individual "total densities" for each column (via
> > update index stats), you might estimate the 3 column
> > join selectivity by multiplying the 3 individual
> densities, but then that may or may not be calculated the
> > same way for the same "column group" density. However,
> > in the absence of each individual "total density" stat,
> > wouldn't the column group's "total density" (available
> via normal update stats) come close enough to avoid the
> > "magic number" method estimates?
> >
> >> Sometimes this is easily spotted if you have a show
> plan and you see the >> merge join and it specifies it is
> using 1 key and you know that the join >> involves 2-3
> > columns or more.
> >
> >


Carl Kayser Posted on 2008-02-13 19:09:17.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.performance+tuning
References: <47acf865$1@forums-1-dub> <47b058d5.506a.1681692777@sybase.com>
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47b3405d$1@forums-1-dub>
Date: 13 Feb 2008 11:09:17 -0800
X-Trace: forums-1-dub 1202929757 10.22.241.152 (13 Feb 2008 11:09:17 -0800)
X-Original-Trace: 13 Feb 2008 11:09:17 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10655
Article PK: 89270


<Tartampion> wrote in message news:47b058d5.506a.1681692777@sybase.com...
>I have observed similar anomalies in Sybase 15.It is really
> hard to believe that ASE15( even 15.02 EDS2) can be used for
> production purposes, onc can not use it before all the
> enormous bugs are fixed by Sybase. Sorry for the unpleasant
> comment.
> tartampion
>
>

Do some of your problems appear to be addressed in ESD#3? Is there a
general nature to your problems such as slow reports versus batch processing
versus "straight OLTP"? Or a combination? I plan to start testing ESD#3
when it comes out. (Original target was ESD#2, which we have been playing
with. Other priorities prevented test startup.)


Sherlock, Kevin Posted on 2008-02-11 22:59:07.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47aa4a02$1@forums-1-dub> <47aa5171$1@forums-1-dub> <47ab83c3@forums-1-dub> <47ab89d3@forums-1-dub> <47acf865$1@forums-1-dub>
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47b0d33b$1@forums-1-dub>
Date: 11 Feb 2008 14:59:07 -0800
X-Trace: forums-1-dub 1202770747 10.22.241.152 (11 Feb 2008 14:59:07 -0800)
X-Original-Trace: 11 Feb 2008 14:59:07 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10654
Article PK: 89269

inline below:

"Jeff Tallman" <tallman@sybase.com> wrote in message
news:47acf865$1@forums-1-dub...
>
> Wellll....can't comment on the *supposed* to part - I only know what
> happened from the 'set option show' output.....and if you think about it,
> we have the group stats (if just using update stats - as you pointed
> out) - and if optimizer only used that, then updating index stats would
> have zero benefit......

??? Not saying that optimizer only uses column group stats for join
selectivity/filtering, rather there is some kind of mathematical
relationship between all (perhaps the product of) the individual column
total densities, and the various available column group densities available
in statistics. This doesn't imply that update index stats isn't usefull if
only column group stats were used for join costing either. If nothing else,
you get histograms with update index stats that aren't used for join
costing, but rather SARGS as you know (as well as a total density that is
used for invalid SARGs and single column join costing). Of course, getting
those individual column densities is, as was my point, also used in join
costing. Indeed, in the absence of indivial column statistics (including
total density), the column group stats ARE used for join costing (pre-15.x
at least). So, I would think that without "update index stats", you AT
LEAST get the "benefit" of the column group densities which may or may not
be enough to convince the costing engine to go with the most optimal plan.
Sounds like that's just plain not true in ASE 15 if I read your response
correctly.

After looking at this whole thread though, it depends on what happens in the
ASE 15 optimizer, and my points above regarding ASE 12.x may be moot. I'll
assume then, that the column group densities then are NOT INVOLVED in the
calculations used to cost joins in ASE 15.x in the absence of individual
column statistics.



> I was a tad surprised when I saw it myself - but then I had a long hard
> think and it wasn't so surprising after that. And it has been validated at
> nearly every ASE 15 upgrade since as out of control merge joins became
> well behaved NLJ's after adding col stats on 2-n cols. Some day when I
> get a free moment (hmmm.....2010 looks good) I may ask the optimizer team
> about the wherefores and what-nots.
>
> ...however, we all need to remember that the ASE 15 optimizer is a
> different model from 12.5.x - so the costing/etc. rules that were used in
> 12.5 may be different in some cases...and our understanding of those rules
> may need to be updated.....almost kinda funny - despite all the
> functionality we wish ASE had - it is hard just to keep up with the
> functionality it does have.....either that or my brain is leaking.
>
>


dbMethods Posted on 2008-02-08 00:31:25.0Z
Message-ID: <47ABA2C9.6000402@nospam.com>
From: dbMethods <dbmethods@nospam.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: John Flynn <jflynn@miqs.com>
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub>
In-Reply-To: <47aa4a02$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Feb 2008 16:31:25 -0800
X-Trace: forums-1-dub 1202430685 10.22.241.152 (7 Feb 2008 16:31:25 -0800)
X-Original-Trace: 7 Feb 2008 16:31:25 -0800, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10647
Article PK: 89260


John Flynn wrote:
> I upgraded a 30Gb database from ASE 11.9.2.6 to 15.0.2 EBF 14328. I am aware
> of the general concept that existing well-running queries may perform
> differently so you should test them all out, and I have no problem with
> that. I am testing out my application now, and 99+% of the queries run just
> as well as before. In general this migration has been amazingly smooth!
>
> But I have found a handful of queries that I can't figure out. Some of these
> are very simple joins, with seemingly good indexes, update statistics run
> periodically, no major index skewing, no funny SARGs, IOW no big red flags
> that I am aware of. Yet the optimizer might choose a plan that takes
> 5,000,000 IOs and several minutes, when I can force the "right" index and
> get it to take 10,000 IOs, near-instantaneously. I can easily rewrite most
> of these queries to perform well again, whether that's by permanently
> forcing an index, or using forceplan, or breaking the join into two smaller
> queries connected by an intermediate temporary table, etc. But it nags at me
> that the seemingly-simple query doesn't work like it used to.
>
> Anything obvious I might be missing? I know I can graduate to the esoteric
> optdiag stuff and abstract query plans and all that, and that would be good
> stuff for me to learn. But before I do that, any other hints? Is there some
> SQL construct that causes a well-known optimizer glitch in ASE15? Maybe some
> particular characteristic of a table that ASE11 handled okay but ASE15 needs
> some help with? I might be willing to upload the specifics of one of these
> queries if anyone thinks that info would help, but I thought I'd get some
> general impressions first.
>
> Thanks.
> - John.
>
>

Somehow, I can't find this EBF 14328 from Sybase site.

Would you mind post

select @@version

99+% is pretty good here. I had a case on 15.0.2 ESD #1, which shows bad
written SQL can have undesired performance problem here. It has 4 table
joins. I haven't tried 15.0.2 ESD #2 yet. At the time, my majority
servers are still at 12.5.4. I am interested to find the clues here.

If you can post the query, maybe I can spot something for you.


dbMethods Posted on 2008-02-08 00:32:35.0Z
From: dbMethods <dbmethods@nospam.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub>
In-Reply-To: <47aa4a02$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47aba323@forums-1-dub>
Date: 7 Feb 2008 16:32:35 -0800
X-Trace: forums-1-dub 1202430755 10.22.241.152 (7 Feb 2008 16:32:35 -0800)
X-Original-Trace: 7 Feb 2008 16:32:35 -0800, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10648
Article PK: 89262


John Flynn wrote:
> I upgraded a 30Gb database from ASE 11.9.2.6 to 15.0.2 EBF 14328. I am aware
> of the general concept that existing well-running queries may perform
> differently so you should test them all out, and I have no problem with
> that. I am testing out my application now, and 99+% of the queries run just
> as well as before. In general this migration has been amazingly smooth!
>
> But I have found a handful of queries that I can't figure out. Some of these
> are very simple joins, with seemingly good indexes, update statistics run
> periodically, no major index skewing, no funny SARGs, IOW no big red flags
> that I am aware of. Yet the optimizer might choose a plan that takes
> 5,000,000 IOs and several minutes, when I can force the "right" index and
> get it to take 10,000 IOs, near-instantaneously. I can easily rewrite most
> of these queries to perform well again, whether that's by permanently
> forcing an index, or using forceplan, or breaking the join into two smaller
> queries connected by an intermediate temporary table, etc. But it nags at me
> that the seemingly-simple query doesn't work like it used to.
>
> Anything obvious I might be missing? I know I can graduate to the esoteric
> optdiag stuff and abstract query plans and all that, and that would be good
> stuff for me to learn. But before I do that, any other hints? Is there some
> SQL construct that causes a well-known optimizer glitch in ASE15? Maybe some
> particular characteristic of a table that ASE11 handled okay but ASE15 needs
> some help with? I might be willing to upload the specifics of one of these
> queries if anyone thinks that info would help, but I thought I'd get some
> general impressions first.
>
> Thanks.
> - John.
>
>

Somehow, I can't find this EBF 14328 from Sybase site.

Would you mind post

select @@version

99+% is pretty good here. I had a case on 15.0.2 ESD #1, which shows bad
written SQL can have undesired performance problem here. It has 4 table
joins. I haven't tried 15.0.2 ESD #2 yet. At the time, my majority
servers are still at 12.5.4. I am interested to find the clues here.

If you can post the query, maybe I can spot something for you.


Bret Halford Posted on 2008-02-08 16:20:46.0Z
Message-ID: <47AC815D.6735DF6D@sybase.com>
From: Bret Halford <bret@sybase.com>
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub> <47aba323@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Feb 2008 08:20:46 -0800
X-Trace: forums-1-dub 1202487646 10.22.241.152 (8 Feb 2008 08:20:46 -0800)
X-Original-Trace: 8 Feb 2008 08:20:46 -0800, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10649
Article PK: 89261


dbMethods wrote:

>
> Somehow, I can't find this EBF 14328 from Sybase site.
>
> Would you mind post
>
> select @@version
>
> 99+% is pretty good here. I had a case on 15.0.2 ESD #1, which shows bad
> written SQL can have undesired performance problem here. It has 4 table
> joins. I haven't tried 15.0.2 ESD #2 yet. At the time, my majority
> servers are still at 12.5.4. I am interested to find the clues here.
>
> If you can post the query, maybe I can spot something for you.

EBF 14328 is the initial 15.0.2 GA release for 64-bit Solaris.


dbMethods Posted on 2008-02-08 23:13:55.0Z
From: dbMethods <dbmethods@nospam.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
References: <47aa4a02$1@forums-1-dub> <47aba323@forums-1-dub> <47AC815D.6735DF6D@sybase.com>
In-Reply-To: <47AC815D.6735DF6D@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ace233$1@forums-1-dub>
Date: 8 Feb 2008 15:13:55 -0800
X-Trace: forums-1-dub 1202512435 10.22.241.152 (8 Feb 2008 15:13:55 -0800)
X-Original-Trace: 8 Feb 2008 15:13:55 -0800, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10650
Article PK: 89264


Bret Halford wrote:
>
> dbMethods wrote:
>
>> Somehow, I can't find this EBF 14328 from Sybase site.
>>
>> Would you mind post
>>
>> select @@version
>>
>> 99+% is pretty good here. I had a case on 15.0.2 ESD #1, which shows bad
>> written SQL can have undesired performance problem here. It has 4 table
>> joins. I haven't tried 15.0.2 ESD #2 yet. At the time, my majority
>> servers are still at 12.5.4. I am interested to find the clues here.
>>
>> If you can post the query, maybe I can spot something for you.
>
> EBF 14328 is the initial 15.0.2 GA release for 64-bit Solaris.
>
>
>

John is too brave on this, taking on GA version :-)
I hope this is not Production outage.


Derek Asirvadem Posted on 2008-03-07 14:50:46.0Z
From: Derek Asirvadem <derek@softwaregemsNOSPAMcomDOTau>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <47d15645@forums-1-dub>
References: <47aa4a02$1@forums-1-dub> <47aba323@forums-1-dub> <47AC815D.6735DF6D@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Mar 2008 06:50:46 -0800
X-Trace: forums-1-dub 1204901446 10.22.241.152 (7 Mar 2008 06:50:46 -0800)
X-Original-Trace: 7 Mar 2008 06:50:46 -0800, vip152.sybase.com
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10701
Article PK: 89316

> EBF 14328 is the initial 15.0.2 GA release for 64-bit Solaris.

My oath, it is. Do not bother with anything less than 15.0.2 ESD#2
which is EBF 15097.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability


Derek Asirvadem Posted on 2008-03-07 14:49:44.0Z
From: Derek Asirvadem <derek@softwaregemsNOSPAMcomDOTau>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <47d15607@forums-1-dub>
References: <47aa4a02$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Poor query plans after upgrade from ASE 11 to 15
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Mar 2008 06:49:44 -0800
X-Trace: forums-1-dub 1204901384 10.22.241.152 (7 Mar 2008 06:49:44 -0800)
X-Original-Trace: 7 Mar 2008 06:49:44 -0800, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10700
Article PK: 89315


> On 2008-02-07 11:00:02 +1100, "John Flynn" <jflynn@miqs.com> said:
>
> Is there some SQL construct that causes a well-known optimizer glitch in ASE15?

no

> Maybe some particular characteristic of a table that ASE11 handled okay
> but ASE15 needs some help with?

The only two items I have, from a number of conversions/asignments,
that have not been offered by others are:
1 badly written SQL often performs worse
2  badly normalised tables often performs worse (and of course cause
bad SQL and overuse of tempdb).

While that second item is somewhat mitigated by the advice to identify
multiple joins between tables, I find that since the new QP resolves
stars, etc, it resolves [a string of] bad joins into one corect string,
so multiplejoins do noting. And it still gets confused by (1) and (2).
Vis-a-vis:
table_A<key> = table_B<key>
table_B<key> = table_C<key>
table_C<key> = table_A<key>
That is simply a gross normalisation error, you have a circle. 15.0.x
QP will resolve this [without suggesting that it overcomes
normalisation errors!] to
table_A<key> = table_B<key>
table_B<key> = table_C<key>

(The highest rules in the database universe still are: normalise and
ensure valid joins. In addition to other massive benefits, that makes
your code impervious to versions and QP changes.)
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability