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.

Example:
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
--
ase_current

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.

Examples:

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.

Example:

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

namecurrentsettingdefaultsetting
optlevelase_currentase_default
full_index_filter1

0

___________________________________________________________________________________

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"

where:

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

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
go
 
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

------------------------------
goal_1571

select @@optlevel

------------------------------

ase157esd1

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
OR
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


CR
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
 
 
Examples:

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



on

transform non-functional update producer lop to semi-join


on

forceplan shouldn't fail with subquery under outer join


on

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
 
 


off

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
 
 


off

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
 
 


on

limit index starting position optimization


on

remove zeros from LIKE lower bound sarg


on

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


on

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

on

intelligent density with null value support

cr744975


on


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
 
 


off

allow bloom filter pushdown for joins    


off

set bulk insert mode as optimization for INSERT  


on

limit index starting position optimization  


on

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


on

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


on

LIKE optimization - upper bound mis-estimation     


on

accurate parallel create index cluster ratios     


on

copy multi node subquery cost             

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


Tags:
Former Member

No comments