Poor Performance of DB after migranting to 10g

[roedelfroe]

Hi,

some weeks ago we upgraded our Oracle Database from Version 8i to 10g (10.1.0.3.0).

To do so, we used the migration tools from Oracle

Since then we are experiencing a poor performance of our database.

e.g. we've got one big table with 128 Billion entries. A "normal" query under Oracle 8i used to last about 20 Seconds, now, under 10g, we are waiting for a response 15 minutes and more!

Oracle recommended to delete all hints and to recreate all our indices, and we folowed this advice.

For one or two days our 10g Database was faster (far from 8i performance but at least really faster) but now it's slow again.

Furthermore, we always get an error message, when we use within SQL Navigator "Extract DDL":

Oracle Dictionary: Requested property not valid for schema object

Whats that? Again, Oracle Support was of no use, they told us that we can ignore this message.

Can we really do so? I simply can't believe this and at least I want to know, what causes this message.

I know, this is a rather rough overview, if you are needing more details... no problem.

Thx for the moment

Roland

 

[sem]

After migrating to any CBO you should always:
1. gather fresh statistics
2. check/change parameters optimizer_index_cost_adj and optimizer_index_caching as their default values are traditionally unsuitable for most real systems

And of course poor performance may be caused by many reasons, so you should check execution plans and wait statistics at least.

Regards, Dima

http://seminihin.narod.ru

 

[roedelfroe]

Hi Dima,

thanks for your tips.

I have changed the parameters you mentioned (Parameters were set on default, good guess from you ) but the database is still performing like... (words are missing).

Tomorrow, a DBA-Guru from an other departement will try to help me. Maybe he is able to help me. I will let you.

BTW, do you know what this error message means (original post)?

Thanks a lot

Roland

 

[sem]

Unfortunately no, I don't work with SQL Navigator.
But how about statistics? Without it CBO is more than helpless. You should also consider disabling _B_TREE_BITMAP_PLANS hidden parameter as in some cases it kills performance. In any case you should evaluate execution plans of long-running queries.

IMHO well tuned 9i optimizer is far beter than all its ancestors, so now I'm looking for my old queries and removing hints, +0 constructs and other old-fashioned stuff.

Regards, Dima

http://seminihin.narod.ru

 

[vidmin]

Have yo set "filesystemio_options" param

 

[sem]

Oops, missed that I'm in 10g forum. Actually all the above written by me is about 9i, sorry.

Regards, Dima

http://seminihin.narod.ru

 

[roedelfroe]

Hi vidmin,

filesystemio_options = async

Regards

Roland