Skip to Content

Performance increase of SRS with Oracle and HANA

Dec 15, 2017 at 03:18 PM


avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

avatar image
Former Member 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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Normal repserver jargon:

RS or SRS - RepServer (RS) or SAP RepServer (SRS) or Sybase RepServer (SRS)

PDS - Primary DataServer (eg, a Sybase ASE instance)

PDB - Primary DataBase (eg, a database within a Sybase ASE)

PRS - Primary RepServer (manages a connection from a PDS.PDB); may also serve as the RRS is only 1 repserver in use

IRS - Intermediate RepServer (sits between PRS and RRS, could have 0,1,2 or more IRS sitting between PRS and RRS)

RRS - Replicate Repserver (manages a connection to a RDS.RDB); may also serve as the PRS if only 1 repserver in use

RDS - Replicate DataServer (eg, a Sybase ASE instance)

RDB - Replicate DataBase (eg, a database within a Sybase ASE instance)


Within SRS, connections to primary/replicate databases are designated as <server>.<database>, which corresponds to PDS.PDB or RDS.RDB


Mapping to your terminology:

PDS.PDB == your Oracle databases

PRS == your local repserver

RRS == your central repserver

RDS.RDB == your HANA database


As for the stable device build up in the RRS (central repserver), you could look at a few things:

- admin who,sqt => looking for the size of the currently executing transaction (ie, how many commands in the current transaction); for example, if you did a 10-million row update in the PDB, when that transaction finally makes its way to the RDB, admin who,sqt should show the transaction size as 10-million; if admin who,sqt shows you some large transactions (eg, millions of commands) then said transaction could be one reason for the delay

- admin who,sqt => shows the same transaction (small or large) running against the RDB for a long period of time; this could indicate excessive blocking in HANA, or possibly a poor query plan (eg, a large volume of UPDATEs where each UPDATE is having to table scan a multi-million row table); in HANA you'd want to look at doing 'normal' HANA-based tuning of said query (eg, add a new index to speed up the UPDATE)

- another possibility, though a bit too involved to go into detail for this medium, is a long-running transaction in Oracle; the RDB connection will 'hang' while waiting for an associated 'commit tran' to come through from the PDB; in extreme circumstances you could see the stable device fill up in the RDS while waiting for said 'commit tran'; for this scenario you'd need to track down the long-running/open transaction in Oracle and decide what to do ... kill it, redesign the process to 'commit tran' more often/frequently; in rare circumstances you could have a corrupted queue where the 'commit tran' is lost, but in that scenario the DBA would need to take explicit/manual actions to clear up the issue (ie, it won't clear itself up)


And, yeah, if you've got a support contract with SAP you could always ping them for more ideas/suggestions/instructions.

The other option would be to bring in a contractor/consultant (eg, SAP prof svcs, 3rd party) to do some analysis.

Former Member

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

Mark A Parsons 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.

10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 19, 2017 at 03:46 PM

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

10 |10000 characters needed characters left characters exceeded
Tony Mao
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.

10 |10000 characters needed characters left characters exceeded