Hi
your 9i plan thinks that (unless my maths or reading of the plan is wrong)
that it will return 78mb of data across the dblink. Is this accurate? If so
you might wish to use the driving site hint to do most of the processing on
the remote table.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"SAP BASIS Consultant" <basis_consultant_at_hotmail.com> wrote in message
news:dd2036f3.0312230922.38ccbf43_at_posting.google.com...
> Hi,
>
> We migrated parts of an 8i database to 9i, as a test. The OS is
> AIX v5.2. We did so by creating a new 9i instance and then
> exporting and importing selected data. The test worked; all
> of the necessary data and indexes came across, etc..
>
> However, one query takes some 600 ms. to execute in 8i, and
> about 35 sec. in 9i. No rows are returned in either 8i or 9i.
> Any ideas as to why this occurs would be greatly appreciated.
>
> The query is on a table, which I renamed 'table_empty' (For the
> purposes of this posting), containing no data and an index PK_PM,
> as well as on a view (Which I renamed 'vtv_view') which contains
> a link to another Oracle database (The 8i DB links to another
> 8i DB, and the 9i DB links to another 9i DB). The links work well.
> The data is consistent between 8i and 9i.
>
> The view contains a 'Group By' clause, which is contained in the 9i
> 'Explain Plan', but not the 8i one.
>
> I run histograms on 'table_empty' in 9i, and hanged
> 'optimizer_max_permutations' to 20000, as I thought that
> perhaps 2000 does not allow the CBO to pick up the correct path.
> ('optimizer_mode' is 'Choose'). The size of the indexes is
> about the same in 8i and 9i.
>
> The statistics are up to date in both 8i and 9i.
>
> I am enclosing the query as displayed by 'tkprof' (With some
> column names changed). 'Tkprof' displayed almost all of the
> time as occuring in the 'Fetch' phase (Probably due to the
> link), as well as the 'Explain Plan' from 8i and 9i.
>
> It seems to me like in 8i, the CBO seems to recognize that
> no rows will be returned, but not so in 9i.
>
> Is there any use in increasing max_permutations to more
> than 20000 in 9i? It seems to me that 20000 should be more
> than enough to determine the correct path.
>
>
> Thanks,
> SAP BASIS Consultant
>
> ***********************************************************************
>
> SELECT tv_yr
> FROM
> vtv_view WHERE 1=2 union select distinct vtv.tv_yr from vtv_view vtv,
> table_empty pm where (number) = pm.acc_number and pm.tax_number =
> vtv.tv_platform order by tv_yr desc
>
> 8i Explain Plan:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=101 Bytes=1329)
> SORT (UNIQUE) (Cost=9 Card=101 Bytes=1329)
> UNION-ALL
> VIEW OF vtv_view (Cost=5 Card=100 Bytes=1300)
> REMOTE*
> NESTED LOOPS (Cost=1 Card=1 Bytes=29)
> INDEX (RANGE SCAN) OF PK_PM (UNIQUE)
> VIEW OF vtv_view
> REMOTE*
>
> 9i Explain Plan:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=1156 Card=1 Bytes=49)
> SORT (UNIQUE) (Cost=1152 Card=1 Bytes=49)
> UNION-ALL
> REMOTE*
> SORT (GROUP BY) (Cost=1152 Card=1 Bytes=49)
> NESTED LOOPS (Cost=1144 Card=1 Bytes=49)
> REMOTE* (Cost=1144 Card=2734639 Bytes=82039170)
> INDEX (UNIQUE SCAN) OF PK_PM (UNIQUE)
Received on Wed Dec 24 2003 - 03:44:00 CST
