onmode and C arguments: Control the B-tree scanner (SQL administration API)
Use the onmode and C arguments with the admin() or task() function to control the B-tree scanner for cleaning indexes of deleted items.
Syntax
| Element | Description | Key Considerations |
|---|---|---|
| alice_mode | The alice mode for the system. | Valid integer values range from 0 (OFF)
to 12. |
| compression_level | For a database server instance, the level at which two partially used index pages are merged. The pages are merged if the data on those pages totals a set level. | Valid values for the level are low, med (medium), high,
and default. The system default value is med. |
| dur_num | The number of seconds that the hot list is valid. | After this number of seconds expires, the hot list will be rebuilt by the next available B-tree scanner thread, even if unprocessed items are on the list. Scanners that are processing requests are not interrupted. |
| range_size | The size of an index before index range cleaning is enabled. | A size of -1 can be used to disable
range scanning. |
| start_count | The number of B-tree scanner threads to start. | If start_count is not specified, 1 more thread is started. A maximum of 32 threads can be started at one time. But, there is no limit on the number of scanner threads run simultaneously. |
| stop_count | The number of B-tree scanner threads to stop. | If stop_count is not specified, a single
thread is stopped. Stopping all index scanners prevents all index
cleaning. If you specify a larger stop_count value than the number of threads than are running, no error is issued, but all scanner threads are stopped. |
| thresh_size | The minimum number of deleted items an index must encounter before an index is placed on the hot list. | After all indexes above the threshold have been cleaned and there is no other work for the B-tree scanner to do, the indexes below the threshold are added to the hot list. |
Usage
The B-tree scanner has statistical information that tracks index efficiency and how much extra work the index places on the server. Based on the amount of extra work the index has accomplished because of committed deleted index items, the B-tree scanner develops an ordered list of indexes that have caused the server to do extra work, called the hot list. The index causing the highest amount of extra work is cleaned first and the rest of the indexes are cleaned in descending order. The DBA can allocate cleaning threads dynamically to configure workloads.
This function is equivalent to the onmode -C command.
Example
EXECUTE FUNCTION admin("onmode","C","start","30");
EXECUTE FUNCTION admin("onmode","C","start","30");EXECUTE FUNCTION admin("onmode","C","stop","30000");