Oracle Tips  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 approaches.

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 the DLM.

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.

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.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.