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

操作指导

  • cursor_sharing = exact，仅启用相同语句计划复用功能。
    1. 建库、建表及设置参数。 
      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_exact_tbl(col1 int, col2 int) DISTRIBUTE BY hash(col1);
CREATE TABLE
ora_db=# INSERT INTO cursor_sharing_exact_tbl VALUES(1, 1);
INSERT 0 1
ora_db=# SET cursor_sharing = exact;
SET
    2. 执行查询。 
      ora_db=# SELECT * FROM cursor_sharing_exact_tbl WHERE col1 = 1;
 col1 | col2
------+------
    1 |    1
(1 row)
    3. 查询视图dbe_perf.gs_plancache_stat展示了该语句的缓存信息。 
      ora_db=# SELECT parameterized_sql, parameter_types, is_parameterized FROM dbe_perf.gs_plancache_stat WHERE parameterized_sql LIKE '%from cursor_sharing_exact_tbl%';
                                                                      parameterized_sql                                                                      | paramet
er_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。 
      ora_db=# SHOW enable_global_plancache;
 enable_global_plancache
-------------------------
 on
(1 row)

ora_db=# SELECT nodename, query, parameterized_info FROM DBE_PERF.GLOBAL_PLANCACHE_STATUS WHERE query LIKE '%from cursor_sharing_exact_tbl%';
 nodename  |                                                                            query
   | parameterized_info
-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------
---+--------------------
 cn1       | select parameterized_sql, parameter_types, is_parameterized from dbe_perf.gs_plancache_stat where parameterized_sql like '%from cursor_sharing_exact_tbl%
'; | exact
 cn1       | select nodename, query, parameterized_info from DBE_PERF.GLOBAL_PLANCACHE_STATUS where query like '%from cursor_sharing_exact_tbl%';
   | exact
 cn1       | select * from cursor_sharing_exact_tbl where col1 = 1;
   | exact
 datanode1 | select * from cursor_sharing_exact_tbl where col1 = 1;
   | exact
(4 rows)
    5. 删库恢复环境。 
      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
  • cursor_sharing = force，同时启用自动参数化以及相同语句计划复用功能。
    1. 建库、建表及设置参数。 
      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_force_tbl(col1 int, col2 int) DISTRIBUTE BY hash(col1);
CREATE TABLE
ora_db=# INSERT INTO cursor_sharing_force_tbl VALUES(1, 1);
INSERT 0 1
ora_db=# SET cursor_sharing = force;
SET
    2. EXPLAIN查看计划，被参数化的计划会显示[Parameterized]的tag。 
      ora_db=# EXPLAIN (costs off) SELECT * FROM cursor_sharing_force_tbl WHERE col1 = 1;
     QUERY PLAN
---------------------
 [Parameterized]
 Data Node Scan
   Node/s: datanode1
(3 rows)
    3. 执行语句。 
      ora_db=# SELECT * FROM cursor_sharing_force_tbl WHERE col1 = 1;
 col1 | col2
------+------
    1 |    1
(1 row)
    4. 查询视图dbe_perf.gs_plancache_stat展示了该语句的缓存信息，如parameterized_sql字段展示了对该SQL语句参数化后的结果。 
      ora_db=# 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）。 
      ora_db=# 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. 删库恢复环境。 
      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

相关文档