WIP - Not ready to publish
Too much of a good thing?
Ever since Oracle was
invented, DBA's have struggled to optimizer the database to his
environment.
You need to give Oracle enough resources
without over-allocating precious server and internal resources.
Even at the design level we have the problem
of over-normalization.
Over-Normalization
While it was appropriate back
in the 1990's to design all databases in 3NF (Third Normal Forms,
without redundancy), the falling costs of disk has made the
re-introduction of redundancy important.
Today, Oracle shops design their tables in
3NF (or BCNF), and then deliberately introduce redundancy into the model
(via the definition of materialized views to "pre-join" tables and
standard denormalization).
This choice of adding
redundancy depends on the redundancy boundary, a design note that all
Oracle professionals should embrace.
At the heart of the matter, there is a
trade-off between redundancy (extra disk) and improved performance (by
doing less table joins).
Over-Allocated
Hardware
At the hardware layer, we also see over-allocation of
dedicated hardware resources:
·
Over-allocated RAM
·
Over-allocated CPU
This was
especially predominant during the dark-ages of client-server computing,
a 20th
century practice of using one tiny server for each Oracle instance.
This one-server, one-instance architecture
led to huge amounts of wasted CPU and RAM, which could not be shared
between servers.
We see the same issue today with VMware,
whereby fencing of RAM and CPU can lead to over-allocation and waste.
For more details on this important issue,
see Bert Scalzo's new book "Oracle
on VMware".
But what about over-allocation
with an instance?
Does Oracle have an insatiable appetite for
resources, or is there a diminishing return on investment?
Even within the instance we have some over-allocation
issues:
·
Over-allocated PGA
·
Over allocated
shared pool
·
Over-allocated data
buffer
So, what are
the best practices for determining when Oracle has an optimal
configuration?
Does Oracle have an insatiable appetite for
RAM and CPU?
Let's take a closer look.
What is the nature of scarce
resources?
There are several important points to
remember.
-
Hardware depreciates rapidly, regardless of
usage - It is the job of the DBA to
use all of the computing resources without over-allocating them.
Under-allocation is a waste because RAM
and CPU depreciate rapidly, and over-allocation causes high CPU
enqueues and RAM paging.
-
Most Oracle data is unpopular
- All databases have frequently-referenced data blocks (popular
data), and a long tail of unpopular data.
Caching unpopular data can be a waste of
expensive RAM resources.
and include the URL for the page.
