Hints for Setting Slow SQL Control Rules

Description

Users can set the execution time, maximum execution time, and maximum IOPS for SQL statements marked as slow SQL statements.

Syntax

1

wlmrule("time_limit,max_execute_time,max_iops")

This parameter is valid only for SELECT statements executed by non-sysadmin or non-monitoradmin users when enable_thread_pool is set to on.

• time_limit: execution time of an SQL statement marked as a slow SQL statement. The value ranges from 0 to INT_MAX.
• max_execute_time: maximum execution time of an SQL statement. If the execution time exceeds the value of this parameter, the SQL statement is forcibly canceled and exits. The value ranges from 0 to INT_MAX. If the value of max_execute_time is less than or equal to the value of time_limit, the rule does not take effect.
• max_iops: maximum IOPS of an SQL statement marked as a slow SQL statement. This parameter is valid only when use_workload_manager is set to on. The IOPS limit applies logical I/O control. For details about the definition of IOPS, see the definition of io_control_unit. The value can be Low, Medium, High, None, or 0 to INT_MAX.

Examples

select /*+ wlmrule("100,500,1") */ * from t2 order by b limit 1;

It indicates that the execution time of the current statement marked as a slow SQL statement is 100 ms, the maximum execution time is 500 ms, and the maximum IOPS is 1.