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
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
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
- 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
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
for 30% off directly from the publisher.