Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Reference/ Functions and Operators/ SPM Functions

Updated on 2025-08-19 GMT+08:00

SPM Functions

GS_SPM_EVOLUTE_PLAN(sql_hash, plan_hash)

Description: GS_SPM_EVOLUTE_PLAN belongs to the DBE_SQL_UTIL schema and is a function used by the SPM feature to plan evolution. As long as the table related to the plan baseline exists, the plan baseline can be evolved.

Parameters: See Table 1.

**Table 1** GS_SPM_EVOLUTE_PLAN input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Specifies a hash value of SQL text.	-
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.	-
evolute_status	OUT boolean	Determines whether the evolution is complete. t indicates normal. If an exception occurs, an error is reported.	t/f

Return type: Boolean

Example:

gaussdb=# SELECT * FROM dbe_sql_util.gs_spm_evolute_plan(107760189, 2284373089);
 evolute_status 
----------------
 t
(1 row)

GS_SPM_SET_PLAN_STATUS(sql_hash, plan_hash, plan_status)

Description: GS_SPM_SET_PLAN_STATUS belongs to the DBE_SQL_UTIL schema and is a function used by the SPM feature to change the baseline status.

Parameters: See Table 2.

**Table 2** GS_SPM_SET_PLAN_STATUS input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Specifies a hash value of SQL text.	-
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.	-
plan_status	IN text	Specifies the status of a plan.	ACC: indicates that the plan has been accepted. UNACC: indicates that the plan is not accepted. FIXED: indicates a special ACC plan. The matching priority of this plan is higher than that of other ACC plans.
execute_status	OUT boolean	Determines whether the plan status change is complete. t indicates normal. If an exception occurs, an error is reported.	t/f

Return type: Boolean

Example:

gaussdb=# SELECT dbe_sql_util.gs_spm_set_plan_status(sql_hash, plan_hash, 'ACC') FROM gs_spm_sql_baseline 
where outline like '%BitmapScan%';
 gs_spm_set_plan_status 
------------------------
 t
(1 row)

GS_SPM_DISPLAY_PLANS(sql_hash)

Description: GS_SPM_DISPLAY_PLANS belongs to the DBE_SQL_UTIL schema and is a function used by the SPM feature to view all baselines of a single SQL statement.

Parameters: See Table 3.

**Table 3** GS_SPM_DISPLAY_PLANS input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Unique ID of an SQL statement.	-
sql_hash	OUT bigint	Specifies a hash value of SQL text.	-
plan_hash	OUT bigint	Specifies a hash value of the outline text of the SQL plan.	-
outline	OUT text	Specifies the combination character string of all hints of the outline corresponding to the current plan.	-
cost	OUT double	Specifies the cost of the current plan.	-
status	OUT text	Specifies the status of the current plan.	ACC: indicates that the plan has been accepted. UNACC: indicates that the plan is not accepted. FIXED: indicates a special ACC plan. The matching priority of this plan is higher than that of other ACC plans.
gplan	OUT boolean	Determines whether the current plan is a gplan. t indicates gplan. If an exception occurs, an error is reported.	t/f

Return type: bigint, text, double, text, Boolean

Example:

select sql_hash, plan_hash, outline, status, gplan from dbe_sql_util.gs_spm_display_plans(107760189) 
order by status, outline;
 sql_hash  |  plan_hash   |                    outline                     | status | gplan 
-----------+------------+------------------------------------------------+--------+-------
 107760189 | 2519317082 |  begin_outline_data                           +| ACC    | f
           |            |  IndexScan(@"sel$1" tb_a@"sel$1" tb_a_idx_c1) +|        | 
           |            |  version("1.0.0")                             +|        | 
           |            |  end_outline_data                              |        | 
 107760189 | 2686653876 |  begin_outline_data                           +| UNACC  | f
           |            |  BitmapScan(@"sel$1" tb_a@"sel$1" tb_a_idx_c1)+|        | 
           |            |  version("1.0.0")                             +|        | 
           |            |  end_outline_data                              |        | 
 107760189 | 2284373089 |  begin_outline_data                           +| UNACC  | f
           |            |  TableScan(@"sel$1" tb_a@"sel$1")             +|        | 
           |            |  version("1.0.0")                             +|        | 
           |            |  end_outline_data                              |        | 
(3 rows)

GS_SPM_RELOAD_PLAN(sql_hash, plan_hash)

Description: GS_SPM_RELOAD_PLAN belongs to the DBE_SQL_UTIL schema and is a function used by the SPM feature to load a baseline in the baseline system catalog to the SPM global cache.

Parameters: See Table 4.

**Table 4** GS_SPM_RELOAD_PLAN input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Specifies a hash value of SQL text.	-
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.	-
execute_status	OUT boolean	Determines whether the baseline loading is complete. t indicates normal. If an exception occurs, an error is reported.	t/f

Return type: Boolean

Example:

SELECT dbe_sql_util.gs_spm_reload_plan(sql_hash, plan_hash) from gs_spm_sql_baseline where outline like '%IndexScan%'; 
 gs_spm_reload_plan 
--------------------
 t
(1 row)

GS_SPM_VALIDATE_PLAN(sql_hash, plan_hash)

Description: GS_SPM_VALIDATE_PLAN belongs to the DBE_SQL_UTIL schema and is a function used by the SPM feature to verify plan availability.

Parameters: See Table 5.

**Table 5** GS_SPM_VALIDATE_PLAN input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Specifies a hash value of SQL text.	-
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.	-
execute_status	OUT boolean	Determines whether the verified plan is available: t: The verified plan is available. f: The verified plan is unavailable.	t/f

Return type: Boolean

Example:

SELECT dbe_sql_util.gs_spm_validate_plan(sql_hash, plan_hash) FROM gs_spm_sql_baseline WHERE outline LIKE '%IndexScan%'; 
 gs_spm_validate_plan 
----------------------
 f
(1 row)

GS_SPM_DELETE_PLAN(sql_hash, plan_hash)

Description: GS_SPM_DELETE_PLAN belongs to DBE_SQL_UTIL schema and is a function used by the SPM feature to delete the plan baseline. If the function is aborted during execution, the number of records in the gs_spm_baseline table may exceed the value of the GUC parameter spm_plan_capture_max_plannum.

Parameters: See Table 6.

**Table 6** GS_SPM_DELETE_PLAN input parameters and return values
Parameter	Type	Description	Value Range
sql_hash	IN bigint	Specifies a hash value of SQL text.	-
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.	-
execute_status	OUT boolean	Determines whether the plan deletion is complete. t indicates normal. If an exception occurs, an error is reported.	t/f

Return type: Boolean

Example:

SELECT dbe_sql_util.gs_spm_delete_plan(sql_hash, plan_hash) FROM gs_spm_sql_baseline WHERE outline LIKE '%IndexScan%'; 
 gs_spm_delete_plan 
--------------------
 t
(1 row)

GS_SPM_GET_PLAN_HISTORY(sql_input_hash, history_time)

Description: GS_SPM_GET_PLAN_HISTORY belongs to the DBE_SQL_UTIL schema and is used by the SPM feature to view all historical plans of a specified query statement before a specified historical time point.

Parameters: See Table 7.

**Table 7** GS_SPM_GET_PLAN_HISTORY input parameters and return values
Parameter	Type	Description	Value Range
sql_input_hash	IN bigint	Specifies a hash value of SQL text.	-
history_time	IN timestamp with time zone	Specifies a historical time point.	-
sql_hash	OUT bigint	Specifies a hash value of SQL text.	-
plan_hash	OUT bigtint	Specifies a hash value of the outline text of the SQL plan.	-
outline	OUT text	Specifies the combination character string of all hints of the outline corresponding to the current plan.	-
plan_status	OUT text	Specifies the status of the current plan.	ACC: indicates that the plan has been accepted. UNACC: indicates that the plan is not accepted. FIXED: indicates a special ACC plan. The matching priority of this plan is higher than that of other ACC plans.
gplan	OUT boolean	Determines whether the current plan is a gplan. t indicates gplan. If an exception occurs, an error is reported.	t/f
cost	OUT double	Specifies the cost of the current plan.	-
sql_text	OUT text	SQL text.	-
param_num	OUT integer	Number of SQL parameters.	-
source	OUT text	Baseline source.	AUTO: The baseline is captured in AUTO mode. MANUAL: The baseline is captured in MANUAL mode. STORE: The baseline is captured in STORE mode.
history_creation_time	OUT timestamp with time zone	Time when the change history is created.	-
invalid	OUT boolean	Specifies whether the current baseline is invalid.	t/f

Return type: record

Example:

gaussdb=# SELECT dbe_sql_util.gs_spm_get_plan_history(sql_hash, creation_time + 1) FROM pg_catalog.gs_spm_plan_history WHERE sql_hash IN (SELECT sql_hash FROM pg_catalog.gs_spm_baseline WHERE sql_namespace = get_schema_oid(CURRENT_SCHEMA::cstring));
-[ RECORD 1 ]-----------+---------------------------------------------------------------------------------------------------------------------------------------
gs_spm_get_plan_history | (812490094,1950233227," begin_outline_data
                        |  TableScan(@""sel$1"" test_spm_gplan_change_perception.test_spm_perception_t1@""sel$1"")
                        |  version(""1.0.0"")
                        |  end_outline_data",UNACC,t,180,"select * from test_spm_perception_t1 where a = $1",1,STORE,10,"Mon Jan 13 06:04:22.614726 2025 PST",f)
(4 rows)

GS_SPM_ACCEPT_HISTORICAL_PLAN(sql_input_hash, history_time, plan_status)

Description: GS_SPM_ACCEPT_HISTORICAL_PLAN belongs to the DBE_SQL_UTIL schema and is used by the SPM feature to set the status of the latest historical plan of an executed query statement before a time point to a specified status.

Parameters: See Table 8.

**Table 8** GS_SPM_ACCEPT_HISTORICAL_PLAN input parameters and return values
Parameter	Type	Description	Value Range
sql_input_hash	IN bigint	Specifies a hash value of SQL text.	-
history_time	IN bigint	Specifies a historical time point.	-
plan_status	IN text	Specifies the status of a plan.	ACC: indicates that the plan has been accepted. FIXED: indicates a special ACC plan. The matching priority of this plan is higher than that of other ACC plans.
plan_hash_previous	OUT bigint	Specifies the hash value of the outline text of a specified plan.	-

Return type: bigint

Example:

gaussdb=# SELECT dbe_sql_util.gs_spm_accept_historical_plan(sql_hash, creation_time + 1, 'ACC') FROM gs_spm_plan_history where sql_hash IN (SELECT sql_hash FROM pg_catalog.gs_spm_baseline WHERE sql_namespace = get_schema_oid(CURRENT_SCHEMA::cstring));
 gs_spm_accept_historical_plan 
-------------------------------
                     602237302
(1 row)

GS_SPM_DELETE_PLAN_HISTORY(sql_hash, plan_hash, plan_hash_previous, userid, creation_time)

Description: GS_SPM_DELETE_PLAN_HISTORY belongs to the DBE_SQL_UTIL schema and is used by the SPM feature to delete a specified record from PG_CATALOG.GS_SPM_PLAN_HISTORY.

Parameters: See Table 9.

**Table 9** GS_SPM_DELETE_PLAN_HISTORY input parameters and return values
Parameter	Type	Description
sql_hash	IN bigint	Specifies a hash value of SQL text.
plan_hash	IN bigint	Specifies a hash value of the outline text of the SQL plan.
plan_hash_previous	IN bigint	Specifies the hash value of the outline text of a plan used last time.
userid	IN oid	OID of a user.
creation_time	IN timestamp with time zone	Time when the change history is created.
delete_count	OUT bigint	Number of deleted records.

Return type: bigint

Example:

gaussdb=# SELECT dbe_sql_util.gs_spm_delete_plan_history(sql_hash, plan_hash, plan_hash_previous, userid, creation_time) FROM pg_catalog.gs_spm_plan_history WHERE sql_hash IN (SELECT sql_hash FROM pg_catalog.gs_spm_baseline WHERE sql_namespace = get_schema_oid(CURRENT_SCHEMA::cstring));
 gs_spm_delete_plan_history 
----------------------------
                          1
(1 row)

Parent topic: Functions and Operators

Previous topic: VACUUM System Functions

Next topic: Statistics Information Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.

The system is busy. Please try again later.