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

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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot