From oracle-l-bounce@freelists.org Sun Oct 10 14:42:22 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9AJgL428117 for ; Sun, 10 Oct 2004 14:42:21 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9AJgLI28112 for ; Sun, 10 Oct 2004 14:42:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 17F8172C33B; Sun, 10 Oct 2004 14:48:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32522-80; Sun, 10 Oct 2004 14:48:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 75F4272C535; Sun, 10 Oct 2004 14:48:27 -0500 (EST) In-Reply-To: To: oracle-l@freelists.org Subject: Consistent reads strange behavior. MIME-Version: 1.0 Message-ID: From: J.Velikanovs@alise.lv Date: Sun, 10 Oct 2004 22:44:51 +0300 X-MIMETrack: Serialize by Router on ROSS/IT ALISE/LV(Release 5.0.11 |July 24, 2002) at 2004.10.10 22:45:12, Serialize complete at 2004.10.10 22:45:12 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 10875 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: J.Velikanovs@alise.lv Precedence: normal Reply-To: J.Velikanovs@alise.lv X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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