Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 393 / 536

CHAPTER 10 Managing User IDs and Permissions
373
For example, you may wish to prevent a single connection from taking too
much of the available memory or CPU resources, so that one connection does
not slow down other users of the database.
Adaptive Server IQ provides a set of database options that the DBA can use to
control resources. These options are called resource governors.
Setting options
You can set database options using the
SET OPTION
statement, which has the
following syntax:
SET [ TEMPORARY ] OPTION
... [
userid. | PUBLIC. ]option-name = [ option-value ]
For reference information about options, see “Database Options” in Adaptive
Server IQ Reference Manual. For information on the
SET OPTION
statement,
see Adaptive Server IQ Reference Manual.
Resources that can be
managed The following options can be used to manage resources. See Chapter 12,
“Managing System Resources” or see the Adaptive Server IQ Reference
Manual for more information on these options.
• AGGREGATION_CUTOFF Sets the precision level at which Adaptive
Server IQ uses a more efficient internal storage type to do calculations on
SUM or AVG numeric expressions.
• CURSOR_WINDOW_ROWS
Defines the number of cursor rows to
buffer.
• LOAD_MEMORY_MB
Sets an upper bound for the amount of heap
memory that subsequent load operations can use.
• MAIN_CACHE_MEMORY_MB Sets the size of the cache for the main
IQ Store.
• MAX_CARTESIAN_RESULT
Limits the number of result rows from a
query containing a cartesian join.
• MAX_IQ_THREADS_PER_CONNECTION Sets the number of
processing threads available to a connection for use in IQ operations.
• TEMP_CACHE_MEMORY_MB
Sets the size of the cache for the IQ
Temporary Store.
• JOIN_OPTIMIZATION Enables optimization of join order. When this
option is on (default), Adaptive Server IQ optimizes the join order to
reduce the size of intermediate results and sorts, and to balance the system
load.
The following options affect the database engine, but have limited impact on
Adaptive Server IQ: