Performance of oracle after upgradation of 8i to 9i in unix

[Former Member]

hi,

I have upgraded my oracle 8i to oracle 9i in an Unix machine.After that the queries are taking longer time in the oracle 9i.Can some help me on this?

thanks,

Anjela

[Former Member]

hi Anjela,

do you know which queries are taking longer time? Are the statistics of these tables "good"? With how many per cent are the statistics for these tables created and how big are they?

Do you know if the right indexes are chosen for these statements?

How are the Storage Qualities of the Indexes of the tables that are used in long time queries?

In order to display the Storage Quality:

DB02 (Oracle) --> Detailed analysis --> <enter table or index> -->Detailed analysis --> Analyze Index --> Storage Quality

As you know, many other things (DB and R/3 Parameter, OS Konfiguration, Network etc...) can be the reason of bad performance?

Best regards

Baran

[Former Member]

Hi Baran,

Thanks a lot for your answer.I will check all these as you have suggested.But the queries which were running fine in the Oracle 8i,why suddenly those are taking longer time in the oracle 9i?

Thanks in advance,

Anjela

[Former Member]

Hi Anjela,

many things can be reason for bad performance. Did you check DB Parameter according to the SAP Notes 124361 and other notes in this Note? With every release there are some Bugs about bad performance which also affect the performance. For these bugs there are either Oracle Patches or some workarounds. You should check if you applied performance relevant patches for 9.2.0.x (which patchset do you have?).

You can provide the init.ora and the applied Oracle Patches (opatch lsinventory) for this database. I can have a look if something important is missing or set not correct.

You should analyze the statements with Transaction: ST04 --> Oracle session, if they have "good" execution plan and they choose right indexes, the chosen indexes are "good" etc...

Best regards

Baran

[Former Member]

Dear Anjela,

Please create the new statistic for all object.

Please apply notes 124361.

Hope this can solve your problem.

Fendi Suyanto

[Former Member]

Thank you very much for your answer.but the query which took 20 secs in oracle 8i after upgrade it is taking 10 mins.What might be the reason for this drastic changes?Do we need to change the statistics of the query for this upgrade?