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

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE FUNCTION--+-admin-+--(--"onmode"--,--"C"--,---------->
                     '-task--'                           

                 .-"1"---------------.               
>--+-"start"--,--+-+---------------+-+-----+--)--;-------------><
   |               '-"start_count"-'       |         
   |            .-"1"--------------.       |         
   +-"stop"--,--+-+--------------+-+-------+         
   |              '-"stop_count"-'         |         
   +-"threshold"--,--"thresh_size"---------+         
   +-"duration"--,--"dur_num"--------------+         
   +-"rangesize"--,--"range_size"----------+         
   +-"alice"--,--"alice_mode"--------------+         
   '-"compression"--,--"compression_level"-'         

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

The following commands start 60 B-tree scanner threads:
EXECUTE FUNCTION admin("onmode","C","start","30"); 
EXECUTE FUNCTION admin("onmode","C","start","30");
The following command stops all of these threads:
EXECUTE FUNCTION admin("onmode","C","stop","30000"); 
No error is issued when the stop_count value is greater than the number of running threads.

Copyright© 2019 HCL Technologies Limited