(which we do not really comprehend)To avoid querying the primary’s SCN at every request (performance and network impact), the sessions on the standby get the last commit SCN from the standby memory structures (last received SCN). Because it’s synchronous, there’s guarantee that whatever has been committed on the primary at any given moment, has been already transferred to the standby RFS processes. So the session can wait for that SCN to be applied (another memory structure on the standby) and consistently read what’s been committed on the primary.
If the transport is ASYNC, the standby does not know if it received all the committed transactions, and would have to get the SCN from the primary for every request, then wait for that SCN to be both transported and applied.
In terms of your proc,
1. I'd use dbms_session not dbms_lock
2. I don't think we go to millisecond granularity, eg
SQL> set timing on
SQL> begin
2 for i in 1 .. 100 loop
3 dbms_session.sleep(0.01);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.97
SQL> begin
2 for i in 1 .. 1000 loop
3 dbms_session.sleep(0.001);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
3. I'm not a fan of loop forever, perhaps have a "get out of jail" in there
4. In terms of the logic, you'd need to check how the SCNs handled when you access the db link.
I dont have an ADG handy but with standard databases, SCNs get aligned when you do db link operations, eg
DB1 , displaying SCN every second and then in DB2 we ask for the SCN at DB1 via database link at point highlighted below
SCN
---------------------
16331128531417
16331128531417
16331128531418
16331128531418
16331128531418
16331128559406 <<<========
16331128559406
The SCN is bumped to align with the other node.
I'm *assuming* we don't do this with an ADG setup, but you want to test that