基础知识之游标共享----CURSOR_SHARING

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
--上述参数完全依赖开发人员自行绑定变量，或者同样的sql标准化编写
SQL> alter system flush shared_pool;

系统已更改。

SQL> select sql_id,child_number,sql_text from v$sql where sql_text like 'select
count(*) from%';

未选定行


SQL> select count(*) from test where object_id<10;

  COUNT(*)
----------
        24

SQL> select count(*) from test where object_id<100;

  COUNT(*)
----------
       294

SQL> select count(*) from test where object_id<1000;

  COUNT(*)
----------
      2826

SQL> select sql_id,child_number,sql_text from v$sql where sql_text like 'select
count(*) from%';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ -----------------------------------------------------

13u8w83yzd77c            0 select count(*) from test where object_id<1000
dft4kr6s6yd8q            0 select count(*) from test where object_id<100
355kdmgpm6upg            0 select count(*) from test where object_id<10

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL> alter session set cursor_sharing=FORCE;

会话已更改。

SQL> select count(*) from test where object_id<20;

  COUNT(*)
----------
        54

SQL> select count(*) from test where object_id<200;

  COUNT(*)
----------
       573

SQL> select count(*) from test where object_id<2000;

  COUNT(*)
----------
      5793

SQL> select sql_id,child_number,sql_text from v$sql where sql_text like 'select
count(*) from%';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ -----------------------------------------------------

crwtm662ycm4w            0 select count(*) from sys.job$ where next_date < :1 an

13u8w83yzd77c            0 select count(*) from test where object_id<1000
dft4kr6s6yd8q            0 select count(*) from test where object_id<100
8uahcgc6uypzg            0 select count(*) from test where object_id<:"SYS_B_0"
355kdmgpm6upg            0 select count(*) from test where object_id<10

--此处强制绑定还有风险的，特别在谓词中的字段上编制了索引的时候