Oracle Read Consistent Overhead
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 17 Apr 2011 10:06:07 +0000
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
