关于ROW CR特性_ROW_CR

ROW CR确实是10g以后引入的一个新特性，该特性针对fetch by key的数据访问优化减少一致性读Consistent read。但该特性也造成了一些问题 例如出现ORA-600错误、SQL性能下降等。

 有不少客户选择关闭了 “_ROW_CR”特性， 其性能影响主要体现在 fetch by key的查询的逻辑读可能略微上升。

注：实际上这类问题已经登记过bug 10425196，但最后oracle认定为“not a bug”

From the customers point of view, the root cause of this is the ROW_CR optimization. ROW_CR is enabled by default.

Solution:
Either
require some sort of application changes to avoid such issue;
OR
go back to the original behavior where row_cr is not implemented. To this you would need to run with _row_cr=false.
The developers explain that this is not a bug but an intended behavio

The behavior you are seeing is indeed due to ROW_CR. This optimisation
was brought with the aim of reducing consistent read rollbacks. What
happens is that for a transaction doing a query that has at least
one “fetch-by-key” row-source in it, we advance the snapshot forward; So,
in this example execution of the cursor for the select using the index
picks a snapshot with an scn of (lets call it) “S1” with row-cr turned
on. “S1” is then advanced (across the commit of the update) to “S2″ due to
ROW_CR. That’s why in your testcase the fetches from the cursor pick values
post-commit.

Q1. If disable parameter _row_cr, will it impact the database performance and function which is upgraded from 10g(10.2.0.4 and before) to 11g(11.2.0.3)?
A1. Disabling row_cr has no impact to function but it maybe impact performance.
_ROW_CR is only applicable to queries which use an unique index to determine the row in the table.
The most promising direction of the fix is to reduce the number of Cleanouts and rollbacks by doing ROW CR on the index blocks for Fetch BY Key operations.

The default value of _ROW_CR in 10.2.0.4 or lower is false (non-RAC). Turn off of this optimization in 11g so that things will work exactly as they used to work in 10.2.0.4.

Q2. To RAC, in which version _row_cr is set to true by default?
A2: It is from release 10.2.0.1

Q3. If we disable _row_cr, in which scenario will cause performance issue?
A3. Disabling row_cr could impact the whole database, but the degree of the impact will depend on how much consistent read (where we have to generate undo) the application does.
Monitoring consistent read undo requests would be necessary to really determine the extent of this.

If a block is modified heavily by one application, which does not commit for a long time, all queries on non modified records in the same block by other sessions have
to do a lot of CR rollback. The upcoming SQLs, which access the same block and are using INDEX UNIQUE SCAN, will be impacted and will need extra rollbacks to construct a CR block.

In RAC, when a select has to perform consistent read potentially you have to construct undo from the local and remote instances.
Potentially if a large number of index blocks have been changed then you can arrive at a situation where there’s a lot of cross instance shipping of blocks going on.

Q4. If we disable _row_cr, what’s the possible impact can be seen in AWR report?(RAC/Non RAC)
A4. In AWR part Instance Activity Stats,”CR blocks created” and “deferred (CURRENT) block cleanout applications” maybe will be increased.

（1）该问题是row cr特性导致，且该特性是一个“优化”，不是“bug”。如果不需要使用该特性，可以考虑通过设置_row_cr=false.关闭该特性。
（2）对于从10g升级到11g的单实例，可以关闭该特性，没有功能和性能方面的影响。
（3）对于从10g升级到11g的RAC，由于10g RAC默认是开启该特性的，是否在11g中关闭该特性，需要分析可能存在的隐患。