Configuring Swapping Policies

Virtual DataPort provides two ways to avoid memory overflows when dealing with huge datasets:

  • Stop retrieving data from a source when the size of these data that have not been processed, exceeds a certain limit.
  • Enable the swapping mechanism, which stores in secondary storage the intermediate results of the execution of a query, if their size exceeds a certain limit. It also swaps the intermediate results of the sorting operations (queries with an ORDER BY involved).

The section Configuring the Memory Usage and Swapping Policy of the Administration Guide explain these methods in detail and how to change the default values of the settings that control them.

You can also change these values in the following elements, by executing certain VQL statements:

Examples

Example 1: Modifying the memory usage parameters of a database to set the following parameters:

  • The “Maximum size in memory of each node” to 100
  • The “Maximum size of blocks stored in swap” to 150
  • The “Maximum size in memory of each intermediate result” to 50
ALTER DATABASE testing
  MEMORYCONFIG (
    SWAP ON (
      SWAPSIZE 100
      SWAPBLOCKSIZE 150
    )
    MAXRESULTSIZE 50
  )

Example 2: Disabling swapping in a view:

ALTER VIEW V SWAP OFF;

Example 3: Enabling swapping in a view, setting the “Max size” to 100 Mb and the “Result maximum size” to 25 Mb:

ALTER VIEW V SWAP ON SWAPSIZE 100 MAXRESULTSIZE 25;

Example 4: Running a query and disabling swapping. By adding the attribute SWAP to CONTEXT clause of the query, the swapping will be disabled, even if it is enabled in the view. That is because the value of the parameters SWAP and SWAPSIZE of the CONTEXT clause override the settings of the view.

SELECT  CONTEXT ('SWAP' = 'OFF')

Example 5: Running a query with swapping enabled and a swap “Max size” of 100 Mb:

SELECT  CONTEXT ('SWAP' = 'ON', 'SWAPSIZE' = '100' )