Switch to standard view 
  Sybase logo
 
 
 



Summary

This white paper is designed to be used as a complement to Document 1011345, The “Why” and “How” of Migrating to ASE 11.9.2 and 12.0 - A UK User Group Presentation. The presentation is a comprehensive discussion of upgrade and migration, including an extensive how-to discussion migration methods. This white paper discusses additional technical issues that you should be aware of when planning an upgrade to Adaptive Server Enterprise 11.9.2 or 12.0.

The major headings “ Planning and Pre-Upgrade“and “ Post Upgrade“in this white paper correspond to headings in the presentation, to make it easier to use both documents. The technical issues discussed in this white paper are limited to these two areas.


Note:

Neither this white paper nor the "Why and How" presentation is intended to be a step-by-step guide to performing an upgrade. Please see your installation manual for the exact upgrade steps.


Contents

This TechNote contains the following sections:

Overview of Upgrade/Migration Process

In general, the process of moving your system from a pre-11.9.2 version of Adaptive Server Enterprise and associated applications, to version 11.9.2 or 12.0 is as follows:

Planning and Pre-Upgrade

Before performing an upgrade to ASE 11.9.2 or 12.0, verify that you have addressed all technical issues listed here. Perform the recommended corrections before beginning the upgrade.

Documentation Changes

ASE 12.0 includes several new manuals that describe separately-licensed Adaptive Server features such as Java, High Availability, and Distributed Transaction Management. See the Related documents section of your installation guide for information on new manuals.

Information Moved to Different Manual

Information from the following manuals:

has been moved to the following manuals:

Information from Configuring Adaptive Server for UNIX has been moved into the Installation Guide.

Discontinued Documents

The ASE documentation set no longer includes the following documents:

Correction to Documentation: Maximum Number of Users and Logins

The maximum number of logins to the server and users to the database has been increased, however the ASE 12.0 documentation incorrectly states the maximum number of users, logins and groups. ASE 12.0 now supports:

There is some new behavior due to this change:

Skipping Versions

If you are skipping versions to the new version of ASE that you are going to (i.e. 11.0.x to 11.9.2 or 12.0), you need to consult the What’s New in Sybase Adaptive Server Enterprise and the Migrating to ASE 11.5 manuals for the interim versions you are skipping to be aware of changes that occurred in those releases.

User-Modified System Tables

If you have modified the schema of the system catalogs in any way, be aware that your changes may clash with changes in the new release. Review the documentation and identify any changes to the system catalogs that may affect you. If the schema has changed for any of the system catalogs in the new release, you need to take this into account in your migration planning.

Changes that Can Affect Applications or Database Management

Sybase periodically makes changes to Transact-SQL for ANSI compliance or to improve functionality. Changes in 12.0 mainly provide more functions and options. However, earlier releases of ASE included changes in behavior of commands and could lead to unpredictable results if applications were not updated to reflect the changes.

In addition, Sybase may change or add new system procedures, configuration parameters, or system databases and tables. These objects may be referenced in scripts and applications, and the changes may lead to unexpected results.

This document notes some of these changes; the documents listed at the end of this section contain the complete list of changes that may affect your system.

Database Sizes

When you upgrade a server, the following system databases require more space than may be required by a new installation. The recommended sizes for upgrade are shown in the following table:

System Database

Recommended Size in MB

Master

7

Model

3

Tempdb

3

Sybsystemprocs

80

pubs2

4

pubs3

4

The sybsystemprocs Database

You may need to increase the size of the sybsystemprocs database and possibly the device it is on. See your installation guide for a discussion of enlarging sybsystemprocs.

If sybsystemprocs is placed on multiple device fragments, all fragments need to have both data and log segments. This means that if the sysusages table has multiple rows for sybsystemprocs, the segmap column value must be “7” for every row.

sybsystemdb

The sybsystemdb database is used for the spt_values table and two-phase commits. It is required if you want to use the Distributed Transaction Management (DTM) component. The database is automatically created on the master device by the configuration utility.

Because this database may have a lot of activity and use a lot of log space, we strongly recommended that you create sybsystemdb on another device prior to upgrade or move it to another device after upgrade. This protects the master device in case the sybsystemdb log fills up.

If you will not use the features that require this database, you can keep it at the minimum size of 4 MB. See the installation guide for more information.

New Reserved Words

Reserved words can only be used by ASE. New ASE objects and commands result in new reserved words being added with every release. To see the new reserved words for ASE 12.0, see the Adaptive Server Reference Manual.

You must change all object names that contain reserved words before you upgrade. You must also change those names in your procedures, SQL scripts, and applications before you can run them against the upgraded server.

Use the reserved word check in sqlupgrade, which you can run without starting the upgrade, to check object names. Subsequent checks can be performed by running the stored procedure, sp_checkreswords. Names that contain reserved words can be changed with the sp_rename procedure, or can be enclosed in double quotes. See the discussion on reserved words in your installation guide.



Note:

The Sybase procedures for detecting reserved words in object names cannot be used to find them in scripts and applications. You need to check scripts and applications separately.


Configuration Parameters

Configuration parameter values that you have set are preserved by the upgrade. Only those parameters that are set at “DEFAULT” in the server configuration file are changed to the new version’s default value. Because some parameter default values are now higher, values that you set in the older version may not be high enough for the new version. The following parameters can cause problems during or after upgrade if they are set too low:

The parameter enable xact coordination can cause a problem for RPCs when set at default. These issues are explained the next sections.

stack size

The default value of the stack size parameter has increased over the last several releases. While stack size varies from platform to platform, the changes on Solaris (shown in the table below) illustrate the increase in stack size requirements with successive versions:

Server Version

Minimum Stack Size

11.0.x

24576 (24K)

11.5

34816 (32k)

11.9.2

34816 (32k)

12.0

46090 (45K)

If the stack size is set too low, the server could experience stack overflow, either during upgrade or later during processing.

Check your stack size parameter and reset it to default if necessary. You may do this by modifying the server configuration file, replacing the existing value with the word “DEFAULT”.



Note:

ASE 12.0 on Solaris may require a stack size two to four times the default in environments with heavy I/O.


cpu grace time

If you are running ASE 11.0.x, the default value for the cpu grace time parameter is 100ms. The default was changed to 500 in ASE 11.5. If cpu grace time is set to more than the default but less than 500, you could get timeslice errors. Before upgrading, change the parameter to “DEFAULT” if it is set to less than 500.

enable housekeeper GC

Housekeeper may begin “garbage collection” at inappropriate times during the upgrade (such as when installmaster is running). This can cause you to get corruption messages. Sybase recommends setting the enable housekeeper GC parameter to 0 before upgrading and resetting to 1 after the upgrade has successfully completed.



Note:

For more information on configuration parameters, see the System Administration Guide.


RPC Problem with “enable xact coordination”

The configuration parameter enable xact coordination defaults to “1”. With this parameter turned on, you cannot execute an RPC from within a transaction, due to a bug in DTM (CR 218736). Set this parameter to “0” if you are not using DTM.

Result Set Issues

Result set problems may consist of data being returned in a different manner than previously or different data sets being returned. These problems are usually caused by changes made to the server to make it more compliant with international standards. Careful investigation of existing code by developers who have reviewed these changes should eliminate most surprises. Listed below are most of these changes. For details on them, please consult the What’s New in Adaptive Server Enterprise manual for that version.

Changes introduced in 11.5.x

If you are upgrading from a pre-11.5 version, you are more likely to encounter issues relating to SQL changes in ASE 11.5. The following list is a summary. For more detailed information, see Migrating to Adaptive Server Enterprise.

 

Datatype Mismatches

Changes introduced in ASE 11.5 are responsible for many datatype mismatches. The most common of these are:

Changes introduced in 11.9.2

 

Changes introduced in 12.0

 

Other System Changes

As is the case for reserved words, you may need to look through your applications and scripts for instances of changed commands or objects.

Look for changes to:

The following documentation lists these changes for ASE 12.0, 11.9.2, and 12.0:

The Upgrade Process

The following sections describe some upgrade process issues and note some differences from previous installation procedures. These include new tools, new directory structures, and new environment variables that you must set. For a complete description of the upgrade steps, see your installation guide.

OS Patches

Before upgrading, make sure your operating system is at the highest patch level possible, not just the minimum required patch level. The documented patch level is the one ASE was certified on. However, the vendor may have already released a higher level of the operating system. Being at the highest patch level available ensures that you have the most recent bug fixes and may help you avoid some operating system problems when you run applications.

New: Studio Installer

In ASE 12, Studio Installer replaces sybsetup. Use Studio Installer to:

The Studio Installer is Java-based and uses XML input, which makes the installation process similar on both UNIX-based and Windows-based computers. You must have a window package installed in order to use the graphical user interface (GUI).

Component Licensing

Licensing of components is done through Sybase Software Asset Management (SySAM), a licensing mechanism that allows system administrators to monitor their site’s use of registered Sybase products and optional features. SySAM handles check-in and check-out requests from all other Adaptive Server instances in the system. See the white paper License Manager - Sybase Software Asset Management for more information.

New directory structure in ASE 12.0

Each Sybase product in a product bundle is referred to as a “component” for installation purposes. In this release, most components, including the executable program, installation and configuration tools, and display-related files needed by the component, are installed in their own subdirectories. The naming convention for subdirectories includes a component identifier, such as ASE or jConnect, and the software release version, such as 12_0 or 4_2. For example, Adaptive Server is installed in $SYBASE/ASE-12_0.

Shared components are installed in subdirectories that are separate from component subdirectories. For example, Open Client is installed in $SYBASE/OCS-12_0. This directory structure enables you to install into an existing $SYBASE directory structure, and to install and use multiple versions of some components.

If you have scripts that explicitly reference the old directory structure (for example, $SYBASE/bin/bcp or $SYBASE/bin/isql) you need to modify these scripts to work with the new directory structure. Alternatively, Unix users can source SYBASE.sh or SYBASE.csh, and utilities such as isql and bcp are added to your path. You can then reference these utilities without including the full path.

The following sections describe the new directories for 12.0. For more information on issues relating to installation directories, see the Migration Resources Page.

ASE-12_0

This is the directory which contains the dataserver component and it’s related files. The dataserver is located in the bin subdirectory of ASE-12_0. This also contains the init, install, and scripts directories.

OCS-12_0

This is the directory which contains the Open Client and Open Server libraries along with client executables. Client applications such as isql and bcp are now in the bin subdirectory under OCS-12_0. Open Client and Open Server libraries are now in the lib subdirectory under OCS-12_0.

jConnect-4_2

This contains the jConnect component that is compliant with the JDBC 1.x specification.

jConnect-5_2

This contains the jConnect component that is compliant with the JDBC 2.0 specification.

FTS-12_0

This contains the Full-Text Search component.

New environment variables

ASE 12.0 has new environment variables that point to Adaptive Server and Open Client/Server installation directories. Update all scripts that refer to the old $SYBASE directory structure to use the new paths and environment variables. For more information, see the installation guide for your platform.

Sybase includes scripts in $SYBASE, SYBASE.sh for Bourne/Korn shells and SYBASE.csh for C shells, which set all the necessary environment variables.

The following two environment variables are required for any 12.0 client connecting to any 12.0 server:

$SYBASE_ASE

This is the new environment variable for the dataserver. In ASE 12.0, the SYBASE_ASE environment variable should be set to ASE-12_0.

$SYBASE_OCS

This is the new environment variable for Open Client software and Open Client Server libraries. In ASE 12.0, the SYBASE_OCS environment variable should be set to OCS-12_0. Utilities such as isql and bcp now reside in the bin directory under OCS-12_0. The proper path to isql is now $SYBASE/$SYBASE_OCS/bin/isql.

Upgrading to an EBF Rollup

Sybase has two kinds of general-availability releases: Maintenance Releases such as 11.9.2 or 12.x, and Electronic Software Distribution (ESD) Releases, formerly known as EBF Rollups. Maintenance Releases do not have an SWR or EBF number; they contain the complete software distribution of a release. ESD releases have an SWR or EBF number, and contain only the software that has been modified since the last Maintenance Release. You may prefer to migrate to the most recent ESD, which contains the latest bug fixes. If you are planning to migrate to an ESD Release (as we recommend) rather than a Maintenance Release, your migration planning needs to account for this.

If you are a registered Technical Support contact, you can find the latest ESD release by going to Sybase.com and clicking the Downloads tab. Look for “EBFs/Updates” on the left navigation column and log into the ESD page.



Note:

New data elements used to manage text and image columns are not always properly initialized during upgrade to early releases of ASE 12.0. If you have text columns in the database(s) to be upgraded, you could encounter corruption in text and image columns. We strongly advise that you upgrade directly to at least 12.0.0.2 ESD #1 to avoid encountering corruption in your text and image data.


If you are upgrading to an ESD release, you need to perform these tasks in this order:

  1. Install the Maintenance Release (That is, ASE 11.9.2 or 12.x) in its own directory.

  2. Install the ESD Release on top of the Maintenance Release in the Maintenance Release directory.

  3. Using the scripts provided with the EBF, run installmaster and installmsgs.ebf.

  4. Finish performing the upgrade to 11.9.2 or 12.x.


WARNING!

You must run installmaster after installing the EBF. This installs the system stored procedures versions that come with the EBF. If you do not run installmaster, the server must rely on older versions that may not contain the most recent updates.


Please refer to the installation guide and the EBF cover letter for complete installation instructions.

Replicated Databases

Be sure to read “Before Upgrading Replicated Databases” in the installation guide.

Run dbcc settrunc(ltm, ignore) in replicated databases. This sets the truncation point in the replicated database’s transaction log.

Run rs_zeroltm dataserver,database in the RSSD for each replicated database. This sets the truncation point in the Replication Server.

Pre-Upgrade Checklist

 

  1. Install the most recent version of the operating system, or at least the certified release for ASE.

  2. Check that you have enough disk space. See your installation guide for requirements.

  3. Check that each database has at least 10% free space. This is needed by the upgrade.

  4. Check your applications to assess impact from SQL changes. See the section Changes that Can Affect Applications or Database Management.

  5. Check for new reserved words, including those in scripts and applications. See the section New Reserved Words.

  6. Replace obsolete environment variables in applications and scripts. See the section New environment variables.

  7. Record size and device fragment information for all databases. You can query the sysdevices and sysusages tables for this information.

  8. Make a note of the default character set and sort order. See the System Administration Guide for information about character sets and sort order.

  9. Verify that sybsystemprocs is large enough and that all device fragments, if there are multiple fragments, contain both log and data segments. See the section “The sybsystemprocs Database”

Just before upgrading, be sure to do the following:

  1. Set master as the default database for “sa”.

  2. If the locations of your error log and configuration file are not the default locations, change them to the default location.

  3. Back up the following:

    • All databases

    • NT Devices

    • Sybase directory

    • (NT) Sybase registry

  4. bcp out critical system tables:

    • Syslogins

    • Sysloginroles

    • Sysdatabases

    • Sysusages

    • Sysdevices

  5. On NT, if server is an automatic service, change the service type for ASE to manual.

  6. Stop mirroring.

  7. Disable auditing: sp_auditoption “enable auditing”, “off”

  8. Set trunc log on chkpt off in all databases.

  9. Save the server configuration (.cfg) file.

Post Upgrade

After the upgrade, you need to verify that your system runs as well as it did before the upgrade. In this section, we recommend some tasks to perform immediately after the upgrade to insure the reliability of the server.

Additionally, this document discusses some changes to query processing that may affect some sites. This information is a summary of information available in more detail in the Performance and Tuning Guideand other documentation.

Finally, this document covers some changes in ASE 12.0 that you should be aware of as you plan for database maintenance. More information on new features is available in the manual, What’s New in Adaptive Server Enterprise.

Immediate Post-Upgrade Tasks

The tasks in the following sections should be performed immediately after the upgrade and before you begin testing.

Run Update Statistics

Statistics are used by the optimizer to calculate the costs of physical and logical I/O for the different access methods. Starting with ASE 11.9, statistics are stored in catalog tables, rather than on distribution pages, and are updated when you run update the statistics.

To have the most recent statistics available, run the command:

update statistics table_name
on all tables as soon as possible following upgrade.

Statistics that are copied from the old distribution page by the upgrade are not as accurate as those created by update statistics, because the weights are average, not actual. Running this command places new default statistics on the leading columns of your indexes.

In addition, we strongly recommend that you consider placing statistics on inner columns of composite indexes and on non-indexed join columns as soon as possible.

It is not necessary or recommended that you run update index statistics or update all statistics on tables after upgrade. Building or updating statistics on all columns of a table can be very expensive in terms of I/O and the time it takes to run. Consider whether your databases will grow enough that the time to run statistics jobs becomes an issue.

Test fully before implementing any additional statistics in the production environment to avoid performance issues. Please review the documentation available on the Web in the Migration Resources Page as well as the Performance and Tuning Guide.

Recreate Compiled Objects

Sybase recommends dropping and recreating all compiled objects. This includes stored procedures, triggers, rules and defaults. This will produce new plans consistent with the new version, and will factor in the new statistics.

data_pgs

In ASE 12.0, the data_pgs command includes a new optional parameter, dbid. Any stored procedures that use data_pgs must be re-created.

dbid changes

In ASE 12, the dbids of the following databases start at 31513:

If you drop and recreate these databases after an upgrade, ASE applies these new dbids. The dbids for all other databases are determined in the same way as in earlier versions.

trace 326

trace 326 no longer exists in ASE 11.9.2 or above. In pre-11.9.2 trace 326 used arithmetic averaging to establish the total density value rather than the default geometric averaging. If you used trace 326 in earlier versions of ASE (perhaps in the RUNSERVER file with the -T flag) you now have to manually change the total density value via optdiag.

Database Administration Issues

The following sections describe database administration changes that you need to take into account following an upgrade to ASE 12.0.

Backup Server

This section is to alert you to changes in Backup Server that you may find advantageous. You are not required to make any changes in your current maintenance routines if you do not wish to. To find out more about Backup Server, see the System Administration Guide.

Stripes

In earlier versions, Backup Server could dump to a maximum of 32 stripes, either disk or tape. In 12.0, more stripes are available. The maximum number of stripes it can use is equal to the maximum number of Open Server threads it can create.

Network Connections

The maximum number of network connections a local Backup Server can originate is limited by Open Server to 9118. Therefore, the maximum number of remote stripes that Backup Server can use in a single dump or load operation is 9118.

File Descriptors

Backup Server uses two file descriptors for each stripe apart from the file descriptors associated with the error log file, interfaces file and other system files. However, there is a per-thread limitation imposed by the operating system on the number of file descriptors. Open Server has a limitation of 1280 on the number of file descriptors that an application can keep track of.

Non-Rewinding Tapes

In ASE 12, the tape head is positioned at the end of valid dump data, which saves time when you want to perform multiple dump operations. In previous versions of ASE, Backup Server rewound the tape before each dump operation.

Starting Backup Server

When you start Backup Server, use the -P flag to configure the Open Server for the maximum number of threads it will create. The maximum number of threads equals the maximum number of stripes available. If you have started Backup Server without setting a high enough -P value and you attempt to dump or load a database to a number of stripes that exceeds the number of threads, the dump or load operation will fail.

You must configure the local and remote Backup Servers at startup by providing the appropriate values to the command line options. A remote dump to greater than 25 stripes with the local and remote Backup Servers started with default configuration will fail because the maximum number of network connections that Backup Server can originate (specified by the -N option) is by default 25. The maximum number of server connections into the remote Backup Server (specified by the -C option) is 30.

Dump Format

In Backup Server 12.0 the stripe number must be stored in integer format. Earlier versions of Backup Server stored the stripe number in the HDR1 label in ASCII format in 4 bytes. Thus, earlier Backup Server versions will not be able to load a dump file that uses the version 12.0 dump format. However, Backup Server 12.0 can read and write earlier versions of the dump format. Dump or load operations involving one or more remote servers will abort with an error message in the following instance: if one or more remote servers uses an earlier Backup Server version and the number of stripes to which the database is dumped (or from which it is loaded) is greater than 32.

CIS Proxy Databases

In 12.0, HA creates databases with names like “__pxy”. As a result, you cannot drop proxy databases with similar names ending in “pxy” from Adaptive Servers configured with Sybase Failover in a high availability system. If you attempt to drop these proxy databases, Adaptive Server issues an error message. This is expected behavior.

If you need to drop a database with this type of name, change configuration so your system is not configured for high availability.

ASE Plug-in for Sybase Central

The Adaptive Server plug-in to Sybase Central no longer displays all servers listed in the sql.ini file. Instead, Sybase Central lists only those servers that you connected to earlier, or those servers that are started as Windows NT services. To access a new server for the first time, use the Connect menu option to select a server listed in the sql.ini file.

Platform Specific Issues

AIX

Hanging dumps in ASE 12.0

The load database process can hang on ASE 12.0 for AIX due to an IBM issue with asynchronous I/O. If you are on AIX 4.3.3, you can upgrade to maintenance level 03, which brings your bos.rte.aio file set to version 4.3.3.11. Alternatively, you can apply APAR 1Y07276. If you are on AIX 4.3.2, you need to upgrade to AIX 4.3.3 maintenance level 03 to avoid this issue.

Solaris

Stack size on Solaris 2.6 and Above

CR 220929 causes stack overflow errors on ASE 12.0 running on Solaris 2.6, 2.7, or 2.8. The problem is related to Solaris’s implementation of kernel asynchronous I/O and its use of Light Weight Processes (LWPs). You can work around the problem by increasing stack size to two to four times normal size before upgrade. Some sites have successfully eliminated the problem by applying the most recent patch levels of Solaris 2.8.

Floating Point Problems on Solaris 2.6

Before upgrading to ASE 12.0, you should apply the most recent patch level of your Solaris operating system. ASE 12.0 systems running on Solaris 2.6 have encountered a problem affecting floating point registers. Under conditions of heavy I/O, the operating system can fail to restore floating point registers, leading to a variety of error conditions. While this problem has only been seen on Solaris, 2.6, it is conceivable that it could occur on 2.7 or 2.8.

Sun has recently released a set of patches for Solaris 2.6, 2.7 and 2.8 intended to address this problem. For Sun patch information, see Sun’s Web page at http://sunsolve.sun.com.

HP

Increased cpu consumption

If you are migrating from ASE 11.0.x or 11.5, you may see an increase in cpu consumption. Sybase implemented I/O polling for both network and disk I/O for every Unix platform except HP beginning with SQL Server 11.0. For HP, disk I/O polling was implemented in 11.5 and network polling in 11.9.2. Sybase now uses spare cpu cycles to poll for I/Os waiting to be serviced.

Unless you see a significant impact on user response times or process performance, there is no problem. If, however, it appears that the additional polling is affecting performance, then you may need to adjust the runnable process search count and/or the i/o polling process count configuration parameters. Refer to the Performance and Tuning Guide for a detailed explanation of how to tune these parameters.

Shared Memory Dumps

Shared memory dumps may be requested by Sybase Technical Support if you experience a serious problem that cannot otherwise diagnosed. The Technical Support engineer takes you through the steps to configure and perform the dump. The dump is then loaded onto another server so that Sybase engineers can analyze the state of the original server’s internal data structures at the time of the error.

If a server running on HP is configured for more than 1.75 GB total memory, it is virtually impossible for Sybase to load a shared memory dump. This is due to the way HP handles shared memory and is true for both 32-bit and 64-bit systems. This could severely limit Sybase’s ability to determine the root cause of a problem in some cases.

Asynchronous I/O

If you are running on HP-UX 11 and your host machine has over 2 GB of physical memory, you should apply HP-UX patch PHKL 20515 11.0 for the Async I/O module. This prevents stack traces caused by asynchronous I/O.

This patch has been re-released, as two superseding patches were causing operating system panics.

Summary of Query Processing Changes in 11.9.2 and 12.0 that May Affect Performance

This section contains an overview of changes in query processing, including optimizer costing methods and query resolution. These changes may or may not affect your site, and there is no single method for avoiding or correcting performance issues after an upgrade. This section, as well as the previous section “Result Set Issues”, should provide some guidance in resolving performance issues. For more complete information about tuning your system for improved performance, see the Performance and Tuning Guide.

ASE 11.9.x Optimizer Statistics and Costing Changes

ASE 11.9.x introduced major changes to statistics and costing. Sybase has developed new and more accurate statistics to help you tune your system. It also introduced the optdiag utility, changes to update statistics, and changes to traceon 302 output. If you decide to take advantage of these changes, you will need information on what they do and how to effect them.

You are not required to add or change your current statistics, and doing so requires a commitment of time and effort. You need SA role to do some of this work with statistics. This section provides an overview of these changes. For fuller information, see the Performance and Tuning Guide as well as the Migration Resources Page.

Please test changes to statistics before implementing them. In most cases adding or modifying statistics improves plans, but in some cases it does not. If applications are running well, it is not necessary to add or modify statistics unless you want to see if you can improve plans.

Use optdiag simulate to test changes to the statistics - see TechNote 20472, Using Optdiag Simulate Statistics Mode.

Why Add or Modify Statistics?

You may wish to add modify statistics for the following reasons:

Column and Table/Index Level Statistics

Release 11.9.2 made fundamental changes to how statistics are stored, how they are used and how granular (or accurate) the statistics are. The statistics used by the optimizer are now stored in two system tables, sysstatistics and systabstats.

For a description of systabstats and sysstatistics see section on “Manual Pages for System Tables” in the Reference Manual.

Column Level Statistics

The sysstatistics table contains statistics related to the data in a column. Column level statistics describe the distribution of values in the column (and in the case of multicolumn density values, a set of columns). These are the “distribution” statistics formerly stored in the single distribution page of each index; they include the histogram and the density values. Also stored in sysstatistics is the date and time of the last modification of the column statistics.

Column statistics are now an attribute of a column, not an index. This is a fundamental change to the statistics. Because statistics are now an attribute of a column they can be placed on any column, whether it is part of an index or not.

The number of steps (cells) to use in the histogram can be specified in the create index or update statistics commands.

Table/Index Level Statistics

The systabstats table contains statistics related to objects: the table and index level statistics. Statistics related to tables and indexes are now stored in a single table and new statistics are also available. Table and index statistics are maintained dynamically by ASE. They should not be written directly; they will be quickly overwritten. Table and index statistics cannot be written with optdiag.

The statistics stored in systabstats are dynamic. An in-memory copy is kept and modified as changes occur. This in-memory copy is then flushed to systabstats in a number of ways, such as by housekeeper, update statistics, optdiag, checkpoint, shutdown, and sp_flushstats.

optdiag

Release 11.9.x introduced the new command line utility optdiag. optdiag enables you to read, write and simulate statistics. Working directly with the statistics as stored in sysstatistics and systabstats is difficult because of their datatypes, optdiag makes it practical to read and modify statistics. Also, optdiag allows you to test performance based on simulated statistics.

optdiag output is essential when analyzing optimizer-related problems. It contains all the information about the data set that you need.

See the Performance and Tuning Guide for information on optdiag.

Costing Large I/O

Large I/Os can now be used to access index pages along with data pages. More accurate costing of large I/Os using cluster ratios was added in 11.9.X. These new statistics values for tables and indexes are stored in systabstats.

Cluster ratios are used to determine if large I/O access will be efficient. The lower the cluster ratio, the less likely a large I/O access will be useful. Look under the heading “Derived statistics” in optdiag or trace flag 302 output (Table Information Block and the Index Selection Block) for the cluster ratios.

Aggregate Optimization

Aggregate optimization was introduced in ASE 11.9.x. This allows the optimizer to factor in an index on the column referenced in an aggregate function. Sometimes the optimizer can be overly optimistic in costing this index and may choose it when it is not the best choice. If you notice poor performance because of this (showplan will show the index on the aggregate column being used), you can turn off aggregate optimization with trace flag 370, which will revert the server to a pre-11.9.x handling of aggregates by the optimizer.

Update Statistics

The update statistics command was given some new extensions and functionality. These include the ability to use update statistics to build or update statistics on an individual column, on all columns of an index or indexes or on all columns of a table. On DOL tables update statistics will use isolation level 0 (dirty reads) and avoid locking the table while running.

As in previous versions, running update statistics table_name [index_name] will update the statistics of the leading column (major attribute) of all indexes on the table or of the specified index.

See the Performance and Tuning Guide for information on histogram cells.

Trace Flag 302

Trace flag 302 prints the optimizer’s index costing and selection phase. It has been redesigned to provide information on the new statistics and costing in an easy-to-read format.

This trace flag is well-documented in New Functionality in 11.9.2 and the sections on query tuning in the Performance and Tuning Guide.

Syntax for Trace Flag 302

When using trace flag (302) to see optimizer information, use trace flag 3604 to send output to the client. (You can use trace flag 3605 to send output to the errorlog, but this is not recommended.) The syntax looks like this:

dbcc traceon (3604,302)

Trace flag 302 output has been divided into the following “blocks” of related information:

The following is an example of trace flag 302 output:

*******************************
Beginning selection of qualifying indexes for table 'lineitem',
varno = 0, objectid 240003886.
The table (Datarows) has 600572 rows, 44308 pages,
The table’s Data Page Cluster Ratio 0.999990

Table scan cost is 600572 rows, 44489 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache'(cacheid 0) with 
MRU replacement

Selecting best index for the SEARCH CLAUSE:
lineitem.l_partkey = 2000
lineitem.l_orderkey >10000

Estimated selectivity for l_orderkey,
selectivity = 0.983249, upper limit = 1.000000.

Estimated selectivity for l_partkey,
selectivity = 0.000052, upper limit = 0.052686.

Estimating selectivity of index 'lineitem_lorder', indid 2
scan selectivity 0.983249, filter selectivity 0.
983249590512 rows, 45991 pages, index height 2,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.998609,
Data Page Cluster Ratio 1.000000

The best qualifying index is 'lineitem_pskey' (indid 4)
costing 35 pages, with an estimate of 31 rows to be returned
per scan of the table,
using no index prefetch (size 2K I/O) on leaf pages,
in index cache 'default data cache' (cacheid 0)
with LRU replacement using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0)
with LRU replacement.
Search argument selectivity is 0.000051 *******************************

See the Performance and Tuning Guide for information on reading trace flag 302 output.

ASE 12.0 Query Processing Changes

The following are query processing and optimizer changes introduced in ASE 12.0. These changes are less extensive than those in 11.9.2 and should provide improved performance for most sites.

Predicate Transformation and Factoring

Predicate Factoring provides significant performance improvement in queries with limited access paths, that is, very few possible SARGs, Joins, or or clauses that can be used to qualify rows in a table.

Additional optimization is achieved by generating new search paths based on join conditions, search clauses, and optimizable or clauses.

Predicate factoring helps query optimization by extracting optimizable clauses from predicates linked with or (difficult to optimize) and replacing them with and clauses (easier to optimize) thus providing the optimizer with more usable SARGs. The more SARGs available, the more information the optimizer has and the more likely it is to chose an efficient plan.

Full cartesian joins are avoided for some complex queries.

Example:

select * from lineitem, part 
where ((p_partkey = l_partkey and l_quantity >= 10) 
or (p_partkey = l_partkey and l_quantity <= 20) )
becomes

select * from lineitem, part
where ((p_partkey = l_partkey and l_quantity >= 10) 
or (p_partkey = l_partkey and l_quantity <= 20) ) 
and (p_partkey = l_partkey)
and (l_quantity >= 10 or l_quantity <= 20)

The addition of the conjuncts adds usable SARGs for the optimizer. If new conjuncts (and clauses) added by predicate transformation and factoring are found not to be useful as part of index access strategy (that is, for filtering) they are not used.

Query semantics do not change.The result set is the same. Predicate factoring cannot be turned off. It is completely transparent to the user. It is implemented as a new compiler phase, just before the start of the optimizer.

When it happens, trace flag 302 output shows additional costing blocks and showplan shows additional “Keys are” messages.

50 Tables in a Query

ASE 12.0 now supports up to 50 user tables and 14 worktables. This is up from 16 user tables and 12 worktables in 11.9.2. Bear in mind, however, the limits on number of referential integrity checks remain at 192 and the number of subqueries allowed remains at 16.

ASE 12.0 Optimizer Changes

Increased Optimization Time

Queries with long chains of join keys may require additional time to optimize with Adaptive Server. If the time required to optimize such a query is unacceptable, consider using an abstract query plan for the query.

Like Optimization Enhancements

ASE 12.0 changes the costing for like clauses that are not migrated into SARGs, using a technique of generating more accurate cost estimates for queries that include a leading wildcard in a like clause.

This provides better selectivity estimates, resulting in better query plans. Previously a like clause with a leading wild card was estimated to qualify all rows in the column (a selectivity of 1.0) since there was no way to search for a match. This was not the most accurate way to cost such a clause.

Example:

select ... from part, partsupp, lineitem
where l_partkey = p_partkey 
and l_partkey = ps_partkey 
and p_title = ’%Topographic%’

The like string is compared with histogram cell boundaries.

A match occurs when the like term is found in a cell boundary by means of a pattern match search.

If no pattern match is found the selectivity is assumed to be 1/number of steps in the histogram. If the default of 20 cells is used, selectivity will be 0.05 if there is now pattern match in the histogram boundary values.

If a pattern match is found in the cell boundaries then the selectivity is estimated to be the sum of the weights of all cells with a pattern match.

In either case the resulting selectivity estimate will be more accurate than in previous versions.

This also applies to queries with like clauses of the type like “_abc”, or like “[ ]abc”.



Back to Top
© Copyright 2010, Sybase Inc.