Query
instr_unique_sql_count
Parameter description: Specifies the maximum number of Unique SQL records to be collected. The value 0 indicates that the function of collecting Unique SQL information is disabled.
If the value is changed from a larger one to a smaller one, Unique SQL statistics will be reset and re-collected. There is no impact if the value is changed from a smaller one to a larger one.
If the number of unique SQL entries generated in the system is greater than instr_unique_sql_count and the function of automatically eliminating unique SQL entries is enabled, the system automatically eliminates a certain proportion of entries based on the update time of unique SQL statements in descending order. In this way, new unique SQL entries can be collected. If the automatic elimination function is not enabled, new unique SQL information generated by the system will not be collected.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 2147483647
Default value: 100
CAUTION:
- If the automatic elimination function is enabled and this parameter is set to a small value, the system may frequently perform automatic elimination, which may affect the database system performance. Therefore, you are advised to set this parameter to 200000.
- If automatic elimination is enabled and this parameter is set to a large value (for example, 38347922), a large memory problem may occur during the clearance. As a result, the clearance fails.
instr_unique_sql_track_type
Parameter description: Specifies which SQL statements are recorded in Unique SQL.
This parameter is an INTERNAL parameter. Set it based on instructions provided in Table 1.
Value range: enumerated values
top: Only top-level SQL statements are recorded.
Default value: top
unique_sql_retention_time
Parameter description: Specifies the memory cleanup interval for the unique SQL hash table. The default value is 30 minutes.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Setting GUC Parameters.
Value range: an integer ranging from 1 to 3650
Default value: 30min
enable_instr_rt_percentile
Parameter description: Specifies whether to enable the function of calculating the response time of 80% and 95% SQL statements in the system.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the function of calculating the response time of 80% and 95% SQL statements is enabled.
- off indicates that the function of calculating the response time of 80% and 95% SQL statements is disabled.
Default value: on
percentile
Parameter description: Specifies the percentage of SQL statements whose response time is to be calculated by the background calculation thread.
This parameter is an INTERNAL parameter. Set it based on instructions provided in Table 1.
Value range: a string
Default value: 80, 95
instr_rt_percentile_interval
Parameter description: Specifies the interval at which the background calculation thread calculates the SQL response time.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 3600. The unit is s.
Default value: 10s
enable_instr_cpu_timer
Parameter description: Specifies whether to capture the CPU time consumed during SQL statement execution.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates the CPU time consumed during SQL statement execution is captured.
- off indicates the CPU time consumed during SQL statement execution is not captured.
Default value: on
enable_stmt_track
Parameter description: Specifies whether to enable the full/slow SQL statement feature.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on: Full/Slow SQL capture is enabled.
- off: Full /Slow SQL capture is disabled.
Default value: on
track_stmt_parameter
Parameter description: After track_stmt_parameter is enabled, the executed statements recorded in statement_history are not normalized. The complete SQL statement information can be displayed to help the database administrator locate problems. For a simple query, the complete statement information is displayed. For a PBE statement, the complete statement information and information about each variable value are displayed. The format is query string; parameters:$1=value1,$2=value2, ….
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on: The function of displaying complete SQL statement information is enabled.
- off: The function of displaying complete SQL statement information is disabled.
Default value: off
track_stmt_session_slot
Parameter description: Specifies the maximum number of full/slow SQL statements that can be cached in a session. If the number of full/slow SQL statements exceeds this value, new statements will not be traced until the flush thread flushes the cached statements to the disk to reserve idle space.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 2147483647
Default value: 1000
track_stmt_details_size
Parameter description: Specifies the maximum size (in bytes) of execution events that can be collected by a single statement.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 100000000
Default value: 4096
track_stmt_retention_time
Parameter description: Specifies the retention period of full/slow SQL statement records. This parameter is a combination of parameters. This parameter is read every 60 seconds and records exceeding the retention period are deleted. Only the sysadmin user can access this parameter.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: a string
This parameter consists of two parts in the format of 'full sql retention time, slow sql retention time'.
full sql retention time indicates the retention time of full SQL statements. The value ranges from 0 to 86400.
slow sql retention time indicates the retention time of slow SQL statements. The value ranges from 0 to 604800.
Default value: 3600,604800
track_stmt_stat_level
Parameter description: Determines the level of statement execution tracing.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1. The value is case-insensitive.
Value range: a string
This parameter consists of two parts in the format of 'full sql stat level, slow sql stat level'.
The first part indicates the tracing level of full SQL statements. The value can be OFF, L0, L1, or L2.
The second part indicates the tracing level of slow SQL statements. The value can be OFF, L0, L1, or L2.
NOTE: If the full SQL tracing level is not OFF, the current SQL tracing level is the higher level (L2 > L1 > L0) of the full SQL and slow SQL statements. For details about the levels, see Table 1.
Default value: OFF,L0
unique_sql_clean_ratio
Parameter description: Specifies the ratio of the number of unique SQL entries that are automatically eliminated each time to the preset upper limit (instr_unique_sql_count) when the number of unique SQL entries generated in the system is greater than or equal to instr_unique_sql_count.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Double type ranging from 0 to 0.2.
Default value: 0.1
- If this parameter is set to a small value, only a few entries are cleared each time, which may cause frequent clearance. If this parameter is set to a large value, a large number of entries are cleared each time, which may cause frequent insertion. The recommended value is 0.1.
- If unique_sql_clean_ratio is set to 0, automatic elimination is not disabled. Use enable_auto_clean_unique_sql to determine whether to enable automatic elimination. When automatic elimination is enabled and unique_sql_clean_ratio is set to 0, unique_sql_clean_ratio is automatically reset to the default value 0.1.
enable_auto_clean_unique_sql
Parameter description: Specifies whether to enable the automatic elimination function of unique SQL statements when the number of unique SQL statements generated in the system is greater than or equal to the value of instr_unique_sql_count.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
Default value: off
enable_slow_query_log (Discarded)
Parameter description: Specifies whether to write the slow query information to the log file. This parameter is discarded in this version.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on: indicates that slow query information needs to be written into log files.
- off: indicates that slow query information does not need to be written into log files.
Default value: on
query_log_file (Discarded)
Parameter description: If enable_slow_query_log is set to ON, slow query records are written into log files. query_log_file specifies the name of a slow query log file on the server. Only the sysadmin user can access this parameter. Generally, log file names are generated in strftime mode. Therefore, the system time can be used to define log file names, which are implemented using the escape character %. This function has been discarded in this version.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
NOTICE: You are advised to use %-escapes to specify the log file names for efficient management of log files.
Value range: a string
Default value: slow_query_log-%Y-%m-%d_%H%M%S.log