performance of BI
+2
Jeff Smith
dellsters
6 posters
Page 1 of 1
performance of BI
To run faster ad hoc queries against a star schema, and improve performance, what are the things to follow? Can somebody share some guidelines for an Oracle database? Bitmap indexing, aggregate tables, etc. But when to use what? Anything else to consider?
dellsters- Posts : 39
Join date : 2009-02-11
Re: performance of BI
Hate to say this, but it depends. It's a little difficult to tune for ad hoc queries unless the ad hoc queries tend to be similar. With standard reports, I start with tweaking the indexes. If that doesn't improve the performance, then I'll create aggregate tables.
I was once told that if more than 10% of the queries are going against detail, then you are missing an aggregate table.
When creating aggregate tables, I try to get an 80% reduction in the volume of records.
I was once told that if more than 10% of the queries are going against detail, then you are missing an aggregate table.
When creating aggregate tables, I try to get an 80% reduction in the volume of records.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: performance of BI
Can you elaborate a little more on tweaking indexes? I put bitmap indexes on all the dimension columns.
Anything else other than indexes and aggregate tables?
Anything else other than indexes and aggregate tables?
dellsters- Posts : 39
Join date : 2009-02-11
Re: performance of BI
There are many things to consider.
You probably need to work with a DBA to analyze explain plans, but here are a couple tips on overall performance considerations.
- correct index strategy (e.g. bitmaps on surrogate keys and fact tables)
- DBStats, used by the optimizer. Are the stats up to date? Are the sample sizes sufficient? (e.g. I like to sample 100% on dimensions if possible)
- is the database configured for star_transformation?
An interesting presentation touches on some of the above
http://www.nyoug.org/Presentations/SIG/DataWarehousing/AIS-DWSIG-StarSchema.pdf
Good Luck.
You probably need to work with a DBA to analyze explain plans, but here are a couple tips on overall performance considerations.
- correct index strategy (e.g. bitmaps on surrogate keys and fact tables)
- DBStats, used by the optimizer. Are the stats up to date? Are the sample sizes sufficient? (e.g. I like to sample 100% on dimensions if possible)
- is the database configured for star_transformation?
An interesting presentation touches on some of the above
http://www.nyoug.org/Presentations/SIG/DataWarehousing/AIS-DWSIG-StarSchema.pdf
Good Luck.
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: performance of BI
General rules for star schemas in Oracle:
1. Turn on the star optimization option
2. Bitmap indexes on each of all fact table foreign keys, rebuild after updating
3. Patitioning always helps, always use local indexes
4. Keep fact tables as thin as possible
5. Don't snowflake
6. Put as much memory on the system as you can afford (64 bit systems)
7. Btree index on dimension PKs, index dimension attributes as you see fit.
1. Turn on the star optimization option
2. Bitmap indexes on each of all fact table foreign keys, rebuild after updating
3. Patitioning always helps, always use local indexes
4. Keep fact tables as thin as possible
5. Don't snowflake
6. Put as much memory on the system as you can afford (64 bit systems)
7. Btree index on dimension PKs, index dimension attributes as you see fit.
Re: performance of BI
What do you mean local indexes on number 3?
About number 7, I put bitmap indexes on dimension attributes. Is that a good choice vs btree index? Should these be rebuilt after updating as well? Should btree index on dimension PKs also get rebuilt?
What is the reasoning behind rebuilding indexes?
About number 7, I put bitmap indexes on dimension attributes. Is that a good choice vs btree index? Should these be rebuilt after updating as well? Should btree index on dimension PKs also get rebuilt?
What is the reasoning behind rebuilding indexes?
dellsters- Posts : 39
Join date : 2009-02-11
Re: performance of BI
When you define indexes for partitioned tables, you can create local or global indexes. A local index only indexes those rows in the parition (one index structure for each parition) while a global index is an single structure for an entire table.
Local indexs are preferable as it allows for parallel use of partitions.
There are no hard and fast rules for dimension attributes, although bitmaps tend to be a better choice. The problem with bitmaps is they quickly grow in size and become fractured and inefficient (real bad in 9, supposed to be better in 11) so, it is always a good idea to rebuild them after updates. When loading paritioned facts, it is usually best to disable all indexes then rebuild the ones in partitions that were updated.
Local indexs are preferable as it allows for parallel use of partitions.
There are no hard and fast rules for dimension attributes, although bitmaps tend to be a better choice. The problem with bitmaps is they quickly grow in size and become fractured and inefficient (real bad in 9, supposed to be better in 11) so, it is always a good idea to rebuild them after updates. When loading paritioned facts, it is usually best to disable all indexes then rebuild the ones in partitions that were updated.
Re: performance of BI
That's helpful. Thank you.
One last question. So I can leave the btree indexes alone and not rebuild them after updates?
One last question. So I can leave the btree indexes alone and not rebuild them after updates?
dellsters- Posts : 39
Join date : 2009-02-11
Re: performance of BI
Yes. BTrees do not have a problem with becoming inefficient after update activity.
Re: performance of BI
For bitmaps, mark the index unusable and then rebuild.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: performance of BI
You should use in Oracle materialized views for storing aggregate data with query rewrite option and if you have star shema with hierarchies in the dimension(s) you can define the so called Oracle dimensions which can cause additional query rewrites for enhancing the performance
gvarga- Posts : 43
Join date : 2010-12-15
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|