Options for Tuning Resource Use

Tune your resources to create faster queries

• Restricting Concurrent Queries
  Set the -iqgovern switch to specify the number of concurrent queries on a particular server. This is not the same as the number of connections, which is controlled by your license.
• Setting the Number of CPUs Available
  Set the -iqnumbercpus startup switch to specify the number of CPUs available. This parameter overrides the physical number of CPUs for resource planning purposes.
• Limiting Temporary dbspace Use By a Query
  Set the QUERY_TEMP_SPACE_LIMIT to specify the maximum estimated amount of temp space before a query is rejected.
• Limiting Queries by Rows Returned
  Set the value of the QUERY_ROWS_RETURNED_LIMIT option to prevent the optimizer from rejecting queries with large result sets.
• Forcing Cursors to be Non-Scrolling
  Eliminate the temporary store node in queries that return a very large result set to improve performance.
• Limiting the Number of Cursors
  Set the MAX_CURSOR_COUNT option to prevent a single connection from taking too much available memory or CPU resources.
• Limiting the Number of Statements
  Set the MAX_STATEMENT_COUNToption to limit the number of prepared statements for a connection can make.
• Prefetching Cache Pages
  Set the PREFETCH_BUFFER_LIMIT and BT_PREFETCH_MAX_MISS options to control prefetch memory behavior.
• Optimizing for Typical Usage
  Set the USER_RESOURCE_RESERVATION option to adjust memory use for the number of current users.
• Controlling the Number of Prefetched Rows
  Set the PrefetchRows and PrefetchBuffer parameters to improve performance on cursors under certain conditions.