Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.