Re: consistent reads and unique key updates
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Thu, 26 Apr 2012 07:26:27 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
