Query

instr_unique_sql_count

Parameter description: Specifies the maximum number of unique SQL statements that can be collected in real time.

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.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647. The value 0 indicates that the function of collecting unique SQL information is disabled.

Default value: 200000

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value is too large, the upper limit of the memory occupied by the dbe_perf.statement view increases. If the value is too small, the number of statements recorded in the view may be too small. As a result, no fault locating method is available, affecting the diagnosis of slow SQL statements.

instr_unique_sql_combination_options

Parameter description: Specifies the configuration items of combining unique SQL statements of the same type. The value of this parameter consists of multiple configuration items separated by commas (,). This parameter can be set at the PDB level.

If this feature is enabled, the IDs of unique SQL statements of the same type are normalized, and the generated unique SQL strings are normalized.

Parameter type: string.

Unit: none

Value range: See Table 1.

• When configuring the combination function, set the character string based on Table 1. Use commas (,) to separate multiple configuration items, for example, SET instr_unique_sql_combination_options='in_clause';.
• If this parameter is left blank, this function is disabled, for example, SET instr_unique_sql_combination_options='';.

Default value: "in_clause,forbid_select_for_update". In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is modified, the Unique SQL ID of the merged query statement will be changed, affecting the SQL statements for which SQLpatch has been created.

• The default value is "" during the upgrade and "in_clause,forbid_select_for_update" when a new instance is delivered.
• For values of the bigint, real, float4, blob, numeric, decimal, number, dec, or integer type, if in() contains a single parameter or multiple parameters, two different unique_sql_id values are generated.
• After the forbid_select_for_update option is enabled, the constant n in the for update wait n clause is not normalized. Different constants are recorded as different statements. (The for update wait 0 and for update nowait clauses have the same semantics and are normalized to the same clause. The statement columns in the dbe_perf.statement, gs_asp, and statement_history views record the statement whose execution time is earlier.)
• After instr_unique_sql_combination_options is modified, unique_sql_id generated by the statements related to the modification is different from the original unique_sql_id. The original SQL patch may become invalid. You need to install the SQL patch again based on the new unique_sql_id.

instr_unique_sql_track_type

Parameter description: Specifies how unique SQL statements are recorded. This parameter can be set at the PDB level.

Value type: enumerated type.

Unit: none

Default value: all. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

The default value of the parameter is all. Compared with top, if the parameter is set to all, additional statistics about statements in the stored procedure are recorded, which causes performance loss for the execution of the stored procedure and more severe impact on the performance of statements that are executed quickly, such as simple expressions in the stored procedure.

unique_sql_retention_time

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

Parameter type: integer.

Unit: minute

Value range: 1 to 3650

Default value: 30

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: You are advised not to modify this parameter because this parameter has no function control point in the current version.

Risks and impacts of improper settings: Changing the value of this parameter may cause unexpected results in the database. Retain the default value.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on indicates that the function is enabled.
• off indicates that the function is disabled.

Default value: on

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

Setting suggestion: You are advised to enable the parameter.

Risks and impacts of improper settings: If this parameter is disabled, 80% and 95% SQL response time cannot be recorded. As a result, system performance cannot be directly monitored.

percentile

Parameter description: Specifies the percentage of SQL statements whose response time is to be calculated by the backend calculation thread.

Parameter type: string.

Unit: none

Value range: "80,95"

Default value: "80,95"

Setting method: This is an INTERNAL parameter. Set it based on instructions provided in Table 1.

Setting suggestion: This parameter can only be viewed.

Risks and impacts of improper settings: none

instr_rt_percentile_interval

Parameter description: Specifies the interval at which the background calculation thread calculates the SQL response time.

Parameter type: integer.

Unit: second

Value range: 0 to 3600

Default value: 10

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 60 without a unit, instr_rt_percentile_interval indicates 60s. If the value is 1min, instr_rt_percentile_interval indicates 1 minute. The unit must be s, min, or h, if required.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on indicates that the CPU time consumed during SQL statement execution is captured.
• off indicates that the CPU time consumed during SQL statement execution is not captured.

Default value: on

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

Setting suggestion: You are advised to enable the parameter.

Risks and impacts of improper settings: If this parameter is disabled, the CPU time consumed by SQL execution in the system may not be recorded, which affects slow SQL analysis.

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.

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

Parameter type: string.

Unit: none

Value range: valid path of a directory.

Default value: specified during installation.

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

Setting suggestion: Set this parameter after thorough tests and verification.

Risks and impacts of improper settings: If the value of asp_log_directory in the configuration file is invalid, the database instance cannot be restarted.

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.

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

Parameter type: string.

Unit: none

Value range: valid path of a directory.

Default value: specified during installation.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value of perf_directory in the configuration file is invalid, the database instance cannot be restarted.

enable_stmt_track

Parameter description: Specifies whether to enable Full/Slow SQL capture.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on: Full/Slow SQL capture is enabled.
• off: Full /Slow SQL capture is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

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 faults. 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 all SQL information. The length of the full SQL information is also controlled by the track_activity_query_size parameter, to prevent the query parameter from occupying too much memory when it is a long string or a large object.

Parameter type: Boolean.

Unit: none

Value range:

• 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

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If most SQL statements are long, more memory and disk space are occupied.

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.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647.

Default value: 1000

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: The memory of each occupied slot is about 800 bits. The specific value depends on the statement complexity. In scenarios with heavy service pressure, if the value is too large, the memory usage will increase rapidly. If the value is too small, some statements to be analyzed may be missed.

track_stmt_details_size

Parameter description: Specifies the maximum size of execution events that can be collected by a single statement. The value of this parameter affects the lock information of the details column in the statement_history table. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: byte

Value range: 0 to 100000000

Default value: 4096. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

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.

Parameter type: string.

Unit: none

Value range:

This parameter consists of two parts in the format of 'full sql retention time, slow sql retention time'. The meanings of the two parts are as follows:

• full sql retention time: The retention period of full SQL statements. The value ranges from 0 to 86400. The unit is second.
• slow sql retention time: The retention period of slow SQL statements. The value ranges from 0 to 604800. The unit is second.

Default value: "3600,604800"

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the retention period is too long, a large amount of space may be occupied. If the retention period is too short, the data retention period may be too short. As a result, no fault locating method is available, affecting diagnosis.

track_stmt_stat_level

Parameter description: Determines the level of statement execution tracing. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range:

This parameter consists of two parts in the format of 'full sql stat level, slow sql stat level'. The meanings of the two parts are as follows:

• 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 > Other System Catalogs > STATEMENT_HISTORY > STATEMENT_HISTORY columns" in Developer Guide.

Default value: "OFF,L0". In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the full SQL tracing function is enabled, the performance is affected and a large amount of disk space may be occupied.

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. Each time elimination is triggered, 10% of unique SQL records are randomly eliminated.

Parameter type: Boolean.

Unit: none

Value range:

• on: The automatic elimination function of unique SQL statements is enabled.
• off: The automatic elimination function of unique SQL statements is disabled.

Default value: off

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Some snapshot information comes from unique SQL statements. Therefore, when automatic eviction is enabled, although some disk space can be saved, the performance may be slightly affected for a short period of time when the eviction function is triggered.

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.

Parameter type: string.

Unit: none

Value range:

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'. The meanings of the four parts are as follows:

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

• Full SQL memory size: The maximum memory space reserved for fast SQL statements. The value range is [16,1024], in MB.
• Full SQL disk size: The maximum disk space occupied by reserved fast SQL statements. The value range is [512,1048576], in MB.
• Slow SQL memory size: The maximum memory space reserved for slow SQL statements. The value range is [16,1024], in MB.
• Slow SQL disk size: 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"

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value is too large, a large amount of memory or disk space may be occupied. If the value is too small, some statements to be analyzed may be missed.

track_stmt_flush_mode

Parameter description: Specifies the storage mode of full SQL statements.

Parameter type: string.

Unit: none

Value range:

This parameter consists of two parts in the format of 'full sql flush mode, slow sql flush mode'. The meanings of the two parts are as follows:

• The first part indicates the full SQL tracing mode. The value can be MEMORY or FILE. If this parameter is set to MEMORY, full SQL statements are recorded in the memory. If this parameter is set to FILE, full SQL statements are recorded in disk files.
• The second part indicates the slow SQL tracing mode. In the current version, the value can only be FILE. If this parameter is set to FILE, slow SQL statements are recorded in disk files.

Default value: "FILE,FILE"

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to "MEMORY,FILE", the kernel supports full SQL statements, which occupies certain shared memory. The memory size is specified by the track_stmt_shm_size parameter.

track_stmt_shm_size

Parameter description: Specifies the size of the full SQL shared memory.

Parameter type: integer.

Unit: byte

Value range: 134217728 to 1073741824

Default value: 134217728

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.