BW queries ran 3 time longer after upgrade to Oracle release 10.2.0.4.0

[Former Member]

Hello experts,

Our BW 3.5 system DB was upgraded to Oracle 10.2.0.4.0. A bunch of our queries now taking 3-4 times longer to run. According to statistics, the bulk of time was spend on DB scanning/retrieving data records.

I ran the DB test in RSRV "Check general database parameters" and got 3 red lights and a bunch of yellow lighs

Red Lights:

1- ORACLE: Parameter COMPATIBLE is set to value 10.2.0 (recommended value is 8.1.x / 9.x)

2- ORACLE: Parameter OPTIMIZER_FEATURES_ENABLE is set to value 10.2.0.4 (recommended value is 8.1.x / 9.x)

3- ORACLE: Parameter OPTIMIZER_MODE is set to value ALL_ROWS (recommended value is CHOOSE)

Yellow lights:

ORACLE: Synonym SAP_DBA_EXTENTS does not exist. See SAP Notes 519448, 565416

ORACLE: Synonym SAP_DBA_SEGMENTS does not exist. See SAP Notes 519448, 565416

ORACLE: Synonym SAP_USER_EXTENTS does not exist. See SAP Notes 519448, 565416

ORACLE: Synonym SAP_USER_INDEXES does not exist. See SAP Notes 519448, 565416

ORACLE: Synonym SAP_USER_IND_COLUMNS does not exist. See SAP Notes 519448, 565416

ORACLE: Synonym SAP_USER_SEGMENTS does not exist. See SAP Notes 519448, 565416

Question regarding the red light items: It does not seem to make sense given that our DB is now having release 10.2.0.4.0, What is the reason(s) for these?

Are the yellow lights relevant to query performance?

Operating system is Linux

Thanks

Edited by: Joe Nguyen on Apr 17, 2009 7:32 PM

[lbreddemann]

HI Joe,

while Stefans remarks are totally correct, one important point often overlooked by customers is to install the most current CBO mergefixes after installing a patchset.

Therefore, make sure to install the SBP #1 plus the Optimizer mergefix on top of it!

Check note

[SAP Note 1165319 Optimizer merge fix for Oracle 10.2.0.4|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes/sdn_oss_bw_sys/~form/handler%7b5f4150503d3030323030363832353030303030303031393732265f4556454e543d444953504c4159265f4e4e554d3d31313635333139%7d]

on that.

regards,

Lars

[anindya_bose]

Hi Joe

Please check the note below if it helps..

Note 838725 - Oracle dictionary statistics and system statistics

Regards

Anindya

[former_member204746]

for the yellow lights, why not try what the error message tells you to do:

See SAP Notes 519448, 565416

as for the red lights, de-activate for change parameters in transaction DB17 and refer to SAP note 830576 to know which value to use.

now that this was said, these errors have no impact on performance, you will need to check elsewhere. have you run UpdateStats in Db13?

Hope this helps.

[Former Member]

Thanks Eric, I will pass your info to our Basis people for further evaluation. Stay tuned.

Based on the note the last 2 red lights are incorrectly set.

Edited by: Joe Nguyen on Apr 17, 2009 8:56 PM

[stefan_koehler]

Hello Joe,

regarding the performance - there were some relevant changes in the optimizer that can lead now to a better or worse performance.

Martin Frauendorfer shows some aspects in his great presentation "The CBO and its Optimism about Cardinalities":

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/10211220-a2a6-2b10-27bf-b4e700e6...

Check the pages 13/14 and 19/20.

So the parameter recommendations in sapnote #830576 are very important for the new CBO behavior.

You can try "the old optimizer behavior" by setting OPTIMIZER_FEATURES_ENABLE to 10.2.0.2 on session level, if the performance is the same as before .. or if you maybe got some other problems.

But without taking a closer look at the queries, it is nearly impossible for a forum to help you.

Regards

Stefan

[Private_Member_101989]

Hello,

Check this note:

Note 1284478 - Poor performance when using Btree bitmap plans

Check also the parameter recommendations:

Note 830576 - Parameter recommendations for Oracle 10g

Regards,

Jorge Diogo