Skip to Content

Performance increase of SRS with Oracle and HANA

Hello everybody,

I have three source databases (oracle) connected with SRS to a target database (HANA). In the oracle databases a lot of data are created each minute and replicated into the HANA. The replication is near real-time, which means that after an entry was created in the oracle database it arrives in the HANA database within few seconds. The queues of the rep serves are normally almost empty. The whole system was set up by SAP.

But from time to time it happens, that a queue on the rep server is built up for some hours and after some hours the queue decreases until it is again almost empty. I did some measurements and found that:

In this situations the statement admin who,sqt shows a lot of closed and open transactions (rep server that is connected with the HANA)

In this situations, the statement admin who,sqm shows that the first pointer moves only very slow (rep server that is connected with the HANA)

There are still a lot of free resources (CPU, memory of rep server host and CPU, memory of HANA).

My feeling is: The rep server that is connected with the HANA is the bottleneck. So my idea was to give more power to the rep server. I found in the documentation that there are a lot of configuration parameters that can be set (e.g. number of packet site, number of threads and so on) for the rep server or for the connection.

My question is: In such a situation, which configuration parameters should be considered first to improve the performance? I'm sure there are a lot of people that had similar problems and "played" a little bit with the configuration parameters and found a good configuration.

Thanks and Regards

Hanno Keidel

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Dec 15, 2017 at 04:46 PM
    Thanks for the answer. What do you mean with RDB and PDB?

    What I can say so far: I have four databases to connect. Each database is connected with its separate rep agent. Each rep agent is connected to a separate rep server (we call this local replication server). Each of this local replication servers is connected to one replication server (we call that central rep server). This central rep server is connected to the HANA database. On the local rep servers the queue (stable device) is always almost empty. The queue problems occur on the (central) rep server that is connected with the target (HANA) database. The (central) rep server and the HANA are in the same LAN, so network performance problems can be excluded. Because of this think the bottleneck is either the central rep server or the HAN itself. But when the queue increases, I can see that HANA has still a lot of free resources, so I think the bottleneck is the (central) rep server.

    I am not a SRS expert. I manage the system, know the architecture and the most important commands (I guess). What can I do to find out where the performance problems occur? Of course my alternative is ordering SAP again, but before doing this I wanted to try something for myself.

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Mark,

      helpful answer. Thanks for explaining the terms. You are correct, my PDB is the oracle database (or the databases since I have four connected), my PRS is the local replication server my RRS is the central replication server and the RDB is the HANA database. Between PDB and RDB there is no other replication server.

      Today there is again the problem that the stable device increases. I found out, that from the four connected PDBs only one is causing this increase. I did this with admin who,sqm. For all queues the first pointer is almost the same as next and last, only for one there is a huge difference.

      Then I did admin who,sqt several times. I found out that the transaction size is small which was expected since the source systems should have a lot of transactions, but each small.

      Then I executed admin who,sqt several times and found out one interesting point. Looking at the column "First trans" I can see each time I execute admin who,sqt, the value changes for all the connections. But only for one connection the value remains the same. Then I execute admin who,sqm again, again, again, and I can see that the value only changes after some seconds.

      So question: If the value "First trans" remains the same for some time, is this an indicator that the transaction needs some time (to much time) in the RDB?

      Greetings from Germany

      Hanno Keidel

  • Dec 15, 2017 at 04:11 PM

    It's not clear (to me) what steps have been taken to rule out issues in the RDB (eg, blocking, resource contention, etc), or possibly even the PDB (eg, long running txn).

    It might also be of interest to look at the transaction activity in the PDB(s) leading up to these periods of increased stable device usage to see if there's something that could be done to tweak/tune said transaction(s) to help reduce stable device usage.

    If your findings (still) suggest the repserver needs tuning, you may then want to look at gathering some (RS Monitor counters) statistics to see if they can help zero in on which RS configurations may be of most benefit.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 19, 2017 at 03:46 PM

    Mark, I have created the answer a comment on your comment. Hope this works.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 20, 2017 at 08:16 AM

    Hello Hanno,

    The fact that "the value "First trans" remains the same for some time" should indicate this is a large transaction, and needs more time of processing. You could keep monitoring to see if it is always this connection, and if it happens during some specific time. We might then try to figure out what transactions are doing that time.

    Also I hope you could get more from this SAP note:

    2207039 - Latency: open transactions in IBQ are moving slowly - SRS 15.7.1

    Best regards.

    Add comment
    10|10000 characters needed characters exceeded