| |
 |
|
Using faster hardware to correct an
Oracle performance problem
Oracle Tips by Burleson Consulting |
Does it make sense to "throw
hardware" at an Oracle performance problem and ignore the root cause of the
problem? Like all Oracle questions, the
answer is "it depends", and it depends on the exact nature of the performance
issue.
If the root cause requires an expensive and risky redesign or
re-writing thousands of lines of code, the answer may be "yes".
Over-stressed Oracle servers are not a myth
It's amazing that many Oracle professionals have never seen performance
degradation in a well-tuned database. The old adage "time takes time"
applies, and eventually any growing database will exceed the hardware resources.
Unfortunately, many root cause solutions are too
expensive and time-consuming to re-write (bad application code, “old” 3NF database design).
Burleson Consulting has fixed many systems after they wasted large sums of
money on self-proclaimed "Oracle Scientists" who made ludicrous recommendations.
In Oracle, most of the “let’s fix the root cause” solutions (let’s redesign this
thing for $2m) are seen by IT managers as ridiculous; too expensive and
time-consuming to re-write, a real-world constraint faced by all IT managers.
I tell my performance tuning clients that it's malfeasance for ANYONE who
claims Oracle expert status not to present clients with all of their tuning
options, (especially when they might cut-down on their consulting fees) by
failing to offer a fast, risk free remedy like faster hardware.
When you consider development time, testing time and re-certification of
a production application, it's easy to understand why managers are averse to
costly root cause fixes. Many Oracle experts agree, as noted here by Mladen Gogala, author of "Easy
Oracle PHP":
It's a myth that throwing hardware at the problem doesn't work.
I've seen hardware thrown at the problem, with good results. It
wasn't SSD, it was EMC cache, pumped up to an incredible proportion,
but the intention was the same and the result was, generally
speaking, satisfactory.
I go into great detail on the application of hardware-based tuning
solutions in my book "Oracle
Tuning: The Definitive Reference", by Rampant TechPress, and I
include scripts for detecting global tuning approaches that can avoid a
hardware-based performance solution.
Do this before
considering hardware solutions
Global optimization is often
the best remedy for poor Oracle performance and hardware
upgrades can be avoided by “silver bullet” fixes such as missing indexes, MV,
and CBO parm changes:
- Oracle Global Parameters -
Parameters such as db_file_multiblock_read_count, cursor_sharing, and others can
have a profound impact on overall Oracle instance performance.
- Oracle Optimizer Parameters -
Parameters such as optimizer_mode, optimizer_index_cost_adj, and others can
have a profound impact on Oracle performance.
- Missing Indexes -
Guy Harrison noted in SELECT Journal (v 13, #4, page 37): "The new
index is implemented, following which the IO rate is reduced to one tenth of
that observed during your original engagement."
- CBO Statistics -
Proper implementation of system statistics and histograms can fix many
system-wide performance problems.
- Adding Materialized
Views - MV's can reduce database stress for low-update systems.
- SQL Tuning - If 20%
of the SQL is responsible for 80% of the performance degradation, tuning the
"low-hanging fruit" can relieve server stress.
|
Managers
want fast, reliable performance solutions, with minimum downtime
and low risk and the BC Oracle heath checks have saved many
companies from expensive upgrades. |
 |
When to apply fast hardware to an Oracle performance
problem
When does it makes sense to throw hardware at an Oracle performance
problem?
Throwing hardware makes great sense for the following types of Oracle
performance problems, even though it does address the root cause of the
performance issue:
- Over-normalized design - A legacy 3NF database (with high
DML, prohibiting materialized views) can run faster with SSD, a far less
expensive solution that a full re-design to introduce redundancy
into the data model. Introducing redundancy into a 3NF model
can greatly reduce table joins, and reduce server stress.
- sub-optimal code - It's not hard to find poor Oracle
code, as noted by the entertaining
Oracle WTF site. Those
offshore "bargain" systems can be
made to run faster, without the expense and risk of re-writing
thousands of lines of code. Some examples of bad code include
large systems where optimizing the code (to fix the root cause) would take months and cost hundreds
of thousands of dollars:
- Packages - Vendor application products (i.e. ERP, MRP,
etc.) that are database neutral and not optimized for Oracle.
- Convoluted code - I've seen shops with thousands of lines
of unmanageable code (especially Pro*C), so poorly written that
Eisenstein himself could not figure it out.
- Granular Network calls - I've seen vendor packages (using
SQL*Forms and JDeveloper) that make individual requests to Oracle
for every screen item, causing huge network bottlenecks. What
else can you do when you cannot change the vendor's code?
- Sub-optimal client-server architectures - The are many
vendor packages with architectures that are "generic" and not
Oracle-centric.
How to apply hardware to an Oracle performance
problem
For these type of systems where it's prohibitive or impossible to
address the root cause of the performance problem, it’s often cheaper and safer to correct
sub-optimal applications with fast hardware. Of course, it’s an inelegant solution
that does not address the root cause of the problem, but
it’s effective, far cheaper than a root cause fix, fast (fixed
overnight), and it has very low risk.
Bottlenecks and hardware solutions
It's critical to understand that speeding-up an areas that is not a
major bottleneck will not help. Years ago, a DBA misunderstood an
article I wrote about raw devices being faster than regular I/O, and he
talked his manager into buying Veritas, an outstanding high-speed file
solution. Unfortunately, while Veritas raw files were very fast,
it did not help because their database was network-bound.
I/O Bottlenecks and hardware solutions
I/O bottlenecks can be sporadic and isolated in several areas of
Oracle, especially high-impact areas such as redo log files. Once
you have tuned all SQL impacting the data files, solid-state disk (SSD)
can be a great solution. For Oracle, SSD can do I/O 300x-2,000x
faster than platter disk (Texas Memory Systems sells 100 gig for
only $130k). For details, see my book "Oracle
Solid State Disk Tuning: High Performance Oracle Tuning with RAM
disk".
Tuning I/O bottlenecks in RAC with RAM-SAN is more challenging, and I
have conducted benchmarks that suggest that SSD is a Godsend for RAC
when combined with specially-designed, tiny data buffers on each node.
See my book "Oracle
RAC & Grid Tuning with Solid State Disk: Expert Secrets for High
Performance Clustered Grid Computing" for details on RAC tuning with
Solid-State Disk.
My latest effort (in
conjunction with other Oracle tuning experts) is called
www.statspackanalyzer.com
, a new tool to help automate the analysis of Oracle STATSPACK and AWR
reports and it shows database where faster RAM disk may improve
performance.
CPU Bottlenecks and hardware solutions
One of the architectural issues with today's SMP processors is the
fact that CPU speed continues to improve while RAM speed has stagnated
at about 50 ns. for decades. This means that RAM must be localized
near the CPU's to keep them running at full capacity, and it presents
special challenges for hardware tuning of CPU bottlenecks., See my
articles on
hardware changes and Oracle tuning for details in this issue.
For database that are CPU-bound (where the SQL and code has been
optimized), faster processors can make a huge difference, especially the
new generation of 32-CPU and 64-CPU monolithic servers:
-
Faster CPU - Itanium 2 64-bit processors
can be 4x faster than 32-bit CPU’s.
-
Monolithic servers - The second age of
Mainframe computing has arrived (32 & 64 CPU’s) UNISYS, HP Superdome
and Sun, all with super-fast Itanium and AMD 64-bit processors.
See my notes on hardware-based tuning:
Is faster hardware a legitimate Oracle tuning
option?
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
