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

Updated on 2025-05-29 GMT+08:00

View PDF

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