RE: Oracle Read Consistent Overhead

From: Matt McClernon <>
Date: Wed, 20 Apr 2011 00:12:09 +0000
Message-ID: <COL117-W5888B7C5DB8F5F887AF059B7930_at_phx.gbl>

> Read-consistency costs are largely about the number of undo records applied, not
> about the number of blocks in the underlying object, and the number of undo
> records is (generally) related to the number of changes, which often means
> number of rows.

Ignore my last post. I re-ran the test under more controlled (isolated) conditions and the CR overhead is identical with and without the index on the MV log. The ratio for both is 2 CRs per row update which seems perfectly acceptable based on your feedback that consistent reads are related to row changes rather than blocks changes. My first test results must have been polluted by another session/job. I did notice that the MV refresh process redundantly updates the SNAPTIME$$ column for the rows which it propagates to the MV before proceeding to delete those same rows. I guess this must be a side effect of Oracle using the same code path for both 'on demand' and 'on commit' refresh MVs. Thanks very much for your help

Received on Tue Apr 19 2011 - 19:12:09 CDT

Original text of this message