by Burleson Consulting
The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
Tuning The Parallel Server
To understand the difference between tuning an Oracle warehouse
on a single instance versus tuning a warehouse mapped to multiple
servers, we need to take a look at the differences between these two
In a warehouse mapped to a single Oracle instance, we tend to look
for those resources that appear to be the most active. This could be
a “hot” data file on a disk, excessive paging of the bugger cache
(buffer hit ratio), or any number of other factors. As we have
discussed, in a parallel server configuration, we have many
independent Oracle instances sharing the same database.
In a sense, we can think of the shared-nothing configuration as
having numerous, independent Oracle instances, and we can expect to
tune each instance as if it were an independent entity. However, we
must always bear in mind that each Oracle instance is competing for
the same data resources. This competition is directly measured by
Oracle parallel server only archives a high degree of parallelism
when careful planning has partitioned the tasks onto each instance
in such a way that no two instances are constantly competing for
data resources. If we find evidence that two Oracle instances are
frequently accessing the same data blocks, the first remedy is to
move common tasks into the same instance, where they can share the
same buffer cache and eliminate calls to the DLM.
Indeed, tuning of the parallel server is all about DLM lock
contention. Our goal should be to independently tune each Oracle
instance and to keep a careful eye on how these instances interact
with each other to manage inter-node locks. As DLM lock contention
is identified, we have numerous options, including repartitioning
the application to move tasks to other instances, adding multiple
freelists to frequently accessed blocks, or using table replication
techniques to alleviate I/O contention.
This is an excerpt from "High Performance
||If you like Oracle
tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference
, with over 900 pages of BC's favorite tuning tips & scripts.
can buy it directly from the publisher and save 30%, and get instant
access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.