Thursday, April 1, 2010

Tuning Data Warehouse Models on Oracle

While not strictly related to OBIEE – I am currently working on a number of database optimization tasks, and will discuss a couple of ideas to be aware of when designing a warehouse schema for the oracle database.

Let me start with a simple question:

Assume you have a table of  1000 blocks – with a column ‘Digit’ (that goes from 0-9 obviously). If the table is properly indexed on ‘Digit’ and you query all records where Digit=’0’ – how may data blocks will you have to read ?

Simple applying the unitary method gives you :

(1/10) * 1000 = 100 blocks.

Unfortunately, it turns out that even though this might be a reasonable estimate for an OLTP application – this can be rather dramatically wrong for a fact table in a DSS environment.

Why? Well, if you have designed your star schema correctly, you only have some surrogate keys, and some measures in the fact table (no large varchars). Assuming 5 (4 byte) surrogate keys, and 10 (8 byte) measures – your record length is

5x4+8x10=100 bytes long

Now – your block size may be 8K – and so the number of records that fit in a single block is 8K/100=80 records.

By now you are wondering what this has to do with the original question. A lot as it turns out. Remember, all reads and write are for the block as a whole – and a block has to be read if *any* of its rows qualify the criteria. What is the probability that out of 80 records on a given block - none of them are a digit 0:

P(No 0’s)= P(1st record Not 0) x P(2nd Record not 0)… P(80th record not 0)

=(0.9) ^ 80

=0.00022

Which means in such an environment, the number of blocks that you would expect to read is 1000 x (1-0.00022) = 999.8 blocks. A very different answer from the simplistic 100 we first got we first got.

And this is not all - if you use table compression (which gives you – say 50% gain) – and a large blocksize (recommended with compression) you may have as many as:

32K/ (100 bytes *50% compression ratio)=640 records per block.

In the next post – I will talk about what this means in terms of optimizer behavior.

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thank you very much for your good information.
    As I noted in the ... given information is very usefull to every student who ever want to learn about
    best obiee Online Training
    .

    ReplyDelete
  3. Halo,I'm Helena Julio from Ecuador,I want to talk good about Le_Meridian Funding Investors on this topic.Le_Meridian Funding Investors gives me financial support when all bank in my city turned down my request to grant me a loan of 500,000.00 USD, I tried all i could to get a loan from my banks here in Ecuador but they all turned me down because my credit was low but with god grace I came to know about Le_Meridian so I decided to give a try to apply for the loan. with God willing they grant me  loan of 500,000.00 USD the loan request that my banks here in Ecuador has turned me down for, it was really awesome doing business with them and my business is going well now. Here is Le_Meridian Funding Investment Email/WhatsApp Contact if you wish to apply loan from them.Email:lfdsloans@lemeridianfds.com / lfdsloans@outlook.comWhatsApp Contact:+1-989-394-3740.

    ReplyDelete