First, a note about how I’m going to write up this year’s UKOUG Conference. There was a looooot happening this conference, both socially and learning-wise, so I’m going to break things up – first, I’ll write up my thoughts on the presentations (over several blog entries!) and then I’ll write up my networking/social/fun thoughts.
So anyway, this year, UKOUG and the Oak Table guys got together and put on an extra day’s worth of sessions (which were repeated later on in the conference). Despite National Express East Anglia/Network Rail’s best efforts (Cambridge train station was closed!) I managed to get to Birmingham in time for the start of OakTable Sunday – mainly due to the fact that my other half very kindly drove me to Milton Keynes so that I could catch the train from there! This had the bonus side effect of me not having to get up early enough to catch the coach from Cambridge station at 5:50am!
Anyway, on with the presentation write-ups:
Tales From The OakTable – Mogens Nørgaard
I’ve never seen Mogens before, though I’ve heard plenty about him, and it was somewhat of a shock! He was incredibly laid back, and started off by saying that we were probably not going to learn anything over the conference, or at most, about 5% would stick with us after the conference.
He treated us to a few stories about the OakTable and its members, encouraging stories from Oakies in the audience too. I wasn’t quite sure what to make of this, really, as it was a very strange talk! Perhaps if I’d seen Mogens present before, then it wouldn’t have been a surprise!
About Multiblock Reads – Frits Hoogland
This was about the changes to multiblock reads in 11g, and how you cannot rely on scattered reads anymore – sometimes it will choose to do direct path reads, which means that you lose the advantages of query results being cached. This could be a good thing or not, but it was something I was unaware of (even though Doug has apparently already blogged about it!). Given that we will hopefully be migrating our 10g databases to 11g next year, I’m very glad I went to this session!
Optimizer Statistics – A Fresh Approach – Connor McDonald
Having seen Connor present at the 2009 UKOUG conference, I was determined that I was going to see as many of his presentations as I could. Thankfully, he presents on topics that are relevant to me, but even if he didn’t, I’d go along and see him in action! Honestly, if you’ve never seen him present, if you should ever get the chance to do so, do! He is *a* *maze* *ing!
Connor talked about statisics, both object and database stats, such as how you should gather stats, etc. There were some ideas in there that I’d never considered before (pending stats), but I’ll definitely go back to work and think about our stats strategies etc!
Statistics Gathering and Histograms – DOs and DON’Ts – Wolfgang Breitling
Following on from Connor’s presentation was Wolfgang’s. He went into more detail on histograms and was of the same opinion as Connor – don’t use histograms if you don’t need to; set them explicitly. Another talk I shall consider when looking at our stats gathering!
Performance and High Availablility Panel Session
Finally, the OakTable Sunday was topped off by a panel session of various OakTable members who took questions from the audience and wanted to know about the longest prod outage that people were brave enough to own up to! Thankfully, I’ve never caused a prod outage, but someone had had an overnight outage and someone else had had 18 hours although I don’t think it was caused by them. Connor won on the monetary outage front – he managed to hose a 10 million Aussie dollar truck through a bit of dodgy code!
Oh no! My Twitter anonymity is blown!
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
Yeah, that sort of makes sense, although I’m not sure it’s correct – first find out the rate at which the blocks are written – observed time period / number of blocks written.
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!
Continuing with the Monty Python theme – I feel like Mr Gumby (“My head hurts!”) I can see the logic of comparing it with the rate of processing, over a period, with the rate of the number remaining over that time. It’s just that his implementation didn’t seem to give the right output. I’m going to run the queries again & generate actual values and pump them in to both yours and the original. Thanks for taking the time to look at this – more soon! G
Hmm. Depends on what you’re taking “lag” to mean, I guess.
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!”
I started putting together a spreadhseet of values on the train this morning, that will show the differential times and the absolute times – that will help prove that the calculation is accurate & returning expected numbers. My ‘worry’ is that it might need some sequences / series type calculations – which I had to give some extra thought to when I did my OU maths course. Like i said, to be continued… G