I’d have thought it would be “How long will it take for the Standby db to catch up to where the Primary db is now?”, which is what my calculation gives you.

I really don’t think your colleague’s query makes that much sense – what if the sby_blocks_s2 was the same as the prim_blocks_s1? That would give you a lag time of 0, when clearly Standby is still 20 blocks behind Primary!

Or worse, what if sby_blocks_s2 was less than prim_blocks_s1 – that’d give you a negative lag time! What does that even mean?

I am now joining you in the Gumbyness. “NURSE! NURSE! NURSE!”

Then multiply that by the number of blocks outstanding, which I’d say would be PRIM_BLOCKS_S2 – SBY_BLOCKS_S2.

I think my calculation would be:

Lag = (time_period * block_difference_at_end_of_time_period) / number_of_blocks_added_to_sby_across_the_time_period)

or in your case:

Lag = (sample_time * (prim_blocks_s2 – sby_blocks_s2)) / (sby_blocks_s2 – sby_blocks_21)
= 10 * (120 – 110) / (110 – 88)
= 10 * 10 / 22
= 4.55 seconds

But perhaps that’s just me; I’m really not sure why your colleague went with PRIM_BLOCKS_S1-SBY_BLOCKS_S2!

So, this relates to Data Guard, but the concept is more general.

My colleague had written a very simple formula to work out the latency between blocks being generated on the Primary, versus being applied on Standby.

He takes two samples, 10 seconds apart(not very exact, as he uses PERL ‘SLEEP’) from both the primary and the standby using the following queries (ignore the block#s absolute values, STANDBY should always be less that PRIMARY):

PRIMARY:
select status, sequence#, block# from v$managed_standby where process =’LGWR’;
STATUS SEQUENCE# BLOCK#
——————– ———- ———-
WRITING 42223 946888

STANDBY
select status, sequence#, block#, blocks from v$managed_standby where process like ‘%MRP%’;

STATUS SEQUENCE# BLOCK# BLOCKS
———— ———- ———- ———-
APPLYING_LOG 42223 949501 1024000

So for exmaple (with easier numbers):
S1 PRIMARY=100 STANDBY=88
S2 PRIMARY=120 STANDBY=110

Anyway, to work out the lag in seconds, he’s used the following:
Lag=SampleTime/(SBY_BLOCKS_S1-SBY_BLOCKS_S2)*(PRIM_BLOCKS_S1-SBY_BLOCKS_S2)

Substituting the values:
Lag=10/(88-110)*(100-110)=10/(-22)*(-10)=0.45secs

Make sense / sound reasonable? If so, what’s his reasoning?

G

Sorry, who are you? Are you a member of the Oak Table Network? I only ever talk to slightly-famous people, you know …

“To me the DBA and developer roles have a massive crossover.” — definitely agree with this, although I’m more than happy to leave the backups and restores etc to the DBAs *{;-)

You’re right; I think that my general disagreement with the statement that Michael Salt said is that it isn’t just the DBA’s or the Dev’s responsibility to create indexes, it’s not that clear cut. That plus the fact that I would automatically think about creating indexes as part of the development process!

You’re right – it is a combination of the both; but I still think that devs should consider creating indexes as part of the development process… or is that just me?!