执行计划不共享的原因分析-凯发app官方网站

 经常遇到执行计划突变，游标不能共享，需要找到其根本原因，可以查看v$sql_shared_cursor视图，这个视图有64种原因：

 
  通常，当执行计划无法共享时，其对应的非共享varchar2（1）列值将设置为“y”。另外还有个reason字段显示其原因，常见的有统计信息、绑定变量mismatch等，可以使用noshared.sql进行格式化查看。
可以看一个例子：
建一个表，字段v1分布不均，less存储100行，more存储9900行，这样模拟extend cursor sharing导致执行计划不共享。如下所示：
drop table t1;
create table t1(n1 number,n2 number,v1 varchar2(100));
insert into t1
select
rownum n1
,trunc ((rownum-1)/5) n2
,case mod(rownum, 100)
when 0 then 'less'
else 'more'
end v1
from dual
connect by level <= 10000;

建立索引：
create index t1_ind on t1(v1);

收集统计信息：
exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t1',estimate_percent=>null,method_opt=>'for all columns size skewonly',no_invalidate=>false,cascade=>true,degree => 10);
数据分布如下：
dingjun123@oradb> desc t1
 name                                                                                                              null?    type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 n1                                                                                                                         number
 n2                                                                                                                         number
 v1                                                                                                                         varchar2(100)


dingjun123@oradb> select v1,count(*)
  2  from t1
  3  group by v1;


v1                                                                                                     count(*)
---------------------------------------------------------------------------------------------------- ----------
more                                                                                                       9900
less                                                                                                        100
dingjun123@oradb> select column_name,histogram from dba_tab_col_statistics where table_name='t1' and owner=user;


column_name                    histogram
------------------------------ ---------------
n1                             none
n2                             none
v1                             frequency
先查询less的，走index range scan:
var v1 varchar2(100);
exec :v1 := 'less';
select count(1) from t1 where v1 = :v1;


dingjun123@oradb> select count(1) from t1 where v1 = :v1;


  count(1)
----------
       100




dingjun123@oradb> select * from table(dbms_xplan.display_cursor);


plan_table_output
------------------------------------------------------------------------------------------------
sql_id  d2h2phry5d881, child number 0
-------------------------------------
select count(1) from t1 where v1 = :v1


plan hash value: 2603166377


----------------------------------------------------------------------------
| id  | operation         | name   | rows  | bytes | cost (%cpu)| time     |
----------------------------------------------------------------------------
|   0 | select statement  |        |       |       |     1 (100)|          |
|   1 |  sort aggregate   |        |     1 |     5 |            |          |
|*  2 |   index range scan| t1_ind |   100 |   500 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------


predicate information (identified by operation id):
---------------------------------------------------


   2 - access("v1"=:v1)
var v1 varchar2(100);
exec :v1 := 'more';
select count(1) from t1 where v1 = :v1;
dingjun123@oradb> select count(1) from t1 where v1 = :v1;


  count(1)
----------
      9900
      
多执行几次：


dingjun123@oradb>  select * from table(dbms_xplan.display_cursor);


plan_table_output
------------------------------------------------------------------------------------------------
sql_id  d2h2phry5d881, child number 2
-------------------------------------
select count(1) from t1 where v1 = :v1


plan hash value: 2264155217


--------------------------------------------------------------------------------
| id  | operation             | name   | rows  | bytes | cost (%cpu)| time     |
--------------------------------------------------------------------------------
|   0 | select statement      |        |       |       |     8 (100)|          |
|   1 |  sort aggregate       |        |     1 |     5 |            |          |
|*  2 |   index fast full scan| t1_ind |  9900 | 49500 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------


predicate information (identified by operation id):
---------------------------------------------------


   2 - filter("v1"=:v1)

游标不共享，因为执行计划变了：
dingjun123@oradb> select child_number from v$sql where sql_id='d2h2phry5d881';


child_number
------------
           0
           1
           2

使用noshared.sql脚本查看不共享原因是extended_cursor_sharing，也就是acs特性：
dingjun123@oradb> @nonshared d2h2phry5d881
show why existing sql child cursors were not reused (v$sql_shared_cursor)...


old  17:                      replace( '&cmd', '"', ''''),
new  17:                      replace( 'select * from v$sql_shared_cursor where sql_id = ''d2h2phry5d881''', '"', ''''),
sql_id                        : d2h2phry5d881
address                       : 00000000bf904318
child_address                 : 00000000bf903eb8
child_number                  : 0
load_optimizer_stats          : y
reason                        : 045nls
settings(0)2x400
-----------------
sql_id                        : d2h2phry5d881
address                       : 00000000bf904318
child_address                 : 00000000bf9466b8
child_number                  : 1
language_mismatch             : y
load_optimizer_stats          : y
reason                        : 140bind mismatch(25)0x0extended_cursor_sharing
-----------------
sql_id                        : d2h2phry5d881
address                       : 00000000bf904318
child_address                 : 00000000bf939dc0
child_number                  : 2
language_mismatch             : y
bind_equiv_failure            : y
reason                        :
-----------------


pl/sql procedure successfully completed.

使用nonshared.sql脚本可以快速分析不共享原因，原始的v$sql_shared_cursor字段多，看起来不是很方便，extend cursor sharing有很多bug，一般都是关闭掉。