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
DBA/ODI jobs
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.
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.
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
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.
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.
No comments:
Post a Comment