游标共享之cursor_sharing=similar

      cursor_sharing=similar   ，exact和force折中的参数。当探测到谓词会导致执行计划的改变，就会重新解析，否则不会。
      适用场景：在无法将应用的代码修改为绑定变量情况下，oracle提供的又一种解决方法，similar的好处是最大限度地避免CBO在绑定变量的情况下做出的错误的判断，但它的代价是分析的次数变大。

       参数还有两种选择：

        游标共享之cursor_sharing=force
        游标共享之cursor_sharing=EXACT

       下面来做个试验：

SQL> Drop table t purge;
SQL> show parameter cursor_sharing;
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_optimizer_extended_cursor_sharing   string                           UDO
cursor_sharing                       string                           FORCE
SQL> create table t as select * from dba_objects;
SQL> Create index ind_t_object_id on t(object_id) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
SQL> alter session set cursor_sharing=similar;
SQL> Set autotrace traceonly
SQL> select /*+test_similar*/* from t where object_id<10;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1630734149
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     7 |   651 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     7 |   651 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJECT_ID |     7 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<10)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1788  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> select /*+test_similar*/* from t where object_id<100000;
已选择50675行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50675 |  4602K|   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 50675 |  4602K|   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<100000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4042  consistent gets
          0  physical reads
          0  redo size
    2460611  bytes sent via SQL*Net to client
      37543  bytes received via SQL*Net from client
       3380  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50675  rows processed

----当探测到执行计划会改变时，则会重新解析
SQL> Set autotrace off
SQL> alter system flush shared_pool;
SQL> Set autotrace traceonly
SQL> select /*+test_similar*/* from t where object_id<10;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1630734149
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     7 |   651 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     7 |   651 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJECT_ID |     7 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<10)
统计信息
----------------------------------------------------------
        941  recursive calls
          0  db block gets
        149  consistent gets
          0  physical reads
          0  redo size
       1788  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> select /*+test_similar*/* from t where object_id<100000;
已选择50675行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50675 |  4602K|   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 50675 |  4602K|   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<100000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4042  consistent gets
          0  physical reads
          0  redo size
    2460611  bytes sent via SQL*Net to client
      37543  bytes received via SQL*Net from client
       3380  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50675  rows processed

SQL> Set autotrace off
SQL> col sql_text format a80;
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%/*+test_similar*/%'
  2  and  sql_text not like '%sql_text%'
  3  And  sql_text not like '%EXPLAIN PLAN%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------------------------------------------
d5101w24qr4hk            0 select /*+test_similar*/* from t where object_id<:"SYS_B_0"
d5101w24qr4hk            1 select /*+test_similar*/* from t where object_id<:"SYS_B_0"


----当探测到执行计划不会改变时，则不会重新解析
SQL> alter system flush shared_pool;
SQL> Set autotrace traceonly
SQL> select /*+test_similar*/* from t where object_name='aaa';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   186 |   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |   186 |   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='aaa')
统计信息
----------------------------------------------------------
        941  recursive calls
          0  db block gets
        846  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select /*+test_similar*/* from t where object_name='bbb';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   186 |   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |   186 |   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='bbb')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        702  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select /*+test_similar*/* from t where object_name='ccc';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   186 |   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |   186 |   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='ccc')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        702  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select /*+test_similar*/* from t where object_name='ddd';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   186 |   149   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |   186 |   149   (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='ddd')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        702  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> Set autotrace off
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%/*+test_similar*/%'
   and  sql_text not like '%sql_text%'
    And  sql_text not like '%EXPLAIN PLAN%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------------------------------------------
2uff8qc1h3jyx            0 select /*+test_similar*/* from t where object_name=:"SYS_B_0"