oracle row cr,关于ROW CR特性_ROW_CR

注：实际上这类问题已经登记过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.