常见问题

A：在自动参数化下，部分语句计划可能会发生变化。由于自动参数化将语句中的常量替换为参数，再进行绑参执行。在计划生成的过程中，部分场景下对常量及参数的处理有区别，因此导致设置cursor_sharing = force后，出现部分计划跳变的问题。

如下示例中，SELECT * FROM cursor_sharing_tbl WHERE col1 = 1;在cursor_sharing = off场景使用索引扫描，在cursor_sharing = force场景下使用全表扫描。

gaussdb=# CREATE DATABASE ora_db dbcompatibility = 'ORA';
CREATE DATABASE
gaussdb=# \c ora_db
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_db" as user "yumingyue".
ora_db=# CREATE TABLE cursor_sharing_tbl(col1 int, col2 int) DISTRIBUTE BY hash(col1);
CREATE TABLE
ora_db=# CREATE INDEX cursor_sharing_tbl_idx ON cursor_sharing_tbl(col1) WHERE col1 != 0;
CREATE INDEX
ora_db=# SET plan_cache_mode = 'force_generic_plan';
SET
ora_db=# SET max_datanode_for_plan = 1;
SET
ora_db=# SET cursor_sharing = off;
SET
ora_db=# EXPLAIN verbose SELECT * FROM cursor_sharing_tbl WHERE col1 = 1;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Output: cursor_sharing_tbl.col1, cursor_sharing_tbl.col2
   Node/s: datanode1
   Remote query: SELECT col1, col2 FROM public.cursor_sharing_tbl WHERE col1 = 1

 Remote SQL: SELECT col1, col2 FROM public.cursor_sharing_tbl WHERE col1 = 1
 Datanode Name: datanode1
   Bitmap Heap Scan on public.cursor_sharing_tbl  (cost=1.36..9.58 rows=11 width=8)
     Output: col1, col2
     Recheck Cond: (cursor_sharing_tbl.col1 = 1), (Expression Flatten Optimized)
     ->  Bitmap Index Scan on cursor_sharing_tbl_idx  (cost=0.00..1.36 rows=11 width=0)
           Index Cond: (cursor_sharing_tbl.col1 = 1)

(13 rows)

ora_db=# SET cursor_sharing = force;
SET
ora_db=# EXPLAIN verbose SELECT * FROM cursor_sharing_tbl WHERE col1 = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 [Parameterized]
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Output: cursor_sharing_tbl.col1, cursor_sharing_tbl.col2
   Node/s: datanode1
   Remote query: SELECT col1, col2 FROM public.cursor_sharing_tbl WHERE col1 = 1

 Remote SQL: SELECT col1, col2 FROM public.cursor_sharing_tbl WHERE col1 = 1
 Datanode Name: datanode1
   [Parameterized]
   Seq Scan on public.cursor_sharing_tbl  (cost=0.00..36.86 rows=11 width=8)
     Output: col1, col2
     Filter: (cursor_sharing_tbl.col1 = $1), (Expression Flatten Optimized)

(13 rows)

ora_db=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "yumingyue".
gaussdb=# DROP DATABASE ora_db;
DROP DATABASE

该场景由于自动参数化将SELECT * FROM cursor_sharing_tbl WHERE col1 = 1;中常量1作为参数生成计划，在设置plan_cache_mode = 'force_generic_plan';强制走gplan的场景，会导致cursor_sharing_tbl_idx部分索引的索引条件为参数，索引失效，因此使用全表扫描。

计划跳变场景可通过设置plan_cache_mode = 'force_custom_plan';强制走cplan解决，或关闭自动参数化解决。

A：自动参数化及相同语句计划复用机制通过缓存已执行语句的执行计划，避免在相同语句场景下重复生成执行计划，从而提高查询执行效率。然而，此机制可能导致执行计划缓存占用过多内存。当前系统已启用计划缓存上限管控功能。若观察到session级计划缓存占用异常，可调整max_plancache_memory_ratio参数进行优化。