Oracle Tips by Burleson Consulting
Back in the mid-1960’s, Gordon Moore, the director of the research
and development labs at Fairchild Semiconductor, published a
research paper titled
“Cramming More Components into Integrated Circuits.” In his
paper, Moore performed a linear regression on the rate of change in
server processing speed and costs and noted an exponential growth in
processing power and an exponential reduction of processing costs.
This landmark paper gave birth to “Moore’s Law,” which postulated
that CPU power will get four times faster every three years as
illustrated in Figure 12.1.
Law for CPU speed
In the 1970’s, a 4-way Symmetric Multiprocessing (SMP) processor
cost over three million dollars. Yet today, the same CPU can be
purchased for less than three thousand dollars. CPUs will increase
in speed four times every three years and only increase in cost by
While Moore’s Law is generally correct, the curve is not linear.
The formerly marginal rate of advances in CPU speed has increased
dramatically in the past decade, most notably with the introduction
of the Itanium2 processors.
The large RAM data buffers enabled by 64-bit operating systems have
shifted the bottleneck for many Oracle databases from I/O to CPU.
Oracle10g accommodates this shift to CPU consumption by providing a
new cpu_cost feature that
allows Oracle’s cost-based SQL optimizer to evaluate SQL execution
plan costs based on predicted CPU costs as well as I/O costs. This
is an adjustable feature in Oracle10g, and it is controlled by the
Even though it is true that a CPU bottleneck exists when the run
queue exceeds the number of processors on the server, this condition
does not always mean that the best solution is to add processors.
Excessive CPU load can be caused by many internal Oracle conditions
including inefficient SQL statements that perform excessive logical
I/O, non-reentrant SQL inside the library cache, and many other
conditions. Fortunately, Oracle 10g Enterprise Manager allows users to look back in time and find
these conditions, even though the immediate run queue issue has
While Moore’s law is quite correct for processor speed and cost,
many have over-generalized this principle as it applies to disks and
RAM. It is true that costs are continually falling for RAM and
disk, but the speed assumptions do not apply.
Law for Disk speed
The old-fashioned spinning platters of magnetic-coated metal disks
have an upper limit of spin speed, and the read/write head movement
speed is limited. In the early 1990’s, it became apparent that the
1950’s disk technology had reached the limits of its physical
capabilities, and it became necessary for disk manufacturers to add
on-board RAM caches to disk arrays and include asynchronous writing
mechanisms to continue to improve disk speed.
One glaring exception to Moore’s law is RAM speed as shown in Figure
Law for RAM speed
RAM has not made many significant gains in speed since the mid
1970’s. This is due to the limitations of silicon and the fact that
access speed in nanoseconds approaches the speed of light. The only
way to further improve the speed of RAM would be to employ a radical
new medium such as Gallium Arsenide.
This flat speed curve for RAM has important ramifications for Oracle
processing. Since CPU speed continues to outpace RAM speed, RAM
sub-systems must be localized to keep the CPUs running at full
capacity. This type of approach is evident in the new Itanium2
servers where the RAM is placed as close to the CPU as possible as
shown in Figure 12.4.
RAM in Itanium2 Servers
This represents the Oracle servers of the 21st century
(e.g. the UNISYS ES7000 series) which have a special L2 RAM that is
placed near the processors for fast RAM access. As we have seen,
low-cost RAM technology has dramatically changed the way that Oracle
databases are tuned.
SEE CODE DEPOT FOR FULL SCRIPTS
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: