Christian Bilien’s Oracle performance and tuning blog

March 6, 2007

Why you should not underestimate the LIO impact on CPU load

Filed under: Oracle — christianbilien @ 4:51 pm

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.

cpuload.JPG

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

Executions: 167,979

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.”

 

1 Comment »

  1. […] under: HP-UX — christianbilien @ 5:41 pm I already mention how important logical I/O (see “Why you should not underestimate the LIO impact on CPU load” ), knowing that most data base systems need much more CPU to access memory than to execute actual […]

    Pingback by Memory partitioning strategy: avoiding design traps on high end HP-UX systems (1/2) « Christian Bilien’s Oracle performance and tuning blog — March 25, 2007 @ 5:44 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: