Re: autotrace issue
- From: Sanjay Madan <madan.sanjay@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 Oct 2005 11:01:26 +0530
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@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@xxxxxxxxxxxxx> 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>
>
Other related posts:
