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, the original data in the system will be cleared and re-collected (the standby node does not support this function). There is no impact if the value is changed from a smaller one to a larger one.

When the number of unique SQL records generated in the system (to view the statistics, query dbe_perf.statement or dbe_perf.summary_statement) is greater than the value of instr_unique_sql_count, the extra unique SQL records are not collected.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32-core CPU and 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 3% by enabling or disabling this parameter.

This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 0 to 2147483647

Default value: 200000

instr_unique_sql_track_type

Parameter description: Specifies which SQL statements are recorded in Unique SQL.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Default value: top

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 is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

• on indicates that the function is enabled.
• off indicates that the function 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 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.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32-core CPU and 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 3.5% by enabling or disabling this parameter.

This 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.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32-core CPU and 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 1.2% by enabling or disabling this parameter.

This 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 used to display complete SQL information and is not controlled by the track_activity_query_size parameter.

This 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 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 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. The unit is byte.

Default value: 4096

track_stmt_retention_time

Parameter description: Specifies the retention period of full/slow SQL statement records. This 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 is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Default value: 3600,604800

track_stmt_stat_level

Parameter description: Determines the level of statement execution tracing.

This 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.

If the tracing level of full SQL statements is not OFF, the current SQL statement tracing level is a higher level (L2 > L1 > L0) of full and slow SQL statements. For details about the levels, see "System Catalogs and System Views > System Catalogs > STATEMENT_HISTORY > STATEMENT_HISTORY columns" in the Developer Guide.

Default value: OFF,L0

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

Some snapshot information comes from unique SQL statements. Therefore, when automatic elimination is enabled, if the selected start snapshot and end snapshot exceed the elimination time, the WDR report cannot be generated.

asp_log_directory

Parameter description: Specifies the directory for storing ASP log files on the server when asp_flush_mode is set to all or file. The value can be an absolute path, or relative to the data directory. Only the sysadmin user can access this parameter.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

If the value of asp_log_directory in the configuration file is an invalid path, the database instance cannot be restarted.

• Valid path: Users must have read and write permissions on the path.
• Invalid path: Users do not have read or write permissions on an invalid path.

Value range: a string

Default value: specified during installation

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 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 is a SIGHUP parameter. Set it based on instructions provided in Table 1.

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

query_log_directory (Discarded)

Parameter description: Specifies the directory for storing low query log files when enable_slow_query_log is set to on. Only the sysadmin user can access this parameter. It can be an absolute path or a relative path (relative to the data directory), which has been discarded in this version.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

If the value of query_log_directory in the configuration file is an invalid path, the database instance cannot be restarted.

Valid path: Users have read and write permissions on the path.

Invalid path: You do not have read or write permission on the path.

Value range: a string

Default value: specified during installation

perf_directory

Parameter description: Specifies the directory of the output file of the performance view dotting task. Only the sysadmin user can access this parameter. The value can be an absolute path, or relative to the data directory.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

• Valid path: Users must have read and write permissions on the path.
• Invalid path: Users do not have read or write permissions on an invalid path.

Value range: a string

Default value: specified during installation

unique_sql_retention_time

Parameter description: Specifies the memory cleanup interval for the unique SQL hash table. The default value is 30 minutes.

This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 1 to 3650. The unit is minute.

Default value: 30min

track_stmt_standby_chain_size

Parameter description: Specifies the maximum memory and disk space occupied by fast/slow SQL statement records on the standby node. This is a combination of parameters. Only the SysAdmin user can access the database.

This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: a string

This parameter consists of four parts in the format of 'Full SQL memory size, Full SQL disk size, Slow SQL memory size, Slow SQL disk size'.

Full SQL and slow SQL statement are stored in different locations. Therefore, four additional values are used for control.

• Full SQL memory size indicates the maximum memory space reserved for fast SQL statements. The value range is [16,1024], in MB.
• Full SQL disk size indicates the maximum disk space occupied by reserved fast SQL statements. The value range is [512,1048576], in MB.
• Slow SQL memory size indicates the maximum memory space reserved for slow SQL statements. The value range is [16,1024], in MB.
• Slow SQL disk size indicates the maximum disk space reserved for slow SQL statements. The value range is [512,1048576], in MB.

The memory size cannot be greater than the disk size.

Default value: 32, 1024, 16, 512