Oracle Read Consistent Overhead

11.1.0.7 EE on Linux x86_64
One of our production databases is experiencing significant materialized view 
contention (enqueue JI waits).
Digging into the problem has revealed that the MV refresh process is executing 
SQL that appears to be visiting too many consistent read blocks.  The SQL is 
visiting many more times the total number of blocks on the MV log segment 
(called MLOG$_RECS) for each execution.  This seems too high.  
I simulated  the situation by executing a medium sized un-commited transaction 
on the master table in the staging database and then in a second session 
executed a small transaction followed by a commit to see how many blocks the 
second (commiting) transaction visited in order to build the read consistent 
image of the MV log.
This showed that the 2nd session visited over 70 times the number of blocks on 
the MV (total segment blocks: 7500):
SQL ID: c8k2aj3stv887
Plan Hash: 4287808183
delete from "ORER"."MLOG$_RECS" 
where
snaptime$$ <= :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.44 1.46 0 540682 2 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.44 1.47 0 540682 2 2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE MLOG$_RECS (cr=540682 pr=0 pw=0 time=0 us)
2 TABLE ACCESS FULL MLOG$_RECS (cr=540682 pr=0 pw=0 time=4 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers chains 4 0.00 0.00
buffer busy waits 287 0.00 0.00
Is this normal behaviour..?  it seems too high for a simple CR of one segment.  
                                  --
http://www.freelists.org/webpage/oracle-l


Other related posts: