From oracle-l-bounce@freelists.org Fri Oct 14 07:56:02 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9ECu0JU004475 for ; Fri, 14 Oct 2005 07:56:01 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9ECtXvX004302 for ; Fri, 14 Oct 2005 07:55:33 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C06E01FEC09; Fri, 14 Oct 2005 07:55:25 -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 01321-07; Fri, 14 Oct 2005 07:55:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3AD4B1FE5B8; Fri, 14 Oct 2005 07:55:25 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=OgMTHZ27qpqgjurFk8FDXnWFHzW8UmcGHoP4xOTwE5lRSS57fyDGa0x/PYd9CWEFbVlAYFNGbcgiVMerJcyLcRI7z3kdryPRiOZVqXIDcPUPpzbsVi3afbHlHBiVLceB23aeY1yYdYeXK47DNX5EfSnu3Hu5uO55FUtRbG77eZk= Message-ID: <9eb766040510140553h36619c6fq73b3ea2000fed7b8@mail.gmail.com> Date: Fri, 14 Oct 2005 18:23:27 +0530 From: Sanjay Madan To: Christian Antognini Subject: Re: autotrace issue Cc: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_23237_9324969.1129294407405" References: X-archive-position: 26986 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: madan.sanjay@gmail.com Precedence: normal Reply-To: madan.sanjay@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.8 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_Part_23237_9324969.1129294407405 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Chris, any input that i give will exist ONLY in one of the partitions. So if 12345= 6 exists, it will be only in one partition. Same for 1234561 ... In this specific case, 12345 has just 100 rows in (10million/5) rows partition. And 1234561 doesnt exist at all. The max number of rows that can exist for field1 in a partition ( and only in one partition) will not excee= d 500. the table is list-partitioned on field2 and field2 is also an input in the query, and its constant for both the queries . ( I should have mentioned this in my initial question.. sorry !) So there is no-way it should be doin= g a FTS of the entire table. using a bind variable returns an index scan output. There is an associated problem too. I am not sure whether the 2 are related= . An autotrace output of FTS or INDX scan doesn't seem to actually guarantee an actual run as per the autotrace plan. i set db_file_multiblock_read_count to 16 with an alter session ( its 32 right on on the db). THAT makes the autotrace output show an INDX scan for the same 1234561 for which it shows a FTS with a db_file_multiblock_read_count =3D 32. (block_size is 8192). HOWEVER, the actual run timings for both the runs are quite similar. But if I specify an Index-hint, it runs in a fraction of that time ( irrespective of whether the db_f_m_r_c is set to 16 or 32 ... sanjay On 10/14/05, Christian Antognini wrote: > > Sanjay > > >I have tried values > >-both within and outside the field lengths of field1 > >-values actually existing in the table > >-values NOT existing in the table. > > Which is the distribution of data in field1? Do you have a histogram on > it? > > >It seems to be happening randomly. For most values it shows an index > range scan. > >For others (fewer) it shows a full table scan. > > It's probably because data is not evenly distributed (I don't speak of th= e > distribution at partition level... but the data in field1). If the follow= ing > queries return very different values it could be good that the CBO genera= tes > different plans: > > select count(*) from ... where field1 =3D 123456 > select count(*) from ... where field1 =3D 1234561 > > > HTH > Chris > > New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com > Italiano: Lugano (24-Nov) > Fran=E7ais: Gen=E8ve (17-Nov) > Deutsch: M=FCnchen (20-Oct), Basel (25-Oct), Frankfurt (27-Oct), > D=FCsseldorf (23-Nov), Z=FCrich (29-Nov), Stuttgart (13-Dec) > ------=_Part_23237_9324969.1129294407405 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Chris,


any input that i give will exist ONLY in one of the partitions. So if  123456 exists, it will be only in one partition. Same for 1234561 ...

In this specific case, 12345 has just 100 rows in (10million/5)  rows partition. And 1234561  doesnt exist at all. The max number of rows that can exist for field1 in a partition ( and only in one partition)  will not exceed 500.

the table is list-partitioned on field2 and field2 is also an  input in the query, and its constant for both the queries . ( I should have mentioned this in my initial question.. sorry !)  So there is  no-way it should be doing a FTS of the entire table.

using a bind variable returns an index scan output.

There is an associated problem too. I am not sure whether the 2 are related= .
An autotrace output of FTS or INDX scan doesn't seem to actually guarantee = an actual run as per the autotrace plan.

i set db_file_multiblock_read_count to 16 with an alter session ( its 32 right on on the db). THAT makes the autotrace output show an INDX scan for the same 1234561 for which it shows a FTS with a db_file_multiblock_read_count =3D 32.
(block_size is 8192). HOWEVER, the actual run timings for both the runs are= quite similar.
But if I specify an Index-hint, it runs in a fraction of that time ( irrespective of whether the db_f_m_r_c is set to 16 or 32 ...



sanjay


On 10/14/05, Christian Antognini <Christian.Antognini@trivadis.com> wrote: Sanjay

>I have tried values
>-both within and outside the f= ield lengths of field1
>-values actually existing in the table
>= ;-values NOT existing in the table.

Which is the distribution of dat= a in field1? Do you have a histogram on it?

>It seems to be happening randomly.  For most values i= t shows an index range scan.
>For others  (fewer) it shows = a full table scan.

It's probably because data is not evenly distributed (I don't speak of the distribution at partition level... but the data in field1). If the following queries return very different values it could be good that the CBO generates different plans:

select count(*) from ... where fi= eld1 =3D 123456
select count(*) from ... where field1 =3D 1234561

HTH
Chris

New Features Oracle Database 10g Release 2 seminar= s @=20 www.trivadis.com
Italiano: Lugan= o (24-Nov)
Fran=E7ais: Gen=E8ve (17-Nov)
Deutsch: M=FCnchen (20-Oct),= Basel (25-Oct), Frankfurt (27-Oct),
      = ;   D=FCsseldorf (23-Nov), Z=FCrich (29-Nov), Stuttgart (13-Dec)

------=_Part_23237_9324969.1129294407405-- -- http://www.freelists.org/webpage/oracle-l