Skip to Content

Optimizer Stability Changes in Adaptive Server 15.x

A Technical White Paper

Starting in Adaptive Server version 15.0.3 ESD #2, performance related optimizer changes are not enabled by default.
Users will need to test the enhancements using specific set options.
In addition, functionality has been added to switch on/off previous optimizer changes and features.
This white paper discusses how and when to enable the changes.
Because the optimizer changes are not enabled by default, applications already running efficiently are not affected by, nor will they necessarily benefit from these changes when upgrading to the latest release.

In 15.0.3 ESD #3 and above, we introduced support for global Optlevels.  Enabling these changes increases query performance for many applications, though Sybase recommends additional performance testing.

In 15.5 ESD #3 and above, we introduced 15.5 global Optlevels that match the 15.5 ESD level. Prior to ESD #3, 15.0.3 ESD versions were mapped to 15.5.   See table 2-1.

Setting Optimizer Levels
There are 6 ways to set the optlevels and/or optcriteria
1.  Setting optlevels at the session Level, available as of 15.0.3 ESD #3 and 15.5 ESD #1.

Use 'set plan optlevel' at the session level to set real time optlevels with the current session in Adaptive Server.

set plan optlevel ase155esd4

Note that not all release levels are available for this change at the session level.
2. Setting optlevels using login triggers

Use sp_modifylogin to set the optlevels with individual logins.

For example:

where my_proc is written as:

create proc my_proc
set plan optlevel ase1503esd2
set cr545180 off
3.  Setting optlevels globally with sp_configure (as of 15.0.3 ESD #3, 15.5 ESD #1)

Use the "optimizer level" configuration parameter to enable the optimizer changes.

For example:

sp_configure "optimizer level", 1, "ase_current"

Note that not all release levels are available for this change globally.

Global Function call
@@optlevel added to display the current optlevel settings:

select @@optlevel

All versions of 15.x will be installed with ase_default  as the optlevel.   This is the equivalent optlevel of ase1503esd1.  Technical support recommends tuning ASE with ase_current either globally, or on a session level, after installation.
4.  Setting optlevels within abstract plans

Use abstract plans to enable the optimizer changes. For example:

select name from sysdatabases

plan "(use optlevel ase_current)"
5.   Individual Optcriteria enabled/disabled using SET command

Enhancements may result in some queries that are improved upon, while other queries may encounter unforeseen complexities.

You may wish to plug and play on a fine grain level.  Use sp_options to view the list of available options in the current release.

Some optcriteria are denoted by their CR numbers, while other more recent optcriteria have been given descriptive_names.
To turn optcriteria on, use "on", or 1.  To turn off, use "off", or 0.


set cr559034 on
set mru_buffer_costing 0

To view all available optcriteria in the current release level:

When enabling or disabling individual optcriteria, the optlevel remains set to the previous setting, with additional individual changes to the settings.


set plan optlevel ase_current
set full_index_filter on
select @@optlevel
sp_options "show", "full_index_filter"




6.   Creating a user-defined global optimization goal


As of  ASE 15.7 ESD #2, we  have the ability to configure user-defined optimization goals to be used on a session level, or globally with sp_configure.

Use sp_optgoal to create a user-defined optimization goal. The syntax is:

sp_optgoal "goal_name", "save"


– which cannot be longer than 12 characters, is the name of the goal you are creating.
– creates the goal if it does not already exist
- deletes the goal

This example creates a goal called goal_1571, which:
1.     Sets the optimization level to ase157esd1
2.     Sets the optimization goal to allrows_mix
3.     Enables hash joins
4.     Enables the optimization criteria for CR683626, which is off in ase_default
5.     Disables the optimization criteria for CR646220, which is on in ase_default
set plan optlevel ase157esd1
set plan optgoal allrows_mix
set hash_join 1
set CR683626 on
set CR646220 0
execute sp_optgoal "goal_1571", "save"
NOW you can set it at the session level
set plan optgoal goal_1571

NOW it is set for this session

select @@optgoal


select @@optlevel



OR to set it globally serverwide
sp_configure "optimization goal", 1, "goal_1571"
sp_configure "optimization goal"
Parameter NameConfig ValueDefault
optimization goalgoal_1571allrows_mix
sp_optgoal                        -- with no arguments lists all the saved optgoals
sp_optgoal goal_1571      -- reports all current optgoal settings
sp_optgoal @@optgoal   
Independently Switchable Optcriteria in each version and ESD level
Table 1-1:  ase1503esd1, 15.5 IR or 15.7 GA.  Optcriteria that are ON by default.  Switchable with the set command.
Not affected by the global optlevels. Only the set command can turn these off.

allow cursor table scans

improves multi-table outer-join and semi-join costing

avoid inner table buffer estimate starvation

covered iscan CPU costing too expensive

disallow reformatting on user forced index scan

avoid reformat with no sargs if useful index exists

reduce usage of buffer manager optimization sorts

compute GROUP BY worktables in nested subqueries only once when possible

increases the number of useful nested loop join plans considered

supports nocase sortorder columns in mergejoin and hashjoin keys

improves DISTINCT costing of multi-table outer joins and/or semi-joins

allow abstract plans and statement cache to work together

infer the nullability of isnull() by looking at its parameters

support NULL=NULL merge and hash join keys

eliminate duplicate rows during reformatting when they're not needed

implicitly updatable cursor non-unique index scan

mark subquery join predicates with distinct view as sargs

CR 546737 clustered row bias added

CR 546737 wash size buffer limit for MRU

CR 594101: PIO costing for scans for in-memory database
*new feature in 15.5.  On by default in 15.5 and up.
Table 1-2:  ase1503esd2: Optcriteria ON when optlevel is ase1503esd2 or ase_current.

CR 557967: allow max row size to be exceeded for top sorts

CR 550574:  avoid sorts used only for buffer manager optimization

CR 567317:  transform distinct to semi-join

CR 556559:  outer join row counts and histogramming

CR 555164:  keep estimated tempdb below resource granularity

CR 556559: avoid overestimates of dups in joins

CR 589473:  timeout queries involving cartesian product and more than 5 tables.

CR 532474:  Open cursor command takes a long time with a complex select stmt

avoid preferring non-covering over covered index scans

facilitates merge joins between small tables
Table 1-3:  15.0.3 ESD #3, 15.5 ESD #1: Additional Optcriteria ON when optlevel is ase1503esd3 or ase_current

CR 540665:  auto generation of statistics for #temptables

CR  575115:  allow special OR in case of mixed datatype sargs in IN/OR list
Table 1-4: 15.0.3 ESD #4 or 15.5 ESD #2: Additional Optcriteria ON when optlevel is ase1503esd4 or ase_current

enable ScanValues costing

extend search space hidden by greedy algorithm

enable in-order rid scan estimates;

avoid duplicate NULL selectivity

dense frequency for 'Jan  1 1900 12:00:00:000AM';

0.0 range density for low cardinality tables

DISTINCT, EXISTS, and GROUP BY row estimation

correlated equi-join constant costing

cost estimation for refomatting plan

avoid_bmo_sorts inconsistent across parallel/serial

605862: optimize in lists under general ors

CR 594101: Use legacy multi-attribute density costing technique

CR 592948: turn off non-binary sort order interpolation costing

Table 1-5: 15.x all ESDs:
OFF by default, not affected by optlevels, only manually with set command


CR 539440: allow duplicate estimates without stats

CR 579962: eliminate non-covered full index scan strategies

CR 488880:
turn off non-covering indices with no limiting sargs and missing stats
ASE 15.5

Version 15.5 is built from the 15.0.3 releases, with additional features added.
Table 2-1: ASE 15.5 Optlevel Compatibility Chart

ASE 15.5 all ESDs

ase_default = ase1503esd1 + imdb_costing ON

ASE 15.5 ESD #1

ase_current = ase1503esd3 + imdb_costing ON

ASE 15.5 ESD #2

ase_current = ase1503esd4 + imdb_costing ON

ASE 15.5 ESD #3

ase_current = ase155esd3

ASE 15.5 ESD #4

ase_current = ase155esd4

ASE 15.5 ESD#5

ase_current = ase155esd5

ASE 15.5 ESD#5.1

ase_current = ase155esd5

ASE 15.5 ESD#5.2

ase_current = ase155esd5

ASE 15.5 ESD#5.3

ase_current = ase155esd5

Version = 15.5 ESD #1 or ESD #2

sp_configure "optimizer level", 1, "ase1503esd1"

This would also equal "ase_default" with  imdb_costing ON.

Version = 15.5 ESD #2

sp_configure "optimizer level", 1, "ase_current"

This  equals "ase1503esd4" with imdb_costing ON.

With the introduction of 15.5 ESD #3, "optimizer level" can be set globally to ase155esd3

Version = 15.5 ESD 3 and above.

sp_configure "optimizer level", 1, "ase155esd3"
Table 2-2: ASE 15.5 ESD #3 additional optcriteria ON when optlevel is ase155esd3 or ase_current.

do not skip good opportunistic distinct plans

semi-join row estimate adjustment

eager virtual column sarg evaluation

CR 643811: allow partitioned tables in basic optimization

CR 643811: allow large index row in basic optimization

disable parallelism in 12.5 optimizer

allow multiple IN lists in large-IN-List transformation
Table 2-3: ASE 15.5 ESD #4 additional optcriteria ON when optlevel is ase155esd4 or ase_current.

correct NULL selectivity estimates

reduce CPU cost for table scan partition elimination

subquery conserve_tempdb_space

optimize always false queries

CR 658105: allow local session to consider MINMAX optimization

enable better store index key generation with correlated predicate

forced index will restrict one row rule
Table 2-4: ASE 15.5 ESD #5 Additional Optcriteria ON when optlevel is ase155esd5 or ase_current

allow general OR scans under dirty reads

composite special OR costing

avoid_bmo_sorts performance INSERT/SELECT INTO with ORDER BY

fixes parallel thread use count calculations

enable XCHG op and basic parallel cost based costing updates

add in sort operator's child costs for fastfirstrow recosting

ASE 15.7

New Optcriteria in 15.7 may be available, but not on by default.

Table 3-1:  ASE 15.7 ESD #1 Additional Optcriteria available when optlevel is ase157esd1 or ase_current


transform non-functional update producer lop to semi-join


forceplan shouldn't fail with subquery under outer join


enhanced subquery and outerjoin rules for busy/right- nested plan
Table 3-2: ASE 15.7 ESD #3 Additional Optcriteria available when optlevel is ase157esd3 or ase_current


allow general-or strategies with more than 128 or terms
Table 3-3:  ASE 15.7 ESD #4 Additional Optcriteria available  when optlevel is ase157esd4 or ase_current


recompile queries with single volatile table when rowcount increases  
Table 3-4:  ASE 15.7 ESD #4.3 or SP50:  Additional Optcriteria available  when optlevel is ase157esd43 or ase_current


limit index starting position optimization


remove zeros from LIKE lower bound sarg


optimize the INSERT..SELECT..ORDER BY plan selection


copy multi node subquery cost             
Table 3-5:  ASE 15.7  SP60:  Additional Optcriteria available  when optlevel is ase157sp60 or ase_current


intelligent density with null value support



cheaper plan with use of ase_current or avoid_bmo_sorts           

15.7 SP100
Table 4-1:   ASE 15.7 SP100  Additional Optcriteria available  when optlevel is ase157sp100 or ase_current


allow bloom filter pushdown for joins    


set bulk insert mode as optimization for INSERT  


limit index starting position optimization  


remove zeros from LIKE lower bound sarg       
Table 4-2:  ASE 15.7 SP110 Additional Optcriteria available  when optlevel is ase157sp110 or ase_current


optimize the INSERT...SELECT...ORDER BY plan selection       


LIKE optimization - upper bound mis-estimation     


accurate parallel create index cluster ratios     


copy multi node subquery cost             

This document will be updated with each new ESD or SP level released.


No comments