Performance problems post Oracle 10.2.0.5 upgrade

[johan_pelser2]

Hi All,

We have patched our SAP ECC6 system's Oracle database from 10.2.0.2 to 10.2.0.5. (Operating system Solaris). This was done using the SAP Bundle Patch released in February 2011. (patched DEV, QA and then Production).

Post patching production, we are now experiencing slower performance of our long running background jobs, e.g. our billing runs has increased from 2 hours to 4 hours. The slow down is constant and has not increased or decreased over a period of two weeks.

We have so far implemented the following in production without any affect:

We have double checked that database parameters are set correctly as per note Note 830576 - Parameter recommendations for Oracle 10g.

We have executed with db02old the abap<->db crosscheck to check for missing indexes.

Note 1020260 - Delivery of Oracle statistics (Oracle 10g, 11g).

It was suggested to look at adding specific indexes on tables and changing abap code identified by looking at the most "expensive" SQL statements being executed, but these were all there pre patching and not within the critical long running processes. Although a good idea to optimise, this will not resolve the root cause of the problem introduced by the upgrade to 10.2.0.5. It was thus not implemented in production, although suggested new indexes were tested in QA without effect, then backed out.

It was also suggested to implement SAP Note 1525673 - Optimizer merge fix for Oracle 10.2.0.5, which was not part of the SAP Bundle Patch released in February 2011 which we implemented. To do this we were required to implement the SAP Bundle Patch released in May 2011. As this also contains other Oracle fixes we did not want to implement this directly in production. We thus ran baseline tests to measure performance in our QA environment, implemented the SAP Bundle patch, and ran the same tests again (simplified version of the implementation route ).Result: No improvement in performance, in fact in some cases we had degradation of performance (double the time). As this had the potential to negatively effect production, we have not yet implemented this in production.

Any suggestions would be greatly appreciated !

[johan_pelser2]

Thanks Fidel and Stefan for both your inputs, it is very helpful !

We have also got it logged with SAP.

Some feedback: it seems the IO time per db file sequential read of 1 ms from before the patching did increase to 6 ms after the patching, but have decreased to 3 ms since then. Which is all still very good response, but unfortunately still doubling runtimes on long running jobs. It could mean that prior to the patching a lot of reads were from cache, instead of physical disk. Although no changes were done to the storage system, we'll be scrutinizing that as well.

Thanks.

[stefan_koehler]

Hello Johan,

well the first goal should be to get the original performance so that you have time to do deeper analysis in your QA system (if the data set is the same).

If the problem is caused by some new optimizer features or bugs you can try to "force" the optimizer to use the "old" 10.2.0.2 behaviour. Just set the parameter OPTIMIZER_FEATURES_ENABLE to 10.2.0.2 and check your performance.

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

To get more information we need an AWR (for an overview) and the problematic SQL statements (with all the information like execution plan, statistics, etc.). This analysis are very hard through a forum. I would suggest to open a SAP SR for this issue.

Regards

Stefan

[fidel_vales]

Hi,

I do not think such "issue" can be solved with a "silver bullet".

1.- you should find what is taking longer (what queries) (probably looking at the ASH)

2.- Then you should take a look at why those queries take longer (has the plan changed? why? from what to what?) You can do this looking at the AWR (requirement, long retention as recommended by SAP and not dome a lot of times)

3.- then figure our why the queries changed. Is the change due to a "bug", new features (group cardinality introduced in 10.2.0.4) or new statistics (or a bug)

Once you have all the previous you can start thinking about a solution.

Before, and without that information, no way.

I'm quite sure there will be some silver bullets shooting this way

IMHO, probably needs deeper analysis that the one done till this moment (this is an assumption as that information is not mentioned)