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:
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 opt goal 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)”
About the Author
The author of the blog is a cross-functional leader and part of the Core team at Tectoro Consulting, India. He currently works on multi-market multi-asset enhancements to Gloss for our client in Hyderabad. His prior expertise is in analysis, design and development for Derivatives Regulations – Dodd Frank, EMIR, MiFID, JFSA, etc across product/entity/counterparty scope, entity registration, mandatory clearing and  transaction reporting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s