Re: consistent reads and unique key updates

From: Ls Cheng <exriscer_at_gmail.com>
Date: Thu, 26 Apr 2012 07:26:27 +0200
Message-ID: <CAJ2-Qb-w7acpkXG-+AgwfyF2mR2kZGzVJmq1ubgY6jxpFAT7sA_at_mail.gmail.com>

Hi
Forgot to mention the version, was going to ut at the end of first post but missed it. It is 10.2.0.5.

About the execution plan. Query plan is

Plan hash value: 4024650034

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

---

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0)|
00:00:01 |

---

Update plan

Plan hash value: 1968341081

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

---

| 0 | UPDATE STATEMENT | | 1 | 8 | 1 (0)| 00:00:01
|
| 1 | UPDATE | EMP | | | |
|
|* 2 | INDEX UNIQUE SCAN| EMP_PK | 1 | 8 | 0 (0)| 00:00:01
|

---

About the "switch current to new buffer" mechanism, I checked the difference of this statistic in the query session which showed 0, in the update session 2 when the SELECT FROM EMP did INDEX UNIQUE SCAN. If I force the query FTS the statistics in the update session becomes 0 and data blocks consistent reads - undo records applied 1 in the query session. So the update session actually changed it's behaviour when the query session was doing FTS or index unique scan.... uhh

Pretty interesting

Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 26 2012 - 00:26:27 CDT