set

set spinlock_aggregation {on | off}

Use the set spinlock_aggregation parameter to determine whether SAP ASE aggregates the spinlock metrics reported in monSpinlockActivity when the result set includes multiple rows with the same value for SpinlockName.

By default, SAP ASE aggregates the values for Grabs, Waits, and Spins for all spinlocks with the same value. Set set spinlock_aggregation to off to configure SAP ASE to return a separate row for each spinlock instance in the monSpinlockActivity table.

This example shows a result set from monSpinlockActivity with set spinlock_aggregation disabled:

1> set spinlock_aggregation off
2> go
1> select * from monSpinlockActivity 
2> where SpinlockName like "default data cache%" 
3> order by Contention
4> go
Grabs                Spins                Waits                OwnerPID
         LastOwnerPID Contention                  InstanceID SpinlockSlotID
         SpinlockName
 -------------------- -------------------- -------------------- -----------
         ------------ --------------------------- ---------- --------------
         ----------------------------------------------------------------------
                37697                  978                    1           0
              1638413                    0.000027          0           2338
         default data cache                 16396                   15     
               2           0
              1638413                    0.000122          0           2340
         default data cache 
                17317                   24                    3           0
              1638413                    0.000173          0           2339
         default data cache
                27533                  629                    5           0
              1638413                    0.000182          0           2341
         default data cache

When you select the number of rows from this instance of monSpinlockActivity:

select count(*) from monSpinlockActivity
-----------
        2384

However, if you enable set spinlock_aggregation and perform the same query:

1> set spinlock_aggregation on
2> go
1> select * from monSpinlockActivity 
2> where SpinlockName like "default data cache%" 
3> order by Contention
4> go

Grabs                Spins                Waits                OwnerPID
         LastOwnerPID Contention                  InstanceID SpinlockSlotID
         SpinlockName
 -------------------- -------------------- -------------------- -----------
         ------------ --------------------------- ---------- --------------
         -------------------------------------------------------------------
                99235                 1646                   11           0
              1769486                    0.000111          0           2338
         default data cache

This instance of monSpinlockActivity now shows much fewer rows:

select count(*) from monSpinlockActivity
-----------
         324

set erase_residual_data {on | off}

SAP ASE version 16.0 introduces the ability to enable the ability to erase residual data.

Using set allows you to enable or disable the removal of residual data based on your needs.

When you enable the option at a session level, residual data is removed from all the page deallocations that occur during that session. This includes page deallocations of tables that have the "erase residual data" option turned OFF explicitly.

This option can be set by any user for the particular session; no special permissions are required.

set statistics query_name_html [queryname | on | off]

set statistics query_name_html helps differentiate or identify files related to the execution of same query.

set lock {wait [numsecs] | nowait | default}

SAP ASE version 16.0 adds the default parameter to the set command, which disables the current session-level lock wait settings, and instead uses the current server-wide lock wait period configuration parameter setting.