Increased Resource Consumption after Upgrade

[former_member1012268]

Hello World

Now ~2 weeks after our ERP Upgrade (from ECC6.0+EHP5 to ERP6.0+EHP7 with SP22 and from MaxDB 7.7.07.028 to 7.9.08) has been done, the native are growing restless.

In our landscape (3 system landscape with single server systems for development, quality assurance and production), all systems experience significantly increased resource consumption, for CPU,Disk and RAM I/O than before the upgrade.

There is an intense dispute going on here  as to whether this increased resource consumption is due to "natural causes" (changed runtime behavior after the upgrade), or due to a faulty system configuration, or an error during the system upgrade.

I adjusted the standard configuration areas (buffer and cache sizes, statistics update a.s.o.), and system performance has improved.

Yet the the root cause for the increased resource consumption as a such has not yet been isolated (simple lack of time on my side and an unwillingness by the complainants to submit me a list of the actual programs/user experiencing these aforementioned problems), and the assumption still persists that there is "something wrong with the system".

It would help of I had an official SAP guideline (SAP note, etc.) on the performance delta for such a release upgrade, or some other "official estimate".

Once more, the issue here is not so much if certain settings need to be adjusted, but why they need to be adjusted in the first place ("it used to run w/o adjustments before the upgrade").

Thanks in advance for any help

[former_member1012268 · Accepted solution]

Well, the solution turned out to be as pathetic as it was profound.

The Upgrade Guide (& SUM) recommended a specific target release for MaxDB (with a warning that using any higher a release - e.g. 7.9.8.18/19 might lead to an unrecoverable error during the upgrade - but it turned out that that target release couldn't handle DB stats that hadn't been updated properly.

Our previous, now defunct, SysAdmin didn't plan any regular optimizer stats updates for PRD (he did for DEV strangely enough).

So 7.9.8.12 (latest pre- *.18 install set presented to me on SAPnet) ended up "loosing" all kinds of File Directory Pointers in the Database, causing it to resort to very costly full table scans in turn.

The solution was to download the latest MaxDB Patch set, run a DB structure check and then update all optimizer stats in the DB.

Now it all works like clockwork again.

While I can understand that SAP warns against using 7.9.8.18/19 for the upgrade, I don't understand why they didn't offer 7.9.8.25 among the install sets.

At least on our account, that MaxDB version was only available from the (different) Patch Set location.

And yes, I know "you should always use the latest patch set <...>", but to be honest I normally install Oracle DBs, where using OPatch is just second nature.

I never figured that a download set still officially available from SAPnet could be so broken as to almost wreak our entire system.

[former_member1012268]

I was able to dig a bit deeper, with onboard tools.

There are a handful of Z* transactions, which access table AFRU.

For some reason now *all* of them issue full table scans (or if they do specify indexes to be used, the SAP code accepting the requests defaults to full table scans again).

Had to open a High Prio ticket wih SAP about it, as this leads to 1000+ read accesses on our data disks per second.

Does anyone have a clue why SQL queries that used to work before the upgrade, now default to full table scans?

We have quiet a few indixes shown as "missing" in DBACockpit, but the developers don't think any of them matter here.

[Reagan]

I would first try to update the database statistics or at least for the tables used by the Z transactions and see if that helps. If the missing indexes are part if these tables then create them at the DB level using the Tx SE14.

[former_member1012268]

Well, at this stage its a *big* problem for me to make any such recommendations w/o an SAP note to go along with it.

By now I confirmed that 95% + of the peak load is due to excessive full table scans in the system, but the DB reads are performed by Z* Transactions, whereas the developers (as usual) insist that "nothing had changed".

So I would need an SAP note explaining why, after a major System Upgrade, DB accesses to an SAP table that used to be indexed, all of a sudden are performed w/o index - even so the SQL statements in the ABAP code didn't change.

[Reagan]

By now I confirmed that 95% + of the peak load is due to excessive full table scans in the system, but the DB reads are performed by Z* Transactions, whereas the developers (as usual) insist that "nothing had changed".

If there are full table scans then I would check the execution plan and indexes for those tables. If there are indexes available and if the database is not using them then you check whether the statistics are up to date. The database should pickup the right index (if present) during an execution.

[former_member1012268]

Two (hopefully not so dumb) questions:

1) Do client dependent SAP original tables require "mandt" (client) field in the (customer) index definition?

2) Would the optimizer skip an index if its stats are badly out of shape (just ignore it and use a completely different one, even so that other index wasn't specified in the SQL query)?

[Reagan]

1) Do client dependent SAP original tables require "mandt" (client) field in the (customer) index definition?

For all the indexes (standard and custom) I have come across I have seen MANDT in it but according to these SAP note it is pointless to have MANDT in an index.

Point 11 : 928037 - FAQ: SAP MaxDB indexes

Point 16 : 912620 - FAQ: Oracle indexes

2) Would the optimizer skip an index if its stats are badly out of shape (just ignore it and use a completely different one, even so that other index wasn't specified in the SQL query)?

This is a possibility. There is also a possibility that customer indexes for certain tables are lost after an upgrade which can be another reason why there is an FTS.

Regards

RB

[former_member188883]

Hi Donaldo,

What is the patch level applied for MaxDB 7.9.08 ?

Suggestion here would be to update to latest patch and look out for information on bug fixes and issues resovled with latest bug fixes in SAP note below

1444241 - Feature List for SAP MaxDB 7.9

Regards,

Deepak Kori

[former_member1012268]

Thanks, but that really doesn't help me.

To quote my boss, "I refuse any explanation that hasn't an SAP note attached to it saying, 'this is why the new SAP release requires more resources than before'"

BTW, I found that the system shows around 9%/12%/46% of ROLL WAIT TIME for Dialog/ALE/RFC tasks during peak moments of system unresponsiveness.

Not sure yet how to interpret these numbers, because naturally "nothing else changed" (or so they claim), thus it must be due to the (screwed up) upgrade.