Parameters related to SQL performance diagnostics|V4.5.1|OceanBase Cloud Platform|docs|OceanBase Documentations

This topic describes the OceanBase Cloud Platform (OCP) parameters that are related to SQL performance diagnostics.

SQL performance modules

The following table describes the parameters for SQL performance diagnostics.

Parameter	Default value	Description
ocp.perf.sql-diag.awful-performance-index-used-config	{"enabled":true, "cpuTimeLimitUs":100000, "execPsLimit":5.0, "fullLogicalReadsLimit":10000}	The diagnostic parameter for poor performance despite the use of an index. `enabled`: specifies whether to enable this check item. `execPsLimit`: the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). `fullLogicalReadsLimit`: the number of logical reads. Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Average CPU time > cpuTimeLimitUs && Actual logical reads > fullLogicalReadsLimit && Full table scan is not performed && Execution frequency > $execPsLimit.
ocp.perf.sql-diag.cpu-time-proportion-high-config	{"enabled":true, "cpuTimeLimitUs":10000, "execPsLimit":10.0, "maxCpuTimeLimitUs":30000, "affectedRowsLimit":100, "execLimit":30, "sqlCountLimit":20, "cpuTimePercentLimit":20}	The diagnostic parameter for a high CPU time percentage for execution. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). `maxCpuTimeLimitUs`: the maximum CPU time. `affectedRowsLimit`: the number of rows affected. `execLimit`: the number of executions. `sqlCountLimit`: the number of SQL queries. `cpuTimePercentLimit`: the percentage of CPU time. Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Average CPU time > $cpuTimeLimitUs \|\| Number of rows affected > $affectedRowsLimit \|\| Maximum CPU time > $maxCpuTimeLimitUs && Execution frequency >= $execPsLimit. Diagnostic criteria: During the diagnostic period, if all the following conditions are met, the CPU time percentage is considered high: (1) The percentage of the CPU time of the diagnosed SQL query to the CPU time of the tenant exceeds $cpuTimePercentLimit. (2) The total number of SQL executions by the tenant exceeds $execLimit. (3) The number of unique SQL queries executed by the tenant exceeds $sqlCountLimit.
ocp.perf.sql-diag.database-white-list	oceanbase,information_schema,mysql,__recyclebin,sys,__public	The names of the databases to be skipped in SQL diagnostics.
ocp.perf.sql-diag.execution-spike-config	{"enabled":true, "cpuTimeLimitUs":3000, "execPsLimit":5.0, "pointsLimit":5, "compareMin":20, "avgExecutionMultiply":{"0":6, "1":5, "5":4, "10":3, "50": 2.5, "100": 2, "500": 1.6}, "stdExecutionMultiply":{"0":80, "1":60 , "3": 30,"5":20, "10":15, "20":8, "30":6, "50":5, "80":4}}	The diagnostic parameter for execution spikes. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). `pointsLimit`: the number of points. `compareMin`: the time of comparison (min). `avgExecutionMultiply`: the multiple of the average execution frequency. `stdExecutionMultiply`: the multiple of the standard deviation of the execution frequency. Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Average CPU time >= $cpuTimeLimitUs && Average number of executions per second >= $execPsLimit. Diagnostic criteria: 1. Find the SQL queries that meet the diagnostic conditions within the first $compareMin after the diagnostics start. Calculate the average execution frequency per minute (avg_exec_ps) of the SQL queries in the first $compareMin, the standard deviation of the execution frequency (std_exec_ps) in the first $compareMin, and the ratio of the standard deviation to the average (range_percent). In other words, the following statistics are calculated: Historical average execution frequency (avg_exec_ps) Standard deviation (std_exec_ps) range_percent (std_exec_ps / avg_exec_ps * 100) 2. Then, calculate the average number of executions per second (exec_ps) of the SQL query. If the exec_ps parameter meets all the following conditions at the same time, the SQL query is considered an execution spike: exec_ps>=avg_exec_ps$multiple_value_1 exec_ps >= avg_exec_ps + std_exec_ps * $multiple_value_2 Note The value of the multiple_value_1 parameter varies with that of the avg_exec_ps parameter, depending on the value of the avgExecutionMultiply parameter. When avg_exec_ps > 500, multiple_value_1 = 1.6. Likewise, 100 -> 2, 50 -> 2.5, 10 -> 3, 5 -> 4, and 1 -> 5. The value of the multiple_value_2 parameter varies with that of the range_percent parameter, depending on the value of the stdExecutionMultiply parameter. When range_percent > 80, multiple_value_ 2 = 4. Likewise, 50 -> 5, 30 -> 6, 20 -> 8, 10 -> 15, 5 -> 20, 3 -> 30, 1 -> 60, and 0 -> 80.
ocp.perf.sql-diag.index-diagnoser-config	{"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":60,"maxDiagPeriodSec":1800}	The parameter for SQL index diagnostics. It takes effect after a restart. We recommend that you leave this parameter unchanged.
ocp.perf.sql-diag.ineffective-hint-config	{"enabled":true, "execPsLimit":5.0, "cpuTimeLimitUs":20000}	The diagnostic parameter for inactive hints. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Average CPU time >= $cpuTimeLimitUs && Execution frequency >= $execPsLimit. Diagnostic criteria: If the index specified in a hint is inconsistent with that of the execution plan, the hint is inactive.
ocp.perf.sql-diag.perf-diagnoser-config	{"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":60,"maxDiagPeriodSec":1800}	The parameter for SQL performance diagnostics. It takes effect after a restart. We recommend that you leave this parameter unchanged.
ocp.perf.sql-diag.performance-degradation-after-plan-changed-config	{"enabled":true, "compareMin":5, "cpuTimeLimitMs":1.0, "cpuTimeMultiply":{"0":50, "1":30, "10":10, "100":8, "1000":6}}	The diagnostic parameter for changes in the execution plan and performance degradation. `enabled`: specifies whether to enable this check item. `compareMin`: the time of performance comparison (min). `cpuTimeLimitMs`: the minimum CPU time (ms) that triggers the rule. `cpuTimeMultiply`: the multiple of CPU time. If the current average CPU time is greater than 0 ms, the performance is considered degraded only when the CPU time is increased by at least 50 times after the execution plan is changed. If the current average CPU time is greater than 1 ms, the performance is considered degraded when the CPU time is increased by at least 30 times, and so forth. Diagnostic criteria: The performance is considered degraded if the average CPU time of the SQL query within $compareMin is increased by more times than the specified multiple of CPU time after the execution plan is changed.
ocp.perf.sql-diag.performance-degradation-config	{"enabled":true, "cpuTimeLimitUs":10000, "execPsLimit":5.0, "maxCpuTimeLimitUs":30000, "affectedRowsLimit":100, "execLimit":5, "compareMin":20, "cpuTimeMultiply":{"0":50, "1":30, "10":10, "100":8, "1000":6}}	The diagnostic parameter for performance degradation. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). `maxCpuTimeLimitUs`: the maximum CPU time. `affectedRowsLimit`: the number of rows affected. `execLimit`: the number of executions. `compareMin`: the source time (min) of the baseline data. It is the last $compareMin before the diagnostics. `cpuTimeMultiply`: the multiple of CPU time. If the current CPU time is greater than 0 ms, the performance is considered degraded only when the CPU time is increased by at least 50 times after the execution plan is changed. If the current average CPU time is greater than 1 ms, the performance is considered degraded when the CPU time is increased by at least 30 times, and so forth. Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Average CPU time > $cpuTimeLimitUs \|\| Number of affected rows > $affectedRowsLimit \|\| Maximum CPU time > $maxCpuTimeLimitUs && Execution frequency > $execPsLimit. Diagnostic criteria: A baseline number of executions is taken as the reference for comparison. The baseline number of executions must be greater than $execLimit. Otherwise, it cannot be used as the reference for comparison. After the reference for comparison is determined, the performance is considered degraded when the SQL query under diagnostics meets all the following conditions at the same time: Current average CPU time >= Baseline CPU time * $cpuTimeMultiply. The default value of cpuTimeMultiply is 6. Current execution frequency >= Baseline execution frequency * 0.5.
ocp.perf.sql-diag.query-timeout	30000000	The timeout threshold of SQL diagnostic queries (μs).
ocp.perf.sql-diag.row-lock-contention-high-config	{"enabled":true, "execPsLimit":0, "cpuTimeLimitUs":1000, "elapsedTimeLimitUs":0}	The diagnostic parameter for high row lock contention. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). `elapsedTimeLimitUs`: the average response time (μs). Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Execution frequency > $execPsLimit && Average CPU time > $cpuTimeLimitUs && Average response time > $elapsedTimeLimitUs. Note The elapsedTimeLimitUs parameter is used only when it is configured. Diagnostic criteria: An SQL query is considered to have caused high row lock contention if its type is %select%for%update%.
ocp.perf.sql-diag.table-scan-index-not-exists-config	{"enabled":true, "execPsLimit":1.0, "cpuTimeLimitUs":20000}	The diagnostic parameter for the full-table scan without any indexes available. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: table_scan > 0 in the v$sql_audit view. These queries involve full table scans. Diagnostic criteria: No index is available if the following conditions are met: A single table is scanned and it has no index. Multiple tables are scanned and some tables have no index.
ocp.perf.sql-diag.table-scan-index-not-used-config	{"enabled":true, "execPsLimit":1.0, "cpuTimeLimitUs":2000}	The diagnostic parameter for a full-table scan without using the index. `enabled`: specifies whether to enable this check item. `execPsLimit`: the execution frequency, which is the number of executions per second. `cpuTimeLimitUs`: the average CPU time (μs). Diagnostic objects: SQL queries that meet the following conditions during the diagnostic period: Execution frequency > $execPsLimit && Average CPU time > $cpuTimeLimitUs && Full-table scan is performed. Diagnostic criteria: The index is not used.
ocp.perf.sql.diag-plan-change-config	{"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":300,"maxDiagPeriodSec":1800}	The parameter for scheduling of diagnostic tasks. We recommend that you leave this parameter unchanged.
ocp.perf.sql.max-query-range	24h	The maximum length of time range for querying SQL performance data. Default value: 24h. If the time range of a query you specified on the TopSQL or SlowSQL tab exceeds the value of this parameter, an error is returned when you submit the query.
ocp.perf.sql.plan-hist-level0-granularity	30s	The time interval for OCP to aggregate the performance data of the Level 0 SQL execution plans, or plans for short. By default, OCP collects and aggregates the performance data of plans once every 30s. The performance data is stored in specific tables for use in TopSQL diagnostics. You can increase this value to relieve the storage pressure for the MetaDB and MonitorDB of OCP.
ocp.perf.sql.plan-hist-level0-query-interval	2h	The maximum time range for querying the Level 0 plan performance data. If the specified query time range exceeds this value, OCP queries data tables with larger aggregation time intervals.
ocp.perf.sql.plan-hist-level0-retention	5d	The period for retaining performance data partitions of Level 0 plans. Performance data partitions are created by day. By default, the data is retained for only five days. Partitions that have been retained for more than five days are automatically deleted.
ocp.perf.sql.prepare-partition-ahead	8	The number of days for creating performance data partitions before the diagnostics. You need to specify the period in days.
ocp.perf.sql.query-timeout	30000000	The timeout threshold for SQL performance data queries (μs).
ocp.perf.sql.sql-hist-level0-granularity	30s	The time interval for OCP to aggregate the performance data of the Level 0 SQL queries. Note To improve the performance of monitoring metric collection, OCP collects and aggregates SQL performance data at different time intervals for TopSQL diagnostics. The data is divided into three levels and is collected and stored in table partitions of the corresponding levels by day. The retention period for performance data partitions is specified by a fixed parameter. For the same type of performance data, a larger time interval requires less storage space.
ocp.perf.sql.sql-hist-level0-query-interval	2h	The maximum time range for querying the Level 0 SQL performance data. If the time range you specified on the TopSQL tab exceeds this value, OCP queries the Level 1 SQL performance data tables.
ocp.perf.sql.sql-hist-level0-retention	2d	The retention period for partitions of Level 0 SQL performance data. Partitions for Level 0 SQL performance data are created by day. By default, the MetaDB and MonitorDB of OCP only retain partitions created in the last two days.
ocp.perf.sql.sql-hist-level1-granularity	2m	The time interval for OCP to aggregate the performance data of the Level 1 SQL queries. By default, OCP aggregates SQL performance data once every 2 minutes and writes the data into Level 2 SQL performance data tables.
ocp.perf.sql.sql-hist-level1-query-interval	12h	The maximum time interval for querying the Level 1 SQL performance data. If the time range you specified on the TopSQL tab exceeds this value, OCP queries the Level 2 SQL performance data tables.
ocp.perf.sql.sql-hist-level1-retention	8d	The retention period for partitions of Level 1 SQL performance data.
ocp.perf.sql.sql-hist-level2-granularity	10m	The time interval for OCP to aggregate the performance data of the Level 2 SQL queries. By default, OCP aggregates SQL performance data once every 10 minutes and writes the data into Level 2 SQL performance data tables.
ocp.perf.sql.sql-hist-level2-query-interval	48h	The maximum time interval for querying the Level 2 SQL performance data. Regardless of whether the time range you specified exceeds this value, OCP queries the Level 2 SQL performance data tables.
ocp.perf.sql.sql-hist-level2-retention	15d	The retention period for partitions of Level 2 SQL performance data.

SQL performance modules

Note

Note

Note