Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 470 / 536

Options for tuning resource use
450
If scrolling cursors are never used in your application, you should make this a
permanent
PUBLIC
option. It will use less memory and make a big
improvement in query performance.
Limiting the number of cursors
The
MAX_CURSOR_COUNT
option specifies a resource governor to limit the
maximum number of cursors that a connection can use at once. The default is
50. Setting this option to 0 allows an unlimited number of cursors.
Limiting the number of statements
The
MAX_STATEMENT_COUNT
option specifies a resource governor to limit
the maximum number of prepared statements that a connection can use at once.
Lowering a connection’s priority
When you set the
BACKGROUND_PRIORITY
option to ON, requests on the
current connection have minimal impact on the performance of other
connections. This option allows tasks for which responsiveness is critical to
coexist with other tasks for which performance is not as important.
Prefetching cache pages
The
SET
option
PREFETCH_BUFFER_LIMIT
defines the number of cache
pages available to Adaptive Server IQ for use in prefetching (the read ahead of
database pages). This option has a default value of 20, which can degrade
multi-user performance. Sybase recommends that you set this option to 0 for
multi-user applications. For more information, see the Adaptive Server IQ
Reference Manual.