SPM

spm_enable_plan_capture

Parameter description: Specifies the SPM plan capture mode. This parameter can be set at the PDB level.

Value type: enumerated type.

Unit: none

Value range:

• off: indicates that the plan capture function is disabled.
• auto: indicates that the automatic plan capture function is enabled. In this mode, the prerequisite for capturing an SQL plan is that the SQL plan is executed twice or more.
• manual: indicates that the manual plan capture function is enabled. In this mode, the prerequisite for capturing an SQL plan does not need to meet the requirement that the SQL plan is executed twice or more.
• store: indicates the behavior based on the auto option. In this mode, the prerequisite for capturing an SQL plan does not need to meet the condition that the SQL statement is executed twice or more. The status of all plans is captured as UNACC.

Default value: off. 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: If SPM-related functions are not used, retain the default value. If SPM-related functions are used, set this parameter as required.

Risks and impacts of improper settings: If SPM-related functions are not used and this parameter is enabled, the performance deteriorates.

During the upgrade, the outline of the new plan is not captured regardless of whether this function is enabled. This is because the upgrade may be rolled back during the upgrade. If the newly generated outline is stored, the outline version may be incompatible after the upgrade rollback.

spm_enable_plan_selection

Parameter description: Specifies whether to enable the SPM plan selection function. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: indicates that the plan selection function is enabled.
• off: indicates that the plan selection function is disabled.

Default value: off. 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: If SPM-related functions are not used, retain the default value. If the SPM-related functions are used, set this parameter to on.

Risks and impacts of improper settings: If SPM-related functions are not used and this parameter is enabled, the performance deteriorates.

spm_plan_capture_filter

Parameter description: Specifies the database and schema to be captured. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: A maximum of 10,000 schemas can be captured, including duplicate schemas.

The parameter format is "$db1_oid:$schema1_oid, $db2_oid:$schema2_oid..", for example, "33245:56432,44321:12332,55432:65432", in which:

• 33245:56432 indicates that the SQL plan whose DB OID is 33245 and schema OID is 56432 is to be captured.
• 44321:12332 indicates that the SQL plan whose DB OID is 44321 and schema OID is 12332 is to be captured.
• 55432:65432 indicates that the SQL plan whose DB OID is 55432 and schema OID is 65432 is to be captured.

Default value: "", indicating that all schemas are captured during SPM plan capture and plan selection. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value. If you only need to collect the plan of a specified schema or the data volume of a schema exceeds the threshold, you can specify a schema.

Risks and impacts of improper settings: If SPM-related functions are used and the schema OID is incorrect, not all plans can be collected.

spm_plan_global_cached_size

Parameter description: Specifies the size of the SPM global plan cache.

Parameter type: integer.

Unit: KB

Value range: 10240 to 20971520

Default value: 1048576, that is, 1 GB.

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1. For example, if this parameter is set to 10240 without a unit, it indicates 10240 KB. If this parameter is set to 1GB, it indicates 1 GB. The unit must be KB, MB, or GB if required.

Setting suggestion: You are advised to set this parameter for database nodes in heavy-load scenarios.

Risks and impacts of improper settings: When SPM-related functions are used, if the value is too small, the SPM-related function performance deteriorates; if the value is too large, the memory usage increases. Therefore, set this parameter according to actual situations.

spm_plan_session_cached_size

Parameter description: Specifies the size of the SPM session plan cache. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: KB

Value range: 1 to 1048576

Default value: 1024, that is, 1 MB. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1. For example, if this parameter is set to 1024 without a unit, it indicates 1024 KB. If this parameter is set to 1GB, it indicates 1 GB. The unit must be KB, MB, or GB if required.

Setting suggestion: You are advised to set this parameter for database nodes in heavy-load scenarios.

Risks and impacts of improper settings: When SPM-related functions are used, if the value is too small, the SPM-related function performance deteriorates; if the value is too large, the memory usage increases. Therefore, set this parameter according to actual situations.

spm_plan_capture_max_plannum

Parameter description: Specifies the maximum number of flush plans in a single database, corresponding to the number of records in PG_CATALOG.GS_SPM_BASELINE and PG_CATALOG.GS_SPM_PLAN_HISTORY, respectively.

Parameter type: integer

Unit: none

Value range: 1 to 1000000

Default value: 200000

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: When SPM-related functions are used, if the value is too small, the number of SPM capture plans will be affected, and new plans cannot be managed; if the value is too large, the disk space will be affected. Set this parameter according to actual situations.

spm_plan_retention_days

Parameter description: Specifies the retention period of unused plans on disks.

Parameter type: integer.

Unit: day

Value range: 1 to 1095

Default value: 365

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

Setting suggestion: Retain the default value. If the disk space is insufficient or services change frequently, you can set this parameter to a small value.

Risks and impacts of improper settings: If SPM-related functions are used and the value is too small, plans will be frequently eliminated. As a result, plans cannot be managed.

spm_enable_log_detail

Parameter description: Specifies whether to enable the notice SPM key execution process when the SPM is used. This parameter is usually used together with the following two parameters to view the SPM execution process in gsql: client_min_messages=notice;logging_module='on(SPM_KEY_FLOW)'. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: off. 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: You are advised to use SPM-related functions only during debugging. Improper settings may cause a large number of logs.

spm_enable_baseline_cleanup

Parameter description: Specifies whether to clear baseline data periodically.

Parameter type: Boolean.

Unit: none

Value range:

• on: The periodic baseline clearance function is enabled.
• off: The periodic baseline clearance function 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: If this parameter is set to off when SPM-related functions are used, the spm_plan_retention_days and spm_plan_history_reserved_percentage parameters are invalid, outdated plans will not be deleted, and no historical records are cleared.

spm_enable_plan_history_logging

Parameter description: Specifies whether to enable the function of querying the plan change history. The spm_enable_plan_capture parameter must be set to STORE.

Parameter type: Boolean.

Unit: none

Value range:

• on: The function of querying the plan change history is enabled.
• off: The function of querying the plan change history is disabled.

Default value: on

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: When SPM-related functions are used, if this parameter is set to off, the spm_enable_plan_history_logging_expired_time parameter is invalid and the change history is not recorded.

spm_enable_plan_history_logging_expired_time

Parameter description: Specifies the size of the time window. When the SPM detects a plan in the baseline and the plan is not executed within a specified time window, the SPM determines that the plan change is caused on the current plan and historical records are recorded.

Parameter type: integer.

Unit: second

Value range: 0 to 31536000.

• 1 to 31536000: valid values, indicating that the time ranges from 1 second to 31536000 seconds (365 days).
• 0: The system does not check the expiration time. When a plan exists in the baseline, the system determines that the plan does not expire and does not change the historical records.

Default value: 900, that is, 15 minutes

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 value is too small, excessive historical records may be recorded. If the value is too large, some historical records may be missing.

spm_plan_history_reserved_percentage

Parameter description: Specifies the maximum percentage of the table that is expected to be filled. The number of records in the gs_spm_plan_history table has an upper limit. The base of this value is spm_plan_capture_max_plannum. The maximum number of rows that can be reserved for gs_spm_plan_history is spm_plan_capture_max_plannum multiplied by spm_plan_history_reserved_percentage (in percentage). The clearing operation is triggered by the SPM background thread every 24 hours. When spm_enable_baseline_cleanup is disabled, the clearing operation is not performed.

Parameter type: integer.

Unit: none

Value range: 1 to 100

Default value: 90

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 is too small, frequent clearance is triggered and historical records will be missed. If the value is too large, more disk space will be occupied.