Oracle Read Consistent Overhead
Date: Sun, 17 Apr 2011 10:06:07 +0000
Message-ID: <COL117-W284EB4BBD8127E2FB0C55DB7AE0_at_phx.gbl>
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 Received on Sun Apr 17 2011 - 05:06:07 CDT