Query Runs Much Longer in 9i than in 8i

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)