Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 717 invoked from network); 11 Dec 2007 10:45:51 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 11 Dec 2007 10:45:48 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E83C7D667D; Tue, 11 Dec 2007 11:45:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25675-09; Tue, 11 Dec 2007 11:45:45 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1A4E87D6BD7; Tue, 11 Dec 2007 11:45:45 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 11 Dec 2007 10:58:49 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0F75E7D7DF9 for ; Tue, 11 Dec 2007 10:58:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18828-08 for ; Tue, 11 Dec 2007 10:58:48 -0500 (EST) Received: from mailx2.trivadis.com (gromit.trivadis.com [212.249.206.2]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9428F7D7E31 for ; Tue, 11 Dec 2007 10:58:48 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Operations that perform multiblock I/O and cluster factor Date: Tue, 11 Dec 2007 16:58:56 +0100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Operations that perform multiblock I/O and cluster factor References: <20071211080901.C95A37D6D74@turing.freelists.org> From: "Christian Antognini" To: "Josh Collier" Cc: X-archive-position: 3841 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Christian.Antognini@trivadis.com Precedence: normal Reply-to: Christian.Antognini@trivadis.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Hi Josh To improve the performance of nested loops prefetching is available. - With prefetching of index blocks multi-block reads are performed during index range and unique scans. - With prefetching of data blocks multi-block reads are performed during ROWID accesses. I have a test case with which I can reproduce it at will. Here a trace file snip: ===================== PARSING IN CURSOR #3 len=99 dep=0 uid=27 oct=3 lid=27 tim=1169311377812522 hv=1565697230 ad='30915040' SELECT /*+ ordered use_nl(b) index(a) index(b) */ a.*, b.* FROM a, b WHERE a.a1 = b.b2 AND a.a1 > 0 END OF STMT PARSE #3:c=4000,e=4181,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1169311377812496 EXEC #3:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1169311377825710 WAIT #3: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=269 tim=1169311377825712 WAIT #3: nam='db file scattered read' ela= 11 file#=8 block#=49 blocks=8 obj#=10378 tim=1169311377826333 WAIT #3: nam='db file sequential read' ela= 2 file#=8 block#=34 blocks=1 obj#=10376 tim=1169311377826349 WAIT #3: nam='db file scattered read' ela= 0 file#=8 block#=73 blocks=8 obj#=10381 tim=1169311377826352 WAIT #3: nam='db file sequential read' ela= 18 file#=8 block#=42 blocks=1 obj#=10377 tim=1169311377826558 FETCH #3:c=1000,e=532,p=18,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1169311377826688 WAIT #3: nam='SQL*Net message from client' ela= 476 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377827408 WAIT #3: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377828791 WAIT #3: nam='db file scattered read' ela= 0 file#=8 block#=35 blocks=6 obj#=10376 tim=1169311377833310 WAIT #3: nam='db file scattered read' ela= 35 file#=8 block#=43 blocks=6 obj#=10377 tim=1169311377833702 FETCH #3:c=5999,e=6379,p=12,cr=19,cu=0,mis=0,r=15,dep=0,og=1,tim=1169311377834 168 WAIT #3: nam='SQL*Net message from client' ela= 3325 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377837515 WAIT #3: nam='SQL*Net message to client' ela= 36 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377838572 FETCH #3:c=6999,e=6670,p=0,cr=18,cu=0,mis=0,r=14,dep=0,og=1,tim=11693113778443 19 WAIT #3: nam='SQL*Net message from client' ela= 5661 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377850609 STAT #3 id=1 cnt=30 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=41 pr=30 pw=0 time=573 us)' STAT #3 id=2 cnt=30 pid=1 pos=1 obj=10376 op='TABLE ACCESS BY INDEX ROWID A (cr=8 pr=15 pw=0 time=2790 us)' STAT #3 id=3 cnt=30 pid=2 pos=1 obj=10378 op='INDEX RANGE SCAN A1_I (cr=3 pr=8 pw=0 time=1550 us)' STAT #3 id=4 cnt=30 pid=1 pos=2 obj=10377 op='TABLE ACCESS BY INDEX ROWID B (cr=33 pr=15 pw=0 time=8619 us)' STAT #3 id=5 cnt=30 pid=4 pos=1 obj=10381 op='INDEX UNIQUE SCAN B2_I (cr=3 pr=8 pw=0 time=4043 us)' WAIT #0: nam='SQL*Net message to client' ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377863627 WAIT #0: nam='SQL*Net message from client' ela= 532 driver id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377864596 ===================== The object IDs are the following: SQL> SELECT object_id, object_name 2 FROM user_objects 3 WHERE object_name IN ('A','B','A1_I','B2_I'); OBJECT_ID OBJECT_NAME ---------- --------------------------------------- 10376 A 10378 A1_I 10377 B 10381 B2_I If you check the IDs you can see that even if the execution plan contains only ROWID accesses and INDEX RANGE/UNIQUE SCAN only scattered reads are performed. The two single-block reads are used for the header of the tables. HTH Chris -- http://www.freelists.org/webpage/oracle-l