Re: can uncommited update influence the performance of select???

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 31 Mar 2003 23:56:27 +1000
Message-ID: <9DXha.3826$1s1.53088@newsfeeds.bigpond.com>

<zhangguoping_at_boco.com.cn> wrote in message news:b61k7t$1k2i$1_at_mail.cn99.com...
>

---

> **
> The next example illustrates the read consistency trap. Without knowing
that
> an
> uncommitted transaction had made a series of updates to the NAME column it
> is
> very difficult to see why so many block visits would be incurred.
>
> Cases like this are not normally repeatable: if the process were run
again,
> it is
> unlikely that another transaction would interact with it in the same way.
> SELECT name_id
> FROM cq_names
> WHERE name = ¡¯FLOOR¡¯;
> call count cpu elapsed disk query current rows
> ---- ----- --- ------- ---- ----- ------- ----
> Parse 1 0.10 0.18 0 0 0 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 1 0.11 0.21 2 101 0 1
>

---

>
> how to explain the example??

Hi There,

If a row you are trying to read is "dirty" in that it has been updated but not committed (or has been committed after the time of the select starting), you are forced to perform additional logical I/Os in order to get the consistent data out of the appropriate undo segment. If you are reading many rows and many of these buggers require these consistent reads then your read performance is potentially going to suffer.

So yes, uncommitted data (or data that has been committed after the time of the select starting) can influence the performance of a select.

Make sense ?

Cheers

Richard Received on Mon Mar 31 2003 - 07:56:27 CST