Question: I have a poorly designed database and I need to know how to tune a database that is poorly designed?  It's too expensive to redesign and I need alternatives to tune the Oracle design that I have.  What are some tips for Oracle design tuning?

Answer:  You are correct, tuning an Oracle design can be a huge problem.  See my book Oracle Tuning: The Definitive Reference" for complete details on Oracle design tuning techniques. 

Tuning a bad design is very tricky, especially when you do not have direct access to the SQL.   While there are some tricks for holistic workload tuning, a poorly designed application can only be tuned so-far.  Sadly, poorly designed databases are relatively common and there are several causes of a poorly designed table and index architecture:

• The offshore bargain database:  Oracle database designed by untrained third world neophytes are called Bangalore Bargains, and they can be a nightmare to tune, which bizarre design features.  
  
  
• Poorly designed vendor packages:  Many application packages such as SAP are not designed for Oracle.  Because you cannot change a vendor design, tuning a vendor product can be very difficult.  
  
  
• Legacy database design:  A database designed in the 20th century is likely to be over normalized.  Using dynamic Oracle redesign mechanisms such as materialized views, over-normalized databases can pre-join read-only tables together for super-fast query performance.  

To understand the issues of tuning a legacy Oracle database, we must remember that hardware technology drives Oracle technology, and databases were designed very differently in the 1980's when a single disk drive would cost a quarter of a million dollars.  

For more, see my notes on Oracle design normalization and Oracle data modeling tips.

Oracle data model extensions

Oracle offers several popular denormalization design tools, some that create non first-normal form structures (0NF):  

• Denormalized object tables:  Oracle varray table design removes repeating groups are called non-first-normal-form tables (0NF) tables.  Oracle also provides a nested table design that pre-joins one-to-many relationships, but they are poorly implemented in Oracle and are not recommended for high performance. 
  
  
• Row co-location:  Oracle provides cluster table design to co-locate related rows onto the same physical data blocks, dramatically reducing I/O. 
  
  
• Materialized Views:  In materialized views, tables can be redesigned to contain redundant data.  The Oracle SQL design remains unchanged because queries are automatically re-written to access materialization.

As we see, it is rarely practical to tune a poorly designed database and we must use special Oracle redesign techniques to optimizer the SQL workload for the poor design.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.