This is my blog on all things Oracle. I was an Enterprise Architect for a unnamed company, doing unamed things.
Please note that the views expressed here are my own.

Twitter Updates 2.2.1: FeedWitter

Monday, February 27, 2012

Setting aside a node for maintenance on Exadata

Actually, this isn't exadata specific, but it becomes even more important on a multi-node cluster.

First the background.

I have a data warehouse application in which we are loading up lots of data. At the same time, we have users reporting off the data. I am finding that we actually have 2 needs, and they are opposed

USERS -- Their needs

Lots of concurrency

Small amounts of data

Small PGA

small temp

Large SGA

If the users need more than this something probably went wrong with their query..

DBA/ODI jobs

Very little concurrency (except for some parallelization)

Large amounts of data

HUGE PGA

HUGE Temp

HUGE Undo segments

Small SGA

The Temp issue is easy enough to fix with a separate temp for each user, and by setting up a temporary tablespace group for users.

But what about my the other things data load jobs need ?? The only answer seems to be to set aside 1 (or more) nodes out of my cluster for maintenance/loading. This node (or nodes) will have a different configuration. This node, let's say node 8, has the following characteristics.

The only service running on this node is my ODI (data load) service, and a service the DBA's to use for index rebuilds

PGA Automatic memory management is not enabled

work_area_size_policy is manual

sort_area_size=60g

hash_area_size=60g

undo tablespace size is set to 1tb, much, much larger than the other nodes. Undo_retention is set to a very large number.

The only work done on Node 8 will be loading of large tables, and rebuild/creation of indexes.

I was even thinking about getting the memory expansion kit for just this node, to bring it up to 144g from 96g.

Anyone else do this ? set aside a node specifically for "heavy lifting" with a different configuration ?

UPDATE --- After writing this, and looking at my load process, I noticed that most of my load time is going to temp.. Both reads, and writes since I am doing huge hashes. I am considering Dedicating SSD luns to the temp area for my ODI jobs only. I might even try dedicating SSD to the i$ (intermediate staging tables) that ODI uses.