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

I had problems with a 8i to 9i upgrade as well. Queries were taking longer with the 9i database. So, I compared both init.ora files and made them as close as possible to each other, even if it meant using old parameters such as sort_area_size instead of pga_aggregate_target.

It turns out that the pga_aggregate_target was causing full table scans in the 9i database and after replacing it with old parameters it went back to working as expected. I then ended up lowering the pga_aggregate_target considerably before I could get it to work as I thought it should.

"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 Tue Dec 23 2003 - 17:39:56 CST