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 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)

Parent topic: Functions and Operators

Previous topic: Undo System Functions

Next topic: Statistics Information Functions