BLOGS


Sybase ASE 15: Optimization Goals


Posted by Vishnu Vardhan Chikoti

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.


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.

With an ability to conduct deep analysis, strong execution skills and an innovative mindset, he has successfully led R&D teams to build engineering solutions to improve reliability of applications. He also has deep expertise in building high volume transaction processing applications for middle & back office functions at Investment Banks using a variety of architectures.

He has been part of leadership teams in driving Site Reliability Engineering transformation and Agile transformation.

Would you like to get notifications about New Blogs?