Oracle 升级到12c和CBO/计划麻烦

Hi Guys!

We are on the way to upgrade from Oracle 11 to 12c. Now we face some Troubles with CBO and SQL-Execution Plans.

Some Basic Information:
OLTP-System (Primary) with nearly all Statements using Bind-Variables
We have disabled Bind Peeking and create stats without Histograms (SIZE 1) (_optim_peek_user_binds=FALSE)

Major Problem now: Several Statements (a bit complex views using plsql collection based tables) "produce" many different plans permanently, and Oracle decides for the worst.
Statements are fired by the same application "unchanged", except of course different bind values given. I compared opt-session-enc etc. but it's all the same. There are also no invalidations which would force a reparse.

Major Problem "i guess" is, that Oracle marks this Problem Statements as IS_SHAREABLE='N' and creates an own Version for each Session.
So i find "n"-entries in v$sql which are all exact the same, but of course with several different plans (and 99% of them are a Desaster).

So my Question is:    
Why does Oracle not recognize this Statements as shareable anymore and what can i do against it ? 

We have about 2 Million distinct Statements running in this System, but only ~ 5-10 face this Problem. But These are our most important ones..
so i can't ignore that, performance varies from 0.01 up to 2000 seconds per execution..

regards
max

嗨，麦克斯，

在Oracle数据库12c中，优化器可能将语句标记为IS_SHARABLE = 'N' 的原因有很多。

最可能的原因是此语句正在启动重新优化或基数反馈。

基数反馈最初是在Oracle数据库11g中引入的，它仅查看单个表基数估计。在优化过程中，会记录已知质量较低的某些类型的估计值 (例如，缺少统计信息的表或具有复杂谓词的表的估计值)，并对产生的游标启用监视。如果为游标启用了基数反馈监视，则在执行结束时，将计划中的某些基数估计与执行期间看到的实际基数进行比较。如果发现其中一些估计与实际基数有很大不同，则将存储正确的估计值以供以后使用。下次执行查询时，将再次对其进行优化，这次优化器使用校正后的估计值代替其通常的估计值。有关11g中基数反馈的更多信息，请参见https://blogs.oracle.com/optimizer/entry/cardinality_feedback

从12c开始，基数反馈被扩展到查看join和group by基数估计以及单表基数，这意味着与11g相比，它可能会启动更多的语句。它也被重命名为重新优化。有关重新优化的更多信息，请查阅12c优化器白皮书http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf或者在优化器博客上https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the

您可以通过在v $ SQL中检查列IS_REOPTIMIZABLE的值来确认重新优化是否是问题。

在oracle数据库12.1或12.1.0.2中，可以通过设置参数OPTIMIZER_ADAPTIVE_FEATURES = FALSE来禁用重新优化。

在Oracle数据库中，默认情况下会自动禁用12.2重新优化，因为两个新参数OPTIMIZER_ADAPTIVE_PLANS和OPTIMIZER_ADAPTIVE_STATISTICS替换了参数OPTIMIZER_ADAPTIVE_FEATURES。重新优化由OPTIMIZER_ADAPTIVE_STATISTICS控制，默认设置为FALSE。

如果您希望Oracle Database 12c第1版中的优化器自适应功能的控制方式与Oracle Database 12.2中的控制方式类似，则可以按照MOS注释 (Oracle Database 12c第1版中的自适应功能的建议 (12.1) (Doc ID 2187449.1)) 中的特定说明进行操作。

如果不是导致问题的重新优化，则可以通过使用语句的SQL_ID查询V $ SQL_SHARED_CURSOR来确定为什么将游标标记为IS_SHARABLE = 'N'，如下所示

SELECT * 
FROM V$SQL_SHARED_CURSOR 
WHERE sql_id='xxxxxxxx';

查询将返回很多列值，其中大部分将是 'N'。您正在寻找值为 'Y' 的列。您可以在SQL参考指南中查找该列的含义，然后解决根本原因。