Index usage wierd after upgrade to 10g...

[JPReyes]

Hi All,

Having an issue after upgrading our DB from Oracle 9 to 10g...

Basically the problem seem to be that all queries are using the primary index no matter what other indexes exist making the performance really slow...

I have checked all the optimizer parameters without luck!

Any ideas?

Regards

Juan

[stefan_koehler · Accepted solution]

Hello Juan,

the optimzier behavior has changed from 9i to 10g.

The statement "Basically the problem seem to be that all queries are using the primary index no matter what other indexes exist making the performance really slow" is not really detailed.

If you have an overall performance problem, you should open a SAP call and get support if you don't have the know-how.

But until you get support you can change the optimizer behavior back to 9i the following way:

> SQL> alter system set OPTIMIZER_FEATURES_ENABLE = '9.2.0' scope=both sid='*';

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams142.htm#sthref597

You don't have to restart your system.. this parameter is dynamic.

Regards

Stefan

[JPReyes · Accepted solution]

We tried that... got better but still uses only the primary index.

Any other thoughts?

Juan

[stefan_koehler · Accepted solution]

Hello Juan,

>> We tried that... got better but still uses only the primary index.

Ok in which transaction / case does oracle use only the primary index.

Are the other indexes valid?

> SQL> select * from ALL_OBJECTS where STATUS = 'INVALID' and OBJECT_TYPE = 'INDEX';

Regards

Stefan

[fidel_vales · Accepted solution]

Hi,

The issue is too generic and would be difficult to solve in the forum.

I recommend you to open a call on SAP.

I do have some questions:

1) have you installed all the patches from note 871096?

2) have you set all parameters as note 830576?

2a) can you give us the output for

select name,value, isdefault from v$parameter

where name like 'opt%' or

name like 'event%' or

substr(name,1,1) = '_' ;

[JPReyes · Accepted solution]

Applied the patches and the parameters in the notes and the problem seems to be solved.

Thanks for the help

Juan

[Former Member]

Hi Juan,

although it is trivial, I would like to ask you if you followed all instructions in the upgrade guide. Did you run update statistics on all tables? This is mandatory after the upgrade.

Regards

Ralph Ganszky