]----- Original Message ----- ]From: "Matt McClernon" <mccmx@xxxxxxxxxxx>
]To: <oracle-l@xxxxxxxxxxxxx> ]Sent: Sunday, April 17, 2011 11:06 AM ]Subject: Oracle Read Consistent Overhead ]]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 How large was the "medium-sized" transaction.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.
Regards Jonathan Lewis http://jonathanlewis.wordpress.com -- //www.freelists.org/webpage/oracle-l