Can you have too much cache?
by Donald K.
There is a great debate
about the rapidly-falling costs of RAM and the performance benefits
of full caching of Oracle databases.
Let's take a closer look at the
issues over large RAM data buffers, tuning by adjusting system
parameters and using fast hardware to correct sub-optimal Oracle
There is nothing wrong
with disk I/O and full caching can hurt Oracle performance.
Have you noticed that all
Oracle10g "world record" benchmarks use over 50 gig data caches? I
worked with one of these TPCC benchmarks and ran repeatable timings.
Up to the point where the working set was cached, the benefit of a
larger data cache outweighed the LIO overhead.
Also, using multiple
blocksizes also helped greatly and the appropriate blocksize was
used depending on the types of objects in the tablespaces.
For example, small OLTP
access rows like a 2k blocksize because you don't waste RAM
hauling-in block space you don't need. A 32k tablespace for index
range scans also showed a large, measurable performance improvement:
The bigger your cache,
the larger your LRU and Dirty List becomes.
This is true, but disk I/O
is even more expensive! For example, check-out the Sun Oracle 10g
Remember, the size of the
buffer cache depends on the size of the "working set" of
frequently-referenced data! A very large OLTP system might need a 60
gig KEEP pool:
Also, took into the
mechanism inside 10g AMM. It computes the marginal benefits of
additional data block buffers based of the costs of less PIO and
does indeed consider the costs of serialization overhead.
There comes a point
where, for a particular 'unit' of hardware, the marginal cost of the
increase in administrative overhead is greater than the marginal
decrease in costs due to less physical I/O.
For reads, disk I/O is
almost always shower then an LIO and full caching is great for
read-only databases such as DSS, OLAP and DW systems! For write
intensive database, a large cache can be a problem. That's why many
DBA's place high DML objects in a separate tablespace (with a
different blocksize), and map it to a smaller buffer.
Why waste time adjusting
initialization parameters and changing SQL optimizer statistics when
the real problem is bad SQL?
When I visit a client I
usually find thousands of sub-optimal SQL statements that would take
months to manually tune. To get a head-start, I tweak the instance
parms to broad-brush tune as much as possible to the lowest common
denominator. Then I can sweep v$sql_plan and tune the exceptions.
Tuning the instance first saves buckets of manual tuning effort and
lowering optimizer_index_cost_adj will sometimes remove sub-optimal
full-tables scans for hundreds of statement in just a few minutes.
What's wrong with that?
Throwing more memory at
the problem does NOT make it go away.
I had a client last month
will a REALLY messed-up database and it was HEAVILY I/O bound (85%
read waits). Instead of charging $100k to fix the mess I replaced
the disks to high-speed RAM-SAN (solid-state disk) for only $40k.
The system ran 15x faster, in less than 24 hours, and the client was
VERY happy. Granted, it's not elegant, but hey, why not throw
hardware at lousy code if it's cheap and fast?
Like it or not, disk will
soon be as obsolete as drums! I remember when 1.2 gig of disk costs
$250k and I can now get 100 gig of SSD for only $110k.
I have several fully-cached
clients (some using SSD and other with a huge KEEP pool), and their
databases runs great. . .
I expect that Oracle
Corporation will soon be working on a solid-state Oracle in a future
release where the caches will disappear completely.
Of course, tuning the SQL
is always the best remedy, and by reducing unnecessary consistent
gets you often reduce PIO too! But it makes sense to me to tune at
the system-level b y adjusting CBO parms (OIAC and
optimizer_index_caching) and by improving the CBO statistics:
Would you please explain
why I have a database running at a BCHR of 99.9 and performance is
A high buffer cache hit
ration with poor performance is often due to too many Logical I/O's
(high consistent gets from sub-optimal SQL) or high library cache
contention (excessive parsing).
Up to the point where the
marginal benefit of adding blocks to the buffer declines (the second
derivative of the 1/x curve of buffer utilization), tuning with BCHR
makes sense. Beyond the point where the working set is cached, the
marginal benefit from RAM data buffers declines:
Throwing hardware at an
Oracle performance problem addresses the symptom, and not the root
cause of the performance problem.
True, but I've never had a
problem throwing hardware (cache, faster CPU) at a messed-up system.
My client's often demand it because they don't want to pay a fortune
for SQL tuning. In many cases it's faster and cheaper. It makes
There are many system-level
"silver bullets" to Oracle tuning, and IMHO you would be foolish not
to try them. For example, if you could tune 500 DSQL statements by
adding an index or building an MV, why not? Consulting client demand
that you tune the whole system before tuning individual SQL
However, this will be a
moot issue in 24 months when SSD makes those stupid magnetic
Market Survey of SSD vendors for
There are many vendors who offer rack-mount solid-state disk that
work with Oracle databases, and the competitive market ensures that
product offerings will continuously improve while prices fall.
SearchStorage notes that SSD is will soon replace platter disks and that
hundreds of SSD vendors may enter the market:
"The number of vendors in this category could rise to several
hundred in the next 3 years as enterprise users become more familiar
with the benefits of this type of storage."
As of January 2015, many of the major hardware vendors (including Sun and
EMC) are replacing slow disks with RAM-based disks, and
Sun announced that all
of their large servers will offer SSD.
Here are the major SSD vendors for Oracle databases
(vendors are listed alphabetically):
2008 rack mount SSD Performance Statistics
SearchStorage has done a comprehensive survey of rack mount SSD
vendors, and lists these SSD rack mount vendors, with this showing the
fastest rack-mount SSD devices:
||performance metrics and notes
sustained external throughput, 400,000 random IOPS
1,00MB/s write with ×4 PCIe, 3 microseconds latency
Solid Access Technologies
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
R/W transfer rate, 35,000 IOPS
Choosing the right SSD for Oracle
When evaluating SSD for Oracle databases you need
to consider performance (throughput and response time), reliability (Mean Time Between failures) and
TCO (total cost of ownership). Most SSD vendors will provide a
test RAM disk array for benchmark testing so that you can choose the
vendor who offers the best price/performance ratio.
Burleson Consulting does not partner with any SSD vendors and we
provide independent advice in this constantly-changing market. BC
was one of the earliest adopters of SSD for Oracle and we have been
deploying SSD on Oracle database since 2005 and we have experienced SSD
experts to help any Oracle shop evaluate whether SSD
is right for your application. BC experts can also help you choose
the SSD that is best for your database. Just
call 800-766-1884 or e-mail.:
SSD support details.
vs. Flash SSD
the talk about the Oracle “flash cache”, it is important to note that there
are two types of SSD, and only DRAM SSD is suitable for Oracle database
storage. The flash type SSD suffers from serious shortcomings, namely
a degradation of access speed over time. At first, Flash SSD is 5
times faster than a platter disk, but after some usage the average read time
becomes far slower than a hard drive. For Oracle, only rack-mounted
DRAM SSD is acceptable for good performance:
Avg. Read speed
Avg. write speed