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.

A funny feature of the ASE 15's optimizer ?

10 posts in Performance and Tuning Last posting was on 2008-04-07 05:49:15.0Z
flybean Posted on 2008-04-03 01:13:32.0Z
From: flybean <flybean@sybaseclub.org>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: A funny feature of the ASE 15's optimizer ?
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47f42f3c$1@forums-1-dub>
Date: 2 Apr 2008 17:13:32 -0800
X-Trace: forums-1-dub 1207185212 10.22.241.152 (2 Apr 2008 17:13:32 -0800)
X-Original-Trace: 2 Apr 2008 17:13:32 -0800, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10742
Article PK: 89355

We had upgrade an application from 11.9.2 to 15 with all indexes/PK
rebuilt, all statistics updated. Some queries (joins between some
tables) runs serious slowly than on 11.9.2. We checked the two plans.
For 11.9.2, the plan used the index, while 15 did not, even we rebulit
the index again.

So we modified the sql by moving the indexed-column (which in the WHERE
clause) to the beginning of the WHERE clause. After that, it ran as fast
as before. The optimizer acts as old Oracle ( 9i before ) which asked
the programmers to write the indexed column first.

Yes, ASE 15 has a fully new optimizer, we need to get used to it. But
it's really to write the indexed-column first?

Flybean


Sherlock, Kevin Posted on 2008-04-03 03:22:16.0Z
From: "Sherlock, Kevin" <ksherlock@tconl.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47f42f3c$1@forums-1-dub>
Subject: Re: A funny feature of the ASE 15's optimizer ?
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47f44d68$1@forums-1-dub>
Date: 2 Apr 2008 19:22:16 -0800
X-Trace: forums-1-dub 1207192936 10.22.241.152 (2 Apr 2008 19:22:16 -0800)
X-Original-Trace: 2 Apr 2008 19:22:16 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10743
Article PK: 89356

can you give an example of the query before and after the code change? Was
it a query, or a statement in a stored procedure?

Did you run "update statistics" or "update index statistics" on the 15
version?

What specific version of 15 are you running?

"flybean" <flybean@sybaseclub.org> wrote in message
news:47f42f3c$1@forums-1-dub...
> We had upgrade an application from 11.9.2 to 15 with all indexes/PK
> rebuilt, all statistics updated. Some queries (joins between some
> tables) runs serious slowly than on 11.9.2. We checked the two plans.
> For 11.9.2, the plan used the index, while 15 did not, even we rebulit
> the index again.
>
> So we modified the sql by moving the indexed-column (which in the WHERE
> clause) to the beginning of the WHERE clause. After that, it ran as fast
> as before. The optimizer acts as old Oracle ( 9i before ) which asked
> the programmers to write the indexed column first.
>
> Yes, ASE 15 has a fully new optimizer, we need to get used to it. But
> it's really to write the indexed-column first?
>
> Flybean
>


flybean Posted on 2008-04-03 05:00:42.0Z
From: flybean <flybean@sybaseclub.org>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: A funny feature of the ASE 15's optimizer ?
References: <47f42f3c$1@forums-1-dub> <47f44d68$1@forums-1-dub>
In-Reply-To: <47f44d68$1@forums-1-dub>
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47f4647a@forums-1-dub>
Date: 2 Apr 2008 21:00:42 -0800
X-Trace: forums-1-dub 1207198842 10.22.241.152 (2 Apr 2008 21:00:42 -0800)
X-Original-Trace: 2 Apr 2008 21:00:42 -0800, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10744
Article PK: 89357


Sherlock, Kevin wrote:
> can you give an example of the query before and after the code change? Was
> it a query, or a statement in a stored procedure?
>

Sorry, I lost the record. :(

> Did you run "update statistics" or "update index statistics" on the 15
> version?

Yes, we had rebuild the indexes and ran "update statistics"

> What specific version of 15 are you running?
>

Adaptive Server Enterprise/15.0.2/EBF
14333/P/PPC64/Linux/ase1502/2486/64-bit/FBO/Thu May 24 07:04:27 2007

> "flybean" <flybean@sybaseclub.org> wrote in message
> news:47f42f3c$1@forums-1-dub...
>> We had upgrade an application from 11.9.2 to 15 with all indexes/PK
>> rebuilt, all statistics updated. Some queries (joins between some
>> tables) runs serious slowly than on 11.9.2. We checked the two plans.
>> For 11.9.2, the plan used the index, while 15 did not, even we rebulit
>> the index again.
>>
>> So we modified the sql by moving the indexed-column (which in the WHERE
>> clause) to the beginning of the WHERE clause. After that, it ran as fast
>> as before. The optimizer acts as old Oracle ( 9i before ) which asked
>> the programmers to write the indexed column first.
>>
>> Yes, ASE 15 has a fully new optimizer, we need to get used to it. But
>> it's really to write the indexed-column first?
>>
>> Flybean
>>
>
>


Sherlock, Kevin Posted on 2008-04-04 20:03:05.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47f42f3c$1@forums-1-dub> <47f44d68$1@forums-1-dub> <47f4647a@forums-1-dub>
Subject: Re: A funny feature of the ASE 15's optimizer ?
Lines: 50
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: <47f68979$1@forums-1-dub>
Date: 4 Apr 2008 12:03:05 -0800
X-Trace: forums-1-dub 1207339385 10.22.241.152 (4 Apr 2008 12:03:05 -0800)
X-Original-Trace: 4 Apr 2008 12:03:05 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10754
Article PK: 89368


"flybean" <flybean@sybaseclub.org> wrote in message
news:47f4647a@forums-1-dub...
> Sherlock, Kevin wrote:
>> can you give an example of the query before and after the code change?
>> Was
>> it a query, or a statement in a stored procedure?
>>
> Sorry, I lost the record. :(

Ok, then it's gonna be hard to substantiate that claim without it.

>> Did you run "update statistics" or "update index statistics" on the 15
>> version?
>
> Yes, we had rebuild the indexes and ran "update statistics"

In ASE 15, it's rather important that any column which participates in an
index have it's statistics kept up-to-date. I would change your statistics
maintaince practice to use "update index statistics" instead of "update
statistics".

>> What specific version of 15 are you running?
>>
>
> Adaptive Server Enterprise/15.0.2/EBF
> 14333/P/PPC64/Linux/ase1502/2486/64-bit/FBO/Thu May 24 07:04:27 2007
>
>> "flybean" <flybean@sybaseclub.org> wrote in message
>> news:47f42f3c$1@forums-1-dub...
>>> We had upgrade an application from 11.9.2 to 15 with all indexes/PK
>>> rebuilt, all statistics updated. Some queries (joins between some
>>> tables) runs serious slowly than on 11.9.2. We checked the two plans.
>>> For 11.9.2, the plan used the index, while 15 did not, even we rebulit
>>> the index again.
>>>
>>> So we modified the sql by moving the indexed-column (which in the WHERE
>>> clause) to the beginning of the WHERE clause. After that, it ran as fast
>>> as before. The optimizer acts as old Oracle ( 9i before ) which asked
>>> the programmers to write the indexed column first.
>>>
>>> Yes, ASE 15 has a fully new optimizer, we need to get used to it. But
>>> it's really to write the indexed-column first?
>>>
>>> Flybean
>>>
>>
>>


Rob Verschoor Posted on 2008-04-03 07:02:20.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.performance+tuning
References: <47f42f3c$1@forums-1-dub>
Subject: Re: A funny feature of the ASE 15's optimizer ?
Lines: 42
Organization: Sypron B.V. / TeamSybase
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: <47f480fc$1@forums-1-dub>
Date: 2 Apr 2008 23:02:20 -0800
X-Trace: forums-1-dub 1207206140 10.22.241.152 (2 Apr 2008 23:02:20 -0800)
X-Original-Trace: 2 Apr 2008 23:02:20 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10745
Article PK: 89358

That sounds interesting. Which exact ASE version was this?
Would you mind posting an example of the query with the query plan and 'set
option show_lio_costing' ?

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"flybean" <flybean@sybaseclub.org> wrote in message
news:47f42f3c$1@forums-1-dub...
> We had upgrade an application from 11.9.2 to 15 with all indexes/PK
> rebuilt, all statistics updated. Some queries (joins between some
> tables) runs serious slowly than on 11.9.2. We checked the two plans.
> For 11.9.2, the plan used the index, while 15 did not, even we rebulit
> the index again.
>
> So we modified the sql by moving the indexed-column (which in the WHERE
> clause) to the beginning of the WHERE clause. After that, it ran as fast
> as before. The optimizer acts as old Oracle ( 9i before ) which asked
> the programmers to write the indexed column first.
>
> Yes, ASE 15 has a fully new optimizer, we need to get used to it. But
> it's really to write the indexed-column first?
>
> Flybean


tartampion Posted on 2008-04-04 18:48:29.0Z
Sender: 1f92.47f67723.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: A funny feature of the ASE 15's optimizer ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47f677fd.1fa7.1681692777@sybase.com>
References: <47f42f3c$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Apr 2008 10:48:29 -0800
X-Trace: forums-1-dub 1207334909 10.22.241.41 (4 Apr 2008 10:48:29 -0800)
X-Original-Trace: 4 Apr 2008 10:48:29 -0800, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10753
Article PK: 89367

If I may suggest:
1:delete the statistics for the tables present in your
query.
2: update all statistics on the concerned tables.
It is strongly possible that you get a better performance.
In my experience, when you move to ASE 15, update statistics
or update index statistics are not often enough.

tartampion

> We had upgrade an application from 11.9.2 to 15 with all
> indexes/PK rebuilt, all statistics updated. Some queries
> (joins between some tables) runs serious slowly than on
> 11.9.2. We checked the two plans. For 11.9.2, the plan
> used the index, while 15 did not, even we rebulit the
> index again.
>
> So we modified the sql by moving the indexed-column (which
> in the WHERE clause) to the beginning of the WHERE clause.
> After that, it ran as fast as before. The optimizer acts
> as old Oracle ( 9i before ) which asked the programmers to
> write the indexed column first.
>
> Yes, ASE 15 has a fully new optimizer, we need to get used
> to it. But it's really to write the indexed-column first?
>
> Flybean


Sherlock, Kevin Posted on 2008-04-04 20:15:52.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <47f42f3c$1@forums-1-dub> <47f677fd.1fa7.1681692777@sybase.com>
Subject: Re: A funny feature of the ASE 15's optimizer ?
Lines: 24
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: <47f68c78$1@forums-1-dub>
Date: 4 Apr 2008 12:15:52 -0800
X-Trace: forums-1-dub 1207340152 10.22.241.152 (4 Apr 2008 12:15:52 -0800)
X-Original-Trace: 4 Apr 2008 12:15:52 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10755
Article PK: 89366


<tartampion> wrote in message news:47f677fd.1fa7.1681692777@sybase.com...
> If I may suggest:
> 1:delete the statistics for the tables present in your
> query.
> 2: update all statistics on the concerned tables.
> It is strongly possible that you get a better performance.
> In my experience, when you move to ASE 15, update statistics
> or update index statistics are not often enough.
>
> tartampion

I would modify the above advice by suggesting that you experiment with
higher settings for "number of histogram steps", and "histogram tuning
factor". If you do "delete the statistics", you also delete the "number of
requested steps" in your current histograms, which you may or may not want
to do.

Also, DO NOT use "update all statistics" unless you are prepared for the
resources necessary to accomplish that, and keeping them up to date. Start
with "update index statistics" first, and if you find that stats on
non-indexed columns might help as well, do them individually.


Mark K Posted on 2008-04-05 04:13:55.0Z
From: "Mark K" <xxxx>
Newsgroups: sybase.public.ase.performance+tuning
References: <47f42f3c$1@forums-1-dub> <47f677fd.1fa7.1681692777@sybase.com> <47f68c78$1@forums-1-dub>
Subject: Re: A funny feature of the ASE 15's optimizer ?
Lines: 44
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: <47f6fc83$1@forums-1-dub>
Date: 4 Apr 2008 20:13:55 -0800
X-Trace: forums-1-dub 1207368835 10.22.241.152 (4 Apr 2008 20:13:55 -0800)
X-Original-Trace: 4 Apr 2008 20:13:55 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10758
Article PK: 89370


> I would modify the above advice by suggesting that you experiment with
> higher settings for "number of histogram steps", and "histogram tuning
> factor". If you do "delete the statistics", you also delete the "number
> of requested steps" in your current histograms, which you may or may not
> want to do.

Keep in mind that increasing the number of steps will result in more
procedure cache usage. The default value of "histogram tuning factor" in
pre-15.0.1 was 1, but now is 20.

As for deleting statistics, it is important to ensure that your stats are
current. I've seen cases where stats were still around from dropped indexes
and didn't cause problems in 12.5.x, but did in 15.0.x. In those cases,
deleting the stats and running update index stats helped.

Mark Kusma

"Sherlock, Kevin" <kevin.sherlock@teamsybase.com> wrote in message
news:47f68c78$1@forums-1-dub...
>
> <tartampion> wrote in message news:47f677fd.1fa7.1681692777@sybase.com...
>> If I may suggest:
>> 1:delete the statistics for the tables present in your
>> query.
>> 2: update all statistics on the concerned tables.
>> It is strongly possible that you get a better performance.
>> In my experience, when you move to ASE 15, update statistics
>> or update index statistics are not often enough.
>>
>> tartampion
>
> I would modify the above advice by suggesting that you experiment with
> higher settings for "number of histogram steps", and "histogram tuning
> factor". If you do "delete the statistics", you also delete the "number
> of requested steps" in your current histograms, which you may or may not
> want to do.
>
> Also, DO NOT use "update all statistics" unless you are prepared for the
> resources necessary to accomplish that, and keeping them up to date.
> Start with "update index statistics" first, and if you find that stats on
> non-indexed columns might help as well, do them individually.
>


flybean Posted on 2008-04-07 05:45:24.0Z
From: flybean <flybean@sybaseclub.org>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: A funny feature of the ASE 15's optimizer ?
References: <47f42f3c$1@forums-1-dub> <47f677fd.1fa7.1681692777@sybase.com> <47f68c78$1@forums-1-dub> <47f6fc83$1@forums-1-dub>
In-Reply-To: <47f6fc83$1@forums-1-dub>
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47f9a6e4$1@forums-1-dub>
Date: 6 Apr 2008 21:45:24 -0800
X-Trace: forums-1-dub 1207543524 10.22.241.152 (6 Apr 2008 21:45:24 -0800)
X-Original-Trace: 6 Apr 2008 21:45:24 -0800, vip152.sybase.com
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10762
Article PK: 89374

Mark K �麓碌�:

>> I would modify the above advice by suggesting that you experiment with
>> higher settings for "number of histogram steps", and "histogram tuning
>> factor". If you do "delete the statistics", you also delete the "number
>> of requested steps" in your current histograms, which you may or may not
>> want to do.
>
> Keep in mind that increasing the number of steps will result in more
> procedure cache usage. The default value of "histogram tuning factor" in
> pre-15.0.1 was 1, but now is 20.
>
> As for deleting statistics, it is important to ensure that your stats are
> current. I've seen cases where stats were still around from dropped indexes
> and didn't cause problems in 12.5.x, but did in 15.0.x. In those cases,
> deleting the stats and running update index stats helped.
>
> Mark Kusma
>
> "Sherlock, Kevin" <kevin.sherlock@teamsybase.com> wrote in message
> news:47f68c78$1@forums-1-dub...
>> <tartampion> wrote in message news:47f677fd.1fa7.1681692777@sybase.com...
>>> If I may suggest:
>>> 1:delete the statistics for the tables present in your
>>> query.
>>> 2: update all statistics on the concerned tables.
>>> It is strongly possible that you get a better performance.
>>> In my experience, when you move to ASE 15, update statistics
>>> or update index statistics are not often enough.
>>>
>>> tartampion
>> I would modify the above advice by suggesting that you experiment with
>> higher settings for "number of histogram steps", and "histogram tuning
>> factor". If you do "delete the statistics", you also delete the "number
>> of requested steps" in your current histograms, which you may or may not
>> want to do.
>>
>> Also, DO NOT use "update all statistics" unless you are prepared for the
>> resources necessary to accomplish that, and keeping them up to date.
>> Start with "update index statistics" first, and if you find that stats on
>> non-indexed columns might help as well, do them individually.
>>
>
>

OK, later we will try to check the query plans on 15 with different
indexed-column position.

Flybean


flybean Posted on 2008-04-07 05:49:15.0Z
From: flybean <flybean@sybaseclub.org>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: A funny feature of the ASE 15's optimizer ?
References: <47f42f3c$1@forums-1-dub> <47f677fd.1fa7.1681692777@sybase.com>
In-Reply-To: <47f677fd.1fa7.1681692777@sybase.com>
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47f9a7cb@forums-1-dub>
Date: 6 Apr 2008 21:49:15 -0800
X-Trace: forums-1-dub 1207543755 10.22.241.152 (6 Apr 2008 21:49:15 -0800)
X-Original-Trace: 6 Apr 2008 21:49:15 -0800, vip152.sybase.com
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10763
Article PK: 89376


tartampion wrote:
> If I may suggest:
> 1:delete the statistics for the tables present in your
> query.
> 2: update all statistics on the concerned tables.
> It is strongly possible that you get a better performance.
> In my experience, when you move to ASE 15, update statistics
> or update index statistics are not often enough.
>
> tartampion
>

update all statistics is not a good idea especially for a large wide
table. :)


Flybean