Sybase ASE 15: Optimization Goals

In Sybase 15, the optimizer has a property called optimizer goal. Each of the goals have criteria within themselves (like turning on NLJ, turning off Merge Join, etc). The optimizer goal can be configured at a server level but can be modified at a session level and even at query level.

3 goals are supported:-

This is useful for OLTP applications. Only Nested Loop Join is used.

This is for a mixed environment having both OLTP and DSS queries. Both MJ and NLJ turned on.

This is for DSS applications. All 3 join methods are turned on. It seems this is currently provided on an experimental basis. I have tried to use this on a large database with tables having millions of rows. I have run some reports by setting this at my session level and I faced tempdb fillups due to hash joins being used in the query. I am unsure what all settings need to be configured for this to work. The same issue of tempdb fillups was not happening with the allrows_mix/allrows_oltp mode.

Modifying the goal at a session level:-

set plan optgoal allrows_oltp

Modifying it at query level:-

SELECT A.a, A.text FROM A, B

WHERE A.a = B.a

plan

(use optgoal allrows_oltp)

PS: I wrote this in Jan 2011.

About the author

Vishnu Vardhan Chikoti is a co-author for the book "Hands-on Site Reliability Engineering". He is a technology leader with diverse experience in the areas of Application and Database design and development, Micro-services & Micro-frontends, DevOps, Site Reliability Engineering and Machine Learning.