文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-10.x）/ 特性使用指导/ 计划缓存/ 自动参数化和相同语句计划复用/ 操作指导
更新时间：2026-07-28 GMT+08:00
查看PDF
分享

操作指导

  • cursor_sharing = exact，仅启用相同语句计划复用功能。
    1. 建表及设置参数。 
      gaussdb=# CREATE TABLE cursor_sharing_exact_tbl(col1 int, col2 int);
CREATE TABLE
gaussdb=# INSERT INTO cursor_sharing_exact_tbl VALUES(1, 1);
INSERT 0 1
gaussdb=# SET cursor_sharing = exact;
SET
    2. 执行查询。 
      gaussdb=# SELECT * FROM cursor_sharing_exact_tbl WHERE col1 = 1;
 col1 | col2
------+------
    1 |    1
(1 row)
    3. 查询视图dbe_perf.gs_plancache_stat展示了该语句的缓存信息。 
      gaussdb=# SELECT parameterized_sql, parameter_types, is_parameterized FROM dbe_perf.gs_plancache_stat WHERE parameterized_sql LIKE '%from cursor_sharing_exact_tbl%';
                                                                      parameterized_sql                                                                      | parameter_types | is
_parameterized
-------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---
---------------
 select * from cursor_sharing_exact_tbl where col1 = 1;                                                                                                      | {}              | f
 select parameterized_sql, parameter_types, is_parameterized from dbe_perf.gs_plancache_stat where parameterized_sql like '%from cursor_sharing_exact_tbl%'; | {}              | f
(2 rows)
    4. 若环境开启了GPC，查询GPC视图DBE_PERF.GLOBAL_PLANCACHE_STATUS，字段parameterized_info被标记为exact表示该语句通过相同语句计划复用特性执行。若环境未开启GPC，请开启enable_global_plancache后重启数据库，并重新执行1~3。 
      gaussdb=# SHOW enable_global_plancache;
 enable_global_plancache
-------------------------
 on
(1 row)
 
gaussdb=# SELECT query, parameterized_info FROM DBE_PERF.GLOBAL_PLANCACHE_STATUS WHERE query LIKE '%from cursor_sharing_exact_tbl%';
                                                                            query                                                                            | parameterized_info
-------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
 select query, parameterized_info from DBE_PERF.GLOBAL_PLANCACHE_STATUS where query like '%from cursor_sharing_exact_tbl%';                                  | exact
 select parameterized_sql, parameter_types, is_parameterized from dbe_perf.gs_plancache_stat where parameterized_sql like '%from cursor_sharing_exact_tbl%'; | exact
 select * from cursor_sharing_exact_tbl where col1 = 1;                                                                                                      | exact
(3 rows)
    5. 删表恢复环境。 
      gaussdb=# DROP TABLE cursor_sharing_exact_tbl CASCADE;
DROP TABLE
  • cursor_sharing = force，同时启用自动参数化以及相同语句计划复用功能。
    1. 建表及设置参数。 
      gaussdb=# CREATE TABLE cursor_sharing_force_tbl(col1 int, col2 int);
CREATE TABLE
gaussdb=# INSERT INTO cursor_sharing_force_tbl VALUES(1, 1);
INSERT 0 1
gaussdb=# SET cursor_sharing = force;
SET
    2. EXPLAIN查看计划。
      1. normal模式下，被参数化的计划会显示[Parameterized]的tag； 
        gaussdb=# SET explain_perf_mode = 'normal';
SET
gaussdb=# EXPLAIN (costs off) SELECT * FROM cursor_sharing_force_tbl WHERE col1 = 1;
                      QUERY PLAN
------------------------------------------------------
 [Parameterized]
 Seq Scan on cursor_sharing_force_tbl
   Filter: (col1 = 1), (Expression Flatten Optimized)
(3 rows)
      2. pretty模式下，则会在Query Others中显示Parameterize: Yes，并给出参数化后的模板SQL，表示该计划是经过参数化的计划。 
        gaussdb=# SET explain_perf_mode = 'pretty';
SET
gaussdb=# EXPLAIN (costs off) SELECT * FROM cursor_sharing_force_tbl WHERE col1 = 1;
 id |                operation
----+------------------------------------------
  1 | ->  Seq Scan on cursor_sharing_force_tbl
(1 row)

       Predicate Information (identified by plan id)
------------------------------------------------------------
   1 --Seq Scan on cursor_sharing_force_tbl
         Filter: (col1 = 1), (Expression Flatten Optimized)
(2 rows)

                                  ====== Query Others =====
----------------------------------------------------------------------------------------------
 Parameterize: Yes, parameterized sql: select * from cursor_sharing_force_tbl where col1 = ?;
(1 row)
    3. 执行语句。 
      gaussdb=# SELECT * FROM cursor_sharing_force_tbl WHERE col1 = 1;
 col1 | col2
------+------
    1 |    1
(1 row)
    4. 查询视图dbe_perf.gs_plancache_stat展示了该语句的缓存信息，如parameterized_sql字段展示了对该SQL语句参数化后的结果。 
      gaussdb=# SELECT parameterized_sql, parameter_types, is_parameterized FROM dbe_perf.gs_plancache_stat WHERE parameterized_sql LIKE '%from cursor_sharing_force_tbl%';
                   parameterized_sql                    | parameter_types | is_parameterized
--------------------------------------------------------+-----------------+------------------
 select * from cursor_sharing_force_tbl where col1 = ?; | {integer}       | t
(1 row)
    5. 查询dbe_perf.statement视图，query字段展示参数化后的语句信息，$表示被参数化的常量。（需要保证当前unique sql记录条数少于最大可记录条数instr_unique_sql_count）。 
      gaussdb=# SELECT query FROM dbe_perf.statement WHERE query LIKE '%from cursor_sharing_force_tbl%';
                          query
---------------------------------------------------------
 select * from cursor_sharing_force_tbl where col1 = $1;
(1 row)
    6. 删表恢复环境。 
      gaussdb=# DROP TABLE cursor_sharing_force_tbl CASCADE;
DROP TABLE

相关文档