Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28655 invoked from network); 10 Dec 2007 16:03:34 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 10 Dec 2007 16:03:34 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 40CD97D6760; Mon, 10 Dec 2007 17:03:34 -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 31550-06; Mon, 10 Dec 2007 17:03:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A45AB7D63F3; Mon, 10 Dec 2007 17:03:33 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 10 Dec 2007 16:16:37 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 674B17D5FA8 for ; Mon, 10 Dec 2007 16:16:37 -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 17359-03 for ; Mon, 10 Dec 2007 16:16:37 -0500 (EST) Received: from troll.tpk.net (mail.tpk.net [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7C63A7D5CF4 for ; Mon, 10 Dec 2007 16:16:35 -0500 (EST) Received: from MWF600XL (216-107-223-2.RightSizing.tpk.static.cust.seg.net [216.107.223.2]) by troll.tpk.net (8.13.6/8.12.11) with ESMTP id lBALGStC005997; Mon, 10 Dec 2007 16:16:33 -0500 From: "Mark W. Farnham" To: , References: <578806DDE66A3A45916740EB73C6982AB42BB5D391@M1EXCHANGE01.mmi.local> Subject: RE: Operations that perform multiblock I/O and cluster factor Date: Mon, 10 Dec 2007 16:18:12 -0500 Message-ID: <009c01c83b72$2c499730$1100a8c0@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_009D_01C83B48.43738F30" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: <578806DDE66A3A45916740EB73C6982AB42BB5D391@M1EXCHANGE01.mmi.local> X-archive-position: 3823 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-to: mwf@rsiz.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 ------=_NextPart_000_009D_01C83B48.43738F30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Sounds like a slightly twisted restatement of the fact that some things that could logically be done as an index range scan are converted to a FFS because the optimizer determines it will be cheaper to read the whole thing than to walk the tree for the required range. Thus having been converted to a FFS, you'll see scattered reads when you thought it might be doing sequential reads to service what is logically a range scan. If your co-worker has a case where what you reported was contended can be shown, I'd like to see it. I hope there is more light than heat in this debate. I'm not clear how Oracle would guess which blocks to multiblock read to perform an index range scan, unless it reads them all, which it should have reported as a FFS to begin with. _____ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Josh Collier Sent: Monday, December 10, 2007 2:15 PM To: oracle-l@freelists.org Subject: Operations that perform multiblock I/O and cluster factor Hi, Having a bit of a debate with a peer. Perhaps you can help me. Its my understanding that the only i/o operations that trigger multiblock I/O, i.e. db file scattered read i/o that is influenced by the multi block read count init parameter, are as follows 1. full table scan 2. index fast full scan All other block access paths use db file sequential read, aka single block reads from the disk. Are there any other access paths that are served by multiblock I/O? My co-worker contends: If the range scan is not a full scan, the query optimizer will decide whether to use a scattered read depending on the fraction of rows in the scan and the cluster factor of the index. I'm very curious about this because there isn't clear documentation anywhere about this. Thanks, Josh C. ------=_NextPart_000_009D_01C83B48.43738F30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Sounds like a slightly twisted = restatement of the fact that some things that could logically be done as an index = range scan are converted to a FFS because the optimizer determines it will be = cheaper to read the whole thing than to walk the tree for the required range. = Thus having been converted to a FFS, you’ll see scattered reads when = you thought it might be doing sequential reads to service what is logically = a range scan.

 

If your co-worker has a case where = what you reported was contended can be shown, I’d like to see it. I = hope there is more light than heat in this debate. I’m not clear how Oracle = would guess which blocks to multiblock read to perform an index range scan, = unless it reads them all, which it should have reported as a FFS to begin with. =

 

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Josh Collier
Sent: Monday, December = 10, 2007 2:15 PM
To: = oracle-l@freelists.org
Subject: Operations that = perform multiblock I/O and cluster factor

 

Hi,

 

Having a bit of a debate with a peer. Perhaps you can = help me.

 

Its my understanding that the only i/o operations = that trigger multiblock I/O, i.e. db file scattered read i/o that is = influenced by the multi block read count init parameter, are as = follows

1. full table scan

2. index fast full scan

 

All other block access paths use db file sequential = read, aka single block reads from the disk.

 

Are there any other access paths that are served by multiblock I/O? My co-worker contends:  If the range scan is not a full scan, the query optimizer will decide = whether to use a scattered read depending on the fraction of rows in the scan and = the cluster factor of the index.

 

I'm very curious about this because there = isn't clear documentation anywhere about this.

 

Thanks,

 

Josh C.

 

------=_NextPart_000_009D_01C83B48.43738F30-- -- http://www.freelists.org/webpage/oracle-l