经常遇到执行计划突变，游标不能共享，需要找到其根本原因，可以查看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
V1字段已经存在直方图：
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)
再次执行查询more的，因为有adaptive cursor sharing特性，多执行几次，则执行计划变为INDEX
FAST FULL SCAN。
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，一般都是关闭掉。
阅读(540) | 评论(0) | 转发(0) |
