St04 Read/User calls is very high after Oracle Upgrade

[Former Member]

Hi,

We recently (2 days back) upgraded our production oracle database from 10.2.0.2 to 10.2.0.4 (AIX 5.3). After upgrade all of sudden the Read/User Calls in ST04 is showing the value around 2300ms compared to 12ms which was before upgrade. All the jobs are taking so long to finish. Any idea what could have gone wrong after upgrade or anythingin particular to be checked for this ?

Thanks,

Murali.

[Former Member]

I have a problem understanding the description. The unit of "reads / user calls" is "blocks", but you speak about "ms". Do you perhaps mean "time / user call"?

[Former Member]

Hi MArtin,

It is Reads / User calls taking 2300ms (time) compare to 12ms earlier before upgrade.

Thanks,

Murali.

[Former Member]

Have you checked note Note 830576 - Parameter recommendations for Oracle 10g for special 10.2.0.4 parameters? There are some new events in 10.2.0.4

Kind regards,

Mark Dijsselbloem

[Former Member]

Yeah we checked it nothing particular we found so far, still looking into it.

Thanks,

Murali.

[Former Member]

Have you executed the post-install steps for the mergefix?

Kind regards,

Mark

[Former Member]

...have you set the parameter optimpeek_user_binds to FALSE or TRUE?

Cheers Carlos

[Former Member]

Hi Carlos,

We did not have the parameter optim_peek_user_binds set, but now we did this in quality to test it and then will do same in production.

Thanks

Murali.

[Former Member]

Hi Murali,

please set the parameter to false...

Regards

Carlos

[Former Member]

Sure will do

Thanks

[Former Member]

All,

After adding the following 4 parameters recommended by SAP, Read/USer Calls came back to 10.1:

fixcontrol (5099019)

optimpeek_user_binds

optimizermjc_enabled

sortelimination_cost_ratio

optimizer_dynamic_sampling

Thanks for everyone who helped on this.

Murali.

[Former Member]

First question to ask:

Did you apply all the required patches for Oracle 10.2.0.4 ?

There is a list in a SAP note; please search for it; I haven't the note number at hand just now.

Next question to ask:

Did you recreate system and dictionary statistics, as described in your upgrade guide?

hope this helps

[Former Member]

Hi Joe,

Thanks for the reply. YEs all the patches are applied and also the stats (both) has been done in the same way like did in several other systems in the landscape so far.

Thanks,

Murali.