Help Center/ Data Warehouse Service / SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ Functions and Operators/ System Administration Functions/ Plan Management Functions
Updated on 2025-10-10 GMT+08:00
View PDF
Share

Plan Management Functions

This topic describes plan management functions.

pgxc_bind_plan(sql_hash text, outline_name text, apply_skew_hint boolean DEFAULT false)

Description: This function associates the outline named outline_name with the SQL statement identified by sql_hash. Specifically, it binds the outline, which is identified by the primary keys outline_name and sql_hash in the SQL_OUTLINE system table, to the corresponding SQL statement. The parameter apply_skew_hint indicates whether to apply the skew hint in the outline. The default value is false, indicating that the skew hint in the outline is not applied. Only the database administrator or users with the gs_role_sql_management role are authorized to execute this function.

Return type: Boolean

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
SELECT pgxc_bind_plan(sql_hash, outline_name) FROM outlines limit 1;
 pgxc_bind_plan
----------------
 t
(1 row)

SELECT pgxc_bind_plan('sql_be2995e824abb8b712b69fca4825b532', 'outline_e5214c8e3901da4d7d34166cb9cb84ee');
 pgxc_bind_plan
----------------
 t
(1 row)

SELECT pgxc_bind_plan(sql_hash, outline_name, true) FROM outlines WHERE outline ILIKE '%skew%' limit 1;
 pgxc_bind_plan
----------------
 t
(1 row)

pgxc_unbind_plan(sql_hash text)

Description: This function unbinds the plan from the statement corresponding to outline. Only the database administrator or users with the gs_role_sql_management role are authorized to execute this function.

Return type: Boolean

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT pgxc_unbind_plan(sql_hash) FROM sql_plan_baseline;
 pgxc_unbind_plan 
------------------
 t
(1 row)

SELECT pgxc_unbind_plan('sql_be2995e824abb8b712b69fca4825b532');
 pgxc_unbind_plan
------------------
 t
(1 row)

drop_outline(sql_hash text, outline_name text)

Description: This function deletes a specified outline from the dbms_om.sql_outline system catalog. Users not associated with an outline can be removed. This action is restricted to the database administrator or users endowed with the gs_role_sql_management role.

Return type: Boolean

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
SELECT drop_outline(sql_hash, outline_name) FROM outlines;
WARNING:  The outline(specified by sql_hash[sql_3d937542c0d24c9c995f31cba327fa56] and outline_name[outline_413ac0e566ff20be320f5ef0774bf1eb]) is bind
CONTEXT:  referenced column: drop_outline
 drop_outline
--------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 f
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(23 rows)

pgxc_reset_planmgmt_hashtable()

Description: This function releases the memory used by plan management to cache outlines on all CNs. Only the database administrator or users with the gs_role_sql_management role are authorized to execute this function.

Return type: Boolean

Example:

Query the memory usage of the outline saved by the plan management module on each CN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT pg_catalog.pgxc_node_str(), contextname, level, parent, pg_size_pretty(totalsize) totalsize, pg_size_pretty(freesize) freesize, pg_size_pretty(usedsize) usedsize FROM PG_SHARED_MEMORY_DETAIL WHERE contextname ilike 'plan%' ORDER BY contextname;
 pgxc_node_str |        contextname         | level |     parent      | totalsize | freesize |  usedsize
---------------+----------------------------+-------+-----------------+-----------+----------+------------
 coordinator1  | plan management hash table |     2 | PlanmgmtContext | 80 KB     | 45 KB    | 35 KB
 coordinator1  | PlanmgmtContext            |     1 | ProcessMemory   | 96 KB     | 94 KB    | 2432 bytes
(2 rows)

SELECT pgxc_reset_planmgmt_hashtable();
 pgxc_reset_planmgmt_hashtable 
-------------------------------
 t
(1 row)

Once you've executed the pgxc_reset_planmgmt_hashtable() function, use this command to check the memory usage of the plan management module for storing outlines on each CN.

1
2
3
4
5
6
 
SELECT pg_catalog.pgxc_node_str(), contextname, level, parent, pg_size_pretty(totalsize) totalsize, pg_size_pretty(freesize) freesize, pg_size_pretty(usedsize) usedsize FROM PG_SHARED_MEMORY_DETAIL WHERE contextname ilike 'plan%' ORDER BY contextname;
 pgxc_node_str |        contextname         | level |     parent      | totalsize | freesize | usedsize
---------------+----------------------------+-------+-----------------+-----------+----------+----------
 coordinator1  | plan management hash table |     2 | PlanmgmtContext | 56 KB     | 33 KB    | 23 KB
 coordinator1  | PlanmgmtContext            |     1 | ProcessMemory   | 0 bytes   | 0 bytes  | 0 bytes
(2 rows)
Parent Topic: System Administration Functions