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.
.
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 se 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.
