Re: Query Runs Much Longer in 9i than in 8i

SAP BASIS Consultant wrote:

> 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)
Hmmmm - SAP, eh?
How about using statspack for statistics collections? It's my understanding you SAP guys still use analyse table

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel