Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: autotrace issue

Re: autotrace issue

From: Sanjay Madan <madan.sanjay_at_gmail.com>
Date: Fri, 14 Oct 2005 18:23:27 +0530
Message-ID: <9eb766040510140553h36619c6fq73b3ea2000fed7b8@mail.gmail.com>


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 = 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_at_trivadis.com> 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 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 field1 = 123456
> select count(*) from ... where field1 = 1234561
>
>
> HTH
> Chris
>
> New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com<http://www.trivadis.com>
> Italiano: Lugano (24-Nov)
> Français: Genève (17-Nov)
> Deutsch: München (20-Oct), Basel (25-Oct), Frankfurt (27-Oct),
> Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 14 2005 - 07:56:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US