Tablespaces for DW Dimensions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Tablespaces for DW Dimensions

  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Question Tablespaces for DW Dimensions

    Hello folks,
    I had a couple of questions on setting up tablespaces for our DW (single instance 10gR2 EE).

    1. We'll have, say, 10 dimension tables (some small, some rather large - maybe into millions of long rows). Is there any performance benefit in creating multiple tablespaces for these dimension tables? In other words, what do we lose if we create all the dimensions on a single, bigfile tablespace? Assume the latest storage with proper RAID and I/O bandwidth. If we should be using multiple tablespaces, what's the recommendation - 1 tablespace per dimension table? Some other ratio?

    2. I keep seeing discussions on setting extents for fact table tablespaces to be uniform instead of auto. What's the benefit there? I'm wondering if it's that big of a performance gain (can't see one via my tests so far).

    3. OK, not a tablespace question, but ... what do most people do with indexes when loading dimension tables? Our loads happen throughout the day (hourly) and obviously so do user queries - doesn't seem to make sense for us to drop indexes during loads. Is it best to just rebuild all bitmap indexes each day (during the night) to clean them up?

    Thanks.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I assume you are going to use ASM, aren't you?

    1- For the large DIM tables one tablespace for each one of them. All small DIM tables in a single generic tablespace.

    2- Auto works just fine in our shop.

    3- Since your business requirements call for running ETL during business hours you just can't drop your indexes(*)
    What you can and should do is setup a process at the end of your ETL checking how large your bitmap indexes got, when they reach some threshold like twice the normal size just rebuild them, only the offending ones, much better if you can do this in a partition by partition basis.
    As per rebuilding all of them during the night... depending on DW size and number of bitmap indexes you might find the night is not long enough to rebuild all of them.

    (*) Just as a side note, if you are planing to resort to bitmap-join indexes you have to drop/create them

    Just my two cents.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2006
    Posts
    13
    Thanks - good suggestions on the tablespaces.

    Yes, we're using ASM.

    for 3, how do you know what "the normal size" is? I guess we won't until we have enough data flowing in ... just wondering if there was a "magical" calculation ... it's a smart idea vs doing all of them at night.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    About the size of your bitmap indexes... try and error, you will have to fine tune it over time.

    I would start by creating the indexes and look how big they are, this is your initial "normal size". We actually have a table keeping that data for us, a simple query populates it when the index or index-partition gets created.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    PAVB, seems u have good practical experience with that. Can u please tell us what is the benefit of having each large dimension in separate TS? Just it's not obvious for me

    Thanks

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Background Information
    - Ora9i
    - Raw devices based storage
    - Some DIM tables are large, in excess of 250 million rows
    - A couple of DIM tables are very large, in excess of one billion rows
    - All large and very large DIM tables are range partitioned
    - Each large DIM table is sitting in its own private tablespace
    - Each very large DIM table is sitting in its own private tablespace/s
    - Small DIM tables are grouped in a couple of generic DIM tablespaces

    Benefits of Having Private Tablespaces for Large and Very Large DIM Tables
    - Allows for more control over physical I/O planning, monitoring & remediation
    - Allows for physical vs. logical I/O crosscheck monitoring
    - Allows for more control on backup/restore strategy
    - Allows for specific blocksize leading to private/semiprivate buffer caching
    - It looks cleaner, nicer, more elegant

    Just my experience and what works for me
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    10x PAVB,
    sounds reasonable, but it's good to have that here, since u understand that in many environments these characteristics will not be considered as an advantage

    Regards

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Is it a question or a statement?

    I'll love to see a Data Warehouse environment where these characteristics will not be considered as an advantage.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by pbarmak
    1. We'll have, say, 10 dimension tables (some small, some rather large - maybe into millions of long rows). Is there any performance benefit in creating multiple tablespaces for these dimension tables? In other words, what do we lose if we create all the dimensions on a single, bigfile tablespace? Assume the latest storage with proper RAID and I/O bandwidth. If we should be using multiple tablespaces, what's the recommendation - 1 tablespace per dimension table? Some other ratio?
    You might go further than PAVB's suggestion and use multiple tablespaces per fact table, based on placing one or more partitions into each TS. This allows you to move partitions of a table between databases using "transportable tablespaces" (once you've exchanged the partitions out to a different table blah blah blah). You'd do well to mirror that on the index side, or place the index partitions in the same TS as their table partitions, to make things easier.

    In a DW the use of tablespaces is much more dynamic than in OLTP systems, and we tend to create and drop them as part of regular ETL work, not through some big DBA-led design process.


    Quote Originally Posted by pbarmak
    2. I keep seeing discussions on setting extents for fact table tablespaces to be uniform instead of auto. What's the benefit there? I'm wondering if it's that big of a performance gain (can't see one via my tests so far).
    There's a marginal performance disadvantage from having some extents smaller than the multiblock read size, but it really is marginal. I like my multiblock reads to all be the same size if possible as you don't end up chasing down the odd strange number in a wait event analysis.

    3. OK, not a tablespace question, but ... what do most people do with indexes when loading dimension tables? Our loads happen throughout the day (hourly) and obviously so do user queries - doesn't seem to make sense for us to drop indexes during loads. Is it best to just rebuild all bitmap indexes each day (during the night) to clean them up?
    If your large dim table is hash partitioned to allow partition-wise joins with a composite range-hash partitioned fact table then you can load your dimension through partition exchanges, having loaded the new data to table with bitmap indexes disabled. Even if the dim table is not partitioned you can still load to a table with a single partition and use partition exchange to swap the data segment with the "live" nonpartitioned dimension table. There's some caveats on that for materialized views, of course, and you have to jump through some FK hoops.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    PAVB,
    1st I said that sounds good, so I do not say it's wrong or anything, just if u have a storage array, u do not really have a control over where the files are placed and u delegated that to the storage
    2nd, about the backup strategy, it depends on the strategy if u will get some advantage on that or not.
    So, again, I like this approach, just I I think it's better to say we do so, because we have that and that and that.
    In the real world, the thinks rarely are just black and white

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width