BT_PREFETCH_MAX_MISS Option

0 – 1000

1

Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Use only if instructed to do so by SAP Technical Support. For queries that use HG (High_Group) indexes, SAP IQ prefetches B-tree pages sequentially until it determines that prefetching is no longer useful. For some queries, it might turn off prefetching prematurely. Increasing the value of BT_PREFETCH_MAX_MISS makes it more likely that SAP IQ continues prefetching, but also might increase I/O unnecessarily.

If queries using HG indexes run more slowly than expected, try gradually increasing the value of BT_PREFETCH_MAX_MISS.

Experiment with different settings to find the setting that gives the best performance. For most queries, useful settings are in the range of 1 to 10.