A few weeks ago I wrote Part III of this series – and fortunately (or unfortunately for my poor ego) an astute reader wrote to ask for some clarification on my Excel chart. Ends up my Excel formula had a mistake – so many thanks to Johannes V. Djernæs from Miracle A/S in Denmark. So I’ll be sending him a few free books and other goodies. Thus note that it can pay dividends to read and reply to these blogs. Now I’m off to double check my checkbook’s latest math as well – I’m probably already broke and don’t know it.

 

So here’s the corrected text and chart. As Homer Simpson would say – doh.

 

Let’s assume we have a Linux host running a Linux based Oracle database client. Let’s examine Scenario #2 from the chart below. Start by assuming that we create the host file system using the default block size: 2K. Let’s further assume that we do the same thing on the client – but that we size the database blocks at 4K. Thus each Oracle physical IO requests asks the client OS for two IO’s, and each client IO asks the host for one IO. That’s a total of four logical IO requests (although only two physical IO’s in reality – but note that there is overhead for each logical IO request, so larger numbers are worse). Now if the database block size had instead been 8K while the other factors had remained at 2K (i.e. Scenario #3), then the logical IO’ would instead now be DB -> Client = 4 and Client -> Host = 4 for a grand total of eight logical IO requests. Here’s a basic chart for some common combinations and their sometimes surprising overheads: