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

 

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

Version 15.7 is built from the 15.5 releases, with additional features added.

ASE 15.7 GA was released without additional optcriteria.

 

Table 3-1: ASE 15.7 Optlevel Compatibility Chart

ASE 15.7 GA	ase_current = ase155esd4
ASE 15.7 ESD 1	ase_current = ase157esd1
ASE 15.7 ESD 2	ase_current = ase157esd1**

 

Table 3-2: 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

 

**ASE 15.7 ESD #2 contains the same optlevels and optcriteria as ASE 15.7 ESD #1. 

 

ASE 15.7 ESD #2 did not include optlevel 'ase157esd2' as there were no additions or changes to the optcriteria.  

In 15.7 ESD #2,  ase_current is equal to ase157esd1.   

 

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.   

 

This document will be updated with each new ESD.