Skip to Content
0

Performance issue when running CAT2 transaction on HANA

Oct 24, 2016 at 07:14 PM

142

avatar image

Hello ,

We have recently performed a DMO migration procedure. We are now running SAP ECC6 ehp8 on HANA DB version 121, Linux Suse 11.4

The production system ID of SAP is P50, the production system ID of HANA DB is H50.

We have just built our QAS system QA2, from a homogeneous system copy of Production database backup H50. Production P50 and QAS systems QA2 are therefore identical : same DB version, same kernels, same Database content, same version of SAP, same tables content, same repository objects, same HR customizing, same users settings.

We have been reported some disturbing performance issues on Production. Some HR transactions performances have seriously deteriorated after migrating to HAN. For example, running the transaction CAT2 takes much more time, on average 25-35 seconds almost systematically . We tried the same scenarios over and over again (not a cache filling issue)

On QAS, the very same transaction, with the same user takes only 3 to 4 seconds, even though much more resources are allocated to Production as you can imagine.

So we have compared PRD and QAS transaction scenarios by running traces through ST12 transaction , which confirms the serious performance discrepancy between Production and QAS

Production

QAS :

By comparing the SQL statements generated by CAT2 transaction we noticed some differences in the SQL staements :

In production when selecting data from table PA0001 for example :

SELECT /* FDA READ */ * FROM "PA0001" WHERE "MANDT" = '500' AND "PERNR" = '00001989' AND "BEGDA" <= '99991231' AND "ENDDA" >= '18000101' ORDER BY "MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" , "SEQNR"

Whereas in QAS :

SELECT /* FDA READ */ * FROM "PA0001" WHERE "MANDT" = '500' AND "PERNR" = '00001989' AND "BEGDA" <= '99991231' AND "ENDDA" >= '18000101' ORDER BY "MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" , "SEQNR" WITH RANGE_RESTRICTION('CURRENT')

As you can see, the additional command " WITH RANGE_RESTRICTION('CURRENT')"

is added in QAS, not in production, and there is absolutely no reason for that, the CAT2 settings are strictly identical in production and QAS. And again QAS has just been built using this week backup of PRD Database

So I managed to retrieve the Statement hash of both SQL commands in QAS and PRD :

The average value per execution , and the average value per row is much more lower in QAS, than it is in PRD.

So, I am no ABAPer but it seems that the command " WITH RANGE_RESTRICTION('CURRENT')" would significantly reduce the average runtime.

But two questions remain:

1. How is it that the same CAT2 scenario executed in two identical SAP systems would generate two different SQL statements?

2. How could I "adjsut" the productionso that the command " WITH RANGE_RESTRICTION('CURRENT')" is generated ?

Thank you

st12prd.png (53.9 kB)
st12qas.png (54.6 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Raoul Shiro Jan 23, 2017 at 05:36 PM
0

Upgrading to HANA 122 fixed the issue

Share
10 |10000 characters needed characters left characters exceeded