Sybase ASE 15: Optimization Goals

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.

Would you like to get notifications about New Blogs?