Re: autotrace issue

From: Sanjay Madan <madan.sanjay_at_gmail.com>
Date: Mon, 17 Oct 2005 11:01:26 +0530
Message-ID: <9eb766040510162231w8442f3n518c513053a3564c@mail.gmail.com>

Chris/ Wolfgang,

Thanks for your replies.

Chris,
U are right .. It isnt a FTS, its a Full Partition Scan. The autotrace output is:
TABLE ACCESS (FULL) OF 'PART_TAB' (Cost=8412 Card =19 Bytes=2109)

The plan_table clearly shows thats it will scan just one partition and not the full table.

Still that behaviour is not as expected. The table access(full) run takes about 1:56 mins .. whereas if i give the index hint, it takes about 0:35 mins. ( I had initially thought that this happens only when the input value is a non-existent one.. maybe thats forcing a full-partn-scan. But thats not the case. It happens randomly even for values that do exist in the table.).

Wolfgang,

user_at_DB1 > show parameter cursor

NAME TYPE VALUE

------------------------------------ ----------- ------

cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 1000
session_cached_cursors integer 0

I collect statistics using the following ( once a week) :

dbms_stats.gather_schema_stats(ownname=> 'DBOWNER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Regards

Sanjay

On 10/14/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> You don't, by any chance, have set cursor_sharing to force or similar?
> How did you gather the table statistics? "options=>'gather auto'" ?
>
> Sanjay Madan wrote:
>
> > Chris,
> >
> >
> > 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 ...
> >
>
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com <http://www.centrexcc.com>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 17 2005 - 00:34:10 CDT