Performance variables

The potential advantage of setting a higher value for this registry variable is fewer allocations will be required to reach a desired amount of memory for a buffer pool.

The potential cost of setting a higher value for this registry variable is wasted memory if the buffer pool is altered by a non-multiple of the allocation size. For example, if the value for DB2_ALLOCATION_SIZE is 8 MB and a buffer pool is reduced by 4 MB, this 4 MB will be wasted because an entire 8 MB segment cannot be freed.

Setting DB2_APM_PERFORMANCE to ON also enables the NO PACKAGE LOCK mode. This mode allows the global query cache to operate without the use of package locks, which are internal system locks that protect cached package entries from being removed. The NO PACKAGE LOCK mode might result in somewhat improved performance, but certain database operations are not allowed. These prohibited operations might include: operations that invalidate packages, operations that inoperate packages, and PRECOMPILE, BIND, and REBIND.

The activation of the DB2ASSUMEUPDATE registry variable is effective on the db2start command.

Specifies whether or not backup and load copy images are cached by the operating system. The default behavior is to cache the image file. When DB2_BACKUP_USE_DIO is set to ON, the backup or load copy image file is directly written to disk, bypassing the file cache.

     NO_NT_SCATTER = 1

   db2set DB2BPVARS=F:\vars\bpvars.vars

Scatter-read parameters

The scatter-read parameters are recommended for systems with a large amount of sequential prefetching against the respective type of containers and for which you have already set DB2NTNOCACHE to ON. These parameters, available only on Windows platforms, are NT_SCATTER_DMSFILE, NT_SCATTER_DMSDEVICE, and NT_SCATTER_SMS. Specify theNO_NT_SCATTER parameter to explicitly disallow scatter read for any container. Specific parameters are used to turn scatter read on for all containers of the indicated type. For each of these parameters, the default is zero (or OFF); and the possible values include: zero (or OFF) and 1 (or ON).

DB2_EVALUNCOMMITTED is only applicable when currently committed semantics will not help avoid lock contentions. When this variable is set and currently committed is applicable to a scan, deleted rows will not be skipped and predicate evaluate will not occur on uncommitted data; the currently committed version of the rows and data will be processed instead.

As well, DB2_EVALUNCOMMITTED is applicable only to statements using either Cursor Stability or Read Stability isolation levels. Furthermore, deleted rows are skipped unconditionally on table scan access while deleted keys are not skipped for index scans unless the registry variable DB2_SKIPDELETED is also set.

The activation of the DB2_EVALUNCOMMITTED registry variable is effective on the db2start command. The decision as to whether deferred locking is applicable is made at statement compile or bind time.

• Database version: Db2 V9.1
• RAW device must be used for database containers (container on file systems is not supported)
• Storage subsystem: Shark DS8000® supports all the enhanced I/O performance features. Refer to the Shark DS8000 documentation for setup and prerequisite information.

The default I/O priority settings for HIGH, MEDIUM, and LOW are 3, 8, and 12, respectively; you can use the DB2_IO_PRIORITY_SETTING registry variable to change these settings.

This variable specifies whether the query optimizer uses optimization extensions to help improve query performance. The values specify different optimization extensions. To specify multiple values, use a comma-separated list.

   INDEX IX2:  dept    ASC
   INDEX IX3:  job     ASC

   WHERE
        dept = :hv1 OR
        (job = :hv2 AND
        years >= :hv3)

You can use the OPT_SORTHEAP_EXCEPT_COL value option to override the value of the sortheap database configuration parameter. The override value affects query optimization only and does not determine the amount of actual memory that is available at run time. If the query accesses a column-organized table, this override value is ignored to allow the query compiler to use the current value of the sortheap database configuration parameter.

One usage of the OPT_SORTHEAP_EXCEPT_COL is for shadow tables. Shadow tables facilitate BLU Acceleration® for analytical queries in OLTP environment. Shadow tables are column-organized tables. The requirements for sort heap memory are higher than you would normally have for databases in OLTP environments. To increase the sort heap memory without affecting existing access plans for OLTP queries, add OPT_SORTHEAP_EXCEPT_COL to DB2_EXTENDED_OPTIMIZATION to override the value of the sortheap database configuration parameter.

DB2_EXTENDED_OPTIMIZATION settings might not improve query performance in all environments. You should test to determine individual query performance improvements.

When this variable is set to CONNECTION, a table lock is released for an application until the application either rolls back the transaction or the connection is reset. The table lock continues to be held across commits and application requests to drop the table lock are ignored by the database. The table lock remains allocated to the application. Thus, when the application re-requests the table lock, the lock is already available.

For application workloads that can leverage this optimization, performance should improve. However, the workloads of other application executing concurrently might be impacted. Other applications might get blocked from accessing a given table resulting in poor concurrency. Db2 SQL catalog tables are not impacted by this setting. The CONNECTION setting also includes the behavior described with the ON or TRANSACTION setting.

This registry variable is checked at statement compile or bind time.

When you are using large or huge pages for database memory (DB), dynamic decreasing of database memory is limited, and the db_mem_thresh setting is ignored. The self-tuning memory manager (STMM) does not adjust the overall size of the database area. However, STMM tunes the areas inside the database memory region subject to configuration. For more information, see the database_memory topic in the related links section.

Memory access-intensive applications that use large amounts of virtual memory might obtain performance improvements by using large or huge pages. To enable theDb2 database system to use them, you must first configure the operating system to use large or huge pages.

To enable large pages for agent private memory on 64-bit Db2 for AIX (the DB2_LARGE_PAGE_MEM=PRIVATE setting), you must configure large pages on the operating system and the instance owner must possess the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities.

On Linux, to verify that huge kernel pages are available, issue the following command:

   cat ⁄proc/meminfo

If huge kernel pages are available, the following three lines should be displayed (with different numbers depending on the amount of memory configured on your server):

HugePages_Total:   200
HugePages_Free:    200
Hugepagesize:    16384 kB

If you do not see these lines, or if the HugePages_Total is 0, you must configure the operating system or kernel.

On Windows, the amount of large page memory that is available on the system is less than the total available memory. After the system has been running for some time, memory can become fragmented, and the amount of large page memory decreases. The DB2_ALLOCATION_SIZE registry variable should be set to a high value, such as 256 MB, in order to achieve consistent performance allocating large memory pages on Windows. (Note that DB2_ALLOCATION_SIZE requires you to stop and restart the instance.)

The system monitor heap can become exhausted if an application contains a very high number of statements in a unit of work, or if there are a large number of applications executing concurrently.

• No rollout - if OFF is specified
• Immediate rollout - if IMMEDIATE is specified.
• Rollout with deferred index cleanup - if DEFER is specified

If only nonpartitioned RID indexes exist on the table, deferred index cleanup rollout is supported.

A DB2MEMDISCLAIM setting of YES results in smaller paging space requirements, and possibly less disk activity from paging. A DB2MEMDISCLAIM setting of NO results in larger paging space requirements, and possibly more disk activity from paging. In some situations, such as if paging space is plentiful and real memory is so plentiful that paging never occurs, a setting of NO provides a minor performance improvement.

If this variable is not set, the Db2 database manager calculates values for minfree and maxfree based on the amount of memory on the server. In limited instance_memory environments, the Db2 database manager calculates values for minfree and maxfree based on the instance_memory setting. The setting of this variable has no effect unless the self-tuning memory manager (STMM) is enabled and database_memory is set to AUTOMATIC.

Starting with version 10.5, a 5% extra buffer is added which is included in the values in the preceding table. This extra buffer is to accommodate the volatility of memory demands in a wider range of environments while maintaining the resilience expected of automatically tuned STMM environments. However, the additional 5% buffer is available to newly activating databases to minimize detuning (scaling back) upon activation. If a version 10.5 or later STMM tuner detects the presence of STMM tuners from previous releases (that are competing with them for system memory), then the additional 5% buffer is removed from the calculation for the databases that are running on version 10.5 or later. This removal of the additional 5% buffer, is to avoid biasing memory allocation towards the databases that are running on previous releases (which would tend to have lower free memory targets).

Performance gains might be achieved by reducing the minfree, maxfree settings in an STMM environment. However, care must be taken to ensure that volatility in memory requirements does not result in paging or memory exhaustion.

Changes to this variable take effect immediately for all STMM tuning operations. There is no need to restart the instance or to run the db2set command with the -immediate parameter.

When these variables are set to ON, data is read into and written from the Db2 buffer pools using memory mapped I/O, and subsequently removed from the file system cache. This avoids double-caching of Db2 data. However, the recommended method to bypass the file system cache is to specify the NO FILE SYSTEM CACHING clause at the table space level, and to leave these variables at the default setting of OFF.

When these variables are set to ON, data is read into and written from the Db2 buffer pools using memory mapped I/O, and subsequently removed from the file system cache. This avoids double-caching of Db2 data. However, the recommended method to bypass the file system cache is to specify the NO FILE SYSTEM CACHING clause at the table space level, and to leave these variables at the default setting of OFF.

1. Parallel FCM Buffers: default size is 512 MB on 32-bit platforms, 4.5 GB on 64-bit platforms; override option is FCM:number_of_bytes
2. Fenced Mode Communication: default size is 80 MB on 32-bit platforms, 512 MB on 64-bit platforms; override option is APLD:number_of_bytes
3. Message Query Memory: default size is 4 MB on 32-bit and 64-bit platforms; override option is QUE:<number of bytes>.

db2set DB2NTMEMSIZE=FCM:1073741824;APLD:268435456

db2set DB2NTMEMSIZE=QUE:67108864

In Windows, files are cached when they are opened, which is the default behavior. One MB is reserved from a system pool for every 1 GB in the file. Use this registry variable to override the undocumented 192 MB limit for the cache. When the cache limit is reached, an out-of-resource error is given.

• NORMAL_PRIORITY_CLASS (the default priority class)
• REALTIME_PRIORITY_CLASS (set by using R)
• HIGH_PRIORITY_CLASS (set by using H)

This variable is used in conjunction with individual thread priorities (set using DB2PRIORITIES) to determine the absolute priority of Db2 threads relative to other threads in the system.

For more information, please refer to the SetPriorityClass() API in the Win32 documentation.

Specify DB2NTWORKSET using the syntax DB2NTWORKSET=min, max, where min and max are expressed in megabytes.

You can also use <entry>[;<entry>...] where <entry> =<buffer pool ID>,<number of pages> to temporarily change the size of all or a subset of the buffer pools so that they can start up.

On Linux, in addition to modifying this registry variable, the library, libcap.so.1 is also required.

Setting this variable to YES means that self tuning for database shared memory (activated by setting the database_memory configuration parameter to AUTOMATIC) cannot be enabled.

For HP-UX in a 64-bit environment, in addition to modifying this registry variable, the Db2 instance group must be given the MLOCK privilege. To do this, a user with root access rights performs the following actions:

On AIX systems with POWER7® or newer processors, or any Linux or Windows system, this variable can be set to AUTOMATIC. When the AUTOMATIC option is specified, the Db2 database system automatically determines the hardware topology and creates a resource group for every set of related resources (processors and memory) that are visible in the hardware topology. Depending on the hardware topology, there may be one or more resource groups, each containing one or more resources.

The AUTOMATIC setting enables processor affinity, whereby the Db2 database system assigns engine dispatchable units (EDUs) to specific resource groups and resources. In addition, the AUTOMATIC setting also determines whether to enable memory affinitization, whereby EDUs attempt to allocate local memory during processing, to improve memory affinity. The assignment of EDUs to resources occurs in a circular round robin fashion, and assumes that each resource is equivalent. On systems using POWER7 and newer processors, use of the AUTOMATIC setting is not recommended when the distribution of resources is skewed and/or subject to change, such as in systems with multiple LPARs. On POWER® systems running AIX, resource allocation can be determined by using the lssrad -av command. On POWER systems running Linux, resource allocation can be determined by using the numactl -H command.

This setting is intended for systems that expose multiple resource groups and/or resources in their hardware topology, and can enhance query performance for some workloads. To validate any performance improvements, it is recommended to run a performance analysis of the workload before and after you set the DB2_RESOURCE_POLICY variable to AUTOMATIC.

You can also set the registry variable to indicate the path to a configuration file that defines a policy for binding Db2 EDUs to specific resource groups and resources. This is useful in cases where the desired configuration cannot be obtained using the AUTOMATIC setting.

Sample configuration files:

Example 1: Bind all Db2 processes to either CPU 1 or 3.

<RESOURCE_POLICY>  	
		<GLOBAL_RESOURCE_POLICY>  
			<METHOD>CPU</METHOD> 
			<RESOURCE_BINDING> 
  		<RESOURCE>1</RESOURCE>	
			</RESOURCE_BINDING>
			<RESOURCE_BINDING>
				<RESOURCE>3</RESOURCE>
			</RESOURCE_BINDING>
		</GLOBAL_RESOURCE_POLICY>
	</RESOURCE_POLICY> 

Example 2: (AIX only) Bind Db2 processes to one of the following resource sets: sys/node.03.00000, sys/node.03.00001, sys/node.03.00002, sys/node.03.00003

<RESOURCE_POLICY>
		<GLOBAL_RESOURCE_POLICY> 
			<METHOD>RSET</METHOD> 
			<RESOURCE_BINDING> 
				<RESOURCE>sys/node.03.00000</RESOURCE> 
			</RESOURCE_BINDING> 
			<RESOURCE_BINDING> 
				<RESOURCE>sys/node.03.00001</RESOURCE> 
			</RESOURCE_BINDING> 
			<RESOURCE_BINDING> 
				<RESOURCE>sys/node.03.00002</RESOURCE> 
			</RESOURCE_BINDING> 
			<RESOURCE_BINDING> 
				<RESOURCE>sys/node.03.00003</RESOURCE> 
			</RESOURCE_BINDING> 
		</GLOBAL_RESOURCE_POLICY> 
	</RESOURCE_POLICY> 

Example 3: (Linux only) Bind all memory from bufferpool IDs 2 and 3 which are associated with the SAMPLE database to NUMA node 3. Also use 80 percent of the total database memory for the binding to NUMA node 3 and leave 20 percent to be striped across all nodes for non-bufferpool specific memory.

<RESOURCE_POLICY>
		<DATABASE_RESOURCE_POLICY>
			<DBNAME>sample</DBNAME>
			<METHOD>NODEMASK</METHOD>
			<RESOURCE_BINDING>
				<RESOURCE>3</RESOURCE>
				<DBMEM_PERCENTAGE>80</DBMEM_PERCENTAGE>
				<BUFFERPOOL_BINDING>
					<BUFFERPOOL_ID>2</BUFFERPOOL_ID>
					<BUFFERPOOL_ID>3</BUFFERPOOL_ID>
 			</BUFFERPOOL_BINDING>
			</RESOURCE_BINDING>
		</DATABASE_RESOURCE_POLICY>
	</RESOURCE_POLICY>

Example 4: (For Linux and Windows only) Define two distinct processor sets specified by CPU masks 0x0F and 0xF0. Bind Db2 processes and bufferpool ID 2 to processor set 0x0F and Db2 processes and bufferpool ID 3 to processor set 0xF0. For each processor set, use 50 percent of the total database memory for the binding.

This resource policy is useful when a mapping between processors and NUMA nodes is desired. An example of such a scenario is a system with 8 processors and 2 NUMA nodes where processors 0 to 3 belong to NUMA node 0 and processors 4 to 7 belong to NUMA node 1. This resource policy allows for processor binding while implicitly maintaining memory locality (that is, a hybrid of CPU method and NODEMASK method).

 <RESOURCE_POLICY>
   <DATABASE_RESOURCE_POLICY>
     <DBNAME>sample</DBNAME>
     <METHOD>CPUMASK</METHOD>
     <RESOURCE_BINDING>
       <RESOURCE>0x0F</RESOURCE>
       <DBMEM_PERCENTAGE>50</DBMEM_PERCENTAGE>
       <BUFFERPOOL_BINDING>
         <BUFFERPOOL_ID>2</BUFFERPOOL_ID>
       </BUFFERPOOL_BINDING>
     </RESOURCE_BINDING>
     <RESOURCE_BINDING>
       <RESOURCE>0xF0</RESOURCE>
       <DBMEM_PERCENTAGE>50</DBMEM_PERCENTAGE>
       <BUFFERPOOL_BINDING>
         <BUFFERPOOL_ID>3</BUFFERPOOL_ID>
       </BUFFERPOOL_BINDING>
     </RESOURCE_BINDING>
   </DATABASE_RESOURCE_POLICY>
 </RESOURCE_POLICY>

Example 5: (AIX operating systems only) You can manually enable resource group awareness by specifying resource groups in the resource policy file. Use the RESOURCE_GROUP element to specify the resources that belong to a particular resource group. The defined resource groups do not have to align with NUMA boundaries. The RESOURCE_GROUP column of the ENV_GET_DB2_EDU_SYSTEM_RESOURCES table function identifies the resource group with which an EDU is associated.

<RESOURCE_POLICY>
  <GLOBAL_RESOURCE_POLICY>

    <METHOD>SRAD</METHOD>

    <RESOURCE_GROUP>
      <RESOURCE_GROUP_NAME>TESTGROUP1</RESOURCE_GROUP_NAME>

      <RESOURCE_BINDING>
        <RESOURCE>0</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>1</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>2</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>3</RESOURCE>
      </RESOURCE_BINDING>

    </RESOURCE_GROUP>

    <RESOURCE_GROUP>
      <RESOURCE_GROUP_NAME>TESTGROUP2</RESOURCE_GROUP_NAME>

      <RESOURCE_BINDING>
        <RESOURCE>4</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>5</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>6</RESOURCE>
      </RESOURCE_BINDING>

      <RESOURCE_BINDING>
        <RESOURCE>7</RESOURCE>
      </RESOURCE_BINDING>

    </RESOURCE_GROUP>

  </GLOBAL_RESOURCE_POLICY>
</RESOURCE_POLICY>

Alternatively, you can use the registry variables DB2PRIORITIES and DB2NTPRICLASS to control the operating system scheduling policy and set Db2 agent priorities. However, the specification of a SCHEDULING_POLICY element in the resource policy configuration file provides a single place to specify both the scheduling policy and the associated agent priorities.

Example 1: Selection of the AIX SCHED_FIFO2 scheduling policy with a priority boost for the Db2 log writer and reader processes.

	<RESOURCE_POLICY> 
		<SCHEDULING_POLICY>
			<POLICY_TYPE>SCHED_FIFO2</POLICY_TYPE>
			<PRIORITY_VALUE>60</PRIORITY_VALUE>

			<EDU_PRIORITY>
				<EDU_NAME>db2loggr</EDU_NAME>
				<PRIORITY_VALUE>56</PRIORITY_VALUE>
			</EDU_PRIORITY>

			<EDU_PRIORITY>
				<EDU_NAME>db2loggw</EDU_NAME>
				<PRIORITY_VALUE>56</PRIORITY_VALUE>
			</EDU_PRIORITY>
		</SCHEDULING_POLICY>
	</RESOURCE_POLICY>

Example 2: Replacement for DB2NTPRICLASS=H on Windows.

	<RESOURCE_POLICY> 
		<SCHEDULING_POLICY>
			<POLICY_TYPE>HIGH_PRIORITY_CLASS</POLICY_TYPE>
		</SCHEDULING_POLICY>
	</RESOURCE_POLICY>

This registry variable is not supported when intrapartition parallelism is enabled.

Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

DB2_SKIPDELETED is only applicable when currently committed semantics will not help avoid lock contentions. When this variable is set and currently committed is applicable to a scan, deleted rows will not be skipped; their currently committed version will be processed instead

This registry variable does not impact the behavior of cursors on the Db2 catalog tables.

This registry variable is activated with the db2start command.

This registry variable has no effect if currently committed semantics are being used. That is, even if DB2_SKIPINSERTED is set to OFF and currently committed behavior is enabled, uncommitted inserted rows are still skipped.

DB2_SMP_INDEX_CREATE has an effect only when it is set to a non-zero value. When increasing the number of agents used to scan and sort the index data, it is important to ensure that the database configuration parameters sortheap and sheapthres_shr are set appropriately. The more memory available for sorting (specified by the sheapthres_shr parameter), the less likely sorting the index data requires writing out temporary results to a system temporary tablespace. If the sort does not spill to disk, it is much faster. Furthermore, to ensure that each agent participating in the sort gets an equal amount of memory, the sortheap parameter should be set to a value no greater than sheapthres_shr/n, where n is the number of agents used to scan and sort the index table.

The default setting for this variable is -2, which means that there will not be any unnecessary file system access for any spilled SMS temporary objects whose size is less than or equal to 1 extent * number of containers. Temporary objects that are larger than this are truncated to 0 extent.

When this variable is set to 0, no special threshold handling is done. Instead, once a temporary table is no longer needed, that file is truncated to 0 extent. When the value of this variable is greater than 0, a larger file is maintained. Objects larger than the threshold will be truncated to the threshold size. This reduces some of the system overhead involved in dropping and recreating the file each time a temporary table is used.

If this variable is set to -1, the file is not truncated and the file is allowed to grow indefinitely, restricted only by system resources.

When DB2_SORT_AFTER_TQ=NO, the optimizer tends to sort at the sending end and merge the rows at the receiving end.

When DB2_SORT_AFTER_TQ=YES, the optimizer tends to transmit the rows unsorted, not merge at the receiving end, and sort the rows at the receiving end after receiving all the rows.

Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

The SQL Workspace contains allocations, in the form of sections, for the execution of SQL. Each SQL statement (static or dynamic) that is being executed on behalf of an application must maintain a unique copy of the section in the SQL Workspace for the duration of execution of that statement. Once the execution of the statement is complete, the section becomes inactive and the memory allocations associated with an inactive section can either be freed, or they can remain cached in the SQL Workspace. When a new execution of the same SQL statement occurs from any connection, it may find a cached copy of the section in the SQL Workspace left from a previous execution, thus saving the costs associated with allocating and initializing a new copy of the section. In such a manner, the SQL Workspace contains both active sections, corresponding to currently executing SQL, and cached sections that are not currently executing.

When the DB2_TRUST_MDC_BLOCK_FULL_HINT variable is set, Db2 skips searching for free space in any block that is marked with the Full_Block bit in the composite block index. This Full_Block bit is only a hint as the bit is only cleared when the entire block is removed and when the composite block index is rebuilt by using the REORG command. The trade-off is that some free space might be wasted if deletes are run that partially empties blocks as oppose to fully emptying them with rollout delete. For more information about rollout deletes, see "Rollout deletion" in the "Optimization strategies for MDC tables" topic.

When this variable is enabled, there is no conversion from the external SQLDA format to an internal Db2 format during the binding of SQL and XQuery statements contained within an embedded unfenced stored procedure. This will speed up the processing of the embedded SQL and XQuery statements.

The following data types are not supported in embedded unfenced stored procedures when this variable is enabled:

• SQL_TYP_DATE
• SQL_TYP_TIME
• SQL_TYP_STAMP
• SQL_TYP_CGSTR
• SQL_TYP_BLOB
• SQL_TYP_CLOB
• SQL_TYP_DBCLOB
• SQL_TYP_CSTR
• SQL_TYP_LSTR
• SQL_TYP_BLOB_LOCATOR
• SQL_TYP_CLOB_LOCATOR
• SQL_TYP_DCLOB_LOCATOR
• SQL_TYP_BLOB_FILE
• SQL_TYP_CLOB_FILE
• SQL_TYP_DCLOB_FILE
• SQL_TYP_BLOB_FILE_OBSOLETE
• SQL_TYP_CLOB_FILE_OBSOLETE
• SQL_TYP_DCLOB_FILE_OBSOLETE

If these data types are encountered, an SQLCODE -804, SQLSTATE 07002 error is returned.

To disable fast preallocation, set DB2_USE_FAST_PREALLOCATION to OFF. This might improve runtime performance, at the cost of slower table space creation and database restore times, on some operating systems, especially AIX, when there is a large volume of inserts and selects on same table space. Note that once fast preallocation is disabled, the database has to be restarted.

To enable fast preallocation for logs, set DB2_USE_FAST_LOG_PREALLOCATION to ON.

• The use of database managed (DMS) table spaces that are configured for direct disk (raw) access is not supported.
• The use of system managed (SMS) table spaces that are configured with NO FILESYSTEM CACHING is not supported.
• There may be a performance penalty accessing large object (LOB) data, depending on workload characteristics.
• There may be a performance penalty when restoring backup images or processing load copy files that were created prior to enabling of 4 KB sector support.
• Backup images and load copy files will be slightly larger in size.
• This feature is not supported for pureScale® instances.