Posted on 10-Aug-2018 19:24:12
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:-allows_oltp
This is useful for OLTP applications. Only Nested Loop Join is used.allrows_mix
This is for a mixed environment having both OLTP and DSS queries. Both MJ and NLJ turned on.allrows_dss
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.
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.