Switch to standard view 
  Sybase logo
 
 
 



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 the necessarily benefit from, these changes when upgrading to the latest ESD.

 

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.


This white paper will be updated with each new ESD release.

 

 

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 optcriteria or optlevels with the current terminal session in Adaptive Server.

 

Example:
set plan optlevel ase_current

Table 1-1: Available optlevels

 

Value

Defined

ase_current

enable all optimizer changes up through the current ESD

ase_default

disable all optimizer changes since 1503 esd #1

ase1503esd2

enable all optimizer changes up through 15.0.3 ESD #2

ase1503esd3

enable all optimizer changes up through 15.0.3 ESD #3

ase1503esd4

enable all optimizer changes up through 15.0.3 ESD #4

ase155esd3 enable all optimizer changes up through 15.5 ESD #3
ase155esd4 enable all optimizer changes up through 15.5 ESD #4
ase_current on ASE 15.7 GA enable all optimizer changes up through 15.5 ESD #4
ase155esd5 enable all optimizer changes up through 15.5 ESD #5
ase157esd1 enable all optimizer changes up through 15.7 ESD #1
ase_current on ASE 15.7 ESD#2 enable all optimizer changes up through 15.7 ESD #1

 

 *NOTE: 15.7 ESD #2 did not include any additions or changes to the optcriteria.  See table 3-1.

 

 

2.Setting optlevels with a login trigger

 Use sp_modifylogin to set the optlevels with individual logins.

For example:

 

sp_modifylogin my_login, "login script", my_proc

 where my_proc =


   create proc my_proc

  as set plan optlevel ase1503esd2

  set cr545180 off

  go

 


 

 3. Setting optlevels globally with sp_configure (as of 1503esd3, 15.5 esd1)

 

Use the "optimizer level" configuration parameter to enable the optimizer changes. For example:

 sp_configure "optimizer level", 0, "ase_current"

 See table 1-1 for supported values.

 

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

 select @@optlevel
 --
ase_current

 

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)"

 

See table 1-1 for supported values

 

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. 

 

 

Examples:

set cr559034 on
set mru_buffer_costing off

To view all available Optcriteria in the current release level:

sp_options "show"

 

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"

 

Name

currentsetting

defaultsetting

Optlevel

ase_current

ase_default

full_index_filter

1

0

 

 

6.      Creating a user-defined optimization goal

 

As of  ASE 15.7 ESD #2, we  have the ability to configure user-defined optimization goals.

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

 

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 1

set CR646220 0

go

execute sp_optgoal "goal_1571", "save"

go

sp_optgoal @@optgoal, "show"    -- reports current optgoal settings

 

 

Table 1-2:  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.

cr562947

OPTLEVEL EXCEPTION SEE CR - allow cursor table scans

cr545771

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

cr545653

avoid inner table buffer estimate starvation

cr545585

covered iscan CPU costing too expensive

cr545379

disallow reformatting on user forced index scan

cr545180

avoid reformat with no sargs if useful index exists

cr545059

reduce usage of buffer manager optimization sorts

cr534175

compute GROUP BY worktables in nested subqueries only once when possible

cr531199

increases the number of useful nested loop join plans considered

cr500736

supports nocase sortorder columns in mergejoin and hashjoin keys

cr487450

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

cr467566

allow abstract plans and statement cache to work together

cr497066

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

cr421607

support NULL=NULL merge and hash join keys

cr552795

eliminate duplicate rows during reformatting when they're not needed

cr546125

implicitly updatable cursor non-unique index scan

cr544485

mark subquery join predicates with distinct view as sargs

data_page_prefetch_costing

CR 546737 clustered row bias added

mru_buffer_costing

CR 546737 wash size buffer limit for MRU

imdb_costing*

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-3:  ase1503esd2: Optcriteria ON when optlevel is ase1503esd2 or ase_current.

 

allow_wide_top_sort

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

avoid_bmo_sorts

CR 550574:  avoid sorts used only for buffer manager optimization

distinct_exists_transform

CR 567317:  transform distinct to semi-join

outer_join_costing

CR 556559:  outer join row counts and histogramming

conserve_tempdb_space

CR 555164:  keep estimated tempdb below resource granularity

join_duplicate_estimates

CR 556559: avoid overestimates of dups in joins

timeout_cart_product

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

search_engine_timeout_factor

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

cr559034

avoid preferring non-covering over covered index scans

cr556728

facilitates merge joins between small tables

 

 

Table 1-4:  1503 esd #3, 15.5 esd #1: Additional Optcriteria ON when optlevel is ase1503esd3 or ase_current

 

auto_temptable_stats

CR 540665:  auto generation of statistics for #temptables

use_mixed_dt_sarg_under_specialor

CR  575115:  allow special OR in case of mixed datatype sargs in IN/OR list

 

Table 1-5: 1503 esd #4 or 15.5 esd #2: Additional Optcriteria ON when optlevel is ase1503esd4 or ase_current

 

cr611385

enable ScanValues costing

cr574923

extend search space hidden by greedy algorithm

cr614461

enable in-order rid scan estimates;

cr611637

avoid duplicate NULL selectivity

cr619965

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

cr439123

0.0 range density for low cardinality tables

cr619756

DISTINCT, EXISTS, and GROUP BY row estimation

cr624835

correlated equi-join constant costing

cr628311

cost estimation for refomatting plan

cr628845

avoid_bmo_sorts inconsistent across parallel/serial 

in_list_general_or

CR 605862: optimize in lists under general ors

multi_attribute_density_costing

CR 594101: Use legacy multi-attribute density costing technique

disable_nonbin_sortorder_interpolation

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

 

 Table 1-6: 15.x all esds:

OFF by default, not affected by optlevels, only manually with set command

 

no_stats_distinctness

CR 539440: allow duplicate estimates without stats

full_index_filter

CR 579962: eliminate non-covered full index scan strategies

 

________________________________________________________________________________

 

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 esd3 additional optcriteria ON when optlevel is ase155esd3 or ase_current.

cr636592 do not skip good opportunistic distinct plans
cr631416 semi-join row estimate adjustment
cr639977 eager virtual column sarg evaluation
basic_optimization_partitions CR 643811: allow partitioned tables in basic optimization
allow_large_index_row CR 643811: allow large index row in basic optimization
cr606288 disable parallelism in 12.5 optimizer
cr644777 allow multiple IN lists in large-IN-List transformation  

Table 2-3: ASE 15.5 esd4 additional optcriteria ON when optlevel is ase155esd4 or ase_current.

cr647051 correct NULL selectivity estimates
cr649006 reduce CPU cost for table scan partition elimination
cr649470 subquery conserve_tempdb_space
cr649590 optimize always false queries
allow_minmax CR 658105: allow local session to consider MINMAX optimization

cr646220

enable better store index key generation with correlated predicate
cr663304 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

cr575761

allow general OR scans under dirty reads
cr675179 composite special OR costing
cr673625 avoid_bmo_sorts performance INSERT/SELECT INTO with ORDER BY

cr674607

fixes parallel thread use count calculations
cr685239

enable XCHG op and basic parallel cost based costing updates

cr646024

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 ON when optlevel is ase157esd1 or ase_current

cr669847

transform non-functional update producer lop to semi-join

cr683626

forceplan shouldn't fail with subquery under outer join

cr685996

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

 

cr703123

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

 

cr698102

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

 

limit_row_constructor

on

limit index starting position optimization

cr731808

on

remove zeros from LIKE lower bound sarg

cr702725

on

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

cr738692

on

copy multi node subquery cost               

 

 

15.7 Carina Release

 

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

 

join_bloom_filter

 off

allow bloom filter pushdown for joins      

INS_BY_BULK

 off

set bulk insert mode as optimization for INSERT    

limit_row_constructor

 on

limit index starting position optimization    

cr731808

 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

 

cr702725

 on

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

cr713738

 on

LIKE optimization - upper bound mis-estimation       

cr739820

 on

accurate parallel create index cluster ratios       

cr738692

 on

copy multi node subquery cost               

 

This document will be updated with each new ESD.