jump to navigation

Making Things Better Makes Things Worse February 11, 2010

Posted by mwidlake in development, Management, Perceptions.
Tags: , ,
12 comments

This could be a Friday Philosophy but I’ve got a couple of those lined up already. Anyway, I am suffering at work at the moment. I’m encountering a phenomenon that I have talked about with Dennis Adams a couple of times. It probably has a proper term, but basically it is the odd situation that when you make things better, you get more complaints. {Dennis, do you know the proper term?}

{Update. Dennis was good enough to link to this paper he wrote on customer feedback}

Anyway, Let me explain. You have an application sitting on a database. The screens are slow, the reports take an age to come out, your might even have considerable system instability and unplanned outages. The users are not unhappy. They were unhappy last year. Now they are just cynical and they just expect the system to be slow, unresponsive, flaky. So they do not report any problems.

Then things change. The system gets some much-needed care and attention and now the slowest reports get tuned up, the screens come back faster and less spontaneous department-wide coffee breaks are caused by the system crashing. Everything gets better. But not for the help desk, now they start getting calls. “This report is too slow”. “Why can’t I jump straight from the customer screen to the pending orders screen?”. This happens because the users now realise that something can be done. There is a point in complaining as there is a chance their piece of misery could be made better. I certainly went through this many years ago when I inherited a system that crashed every week. No one mentioned it, they just went to tea and complained about it. The first time it crashed after I arrived I could not believe that no one had called before I had realised it had died. Once it had been up solidly for a couple of months, then when it crashed boy did we hear about it!

Also, when you improve a system and things generally get better, occasionally something will improve and then fall back a bit. Hardly anyone says “thanks” for the initial improvement but they will say something if it improves and then drops back.

That is what is happening for my main client at the moment. The system was not that bad, but it needed some help. Why else would I be there? I’ve been beavering away with the rest of the team and we are making things better, so far mostly at an underlying “getting the overall system straight” level. A new chap has joined to help concentrate on performance and he is really making inroads into specific things, individual reports and processes that need a good sorting out.

So now that things are getting better and performance is generally improving, anything that is still slow is being brought up by the development and support teams. Also, we’ve made a few things slower (I’m sorry, it just happens like that) and they certainly get mentioned.

So, I’m busy. And I could get annoyed at people asking why X is slower when Y and Z are faster. But I don’t, because Dennis explained this counter intuitive theory to me.

I know things are getting better as people are annoyed as opposed to apathetic :-)

Testing Methodolgy – The Groundwork January 20, 2010

Posted by mwidlake in Perceptions, Testing, Uncategorized.
Tags: , ,
add a comment

<Previous PostNext Post …>

I want to start learning about using SQL AUDIT, as I mentioned a couple of days ago.

First question. What do I want to know about SQL AUDIT? I might just have thought “well, I do not know much about this area and I think I should – so I want to know everything”. That is OK, but personally I find it helps to make up a specific aim. Otherwise you can just flounder about {well, I do}. In this case I have decided I want to know:

  1. The options for auditing who is selecting key tables.
  2. How to audit when those key tables get changed.
  3. The impact on performance of that audit, and if it could be an issue.
  4. (4) follows on from (3), in that I want to find out how to control that performance impact.

For any of you who have been or are code developers, you hopefully appreciate test-driven coding. That is, you decide up front what the new code must achieve and design tests to ensure the code does it. You do not write any code until you have at least thought of the tests and written them down in a document. {Ideally you have written the tests and built some test data before you start, but then in an ideal world you would get paid more, have more holidays and the newspapers would tell the truth rather than sensational rubbish, but there you go :-) }

I do not think that learning stuff/testing as much different from developing code, thus the list above. I now know what I want to understand.

What next? I’m going to go and check the Oracle Documentation for the feature. And I am very careful to check the documentation for the version I will use. This is 10.2 for me. I know, the Oracle documentation can be pretty dry, it can be rather unhelpful and it is not very good at examples. But you can expect it to be 90% accurate in what it tells you. You can also expect it to be not-very-forthcoming about the issues, gotchas and bits that don’t work. {I have this pet theory that the official documentation only mentions how things do not work once a feature has been out for a version and people have complained that the documentation should let on about the limitations}.

So, for SQL AUDIT I suggest you go and read:

  • Concepts Manual, chapter 20 Database Security. If I am not rushed I would read the whole chapter, I might realise that what I want to do is better done with some other tool (If I wanted to see who had changed records months down the line, I think I would pick up that database triggers were a better bet, for example).
  • SQL Reference, chapter 13 , the section on AUDIT (no surprises there). I do not do much more than read through the SQL manual once though, as frankly I find it pretty useless for explaining stuff, but it puts into your head what the parts of the command there are and pointers to other parts of the documentation. I’ll read the concepts manual with more attention. In this case, the manual will lead me to:
  • Database Security Guide chapter 8. Which is pretty good, actually.
  • My next source of information, may not immediately spring to mind but I find it very valuable, is to find out which data dictionary objects are involved in the feature. In this case, the previous sources would lead me to go to the Database Reference and check out:
  • DBA_AUDIT_TRAIL, DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS. And of course, SYS.AUD$. {I actually queried DBA_OBJECTS for anything with the word “AUDIT” in it, check out all the tables and also had a quick peak at the text for any views, which would have led me to SYS.AUD$ if I did not already know about it}.

Why do I go and look at the data dictionary objects and the reference guide? After all, is it not nerdy enough to have put myself through reading the SQL Reference manual? {and let us be honest, it is rarely enjoyable reading the SQL Reference manual}. Because  I want to know how it works, not just how to use it. Seeing the table give me a lot of information and the description of the columns may well tell me a lot more. First thing, SYS.AUD$ only has one index, on a column SESSIONID# (I know, there is another column in the index, I need to get to a DB to check this). Any queries not via this index are going to scan the whole damned thing. Right off, I suspect this will be an issue.

I will DESC the tables, see if there is already any information in them. In this case, there was not. A clean sheet.

Why did I not just go to Google (Bing, Yahoo, whatever) and search? Because I can trust the manuals to tell me stuff which is mostly true and is valid for my release. Not stuff about older versions, about later versions, urban myths or just outright fallacies. The manuals certainly will not tell me everything, far from it, but what it does say is solid stuff. With this reliable start I can now go to other sources and have some chance of filtering  all the other stuff that is Out There. Once filtered, it is probably a lot more informative and easier to digest than the manuals.  

I’ll ramble on about that next posting.

New Year, same old rambling thoughts January 5, 2010

Posted by mwidlake in Blogging, Perceptions.
Tags: ,
3 comments

It’s not Friday but, heck, it’s a New Year, there are many of us who might appreciate a non-techie, pointless ramble at the start of the first full working week of a new decade…A Friday Philospohy for the New Year. {If anyone wants to point out the New Decade starts on 1st Jan 2011, go take a running jump – popular opinion is against you, even if logic is for you}.

I found the UKOUG techie conference this year particularly interesting as it was the first major meeting I have been to since I started blogging, and I came across two main opinions about my attempts:

Those who like my blog as it is “chatty” and rambles a bit.
Those who dislike it – because it is “chatty” and rambles a bit…
{oh, and the third opinion, the most common, of utter ignorance of my blog – there goes the ego}.

Well, you can’t please everyone. I was a little saddened, however, as I spoke to a couple of people I really admire in the Oracle Knowledge world and they landed on the “chatty and rambling – bad” side of things. Damn. But they are so good at what they do, I forgive them. The swines.

But then I remembered what I said to a fellow blogger the other month. We bloggers/twitterers all put forward what we blog about in our own style. We might not blog something that is new, we might blog something that is “well known”, but we put it in our own style. Some like it, some do not. It matters not, so long as it adds to the sum of decent knowledge out there.
Some will hate our style and not read, some will read and enjoy. So long as the information gets out there to more people, that is fine.

So, do I think everything I blog is decent knowledge? Oh, I wish. I like to think it is mostly there {and I wish it was all correct} but I am realistic. I test most of what I blog, or I have lived for real most of what I blog, but I will make mistakes. And sometimes I will hit the edge of something good and I put it up there in the hope others will contribute {like the recent one one translating min-max column values into human readable stuff}. And often people do contribute and that is really, really good.

But I do and will continue to make mistakes, be daft, or just put things poorly. I have learned a fair bit in the last 8 months about written communication, the art of communicating to a global audience and also about how not to spread a topic over several weeks as you hope you can “just finish of those half-written blogs in an hour or two” and find it takes weeks. If anyone wants to give me any constructive criticism, please do, but maybe use my email (mwidlake@btinternet.com) rather than flame my postings.

And my last rambling thought for the start of 2010? I am probably going to post less in the next 6 months. I am always sad when the blog by someone I enjoy goes very quiet, but then we all have real jobs to do, so I try to be patient. In my own case, I have noticed I now read a lot less of other people’s blogs as writing my own takes so long. And I am missing too much. There are blogs I really admire or I have discovered in the last 6 months (sometimes both) that I simply fail to really read and they know stuff. So I need to read them. I am going to try and maintain a steady 2-3 blog entries a week, but for the next 6 months I am going to concentrate on learning. Something blogging has taught me is I am really quite ignorant :-)

Good wishes for 2010 to all and everyone who stumbles across my ramblings.

What were you doing 10 years ago? December 24, 2009

Posted by mwidlake in Perceptions.
Tags: , ,
4 comments

It is coming towards the end of December 2009. What were you doing 10 years ago today? If you were at school or college I don’t want to know, it just depresses me. You might have been doing last-minute Christmas things, you could have been traveling to see friends , loved ones or maybe {and often less pleasurably} relatives. If, however, you were working in IT I probably know what you were doing:

You were somehow involved in preparing for “The Y2K bug!!!” (Cue dramatic drum roll, ominous music, thunder-and-lightening video and the quiet shrill laughter of consultancy firms running all the way to the bank).

Remember that? I’m a little surprised not to have seen anything much in the media yet celebrating it, {I’ve not seen it in the UK anyway}, which is odd as it was such a very big deal back then. You remember? All the nuclear power plants were going to blow up, air control systems go haywire, bank systems crash and generally the whole of modern civilisation was going to crumble.

It did not of course. It’s biggest impacts seemed to be firstly to give old Cobol and Fortran programmers a bit of a financial boost to help them bolster the pension fund and secondly so much time, effort and planning was spent on Y2K preparation that 75% of other IT programs were shut down to cope. There certainly seemed a little less work to be had in the immediate aftermath.

I never decided who was more to blame for the hype and the fear. The Media, who can never pass a chance to boost revenue by outrageous scare tactics, or business/it consultancies who can never pass a chance to boost revenue by… I better stop there, in case I ever decide to go back to working for a consultancy.

I personally learnt a couple of things.

One was to prepare. In my particular case, I had planned a big Y2K party with a bunch of friends, hired a big house to hold a dozen of us plus kids and found somewhere to buy big fireworks from. All in 1998. And for 18 months before the event told anyone I went to work for that I would not be available for that particular week. I put it into my contract. Of the two or three companies I picked up contracts with during that period, none of them batted an eyelid when I mentioned this. Of course, this meant nothing. With 3 months to go before Y2K, the missive came rolling out from top management that no one, absolutely no one in IT was being allowed to take New years eve off.
I said to my boss “except me”. No, no exceptions. “It’s in my contract, I stated when I joined I was not available that week”. No exceptions. “Bye then”. Huh? “Well, I said at the time and I am sorry to upset you, but you see, this is a job, we had an agreement and what I have organised is my life and well, you lose”. I was a little more diplomatic about it, but I insisted. After all, we had fully Y2K tested the app I was responsible for and I had an agreement.
I had the week off (with a mobile phone by my side, admittedly, but I was not in a fit state to do much by the time midnight came around). I learnt that if you have an agreement and you calmly refuse to capitulate, and you negotiate a little, you can avoid “no exceptions”. {My friend Nasty Mike took the more direct approach of swearing loud defiance. He won also, but maybe with more bad feeling…}

The other thing I learnt was that companies will not pay less than they expect for a job. The five of us had written this app and it used four digit year dates all the way through the system. It was on Oracle 8. It worked. But no, Top Management wanted the system Y2K proving. So they asked a company to test it. This company wanted something like £50,000 to test it and it was to come out of our development budget. Ouch. That was pretty much half the budget.
So one of the team put forward a proposal to Y2K test the system via their company, for about £5,000.This was refused; it was too cheap to be believed.
So we put exactly the same proposal forward through another of our companies for £15,000 plus expenses and an exorbitantly hourly rate if extra work was needed.
This proposal was accepted.
So we did the work, we ran all the tests we specified, rolled the system past Y2K, repeated the tests, then…did a full refresh of the O/S, oracle and the app and recovered a full backup from before the initial tests. We were delayed by 24 hours as central IT screwed up the full oracle restore, so we got to charge the exorbitant hourly rate.
We handed the test results pack to the central IT team and their one question was “Why had we refreshed the O/S and re-installed Oracle? Well, we said, how do you know that going past Y2K had not set some internal variables within the O/S or the database that just setting back the system clock would not fix? The O/S is a complex thing.
The head of central IT looked ever so worried. No one had mentioned that before. And they had spent a lot on external Y2K testing consultancy…

Isn’t business odd at times?

The Evenings are Drawing Out December 14, 2009

Posted by mwidlake in Perceptions.
Tags: ,
4 comments

When do the evenings start drawing out? This year, 2015, it is December 13th. On that day sunset will be 15:51 and about 30 seconds in London. On the 14th it will be a few seconds later . By the 16th of December, the sun will resolutely stay in the sky until 15:52 {and a few seconds}. The days will be drawing out at last. {the exact times will be different for other cities and countries but the DATE will be the same – see the links at the bottom of this page to check for your town and country}

English Sunset by Angie Tianshi

But it is not the shortest day of the year {I should say daytime really, all days are the same length based on the time it takes for the earth to spin once in relation to the galactic centre.}

What is the shortest day, I hear you all cry?

This year, 2015,

The shortest day is December 22nd

The date with the shortest period of daylight {in the Northern hemisphere} is the the 21st/22nd December, depending on how long ago the last leap-year was. And everyone knows that the the shortest day will also be the day where the sun sets earliest, it makes sense.

Except it does not quite work like that.

We probably all remember from our school days that the earth goes around the sun at an angle from the “vertical”, if vertical is taken as at 90 degrees to the circle the planet takes as it spins around the sun. Think of it like an old man sitting in a rocking chair. He is rocked back in his chair, head pointing back away from the sun in the Northern Hemisphere’s winter and feet pointing slightly towards the sun. Come Midsummers day, around June 24th he has rocked forward, head pointing towards the sun for the Northern Hemisphere summer. One rocking motion takes a year. That rocking motion gives us all summer and winter.

The other complication is that the earth does not go around the sun in a perfect circle. You might remember from diagrams of comets at school that they circle the sun in a big “egg” shape, sweeping in towards the sun, swinging around close to it and then looping out in the solar system before coming back around and in. Well, Earth and all the planets do the same slightly. We are actually closest to the sun during the Northern Hemisphere winter – the day we are closest will be Jan 2nd 2016. Or maybe Jan 3rd… there is not a lot in it. It is interesting that most people who live in the Northern Hemisphere just assume we are furthest from the sun’s warmth in our winter. Well, we are closest. We are furthest around the good ‘ol 4th July.

A result of being closer to the sun is that we are move through our orbit around the sun slightly faster – when in orbit, the closer you are to what you are orbiting the quicker you move. Our day length is made up of the time it takes the earth to spin  and also about 1/365th of a day – as we in effect spin one extra time in relation to the sun by going around the sun each year. That is why I said the earth spins at the same rate every day when compared to a more “fixed” point of reference like the galactic centre. It is about 23 hours, 56 minutes and 4 seconds. The extra 3 minutes and 5-ish seconds is taken going around the sun a bit and that time varies depending how fast we are orbiting.

Thus at closest approach (our winter here in the UK) solar noon {sun at highest point} to solar noon is less than 24 hours and in our summer it is slightly more than 24 hours. But we do not change our clocks, that would be too complicated, we just let them stick to 24 hours. And, as a result, the clock day and the astronomical day are slightly different.

If you think of a day as a white bar on a black string of 24 hours, that white bar gets longer and shorter as we go through summer and winter. But the bar also moves slowly left and right along the string as the year progresses.

The two don’t quite match as, not only is the earth like an old man rocking in his chair, he is also slumped slightly to one side – he is not sitting up straight.

So there you go. Here in the UK this year (2015)

  • The nights start drawing out on the 13th December.
  • The shortest period of daylight is the 22nd December.
  • We are actually closest to the sun, despite it being our winter, on the 3rd January 2016.

And just some interesting things to remember:

  • A day as measured from noon to noon (sun at it’s highest point) varies by a few minutes over the year but on average is 24 hours.
  • Part of that day length is not the earth spinning but the earth going around the sun – about 4 minutes.
  • Summer and Winter is not due to closeness to the sun but the tilt of the Earth’s axis in relation to the orbit around the sun.
  • Earth is slumped to one side in it’s orbit
  • Even though this is all simple “clockwork” Newtonian mechanics and mathematicians can work it out very accurately, it is not as simple as it seems.

If you take into account the tiny changes made by the moon and other planets (Mars does have a slight influence on our orbit) it gets even more complex, but those differences are tiny tiny tiny in any given year. Over a million years they make a difference though.

 

The below tables will help you look up sunset, sunrise, day length and all those things.  I include one for the UK and one for Australia. The jolly nice site the links go to allows you to change the location to wherever you are in the world (well, the nearest Capital).

Table of sunrise/sunset times for London

Table of surise/sunset for Sydney, Australia

What has this to do with Oracle, Database Performance and my day job? Nothing much, except to highlight that the obvious is not always correct, just as it is with Databases, IT and in fact science in general.

I’ll finish with a sunset picture from Auz. Ahhhh.

Outback sunset from ospoz.wordpress.com

The Frustrated User’s perspective. November 28, 2009

Posted by mwidlake in Perceptions.
Tags: ,
1 comment so far

I got the below email from a friend this evening. Said friend does not work in IT. He works in a large organisation with a large and active IT department that might just be forgetting they provide a service as opposed to laying down the law…

****************************************************************
Hi Martin

For the last few weeks since {an edited out software virus disaster} we have been bombarded with unsolicited security policies from I.T. They pop up during the 10-15 minutes it takes to logon to our computers. You then have to download the policy and sign at the bottom to say whether you accept or decline the policy. When I scanned through the 10th policy I was struck by the fact that none of it applied to my area of responsibility except for one small part that had been covered in excruciating detail in one of their previous pathetic attempts at communicating what is expected of us. And all said missives using what looks like a variation of the english language. Having skipped the policy during a number of recent logons I was now being informed that it is “mandatory” to accept the policy or decline it giving a reason. I declined giving the above observation on the lack of relevance to my role as a reason.

I have now been informed that it is not possible to issue only the relevant policies to individuals (and presumably having identified this is not possible, have not bothered trying in the first place?) and in any case there might come a time when I “might” be given a task where the latest I.T policy applies and therefore I have to be aware of the existance of the policy. I think this latest one was something to do with purchasing software packages from suppliers -although this isn’t entirely clear. There is no way that I would be allowed to purchase software packages, which is a shame as there are off the shelf products that do what we require, whereas the in-house system foisted upon us simply does not provide any reliable or useful information what-so-ever.

The following senario occurs to me. I write a policy on controlling legionella – not unreasonable given that we have swimming pools, showers, air con etc. in our premises. I then send a copy to every employee requiring them to open it — expect them to read it —- understand it —- and accept it, “just-in-case” they get asked to go and run a sports centre. What response do think I would get?

Although the risk of catching legionella is low, people have died as a result, but we do not require everyone to sign a policy for this or any of the other more serious hazards they face at work. I am not aware of any software-purchasing-related deaths of late. For dangerous stuff employees sign one policy when they join the organisation. If they have to deal with a hazard we make them aware by warning them about it and if necessary give them additional training, guidance and support so that they can manage the risk in accordance with the overall policy.

Perhaps we have got this wrong. Maybe we should require all computer users (just for example) to complete a workstation assessment online every day when they start work – and if they don’t their computer should blow up in their face and a guilotine then drop from the ceiling removing their hands so they can’t sue for RSI or eyestrain.

That’ll teach them
************************************************************

I hope I have never been responsible for inflicting enough inconvenienve on my users to make them as aggrieved and angry as my friend.. Thing is, I now worry that I might have…

Friday Philosophy – Memory (of the Human Kind) November 14, 2009

Posted by mwidlake in Perceptions, Uncategorized.
Tags:
7 comments

One of the Joys of having been around for a while (I’ve hit that point where I occasionally find myself working with people who were not born when I got my first job in IT) is that you start to re-learn things. Well, I do. {I have to point out that, this being a Friday Philosophy, you will learn nothing yourself, especially not about Oracle. from this opost. If you want Oracle knowledge you might want to pop by Dion cho, Tanel Poder or Richard foote}

Several times over the last couple of weeks I have learnt something “new” and realised a little while later that I already knew it, but had forgotton (BLEVEL is one, consistent gets being impacted by array fetch size is another) . This is not something to be proud of – not knowing something you once did is far worse, and slightly worrying, then never having known it…

I like to think that it is all the new stuff I am learning, pushing previous knowledge out of my brain’s buffer cache {if it is not warmed by re-using it}, but I suspect that it might just be an uptime-related memory leak. A bit like having your database open for a few months and doing a lot of work that includes massive PL/SQL in-memory tables and hash joins (pickler fetches). No one seems sure of the exact mechanics, but after a while you have a hell of a lot less spare memory in your serever than you started with :-)

Maybe the memory loss is closer to the “pickler query” analogy than I thought, you can preserve soft tissue for a long time in alcohol. I’ll have another glass of wine and think on that.

This Forgetting Stuff was actually a major factor to my starting a blog. I was in the process of putting a load of notes on Oracle Odditites and things I had learnt from years past onto a web site so I could get at it from wherever I was in the world, and a friend told me I was being stupid – I should put them on a blog. So I did. There are only two little issues with this.

  • I can’t remember what I have already put on my blog.
  • I’ve forgotten where I put the ZIP disk with the notes on.

So I was contemplating this drift of knowledge and two things struck me.

1) I reckon that the very best people, in any area of expertise, are blessed with excellent memories. There is a comedian in the UK called Stephen Fry, and he is renowned for being stunningly clever. I think he probably is stunningly clever, but he also remembers everything he has learnt (Rory McGrath is another UK comedian with a perfect memory, but somehow he lacks the charm of Stephen Fry, and he has a beard, so not so highly renowned).
2) My poor memory is not maybe such a bad thing. I don’t have to get so upset when a whole chunk of my Oracle knowledge becomes obsolete. I used to be really good at sizing objects and utilizing space, taking into account the 5-block minimum, getting the extents to fit nicely into the datafiles, little scripts to resize segments into a small but sensibele number of extents to reduce wasted space, considering initrans, pctfree, pctused… Pretty much all pointless now :-)

Buffer Cache Hit Ratio – my “guilty” Confession November 1, 2009

Posted by mwidlake in Perceptions, performance.
Tags: , ,
14 comments

My Friday Philosophy this week was on Rules of Thumb on buffer gets per row returned.

Piet de Visser responded with a nice posting of his own, confessing to using ratios to help tuning {We seem to be playing some sort of blog-comment tag team game at the moment}.

Well, I have a confession so “guilty” or “dirty” that I feel I cannot inflict it on someone else’s blog as a comment.

I use the Buffer Cache Hit Ratio.

And the Library Cache Hit Ratio and the other Ratios.

As has been blogged and forum’d extensively, using these ratios is bad and stupid and anyone doing so does not know what they are doing as they do not help you solve performance problems. I mean, hell, you can download Connor McDonald’s/Jonathan Lewis’s script  to set it to what you want so it must be rubbish {go to the link and chose “tuning” and pick “Custom Hit Ratio” – it’s a rather neat little script}.

The point I am trying to make is that once the Buffer Cache Hit Ratio (BCHR) was wrongly elevated to the level of being regarded as a vital piece of key information but the reaction against this silly situation has been that it is now viewed by many (I feel) as the worst piece of misleading rubbish. Again a silly situation.

I think of the BCHR as similar to a heart rate. Is a heart rate of 120 good or bad? It’s bad if it is an adult’s resting heart rate, but pretty good if it is a kitten’s resting heart rate. It’s also probably pretty good if it is your heart rate as you walk briskly. Like the BCHR it can be fudged. I can go for a run to get mine higher, I can drain a couple of pints of my blood from my body and it will go up {I reserve the right not to prove that last one}. I can go to sleep and it will drop. Comparing my resting heart rate to yours (so like comparing BCHRs between systems) is pretty pointless, as I am a different size, age and metabolism to you {probably} but looking at mine over a year of dieting and exercising is very useful. If only I could keep up dieting and exercising for a year…

So what do I think the much-maligned Buffer Cache Hit Ratio gives me? It gives me what percentage of sql access, across the whole database activity, is satisfied from memory as opposed to disc. Or, put another way, the percentage of occurences a block has to be got from the I/O subsystem. Not how many blocks are read from storage or memory though, but you can get that information easily enough. As Physical IO is several orders of magnitude slower than memory access {ignoring I/O caches I should add} , it gives me an immediate feel for where I can and can’t look for things to improve.

If I am looking at a system that is overall very slow (eg high process wait queues under l/unix, the client has said the system is generally slow) and I see that the BCHR is low, say below 90%, this tells me I probably can get some performance increase by reducing physical access. I’ll go and look for those statements with the highest physical IO and the hottest tablespaces/objects in the DB.
If the BCHR is already up at the 99% level, I need to look at other things, such as tuning sort, looking at removing activity in the database, to be very mindful of nested loop access where maybe it is not the best access method (very likely due to old stats on tables).

When I have got to know a system and what it’s BCHR generally sits at, a sudden change, especially a drop, means there is some unusual physical IO going on. If the phones start going and someone is complaining “it’s all slow”, the BCHR is one of the first things to look at – especially as it is available from so many places.

Another thing the BCHR gives me is, if I am looking at a given SQL statement or part of an application, it’s specific BCHR can be compared to the system BCHR. this does not help me tune the statement itself, but I know if it’s specific BCHR is low then it has unusually high IO demands compared to the rest of the system. Further, Reducing it might help the whole system, so I might want to keep an eye on overall system throughput. If I reduce the statement’s execution time by 75% and the whole system IO by 1%, the client is likely to be more happy, especially if that 1% equates to other programs running a little faster “for free”.

So, I don’t use the BCHR to tune individual statements but I feel confident using it to track the general health of my database, so long as I am mindful of the impact of new functionality or upgrades. It’s a rule of thumb. It’s a database heart rate. (and so is redo generation and half a dozen other things).

Friday Philosophy – How many Consistent Gets are Too Much? October 30, 2009

Posted by mwidlake in Perceptions, performance.
Tags: , ,
2 comments

One of my good friends, Piet de Visser commented on a recent post that “380 {consistent gets} is too much” per row returned. He is right. He is wrong. Which?

Piet is actually referring to a given scenario I had just described, so he is probably {heck, he is spot on} correct as his comment was made in context – but it set me to thinking about the number of times I have been asked “is the number of consistent gets good or bad” without any real consideration to the context. The person asking the question usually just wanted a black/white good/bad ratio, which is what Piet also mentioned in his comment, a need to discuss such a ratio. I am on holiday in New England with my second bottle of wine, memories of having spent the day eating Lobster, kicking though Fall leaves and sitting by a warm fire reading a book, so I am mellow enough to oblige.

Sadly, out of context, no such ratio probably exists. *sigh*. There evaporates the warm glow of the day :-).

The question of “is the number of consistent gets per row good or bad?” is a bit like the question “is the pay rate good enough?”. It really depends on the whole context, but there is probably an upper limit. If I am helping my brother fit {yet another} kitchen then the pay rate is low. He has helped me fit a few, I have helped him fit a few, a couple of pints down the pub is enough and that equates to about 30p an hour. Bog standard production DBA work? 30-60 pounds an hour seems to be the going rate. Project Managing a system move that has gone wrong 3 times already? I need to factor in a long holiday on top of my normal day rate, so probably high hundreds a day. £10,000 a day? I don’t care what it is, I ain’t doing it as it is either illegal, highly unpleasant, both or involves chucking/kicking/hitting a ball around a field and slagging off the ref, and I ain’t good at ball games.

I have a rule of thumb, and I think a rule of thumb is as good as you can manage with such a question as “is {some sort of work activity on the database} per row too much?”. With consistent gets, if the query has less than 5 tables, no group functions and is asking a sensible question {like details of an order, where this lab sample is, who owes me money} then:

  • below 10 is good
  • 10-100 I can live with but may be room for improvement
  • above 100 per record, let’s have a look.

Scary “page-of-A4” SQL statement with no group functions?

  • 100-1000 consistent gets is per row is fine unless you have a business reason to ask for better performance.

Query contains GROUP BY or analytical functions, all bets are pretty much off unless you are looking at

  • a million consistent gets or 100,000 buffer gets, in which case it is once again time to ask “is this fast enough for the business”.

The million consistent gets or 100,000 buffer gets is currently my break-even “it is probably too much”, equivalent to I won’t do anything for £10 grand. 5 years ago I would have looked quizzically at anything over 200,000 consistent gets or 10,000 buffer gets but systems get bigger and faster {and I worry I am getting old enough to start becoming unable to ever look a million buffer gets in the eye and not flinch}. Buffer gets at 10% of the consistent gets, I look at. It might be doing a massive full table scan in which case fair enough, it might be satisfying a simple OLTP query in which case, what the Hell is broken?

The over-riding factor to all the above ratios though is “is the business suffering an impact as performance of the database is not enough to cope”? If there is a business impact, even if the ratio is 10 consistent gets per row, you have a look.

Something I have learnt to look out for though is DISTINCT. I look at DISTINCT in the same way a medic looks at a patient holding a sheaf of website printouts – with severe apprehension. I had an interesting problem a few years back. “Last week” a query took 5 minutes to come back and did so with 3 rows. The query was tweaked and now it comes back with 4 rows and takes 40 minutes. Why?

I rolled up my mental sleeves and dug in. Consistent gets before the tweak? A couple of million. After the tweak? About a hundred and 30 million or something. The SQL had DISTINCT clause. Right, let’s remove the DISTINCT. First version came back with 30 or 40 thousand records, the second with a cool couple of million. The code itself was efficient, except it was traversing a classic Chasm Trap in the database design {and if you don’t know what a Chasm Trap is, well that is because Database Design is not taught anymore, HA!}. Enough to say, the code was first finding many thousands of duplicates and now many millions of duplicates.
So, if there is a DISTINCT in the sql statement, I don’t care how many consistent gets are involved, of buffer gets or elapsed time. I take out that DISTINCT and see what the actual number of records returned is.

Which is a long-winded way of me saying that some factors over-ride even “rule of thumb” rules. so, as a rule of thumb, if a DISTINCT is involved I ignore my other Rules of Thumb. If not, I have a set of Rules of Thumb to guide my level of anxiety over a SQL statement, but all Rules of Thumb are over-ridden by a real business need.

Right, bottle 2 of wine empty, Wife has spotted the nature of my quiet repose, time to log off.

When do We Learn #2 October 20, 2009

Posted by mwidlake in Blogging, Perceptions.
Tags:
4 comments

I exchanged a couple of mails with a friend a few weeks back about how the same topic can arise in a couple of blogs at the same time. Well, I had just blogged myself on when we learn and, blow me over with a feather, that Jonathan Lewis goes and post in a similar vein. He must be nicking ideas off my blog :-) {and yes, I am being tongue-in-cheek here}. We both post thought about needing spare capacity in your life to be able to spend the time to really understand how something works. Yes you learn a lot in the heat of a crisis, but you rarely reallu understand the details, ie become an expert, without having time to digest and qualify that knowledge.

I did write a long comment on his posting, including some links back to my own meandering thoughts on the topic, then realised that I would come across as a bit “me too” so I trimmed it and took out the links. But that is part of why I do my own blog, I found I was spamming other people’s pages with my diatribes and so decide to spam my own. {And I know I am meandering, I’m a bit sleep-deprived, stream of consciousness at the moment}. So here I can refer back to my own stuff and say “me too”, but you are already here reading this, so you only have yourself to blame :-)… Anyway, I wanted to refer back to a very early blog of mine about how much knowledge is enough. I try and make the point that you do not need to know everything, you can become a small-field or local expert just by being willing to learn a bit more.

Jonathan raises the point that he does not have a full time commitment to one client and so he has the luxury to investigate the details and oddities of what he looks into. He suggest this is a large part of why he is an expert, which I feel is true, and I am very happy to see one of the Oracle Names acknowledging that relative freedom from other pressures is key to having the luxury to chase down the details. Those of us in a full time role doing eg DBA, development or design work, have more than enough on our workday plates to keep us too busy. We cannot be top experts, we have a boss to satisfy and a role to fulfill. {Jonathan does not mention that chosing a career where you have luxury of time is also a pretty brave choice – you stand a good chance of earning a lot, lot less whilst working very hard to establish enough of a reputation to be able to earn enough to feed yourself and the cat}.

But this is not a black and white situatuation. There is room for many of us to become experts in our domain or in our locality. Our breadth of knowledge may never be as wide as others, we may not know more than anyone else in a given area {and let’s face, logically there can only be one person who knows the most about a given topic, and that one person is probably in denial about their superiority, which seems to be a defining quality of an expert – it is not so much humility I think as an acknowledgement of there being more to know and a desire to know it}. However, most of us can become the person in our organisation who knows most about X, or who can tie A, B and C together in a more holistic way than others (and that can be a real trick you know). There are always the top experts that you can call on for the worst problems, but you could become the person people come to first.

My advice would be to not try and learn everything about all aspects of Oracle, because you can’t, but rather learn a lot about one or two areas {and consider areas that are more unusual, not just “tuning SQL” or “the CBO”} and expand just your general knowledge of the wider field. And never forget that there is more to learn. So long as you are taking in more knowledge and understanding, you are improving. The best way to do it? Don’t just read other people’s stuff, try teaching someone else. It never ceases to amaze me how stupid I realise I am when I try and show someone else how something works. But that’s OK, so long as they learn it’s fine. If I learn as well, it’s great, and I nearly always do.

I’m getting on a bit, I think I am finally getting the hang of the idea that the more you know the more you realise you don’t know, I wish I knew that when I knew nothing.

Follow

Get every new post delivered to your Inbox.

Join 209 other followers