SAP Adaptive Server Enterprise 16 New Features - 20 May 2015 04:05

0. Contents

• 1. Speed enhancements
• 1.1. Index compression
• 1.2. Sort Operator Performance Improvement
• 1.3. Hash Join Operator Performance Improvement
• 1.4. Query Plan Optimization with Star Joins
• 1.5. Dynamic Thread Assignment
• 1.6. Load Performance Enhancements for SAP Business Warehouse
• 2. Scale enhancements
• 2.1. Run-time Logging Enhancements.
• 2.2. Metadata Cache and Latch Enhancements
• 2.3. Lock Management Enhancements
• 2.4. Partition-level Locking
• 2.5. Relaxed Query Limits
• 3. Security enhancements
• 3.1. Full-Database Encryption
• 3.2. Residual Data Removal
• 3.3. Full-text Auditing
• 3.4. Auditing for Authorization Checks Inside Stored Procedures
• 4. Simplicity enhancements
• 4.1. Multi-trigger Support
• 4.2. Integrated Disaster and Recovery Support with Synchronous Replication for SAP Business Suite
• 4.3. ASE to HANA Data Access
• 4.4. Support “create or replace” command for database objects
• 4.5. Enhanced SAP Control Center Support
• 4.5.1 Compression Advisor Utility
• 4.5.2 Create or replace command
• 4.5.3 Partition-level locking
• 4.5.4 Index compression
• 4.5.5 Error log enhancements
• 4.5.6 Server configuration alert
• 4.5.7 Management of Large scale deploymen
• 4.5.8 Secure Store
• 4.5.9 Data Cache Spinlock Contention Monitoring
• 4.6 Configuration History Tracking
• 4.7 Cyclic Redundancy Checks for Dump Databases
• 4.8 Calculating Transaction Log Growth Rate
• 4.9 Monitoring Threshold-based Events
• 4.10 Statistics Stickiness Enhancements
• 4.11 ddlgen
• 4.12 sybmigrate
• 4.13 sybrestore
• 4.14 dbisql
• 4.15 Query Plan and Execution Statistics in HTML
• 4.16 SAP JVM Support

1. Speed enhancements

1.1. Index compression

Page compression is now supported for indicies

create table a (b int)
sp_configure 'enable compression',1
create index a1 on a(b) with index_compression = page
sp_help 'a' shows
a1                    b                  
	 nonclustered, compressed, contain compressed data 
create index a2
    on a(b) 
    local index ip1 with index_compression = PAGE, 
    ip2 with index_compression = PAGE,ip3 

Session options are: set compression default|ON|OFF

This command affects only leaf rows that are built for compressed indexes after the command is executed.

create table b (c int) with index_compression = PAGE|NONE

alter table order_line set index_compress = PAGE|NONE

alter table sales modify partition Y2009 set index_compression = PAGE

alter index a.b set index_compression = PAGE|NONE

1.2. Sort Operator Performance Improvement

This applies to a the parallel query where:

• A SORT operator appears on top of the Exchange operator
• Exchange operator must be a Replicated Exchange with a single producer and multiple consumers.

SAP ASE version 16.0 and later moves the build part of the SORT operator below the Exchange Operator and keeps the sort-table reading part above the Exchange

The benefits of the new parallel SORT operator are

• Splits the SORT operator into two operators: SORTBUILD and GETSORTED. A single sort table built by the SORTBUILD is used by multiple instances of GETSORTED for a parallel reading of sorted rows, reducing the I/Os. The DEFAULT SORT operator plan creates multiple copies of SortTables.
• Sends a single row containing meta data from the SORTBUILD operator to multiple instances of GETSORTED operator, providing a reference for accessing the sort-table directly, which avoids copying and propagating many rows via Exchange.

The parallel SORT operator includes these restrictions:

• Does not support Repartitioned Exchange. However, SORT operators that occur above Repartitioned Exchange use the DEFAULT sort in the query execution plan.
• Although abstract plans do not explicitly support forcing GETSORTED and SORTBUILD, you can force these plans by making sure the SORT operator appears above the Replicated Exchange.
• SAP ASE implicitly disables dynamic thread assignment (DTA) for the query execution plan that contains GETSORTED and SORTBUILD operators.

1.3. Hash Join Operator Performance Improvement

SAP ASE 16.0 replaces the HASH JOIN operator with the HASH PROBE and the HASH BUILD operators, and includes the replicated EXCHANGE operator between these operators. The HASH BUILD operator builds the hash table and the HASH PROBE operator reads the inner stream and probes the hash table to find matching rows (in earlier releases of SAP ASE, the HASH JOIN operator performed both these steps).

A single worker thread executes the HASH BUILD operator, building a single hash table. The query engine passes this hash table through memory pipes to all producers executing the HASH PROBE operator. These producers share this hash table and probe it for matches to the joining columns in their inner streams. Earlier releases of SAP ASE required multiple producers to execute the HASH JOIN operator.

A query plan must include these attributes for the query processor to use the HASH JOIN improvements:

• The query processor executes the HASH JOIN operator in parallel
• The left child Operator of the HASH JOIN Operator is a Replicated EXCHANGE Operator with a single Producer

1.4. Query Plan Optimization with Star Joins

To improve performance for star join query evaluation these changes have been made in version 16.0:

• For parallel plans, the bloom filter probe moves below the Xchg operator.
• A new star join hint uses abstract plan to help the query processor better detect star join queries.
• he final plan of any star join is now forced to use bloom filter under the use fact_table hint.
• Hash joins and a join orders are forced based on the selectivities between fact and dimension tables for the star join query under the use fact_table hint. The query processor adjusts the query plan to provide the greatest benefit from the bloom filter.

The query optimizer uses star joins when appropriate. However, SAP does recommend that you enable the set join_bloom_filter option and parallel query processing when using star joins.

SAP ASE version 16.0 introduces the use fact_table abstract plan hint, which specifies the central fact table in a star join query, and triggers special query plan optimization strategies for the query.

PLAN '(use fact_table fact_table_name_or_alias_name)'

The use fact_table abstract plan hint allows the query processor to chose a parallel hash join plan for the star join query. Parallel plans enable the query processor to push bloom filter probings (which allow for faster joins between dimension and fact tables) below the EXCHANGE operator, further reducing the number of qualifying rows from fact tables.

1.5. Dynamic Thread Assignment

SAP ASE applies dynamic thread assignment to parallel lava query plans that are generated for select queries to use fewer resources

The following commands continue to use static thread assignment:

select into
• reorg commands that include a data copy
• alter table commands that include a data copy
• create index

Dynamic thread assignment improves performance by:

• Executing query plans in parallel with fewer threads (static thread assignment requires serial query execution).
• Executing dynamic load balancing between worker threads: When there are fewer threads executing the query plan than there are work units, the threads that execute the smallest work units complete their task more quickly, freeing themselves to execute the remaining available work units while the threads executing larger work units complete their tasks.
• Using existing semantic partitioning in joins more effectively. Dynamic thread assignment allows a partition-to-partition join when joining two tables that are already partitioned on the joining column, as opposed to static thread assignment, which joins all the partitions of the outer table to all of the partitions of the inner table in one operation. Dynamic thread assignment allows a single worker thread to join the first partition of the outer table to the first partition of the inner table, then reexecutes the query plan fragment to join the second partition, and so on.

1.6. Load Performance Enhancements for SAP Business Warehouse

2 enhancements improve load times for SAP Business Warehouse

• Improves the performance of data insertion and index maintenance for merge into commands, leveraging both bulk inserts and parallel index updates
• Automatically triggers the use of the bulk insert mode at runtime based on insert/load performance statistics.

2. Scale enhancements

2.1. Run-time Logging Enhancements.

ASE version 16.0 and later partitions each user log cache into a number of smaller blocks, each the size of the server's logical page size. SAP ASE version 16.0 adds log records directly to the current active block within the user log cache (the same manner that earlier releases add log records directly to the user log cache). When concurrent open transactions make changes to the same data page, instead of moving log records directly from the user log cache to syslogs, SAP ASE adds (or links) the current block within the user log cache to the end of the global queue; it can later transfer the log records from the global queue to syslogs. This enables SAP ASE to efficiently batch additions to syslogs, improving the performance of run-time logging, regardless of issues associated with datarow-locked tables.

After the current block is added to the global queue, a new free block within the user log cache becomes the current block, and continues to accept log records from the transaction.

Use the "user log cache queue size" configuration parameter to enable and disable this functionality.

• 1 – SAP ASE uses the queuing strategy (the default)
• 0 – SAP ASE use the pre-16.0 version behavior

2.2. Metadata Cache and Latch Enhancements

ASE 16 reduces CPU utilization for latch conflicts with very high transaction rates

There is decreased contention:

• On internal structures during very high transaction rate cross-database queries and transactions, including contention on locks, latches, and data caches due to implicit or explicit cross-database references
• During high transaction-rate operations on tables with a large number of partitions
• On internal structures during very high rates of create and drop table, including decreased lock and latch contention on some system tables

The metadata and latch management enhancements are enabled by default, and you need not to perform any configuration to enable these enhancements.

ASE 16 allows setting exp_row_size for tables with only fixed length columns to reduce contention on a single page

ASE 16 increased the default amount of procedure cache used for Engine Local Cache(ELC) from 25% to 50%

This is controlled by new parameter "engine local cache percent"

Additional parameters are "enable large chunk elc" (replaces traceflag 758) and "large allocation auto tune" (replaces traceflag 753)

Defaults are static parameter "enable large chunk elc" enabled, static parameter "large allocation auto tune" enabled

2.3. Lock Management Enhancements

Lock management improvements are:

• Engine lock transfer – Improves the transfers of locks between global pool and engine local caches.
• Engine lock caching – Optimizes the number of locks an engine can cache locally by:
  • Increasing the default engine local cache size
  • Increasing the transfer size of locks from global to local caches.
  • Improves lock transfers between local and global by using of blocks of locks instead of individual locks
  • Draining or reclaiming locks from local to global caches less frequently
• LOCK_VERIFY operations – Optimizes LOCK_VERIFY operations to deploy an optimistic approach when possible and avoid taking locks.
• Lock promotion - Tracks repeatedly failed lock promotion attempts, and disables lock promotion for the DML statement incurring the failed lock promotion after a number of attempts.
• Log semaphore locks - Avoids taking locks for log semaphores since only one lock can be held by the semawait.
• Deadlock checking - Ensures that specifying a small value for the deadlock checking period never results in a value less than 1, and that deadlock checks are performed on a dedicated service thread
• Hot DOL tables - Allows DOL tables to be hot tables, which alleviates high table lock contention

2.4. Partition-level Locking

Locks now indentified by database,object and parttion, -1 = all partitions

Enable with sp_chgattribute object, 'ptn_locking', 1

sp_lock,sp_familylock,monLocks,monDeadLock now include partitionid

Set partition lock promotion thresholds with sp_setpglockpromote_ptn,sp_setrowlockpromote_ptn,sp_ dropglockpromote_ptn,sp_droprowlockpromote_ptn

Partition level operations now only block operations on the same partition, other partitions are no affectedL

• alter table … split partition,merge partition,move partition,drop partition
• truncate partition
• dbcc checkindex,checktable,tablealloc,indexalloc

alter table x [merge|drop|move|split] partition...with online

truncate table x partition y with online

For partition-level online operations, such as splitting a partition or moving a partition, the table must have a local unique index.

The move command is allowed for concurrent DMLs to all partitions in the table, including ones being operated by split.

With a global index:

• Concurrent DML access to a table does not use global index while a partition-level operation is in progress on the table. SAP ASE uses alternate local index scan or table scan for concurrent DML scans.
• Multiple partition-level operations on different partitions can operate concurrently. Each one does not perform global index rebuild. Only the last committed partition-level operation performs global index rebuild.
• The last partition-level operation leading to abort may result in global index marked as suspect. The global index in such case has to be explicitly rebuilt.

Schema locks allow enhanced partition-level operations to update table schema or metadata by achieving isolation from concurrent operations.

The new schema locks are:

• Shared schema lock – indicates that a task is using the current schema of the table for query execution. Scans and DMLs acquire this lock before starting the execution of the query.
• Exclusive schema lock – indicates that a task is changing the schema of the table. The partition-level operation acquires this lock to update the schema of the table.

2.5. Relaxed Query Limits

The number of tables in a select query has been increased from 50 to 250

The number of subqueries in a select query has been increased from 50 to 250

The maximum number of columns allowed in an order by clause has been increased from 31 to 400

3.1. Full-Database Encryption

First we need to create a database encryption key (DEK) in the master database which is used to encrypt the database

To create a database encryption key (DEK):

• sp_configure "enable encrypted columns",1
• Create a master key and optionally a dual master key
• create encryption key master with passwd "password"
• set encryption passwd 'password' for key master
• Create the database encryption key
• create encryption key dbkey for database encryption

Options for creating a database encryption key are:

create encryption key <name> [ for <algorithm>] for database encryption [with {[master key] [key_length 256] [init_vector random] [[no] dual_control]}

Currently algorithm must be AES and key_length must be 256

init_vector must be randon for full database encryption

Encrpytion key can also be encrypted for dual control

The way the encryption key is protected and the owner can be changed with "alter encryption key"

You must backup the Database Encryption Key,master or dual master key and encrypted database

E.g. use ddlgen to generate the sql for creating the keys

To load an encrypted database restore the master key and DEK, create the database for encryption with the same DEK as the original database

load database with verify only = full is not supported as the backup server cannot decrypt

You cannot mount/unmounted encrypted database

Keys can be dropped via "drop encryption key"

create database <name> encrypt with <keyname>

You cannot encrypt an in-memory database

An existing database can be encrypted via:

alter database <name> encrypt with <keyname> [parallel N]

alter database <name> resume encryption [parallel N]

alter database <name> suspend encryption

To check if a database is encrypted as well as progress either use sp_helpdb or

• select dbencryption_status(“status”, db_id(“existdb”))
• select dbencryption_status(“progress”, db_id(“existdb”))

3.2. Residual Data Removal

Databases can be marked as sensitive and have residual data removed (zero-ed out)

Residual data cannot be removed from system databases e.g. master,sybsystemdb,sybsystemprocs

sp_dboption dbname, "erase residual data", true # Database level
create table a (b int) with "erase residual data" {on | off} # Table level - overrides database level
alter table a set "erase residual data" {on | off} # Table level
set erase_residual_data {on | off} # Session level - overrides database and table level settings 

3.3. Full-text Auditing

Full Text Auditing prints parameter names and values with sensitive parameters masked when DML Auditing is enabled

Full-Text Auditing is recorded for select,insert,update,delete,select into

ASE-16_0/bin/auditinit
Restart ASE
sp_configure 'auditing',1 
sp_audit "update", "all", "a", "on"
insert into a values(1)
update a set b=2 where b=1
use sybsecurity
select extrainfo from sysaudits_01
sa_role sso_role oper_role sybase_ts_role mon_role; update a set b=2 where b=1;
	  ; ; ; ; sa/ase;

See also Security Administration Guide

3.4. Auditing for Authorization Checks Inside Stored Procedures

The audit option sproc_auth enables auditing for authorization checks that are performed inside system stored procedures.

The audit event 80 is audited when the audit option security is enabled, or when the audit option sproc_auth is enabled.

The audit event 146 is only audited when the option sproc_auth is enabled.

4. Simplicity enhancements

4.1. Multi-trigger Support

Multiple triggers can be created and the order in which they fire can be controlled

Up to 50 triggers for each command (insert,update,delete) can be created and the new "order" parameter specified.

Multiple triggers can be created without an "order" clause

create or replace trigger [owner].trigger_name on [owner].tablename for 
[insert|delete|update] 
[order <integer>]
as <sql_statement>

Triggers created without an order have an order of 0 and fire after triggers defined with an order

The set of triggers created without an order fire as a set in a undefined order

The "order" clause cannot be used with "instead of" triggers

Attempting to create a duplicate "order" number is an error

To change the "order" number for a trigger recreate the trigger - potentially via "create or replace"

The "merge" statement fires triggers in a special order (insert,update,delete) which overrides the "order" number

Within each operation e.g. "insert" the triggers are fired in "order" number sequence

A rollback statement in a trigger rolls back the previous triggers and does not fire any more triggers

Multiple triggers can be enabled/disabled via alter table tablename [disable|enable] trigger triggername

Global variable @@triggername contains the name of the currently executed or last executed trigger

4.2. Integrated Disaster and Recovery Support with Synchronous Replication for SAP Business Suite

SAP ASE 16 with SAP Replication Server 15.7 SP200 adds several enhancements

• Synchronous replication for "zero data loss" - transactions are also written to the replication server stable queue before being committed
• A new DR Agent manages the complete failover/switching of active systems
• There are 3 modes of synchronous replication - Sync, Near-Sync and Async

4.3. ASE to HANA CIS Access

ASE 16 adds a native ODBC interface to CIS which allows direct connection to HANA

The SAP HANA Client Package must be installed on the same machine as ASE

In Windows use the "HDBODBC" ODBC driver

Also add the HANA Server to the interfaces file

UNIX:
HANA_DB
       query tcp ether 157.133.66.75 1870 libodbcHDB.so 
Windows:
[HANA_DB]
Query=NLWNSCK,157.133.66.75,1870,HDBODBC 

SAP ASE uses a Pluggable Component Interface (PCI) Bridge which implements on-demand software dispatching, to load shared objects when it invokes a target function.

ODBC Driver Manager is typically bootstrapped from the pluggable component adapter for ODBC (PCA/OBDC), which is configured with PCI Bridge.

PCA/ODBC acts as a broker, managing service requests between the SAP ASE and the ODBC Driver Manager.

PCA/ODBC forwards and controls requests in both directions—from the SAP ASE to ODBC Driver Manager, and vice versa.

SAP ASE requires the sybpcidb database when you enable PCI Bridge. The sybpcidb contains all configuration data for PCI Bridge and its associated PCAs, such as PCA/ODBC.

• Create the sybpcidb database using srvbuild,sybconfig or by running ${SYBASE}/${SYBASE_ASE}/scripts/installpcidb
• sp_configure "enable pci",1
• Restart ASE
• Use sp_odbcconfig to config parameters
  • pca_odbc_load_dir - UNIX only,array containing the location of ODBC Drivers sp_odbcconfig 'add/delete', 'pca_odbc_load_dir', 'path_to_driver_location'
  • Restart ASE after making changes to pca_odbc_load_dir
  • pca_odbc_root_dir - UNIX only, folder contains odbc.ini file, default $SYBASE, sp_odbcconfig 'update', 'pca_odbc_root_dir', 'old_path', 'new_path'
  • Restart ASE if the ODBC Driver is already loaded under the control of PCA/ODBC
  • pca_odbc_dsn_enabled – enables or disables the DSN mode for the PCA/ODBC subsystem.
  • 0 – disables DSN (the default). SAP ASE automatically gathers DSN information from the SAP directory control layer (DCL) from the interfaces file.
  • 1 – enables DSN. SAP ASE consults the DSN records stored under odbc.ini.
  • UNIX only, add $SYBASE/DataAccess64/ODBC/dm/lib64 to the LD_LIBRARY_PATH.
• Add the server using server class HANAODBC - sp_addserver logical_server_name, HANAODBC, ODBC_DSN_name
• Use sp_addexternlogin to map the SAP ASE login account and password.
• sp_addexternlogin hana_server, sa, HANA_login, HANA_login_password

Data type mapping are listed at Datatype Mapping Between SAP ASE and HANA

Restrictions are listed at Restrictions

4.4. Support “create or replace” command for database objects

create or replace has been added to the following commands

• create default
• create function
• create function (SQLJ)
• create procedure
• create procedure (SQLJ)
• create rule
• create trigger
• create view

create or replace is only supported for objects which do not contain data

When an object is replaced, SAP ASE replaces its definition in the following system tables: sysprocedures, syscomments, sysdepends, and syscolumns. Some fields in the sysobjects table are also updated. The query tree for the object is normalized before being replaced in sysprocedures.

With granular permissions enabled or disabled, you must be the object owner to replace a compiled object. You cannot replace a compiled object by impersonating the object owner through an alias or setuser. However, if you are the owner through set proxy, you can replace a compiled object.

Stored procedures used in install scripts have been changed to use create or replace.

Display  a summary the log growth for the transaction log over a 24 hour period, calculating the growth in one-hour intervals:

sp_logging_rate 'sum', '1,00:00:00', '01:00:00'
=========================
Total Summary Information
=========================
Transaction Log Growth Rate      Min GB/h        Max GB/h        Avg GB/h
---------------------------   --------------  --------------  --------------
                                  0.000000        1.970084        1.566053