The role of Logical I/O in CPU consumption is often underestimated. Here is a practical case:
We have a poorly designed partitioning strategy which does not allow for any partition pruning. In other words, the range partitioning strategy will never contain an equal, between or any other clause that could be used by the optimizer for pruning. However, the number of active transactions is fairly low, 2 or 3 on average. A pretty good strategy had been so far to parallelize reads across the partitions. The downside is of course a CPU hog on top of a huge I/O throughput.
So our baseline before Feb 28th is a busy system, a low run queue and pretty good response times.
March 1st was a busy day, an unusual number of inserts where performed in a table which holds many indexes. The insert profile is:
Buffer gets: 16,739,612
CPU Time (s): 904.05
This shows that each buffer get “costs” 54 micro seconds on my SPARC IV domain (interestingly close to Cary Millsap’s 53 microseconds in “Why You Should Focus on LIOs Instead of PIOs”, although his article was written in 2001 or 2002 – wonder why we are putting that much million euros to buy those huge boxes – just joking, this time is of course dependent upon the sql call and the underlying objects).
So here we are, 100 000 inserts will cost approximately:
100000 x 50 microseconds x gets per insert (100 here) = 500s, 200000 inserts will cost 1000s, and so forth.
By the way, what is an Oracle LIO and why does it cost 50 microsecond when a memory page access time is roughly 100 nanoseconds?
Quote from “O’Reilly’s Optimizing Oracle Performance”:
An operation in which the Oracle kernel obtains and processes the content of an Oracle block from the Oracle database buffer cache. The code path for an Oracle LIO includes instructions to determine whether the desired block exists in the buffer cache, to update internal data structures such as a buffer cache hash chain and an LRU chain, to pin the block, and to decompose and filter the content of the retrieved block. Oracle LIO operations occur in two modes: consistent and current. In consistent mode, a block may be copied (or cloned) and the clone modified to represent the block at a given point in history. In current mode, a block is simply obtained from the cache “as-is.”