# 操作指导
- 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
     ```