Consistent reads strange behavior.

From: <J.Velikanovs_at_alise.lv>
Date: Sun, 10 Oct 2004 22:44:51 +0300
Message-ID: <OFB0D60856.FE5925DB-ONC2256F29.006C3293-C2256F29.006C9FC1@alise.lv>

Hi All,
Take a look on the testcase below.
Couple of things seems strange to me in this testcase: 1. Step Nr. 5. Why Oracle can’t use already created copy of the block during step Nr.4?
2. Step Nr. 4. Why there “52 redo size” during the CR? Anybody have idea?

Jurijs

---

Test case

---

Tested on 9.2.0.4/5 Win32/Linux

• 1
• Make test drop table testcr; create table testcr (n number) tablespace users; insert into testcr values (1); commit;
• 2
• So far so good DROPME:jozh> set autotrace on stat DROPME:jozh> select * from testcr;

         N

---

         1

Statistics

---

…

          7  consistent gets
          0  redo size

…

• 3
• In parallel session run begin for f in 1..10000 loop update testcr set n=f; end loop; end; /
• 4
  -- “10008 consistent gets” understandable. Session create copy of the
  block and apply UNDO 10000 times. DROPME:jozh> select * from testcr;

         N

---

         1

Statistics

---

…

      10008  consistent gets
         52  redo size

…

• 5
  -- “10008 consistent gets” Seams very strange for me. There copy of the
  block already. DROPME:jozh> select * from testcr;

         N

---

         1

Statistics

---

…

      10008  consistent gets
         52  redo size

…

• 6
• In parallel session run commit;
• 7
• It’s OK. DROPME:jozh> select * from testcr;

         N

---

     10000

Statistics

---

…

          7  consistent gets
          0  redo size

…
--

http://www.freelists.org/webpage/oracle-l Received on Sun Oct 10 2004 - 14:42:22 CDT