ANBOB™

sys@anbob>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

sys@ORCL>show parameter cursor_shar

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

SQL>  select       
  2       plan_hash_value,
  3       BUFFER_GETS,ROWS_PROCESSED,CHILD_NUMBER,ELAPSED_TIME,cpu_TIME,
  4       is_bind_sensitive,
  5       is_bind_aware,LAST_ACTIVE_TIME
  6     from   v$sql WHERE SQL_ID='6vd7zxpj6qd8h';

PLAN_HASH_VALUE BUFFER_GETS ROWS_PROCESSED CHILD_NUMBER ELAPSED_TIME   CPU_TIME I I LAST_ACTIVE_TIME
--------------- ----------- -------------- ------------ ------------ ---------- - - -------------------
      841592968      233739              6            0       672757     670899 N N 2013-12-05 15:28:54
     3830877904     4336911              6            1    172400111  172367796 Y N 2013-12-05 15:31:47

Note:
From above output you can see the SQL parsing the two cursors and generated different execution plan, and ELAPSED_TIME, CPU_TIME also reflects the time required to perform before and after the gap between the two

sys@anbob>oradebug short_stack;
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-kxhrHash()+386<-qerhjSplitProbe()+184<-qerhjInnerProbeHashTable()+74<-qerixFetchFastFullScan()+2476<-rwsfcd()+103<-qerhjFetch()+593<-qerflFetch()+135<-rwsfcd()+103<-qeruaFetch()+574<-qervwFetch()+139<-qerjotRowProc()+353<-qertbFetchByRowID()+3236<-qerjotRowProc()+353<-qertbFetchByRowID()+3236<-qerjotRowProc()+353<-qerixtFetch()+800<-qerjotFetch()+1155<-qerjotFetch()+1961<-qerjotFetch()+1961<-qerjotFetch()+1961<-qerflFetch()+135<-qergsFetch()+775<-qervwFetch()+139<-qergsFetch()+775<-qervwFetch()+139<-qerjoFetch()+390<-rwsfcd()+103<-qerhjFetch()+3187<-qercoFetch()+205<-opifch2()+2995<-kpoal8()+2934<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244<-_start()+36

Note:
When I dump the call stack of slowly oracle process , the display is also doing HASH related operations

trace file of formated by tkprof utility

--note execution plan had truncated

PLAN_HASH_VALUE：841592968

Rows		     Operation
---------      ---------------------
38,045	    NESTED LOOPS (cr=134876 pr=0 pw=0 time=286598 us cost=6 size=45 card=1)
38,045	       TABLE ACCESS BY INDEX ROWID ICME_PROJECT_SCORE (cr=53372 pr=0 pw=0 time=160677 us cost=5 size=31 card=1)
38,045	          INDEX RANGE SCAN IDX_ICME_PROJECT_SCORE_M (cr=23985 pr=0 pw=0 time=66046 us cost=4 size=0 card=1) (object id 17468)
38,045	       INDEX UNIQUE SCAN PK_ICME_SUBJECT (cr=81504 pr=0 pw=0 time=93357 us cost=1 size=14 card=1) (object id 17554)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       0.15          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.74       0.74          0     233737          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.89       0.89          0     233737          0           6

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.08          0.09
********************************************************************************

PLAN_HASH_VALUE：3830877904

Rows		     Operation
---------      ---------------------
38,045		      HASH JOIN (cr=4220506 pr=0 pw=0 time=346507995 us cost=216 size=1727775 card=38395)
38,045		         TABLE ACCESS BY INDEX ROWID ICME_PROJECT_SCORE (cr=53338 pr=0 pw=0 time=262176 us cost=5 size=1179395 card=38045)
38,531		            INDEX RANGE SCAN IDX_ICME_PROJECT_SCORE_IDS (cr=23496 pr=0 pw=0 time=102193 us cost=4 size=0 card=1) (object id 17470)
1,771,236,310	              INDEX FAST FULL SCAN PK_ICME_SUBJECT (cr=4167168 pr=0 pw=0 time=231040457 us cost=209 size=4569180 card=326370) (object id 17554)



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.15       0.15          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    371.90     371.97          0    4336909          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    372.06     372.13          0    4336909          0           6


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.05          0.05

Note:
从上面的trace显示sql两次的执行计划发生了改变有NL join 转为 Hash join，解决可以使用 Sql hint 的use_nl 强置指定执行计划， 可是为什么同一sql 在几乎样的环境(资源空闲)里执行会产生两个执行计划，与11G 优化器的游标生成有关， 尝试禁用自适应游标共享(Adaptive Cursor Sharing）

自适应游标共享

绑定变量窥探的副作用在于，使用绑定变量的目标SQL会一直沿用之前硬解析时产生的解析树和执行计划，即使这种策略在当前情况下并不合适。在Oracle 10g及其后续版本中，Oracle会自动收集直方图统计信息。

为了解决绑定变量窥探带来的问题，Oracle在11g中引入了自适应游标共享（Adaptive Cursor Sharing, ACS）。自适应游标共享使得在启用绑定变量窥探的前提下，目标SQL不再必须固守之前硬解析产生的解析树和执行计划，而是能够在多个可能的执行计划之间“自适应”地选择。ACS的核心在于其能够根据具体情况选择最合适的执行计划，从而一定程度上减少绑定变量窥探的副作用。

Oracle根据执行目标SQL时的运行时统计信息（如逻辑读、CPU时间、结果集行数等）以及当前传入的绑定变量值所对应的谓词条件的选择性来判断是否需要重新进行硬解析。执行计划可能会在下次执行该语句时被标记为需要修订，即使底层统计数据没有变化。v$sql中的is_bind_sensitive和is_bind_aware列反映了这一点。前者表示某个特定对象是自适应游标共享的候选对象，后者表示Oracle根据收集的游标信息采取行动并更改执行计划。值得注意的是，自适应游标共享只有在SQL语句至少失败一次后，才有可能导致改进的计划。

自适应游标共享要做的第一件事就是所谓的扩展游标共享（Extended Cursor Sharing），而扩展游标共享做的主要事情就是将目标SQL所对应的Child Cursor标记为Bind Sensitive。Bind Sensitive是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定变量输入值的变化而变化。

当满足如下三个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Sensitive：

• 启用了绑定变量窥探。
• 该SQL使用了绑定变量（不管是该SQL自带的绑定变量，还是开启常规游标共享后系统产生的绑定变量）。
• 该SQL使用的是不安全的谓词条件（例如范围查询，目标列上有直方图统计信息的等值查询等）。

自适应游标共享要做的第二件事情就是将目标SQL所对应的Child Cursor标记为Bind Aware。Bind Aware是指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化。

当满足如下两个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Aware：

• 该SQL所对应的Child Cursor在之前已经被标记为Bind Sensitive。
• 该SQL在接下来连续两次执行时，所对应的runtime统计信息与该SQL之前硬解析时所对应的runtime统计信息均存在较大差异。

对于自适应游标共享而言，V$SQL中的列IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE分别用来表示Child Cursor是否是Bind Sensitive、Bind Aware和共享的。这里“共享”的含义是指存储在该Child Cursor中的解析树和执行计划是否能被重用，一个非共享的Child Cursor中存储的执行计划和解析树是不能被重用的，并且该Child Cursor也会在第一时间从Shared Pool中被清理出去。

Then I try to modify a hidden parameter of ECS,

alter system set "_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL" = NONE

Oracle数据库中自适应游标共享的整体执行流程为如下所示：

（1）当目标SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据一系列条件（如该SQL有没有使用绑定变量，参数CURSOR_SHARING的值是多少，绑定变量所在的列是否有直方图，该SQL的WHERE条件是等值查询还是范围查询等）来判断是否将该SQL所对应的Child Cursor标记为Bind Sensitive。对于标记为Bind Sensitive的Child Cursor，Oracle会把执行该SQL时所对应的runtime统计信息额外地存储在该SQL所对应的Child Cursor中。

（2）当目标SQL第二次被执行时，Oracle会用软解析，并且会重用该SQL第一次执行时所产生的Child Cursor中存储的解析树和执行计划。

（3）当目标SQL第三次被执行时，如果该SQL所对应的Child Cursor己经被标记成了Bind Sensitive，同时Oracle在第二次和第三次执行该SQL时所记录的runtime统计信息和该SQL第一次硬解析时所记录的runtime统计信息均存在较大差异，那么该SQL在第三次被执行时就会使用硬解析，Oracle此时会产生一个新的Child Cursor（这个新的Child Cursor会挂在原Parent Cursor下），并且Oracle会把这个新的Child Cursor标记为Bind Aware。

（4）对于标记为Bind Aware的子游标（Child Cursor）对应的目标SQL，当该SQL再次执行时，Oracle会根据当前传入的绑定变量值对应的谓词条件的选择性，决定使用硬解析还是软解析/软软解析。判断的原则是：如果当前传入的绑定变量值的谓词条件选择性在该SQL之前硬解析时相同谓词条件在V$SQL_CS_STATISTICS中记录的选择性范围内，则Oracle会使用软解析或软软解析，并重用相关子游标中存储的解析树和执行计划；反之，则进行硬解析。

如果进行硬解析且新生成的执行计划与原有子游标中存储的执行计划相同，Oracle不仅会生成一个新的子游标，还会将原有子游标标记为不可共享（即V$SQL中IS_SHAREABLE列的值从Y变为N）。同时，Oracle会对新生成的子游标和原有子游标进行游标合并（即合并存储相同执行计划的游标）。如果进行的是软解析或软软解析，Oracle则会重用相关子游标中存储的解析树和执行计划。

绑定变量窥探所带来的副作用

• 会额外地增加硬解析数量。
• 会额外地增加同一个父游标下子游标的数量，这会增加软解析/软软解析时查找匹配子游标的工作量。
•  为了存储这些额外增加地子游标，共享池（Shared Pool）在空间方面也会承受额外的压力。

According to Oracle, if any of the following checks fail, extended cursor_sharing will be disabled:

1. other hints are used in the query
2. Stored outlines (plan stability) is in-use
3. There are more than 14 binds in the SQL statement
4. Parameters such as bind_peeking=false is set
5. The release of Oracle does not support extended cursor sharing

These parameters seems to be related to ACS/ECS management:

将隐含参数“_OPTIMIZER_EXTENDED_CURSOR_SHARING”和“_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL”的值均设为NONE，这样就相当于关闭了可扩展游标共享。一旦可扩展游标共享被禁，所有的Child Cursor都将不能再被标记为Bind Sensitive，那么自然就不能被标记为Bind Aware，也就是说此时自适应游标共享就相当于被禁掉了。

l 将隐含参数“_OPTIMIZER_ADAPTIVE_CURSOR_SHARING”的值设为FALSE。一旦此隐含参数的值被设为FALSE，则所有的Child Cursor都将不能再被标记为Bind Aware（即使它们己经被标记成了Bind Sensitive），也就是说此时自适应游标共享就被直接禁掉了。

小结

在开发或测试系统中，自适应游标共享和自适应执行计划可能会掩盖在代码投入生产之前需要调查和解决的潜在问题，这就是为什么应该关闭它们的原因。甚至有些人认为这些功能在生产系统中也不应该存在，因为一旦确定了最佳执行计划，就不应该更改，以免遇到意外情况。