watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

现在把隐含参数_optim_peek_user_binds设置为false关闭绑定变量窥探:

SQL> alter session set "_optim_peek_user_binds" = false; 会话已更改。

然后保持x不变,y修改为1000,再次执行目标SQL:看到目标SQL对应列的version_count和executions值由之前的1变为了2,说明Oracle在执行该SQL时用的还是硬解析:


SQL> exec :y := 1000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

SQL> set linesi 200

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               2          2



这也就意味着SQL所在的parent cursor下挂了两个child cursor:

SQL> select plan_hash_value,child_number from v$sql where sql_id='9dhu3xk2zu531'; PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------     1410530761            0     2351893609            1

而child_number为1的child cursor对应执行计划如下:


SQL> select * from table(dbms_xplan.display_cursor('9dhu3xk2zu531',1,
'advanced'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 1

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 2351893609

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   230 |  1150 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:Y>=:X)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

从上述显示内容看出,之前走的是索引快速全扫描,而此时走的是索引范围扫描,执行计划中有  OPT_PARAM('_optim_peek_user_binds' 'false'),而并没有出现之前启用绑定边路窥探的时候的“peeking binds”的内容,这说明已经禁用这个功能。

        之前介绍使得Oracle执行目标SQL再一次使用硬解析是使用DDL操作,但是由于其影响范围大,可能造成一定的性能问题,现在再介绍一种方式仅限于目标SQL对应的shared cursor,也就是说可以做到只让Oracle在执行此目标SQL使用硬解析,执行其他SQL都和原来一样,保持不变。

        这种方法使用DBMS_SHARED_POOL.PURGE。DBMS_SHARED_POOL.PURGE是从10.2.0.4引入的,用来删除指定的缓存在库缓存中的shared cursor,DBMS_SHARED_POOL.PURGE可以让Oracle在执行目标SQL使用硬解析,就是如果某个SQL对应shared cursor被删除,那么再次执行该SQL自然也就使用硬解析了。

如下查询目标SQL对应address以及列hash_value:


SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          ADDRESS          HASH_VALUE

----------------------------------------------------------------- --------------- ---------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6   00007FFD0FADBBB0 2571518534

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn   00007FFCD84D4A60  924127028

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531   00007FFD0F8F7170 2247955553


然后将其拼接成一个字符串输入参数传入DBMS_SHARED_POOL.PURGE并执行,第二个参数是常量c,表示要删除的是shared cursor;

SQL> exec sys.dbms_shared_pool.purge('00007FFD0F8F7170,2247955553','c'); PL/SQL 过程已成功完成。

然后查看发现目标SQL对应的shared cursor确实被删除:


SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1



额外注意:

如果要在10.2.0.4版本中使用dbms_shared_pool.purge,则使用之前手工设置event 5614566(alter session set events ‘5614566 trace name context forever’),否则dbms_shared_pool.purge将不起作用,在其以上的版本就不存在这个限制了。

现在在进行测试修改如下:


SQL> exec :y := 60000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     58577

 

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

 

SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS

----------------------------------------------------------------- --------------- ------------- ----------

select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1

select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1

select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               2          1



executions为1证明确实执行的硬解析,但是version_count是2?应该是1才对,如下查询的child cursor确实只有一个,所以应该为1,也许是Oracle bug导致。


SQL> select plan_hash_value,child_number from v$sql where
sql_id='9dhu3xk2zu531';

 

PLAN_HASH_VALUE CHILD_NUMBER

--------------- ------------

     2351893609            0

再来看其执行计划:


SQL> select * from table(dbms_xplan.display_cursor('9dhu3xk2zu531',0,
'advanced'));

 

PLAN_TABLE_OUTPUT
----------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 2351893609

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   230 |  1150 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / [email protected]$1

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:Y>=:X)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择 47 行。

选择是还是idx_t1的索引范围扫描,意味着把绑定变量关掉以后,无论传入值为多少都不会影响Oracle对于目标SQL执行计划的改变,并且其结果集从上述执行计划看出无论传入值为多少,cardinality都为230,如下来解释如何计算的

计算公式如下:

Cardinality = NUM_ROWS * SelectivitySelectivity = 0.05 * 0.05

注释:

1)、上述计算公司适用于禁用绑定变量窥探且where条件为目标列between x and y的selectivity和cardinality计算

2)、num_rows表示目标列所在表的记录数。

3)、where条件为“目标列between x and y”,相当于“目标列 >= x and 目标列 <= y”。对于“目标列 >= x”和“目标列 <= y”而言,Oracle均会使用5%的可选择率,所以“目标列 >= x and 目标列 <= y”总的可选择率是0.05*0.05

将之前查询的num_rows和0.05*0.05带入公式,查看结果:

SQL> select round(91944*0.05*0.05) from dual; ROUND(91944*0.05*0.05)----------------------                   230