Re: Consistent reads strange behavior.

From: <J.Velikanovs_at_alise.lv>
Date: Sun, 10 Oct 2004 22:55:52 +0300
Message-ID: <OF887403F7.5FEA8C01-ONC2256F29.006CF22A-C2256F29.006DA1F3@alise.lv>

The same testcase except “set transaction read only”
>1. Step Nr. 5. Why Oracle can’t use already created copy of the block
>during step Nr.4?

Oracle use already created copy of the block.
>2. Step Nr. 4. Why there “52 redo size” during the CR?
Nothing is like in first test here.

Test case

2 DROPME:jozh> set transaction read only;

Transaction set.

DROPME:jozh> select * from testcr;

10000

Statistics

          7  consistent gets
          0  redo size

4 DROPME:jozh> /

10000

Statistics

      10002  consistent gets
          0  redo size

5 DROPME:jozh> /

10000

Statistics

          7  consistent gets
          0  redo size

On 10.10.2004 22:44:51 oracle-l-bounce wrote:

>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

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

Received on Sun Oct 10 2004 - 14:53:36 CDT