foreverlearning
MIS
I am in the process of setting up a large enterprise DW (500GB+, growing to ~6TB), on a 4-way IBM M80 with 8GB RAM. I am trying to optimize my Oracle buffering and have the following questions:
· If a dimension table is going to be placed in the buffer_pool_keep, is there any point to building any indexes (btree or bitmap), other than the index for uniqueness?
· The indexes for enforcing uniqueness on the dimension surrogate keys - should they go into buffer_pool_recycle, since they will only ever be used during loads of new dimension rows?
· Recommendation on percentages of db_block_buffers that should be carved out for buffer_pool_keep and buffer_pool_recycle?
· I know at under a GB of memory, Oracle suggests no more than 50% of the RAM be assigned to Oracle and that swap space should be 2x RAM. I also know that they say that the % of RAM for Oracle can go up and swap space can go down as RAM increases on the box. We have 8GB memory on this box. I understand that we will have 4 instances (production and development and an Informatica repository for each). Can you make recommendations for total Oracle memory consumption and how much we should be giving to the production instance? Also, swap space recommendations for this box?
Thanks
· If a dimension table is going to be placed in the buffer_pool_keep, is there any point to building any indexes (btree or bitmap), other than the index for uniqueness?
· The indexes for enforcing uniqueness on the dimension surrogate keys - should they go into buffer_pool_recycle, since they will only ever be used during loads of new dimension rows?
· Recommendation on percentages of db_block_buffers that should be carved out for buffer_pool_keep and buffer_pool_recycle?
· I know at under a GB of memory, Oracle suggests no more than 50% of the RAM be assigned to Oracle and that swap space should be 2x RAM. I also know that they say that the % of RAM for Oracle can go up and swap space can go down as RAM increases on the box. We have 8GB memory on this box. I understand that we will have 4 instances (production and development and an Informatica repository for each). Can you make recommendations for total Oracle memory consumption and how much we should be giving to the production instance? Also, swap space recommendations for this box?
Thanks