Top 60 Oracle Blogs

Recent comments

April 2011

Envisioning NFS performance

What happens with I/O requests over NFS and more specifically with Oracle? How does NFS affect performance and what things can be done to improve performance?

I hardly consider myself and expert on the subject, but I have yet to find a good clear  targeted description of NFS and especially NFS with Oracle on the net. My lack of knowledge could be a good thing and bad thing. A bad thing because I don’t have all the answers but a good thing because I’ll talk more to the average guy and  make less assumptions. At least that’s the goal.

This blog is intended as the start of a  number of blogs entries on NFS.

What happens at the TCP layer when I request with dd an 8K chunk of data off an NFS mounted file system?

Here is one example:

I do a

dd if=/dev/zero of=foo bs=8k count=1

where my output file is on an NFS mount, I see the TCP send and receives from NFS server to client as:

(the code is in dtrace and runs on the server side, see tcp.d for the code)

There is a lot of activity in this simple request for 8K. What is all the communication? Frankly at this point, I’m not sure. I haven’t looked at the contents of the packets but I’d guess some of it has to do with getting file attributes. Maybe we’ll go into those details in future postings.

For now what I’m interested in is throughput and latency and for the latency, figuring out where the time is being spent.

I most interested in latency. I’m interested in what happens to a query’s response time when it reads I/O off of NFS as opposed to the same disks without NFS. Most NFS blogs seem to address throughput.

Before we jump into the actually network stack and OS operations latencies, let’s look at the physics of  the data transfer.

If we are on a 1Ge we can do about 122MB/s, thus

122 MB/s
122 KB/ms
12 KB per  0.1ms  (ie 100us)

12 us  ( 0.012ms) to transfer a 1500 byte network packet (ie MTU or maximum transfer unit)

a 1500 byte transfer has IP framing and only transfers 1448 bytes of actual data

so an 8K block from Oracle will take 5.7 packets which rounds off to 6 packets

Each packet takes 12us, so 6 packets for 8K takes 76us (interesting  to note this part of the transfer goes down to 7.6us on 10Ge – if all worked perfectly )

Now a well tuned 8K transfer takes about 350us (from testing, more on this later) , so where is the other ~ 274 us come from?

Well if I look at the above diagram, the total transfer time takes 4776 us ( or 4.7ms) from start to finish, but this transfer does a lot of set up.

The actual 8K transfer (5 x 1448 byte packets plus the 1088 byte packet ) takes 780 us or about twice as long as optimal.

Where is the time being spent?

WordPress 3.1.2 Released…

WordPress 3.1.2 has been released. Happy upgrading…



Fast & Furious 5: Rio Heist…

It was only as I started to write this post about Fast & Furious 5: Rio Heist that I realised I’ve not seen the 4th film yet. I’m sure it’s not much different to all the others. This one has most of the old gang back together to drive around really fast and do lots of impossible stuff. It’s a nice bit of mindless escapism, just before getting back into my diesel Renault Clio to drive home at a sedate pace… :)



Perhaps I’m not making myself clear… Virtual Insanity…

What is it with virtualization? It’s like people remove their brain as soon as they hear the word. I get a lot of questions about virtual RAC installations and the vast majority of problems are due to people trying to run them on a host that can’t cut it.

  • When I explain you need 3-4G RAM for each VM node in a VirtualBox RAC installation, would you consider it sensible to try and build a 3 node virtual RAC on a host with 8G RAM? Do the maths. If you are attempting a virtual 11.2 installation with less than 3G RAM per node and you have problems, don’t come asking me because I will be out.
  • Do you really expect disk speed to be good when you put 8 virtual disks on to the system disk on the host machine? Do you think virtual disks magically run independently of the hosts disk subsystem? Putting all the virtual disks on one physical disk will work, but it will be so darn slow you will probably be leaving installations running overnight. Believe me, I’ve done it. If you want any sort of speed, spread the virtual disks around on separate physical disks.
  • Yes, you really are going to need all those IP addresses. Deal with it.
  • Use DNS or accept the fact there will be SCAN configuration errors during the installation. It’s one or the other baby!
  • Have you really done what my article suggests? Normally, after about a week of going back and forward I get a message saying something like, “Oh I fixed it. I didn’t bother doing step X before and once I did it it worked fine.” Don’t waste my time and yours. Follow the steps, or do it your own way and don’t blame me if it doesn’t work.
  • Use the same software I list in the installation. Don’t just assume that if it works fine with OL 5.x it’s going to work unchanged on OL6, SUSE x.x or Ubuntu (Lethargic Leaper) etc. If you want to try with different distributions that’s fine, but I’m probably not going to be able to help you because if there isn’t an installation guide for it on my site, it probably means I’ve not done it.
  • If it is the first Oracle installation you’ve ever done, don’t start by trying to install a virtual RAC. Learn to walk before you try running…



New Blog Round-Up

I've spotted a few useful posts lately and a few friends have started blogging so I thought I'd draw people's attention to them.

Neil Chandler is a UKOUG regular as well as a central and well-loved member of the informal London Oracle drinking massive (well, that's what I'll say about him in public anyway ;-)). He's an Oracle DBA and a contractor like myself so hopefully he can add a few interesting technical tales and gotchas as he encounters them. He's only just started blogging but hopefully if I draw attention to his blog, it will kick-start him past that critical start-up phase.

I'm researching SQL Plan Management for a client at the moment and whilst trawling the net for any useful resources, Maxym Kharchenko's Intermediate SQL kept cropping up. He has a number of posts about SPM that delve into the nuts and bolts and have proved really useful to me. It looks like there's other good stuff on there too.

But, with apologies to Neil and Maxym, I saved the best for last even thought it's absolutely nothing to do with Oracle. Over the past couple of years, I've been fortunate to work with some very smart, young and occasionally handsome people but that's more true of some than others ;-) One that it's been a pleasure to meet but is going to be lost to me all too soon is Gustav Andersson who, as well as being a top-notch Java guy (confirmed by other Java guys) has stuffed my head full of Agile/Scrum nonsense and been a good boss in recent times. (There's a lesson in there for managers everywhere - old guys can work perfectly well with much younger managers as long as they respect them!) But there's more to Gustav than that - a lot more - and, in retrospect, I realise I shouldn't have been surprised that he's about to embark on a magnificent venture. He will be blogging about it at The Modern Nomad and as soon as I read the first couple of posts I was as jealous of him as usual. You'll see what I mean when you read it. It's personal and far from light but it's so refreshing to read something completely different and nothing to do with Oracle.

The posts are currently just slightly in the past while he plays catch-up and he was reluctant for too much early publicity. As usual, there's plenty for me to jealous of there - a better template, better grammar, more substantial thoughts expressed more clearly and all from a Swede teaching me to write English. Actually, damn that guy. I'm glad he's on his way so he can stop the rest of us looking bad ;-) His blog is going to be great.

Good travels, man ...

The Final Adventures of Roger Wilco?

Regular readers might recall the previous adventures of Roger Wilco, the somewhat superior raccoon. (There are several links there, best read in order.)

Well last week I suspect we saw the final chapter in Roger's round-the-world trip which has included Birmingham, Malov, San Francisco, Malov, Birmingham, Cirencester and many points in between.  Through the magnificent efforts of some good Oak Table friends, he arrived with James Morle in London at the UKOUG Exadata event to complete the final leg. (I also finally took delivery of my new OT T-Shirt, which is very cool ...)

We all adjourned to a bar with outside tables and a wide selection of good beers so that Roger could say farewell to some of his new friends.

Left-Right - Roger Wilco, Paul Logan of Pythian, Dan Norris, James Morle, David Kurtz, Tanel Poder and the back of Peter Brink's head.

He almost didn't make it back though as he went missing again and I knew that I would be in deep trouble when I got home. When I saw the evil glint to Lisa Dobson's smile, I realised something fishy had been going on. He'd been kidnapped!

Once untangled, I was able to finally delivery him home to Edinburgh. His fur looks a bit bedraggled but he has lost none of his sense of superiority. In fact, he's much worse because he claims to have been places and seen things that a cuddly toy can never forget and the others are never likely to see. To say the atmosphere at home is a little frosty is an understatement!

I only hope that in time they bring him down to earth with a sharp bang and we can all get back to normal again. If not, I worry he might decide to go off on a new set of adventures.

As long as he doesn't find himself in this sort of situation.

Delayed Block Cleanout / ORA-01555

Here is a link to a collection of scripts that can be used for some entertainment (well, it probably depends on your personal definition of "entertainment"...) regarding "Delayed Block Cleanout". These scripts are meant to be used in a playground environment - do not attempt to get them close to anything important as they might have some undesirable side-effects. Please read the comments in the file header description before attempting to run them.

YouTube Playlist…or “What someone does during Easter…”

Currently on (DBA) standby duty for some mayor Dutch sites (eh databases). Was a bit bored so created a YouTube playlist while listening to some of my favorite bands/musicians…

“Stuff I Like” (YouTube playlist) – or how sad can life be during Easter…Pretty good, I guess, after hearing this.


Nulls in Ordering

You want to find out the tables with the highest number of rows in a database. Pretty simple, right? You whip up the following query:

select owner, table_name, num_rows
from dba_tables
order by num_rows;

And, here is the output:

OWNER         TABLE_NAME                       NUM_ROWS
------------- ------------------------------ ----------
SYS           L$1
SYS           L$2
... output truncated ...


How to ensure that you never give the wrong answer – as demonstrated by my bank’s telephone help system:

Stage 1 – after calling in through a special high-cost number, of course:

“{long message extolling the virtues of using the bank’s internet system for all your requirements}”

Stage 2 – you wait for the list of options to be recited

“Press 1 for …”
“Press 2 for …”

“Press 9 to speak to a member of the helpdesk”

Stage 3 – you decide the only relevant option is to press 9 to speak to a person:

“I’m sorry, I don’t understand what you want.” click, brrrrrrrrrrrrr …

Maxim: If you don’t want to fail, don’t even try.