Re: Oracle Read Consistent Overhead
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 17 Apr 2011 11:18:08 +0100
Message-ID: <761FFB0EDEA14B099DE5DE5C5330D319_at_Primary>
]----- Original Message -----
]From: "Matt McClernon" <mccmx_at_hotmail.com>
]To: <oracle-l_at_freelists.org>
]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
Date: Sun, 17 Apr 2011 11:18:08 +0100
Message-ID: <761FFB0EDEA14B099DE5DE5C5330D319_at_Primary>
]----- Original Message -----
]From: "Matt McClernon" <mccmx_at_hotmail.com>
]To: <oracle-l_at_freelists.org>
]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
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Apr 17 2011 - 05:18:08 CDT