| How to determine blocksize for new data warehouse? [message #296957] |
Tue, 29 January 2008 11:59  |
DrNeko
Messages: 17
Registered: January 2007
Location: NJ
|
Junior Member |
|
|
Hello,
I'm currently upgrading an existing data warehouse (9i 32-bit on Windows 2000) to 10g (64-bit on Windows 2003 x64; it's a new server). I'm rather new to performance tuning, and I'm also new to the company I'm working. When I looked at the structure of the 9i data warehouse, it was set to the blocksize of 4K (I was told that was set by mistake)! Needless to say, I want to increase it when I migrate to 10g. My question is: Can I generate some stats on the 9i data warehouse help determine the blocksize? I was thinking to use 16K, but I can choose up to 64K. Thank you.
|
|
|
|
| Re: How to determine blocksize for new data warehouse? [message #296995 is a reply to message #296957] |
Tue, 29 January 2008 20:16  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I have read that 16Kb and 32Kb are the most common block sizes for reporting applications. I think Oracle reccommend 32Kb, but don't quote me on that.
The determining factor would be the profile of your queries:
- Do you want to optimise for ETL or for BI (user-queries)? Choose one.
- If you chose ETL, it performs a lot of bulk operations typically involving FTS and Hash joins. This would benefit from a larger block size. This assumes an ETL cycle no more frequent than daily. A near-real-time ETL will perform a lot of transactional updates benefiting from a smaller block size.
- If you chose BI, what is your BI platform? Do you have a ROLAP application accessing the database directly? Or do you build 3rd party OLAP cubes? The latter will perform big full scans to rebuild cubes, which again would benefit from a larger block size.
- If your BI is primarily ROLAP reports accessing the database directly, what type of reports are they? Operational reports showing highly focused transactional data? Or analytic reports showing huge volumes of data aggregated into a few pages? The former will use indexes and benefit from a smaller block size, the latter will FTS and benefit from a larger block size.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
