How does the optimizer work?

This is a multi-part message in MIME format.

---=_forums-1-dub4cbc63d7
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Recently migrated to Sybase ASE 15 and have found that the
performance for about 30% or our 1500 stored procs have
greatly degraded and I'm wondering if anyone can help me
understand how the optimizer is making decisions.  My most
basic example, tables have ~ 9 million rows. Both tables
have a unique clustered index on the l_ln_id column.
Two tables
Ln
  Loan_number varchar (40)
  L_ln_id varbinary (16)

Ln_2
    L_ln_id varbinary (16)

Select loan_number from ln a, ln_2 b where a.l_ln_id =
b.l_ln_id

The i/o is about 1 million with a table scan of the ln
table.

attached is the query plan

---=_forums-1-dub4cbc63d7
Content-Type: text/plain; name="queryplan.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="queryplan.txt"

DQpRVUVSWSBQTEFOIEZPUiBTVEFURU1FTlQgMSAoYXQgbGluZSAyKS4NCg0K
DQogICAgU1RFUCAxDQogICAgICAgIFRoZSB0eXBlIG9mIHF1ZXJ5IGlzIFNF
TEVDVC4NCg0KICAgIDQgb3BlcmF0b3IocykgdW5kZXIgcm9vdA0KDQogICAg
ICAgfFJPT1Q6RU1JVCBPcGVyYXRvciAoVkEgPSA0KQ0KICAgICAgIHwNCiAg
ICAgICB8ICAgfE1FUkdFIEpPSU4gT3BlcmF0b3IgKEpvaW4gVHlwZTogSW5u
ZXIgSm9pbikgKFZBID0gMykNCiAgICAgICB8ICAgfCBVc2luZyBXb3JrdGFi
bGUyIGZvciBpbnRlcm5hbCBzdG9yYWdlLg0KICAgICAgIHwgICB8ICBLZXkg
Q291bnQ6IDENCiAgICAgICB8ICAgfCAgS2V5IE9yZGVyaW5nOiBBU0MNCiAg
ICAgICB8ICAgfA0KICAgICAgIHwgICB8ICAgfFNDQU4gT3BlcmF0b3IgKFZB
ID0gMCkNCiAgICAgICB8ICAgfCAgIHwgIEZST00gVEFCTEUNCiAgICAgICB8
ICAgfCAgIHwgIGxvYW4uLmxuXzINCiAgICAgICB8ICAgfCAgIHwgIGINCiAg
ICAgICB8ICAgfCAgIHwgIEluZGV4IDogaV9sbl8yX1BLDQogICAgICAgfCAg
IHwgICB8ICBGb3J3YXJkIFNjYW4uDQogICAgICAgfCAgIHwgICB8ICBQb3Np
dGlvbmluZyBhdCBpbmRleCBzdGFydC4NCiAgICAgICB8ICAgfCAgIHwgIElu
ZGV4IGNvbnRhaW5zIGFsbCBuZWVkZWQgY29sdW1ucy4gQmFzZSB0YWJsZSB3
aWxsIG5vdCBiZSByZWFkLg0KICAgICAgIHwgICB8ICAgfCAgVXNpbmcgSS9P
IFNpemUgMTYgS2J5dGVzIGZvciBpbmRleCBsZWFmIHBhZ2VzLg0KICAgICAg
IHwgICB8ICAgfCAgV2l0aCBMUlUgQnVmZmVyIFJlcGxhY2VtZW50IFN0cmF0
ZWd5IGZvciBpbmRleCBsZWFmIHBhZ2VzLg0KICAgICAgIHwgICB8DQogICAg
ICAgfCAgIHwgICB8U09SVCBPcGVyYXRvciAoVkEgPSAyKQ0KICAgICAgIHwg
ICB8ICAgfCBBdmVyYWdlIFJvdyB3aWR0aCBpcyAzNC4yMjA3NTMgDQogICAg
ICAgfCAgIHwgICB8IFVzaW5nIFdvcmt0YWJsZTEgZm9yIGludGVybmFsIHN0
b3JhZ2UuDQogICAgICAgfCAgIHwgICB8DQogICAgICAgfCAgIHwgICB8ICAg
fFNDQU4gT3BlcmF0b3IgKFZBID0gMSkNCiAgICAgICB8ICAgfCAgIHwgICB8
ICBGUk9NIFRBQkxFDQogICAgICAgfCAgIHwgICB8ICAgfCAgbG9hbi4ubG4N
CiAgICAgICB8ICAgfCAgIHwgICB8ICBhDQogICAgICAgfCAgIHwgICB8ICAg
fCAgVGFibGUgU2Nhbi4NCiAgICAgICB8ICAgfCAgIHwgICB8ICBGb3J3YXJk
IFNjYW4uDQogICAgICAgfCAgIHwgICB8ICAgfCAgUG9zaXRpb25pbmcgYXQg
c3RhcnQgb2YgdGFibGUuDQogICAgICAgfCAgIHwgICB8ICAgfCAgVXNpbmcg
SS9PIFNpemUgMTYgS2J5dGVzIGZvciBkYXRhIHBhZ2VzLg0KICAgICAgIHwg
ICB8ICAgfCAgIHwgIFdpdGggTVJVIEJ1ZmZlciBSZXBsYWNlbWVudCBTdHJh
dGVneSBmb3IgZGF0YSBwYWdlcy4NCg0KDQpUb3RhbCBlc3RpbWF0ZWQgSS9P
IGNvc3QgZm9yIHN0YXRlbWVudCAxIChhdCBsaW5lIDIpOiA5NTY3ODcu

---=_forums-1-dub4cbc63d7--
0
Andy
10/18/2010 3:12:23 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

2 Replies
363 Views

Similar Articles

[PageSpeed] 54

There are a few things that I'm going to take on here:

1.  The SORT on the load..ln table makes me a bit suspicious as to the exact 
nature of the query, or index structures involved.  I'm guessing that the 
"load_number" column in your select list comes from load..ln or that there 
is no index on the load..ln table that is sorted by "l_ln_id".  Otherwise, 
the SORT would NOT be necessary (and the "Average Row width of 34.220753" of 
the worktable would more closely resemble an index on a varchar(16) column).

2.  Have you measured IO and response times by forcing a Nested Loop Join 
for this example?  In other words, is this query plan _really_ slower/worse 
than before?

3.  Merge Joins happen (and are preferred over nested loop joins) when both 
inputs are sorted, and there is relatively low cardinality in one of the 
tables on the join key.  You mentioned ~9 million rows for each table.  I'd 
like to know what the stats say about both of these columns and their 
cardinalities.

4.   Your optimization goal is "allrows_mixed", or "allrows_dss". 
Otherwise, MJ is not an option.  Have you tried using "allrows_oltp" as a 
optgoal and re-run your pt testing on 15?

I'm going to stop at this point and ask you to post more details:

a.  What exact version of ASE are you running (select @@version)
b.  Post the exact SQL code you are using
c.  Post the optdiag output for each table
d.  Post the "sp_help" output (all of it, including the index defs) for each 
table
e.  Post the output of   "  exec sp_configure 'optimiz'    "

We'll pick it up from there.


<Andy> wrote in message news:4cbc63d7.3b26.1681692777@sybase.com...
> Recently migrated to Sybase ASE 15 and have found that the
> performance for about 30% or our 1500 stored procs have
> greatly degraded and I'm wondering if anyone can help me
> understand how the optimizer is making decisions.  My most
> basic example, tables have ~ 9 million rows. Both tables
> have a unique clustered index on the l_ln_id column.
> Two tables
> Ln
>  Loan_number varchar (40)
>  L_ln_id varbinary (16)
>
> Ln_2
>    L_ln_id varbinary (16)
>
> Select loan_number from ln a, ln_2 b where a.l_ln_id =
> b.l_ln_id
>
> The i/o is about 1 million with a table scan of the ln
> table.
>
> attached is the query plan
> 


0
Sherlock
10/18/2010 6:48:44 PM
  On 18-Oct-2010 17:12, Andy wrote:
> Recently migrated to Sybase ASE 15 and have found that the
> performance for about 30% or our 1500 stored procs have
> greatly degraded and I'm wondering if anyone can help me
> understand how the optimizer is making decisions.  My most
> basic example, tables have ~ 9 million rows. Both tables
> have a unique clustered index on the l_ln_id column.
> Two tables
> Ln
>    Loan_number varchar (40)
>    L_ln_id varbinary (16)
>
> Ln_2
>      L_ln_id varbinary (16)
>
> Select loan_number from ln a, ln_2 b where a.l_ln_id =
> b.l_ln_id
>
> The i/o is about 1 million with a table scan of the ln
> table.
>
> attached is the query plan

First, did you follow the recommendations for how to run update 
statistics in ASE 15?
It is recommended to follow the recommendations in this whitepaper: 
http://www.sybase.com/detail?id=1056243 . Without this, it is not a 
surprise if you run into query plan issues.

HTH,

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

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/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"

rob@NO.SPAM.sypron.nl | www.sypron.nl  | Twitter: @rob_verschoor
Sypron B.V., The Netherlands  |  Chamber of Commerce 27138666
-----------------------------------------------------------------

0
Rob
10/18/2010 10:54:21 PM
Reply: