Pieter Van Puymbroeck, guest author —

Last year, I wrote a small blog post on my Oracle Data Guard specific blog about how the 19c DML on the Standby feature behaves with the Far Sync feature.

New features are always exciting, but how do they perform? In order to make good recommendations, we need to dig into the feature to explain how it exactly works. Once there is a good understanding on all steps, it is easier to understand why certain recommendations are made.

First of all, let me clarify what the DML redirection feature is exactly. To understand that, we need to dig into the history of the product. In the beginning, Data Guard provided a normal physical standby database in mount mode. It protected your data, but not much more.

Although that is the key feature of Data Guard! Protecting data!  However, we always intended to expand upon this primary product goal right from the beginning.

Therefore, to make more use of the available resources, we came up with Active Data Guard. As of that point, we allowed DBAs to open the Standby Database in read-only mode while we keep applying the redo coming from the primary database. This is extremely useful, because now we can run reporting applications against the standby database instead of the primary, which gives a double benefit.

  1. The primary load decreases
  2. The resources on standby are used as well, which reduces waste of resources

Everybody happy. Right? Or not completely?

Reporting tools often need to register when a report has been run or not. Meaning, doing some simple DML. No problem DBAs are creative people, insert database links and there you go. Not very efficient, but it works and it usually works well.

Another use case is to have a situation where people need to look up things in the database, verify some data and then flag it as “done”. Read “mostly”…

All data you can read are potentially candidates to offload to the standby database. If you need to just occasionally register some small records in the database, you are probably good to go with this feature.

Regardless, an important job of Product Managers is to listen to people and also to spot where we can improve our products. So, with the introduction of Oracle Database 19c, we did just that an now allow you to issue “occasional” DML against the standby database.

It works as follows:

The DML Redirection process breaks down in 5 steps:

  1. The Client issues a DML against the read-only Standby Database
  2. The standby notices it is DML and sends this DML towards the primary database using an internal Db-link
  3. The primary executes the DML (which then generates redo)
  4. This redo is a normal redo stream and together with the normal redo stream this is sent to the standby database
  5. The standby database applies the received redo stream and releases the lock on the session so the session can see the result.

So, if you are following along closely, you see that some of the steps above could potentially introduce some performance overhead.  This is the reason behind the earlier “read-mostly” and “occasional” application comments and is key to the success of using this feature properly to avoid performance impact.

This brings us to the question:

How to quantify what “occasional updates” really means in regards to DML redirection?

Sticking numbers on something like this is always ideal, but only really works in settings where the exact configuration (including the data) have been tested against. Before you ask, yes of course we do an extensive amount of testing (in fact, a ton of testing) before introducing new features. However, digging into this direction wouldn’t provide the guidance everyone wants as your data is well…”your data” and the type of DML you plan to run in your environment is often unique to your “read mostly” application.
From the guidance perspective, we can give you some rules of thumb to work with to get you on the correct path to success in regards to taking advantage of DML Redirection. Given the nature of the feature, performance mostly depends on:

  • The redo rate you are generating
  • The bandwidth/latency between the Primary and Standby
  • The current load on the Primary
  • CPU resources on both systems
  • I/O storage subsystem load/throughput.

So, we do we recommend?

  1. Test, test, test, test, test what Read vs DML load rate works for you with your application!!
  2. As for the rule of thumb we mentioned earlier, we would advise keeping the DML updates/inserts on the standby beneath 3 to 5% of the workload, but depending on your situation, you can explore the feature with up to 10% of the workload representing updates/inserts  to see if performance is still acceptable for you.

The bottom-line with DML Redirection is it is a huge leap forward in regards to providing the flexibility you need to make the best use of making use of Standby Databases to offload your “read mostly” applications.  We on the Active Data Guard development team are constantly going to be cooking up ways to make the solution even better over time so keep an eye on this blog and new product updates.  In the meantime though, I suggest trying out DML Redirection with your applications as you upgrade to 19c or 20c and please feel free to provide comments or questions below regarding your experience.

For more details, refer to:

Oracle.com/goto/dataguard

Oracle.com/goto/maa

 You can also follow me on Twitter at @VanPupi or Oracle MAA at @OracleMAA for new updates.