bitmap index query is slow in DW

[tamilselvan]

Why don't try with PLAN STABILITY?
Its main purpose is for tuning 3rd party code where you cannot touch.

Tamil

[mike9]

how long does it take to execute query in question 1?

For the question 3 you need to specify /*+ full(a) full(b) full(c) */. How long does it take?

Also you you want to check how long it would take when executed in parallel you could just add the following hints:
/*+ full(a) full(b) full(c) parallel(a) parallel(b) parallel(c) */
If it is much faster it may be worth to execute an ALTER TABLE table_name PARALLEL.

[pranavgovind]

Tamil, Let me take a look at the PLAN STABILITY. Thanks.

Why don't try with PLAN STABILITY?
Its main purpose is for tuning 3rd party code where you cannot touch.

Tamil

[pranavgovind]

Mike, I ran all the queries, it took around 3 min. Another thing, we can not use any hint inside the cognos query. Cognos has his own interpretation.

Finally, i decided to create a materialized view for all the reports. I will ask cognos people to point the MV's to pull the data. Thanks for all the support. I really appreciated.

Thanks a lot Mike. Thanks to Tamil.

This site is really useful...

Regards
GT.

how long does it take to execute query in question 1?

For the question 3 you need to specify /*+ full(a) full(b) full(c) */. How long does it take?

Also you you want to check how long it would take when executed in parallel you could just add the following hints:
/*+ full(a) full(b) full(c) parallel(a) parallel(b) parallel(c) */
If it is much faster it may be worth to execute an ALTER TABLE table_name PARALLEL.

[slimdave]

Finally, i decided to create a materialized view for all the reports. I will ask cognos people to point the MV's to pull the data.

With the appropriate configuration Oracle will use query rewrite to select from the MV instead of the master tables.

[mike9]

2 last recommandations:

For long running queries I would check how much faster it would be when the queries are executed in parellel. To test it you can either use hints
or execute an ALTER TABLE table_name PARALLEL
http://download-west.oracle.com/docs...0/tuningpe.htm

I strongly recommand you to range partition your fact table, for example over your date column.
http://download-west.oracle.com/docs...part.htm#97896

Good luck
Mike

[pranavgovind]

Mike, After changing the table to parallel mode, i got a better performance. Queries were taking 2 min before PARALLEL option. But now it is taking 30 sec. It is acceptable time. I guess, finally PARALLEL option worked out. Thanks Mike. Thanks Slimdave.

Mike, one more question, should i keep FK constraint in data mart?. Does it impact performance if i remove the FK?

Please advise. thanks a lot for all you help..

Regards
GT

2 last recommandations:

For long running queries I would check how much faster it would be when the queries are executed in parellel. To test it you can either use hints
or execute an ALTER TABLE table_name PARALLEL
http://download-west.oracle.com/docs...0/tuningpe.htm

I strongly recommand you to range partition your fact table, for example over your date column.
http://download-west.oracle.com/docs...part.htm#97896

Good luck
Mike

[mike9]

You need FKs to enable star transformation. Also it provides some important information to the Oracle optimizer. So yes I recommand you to keep the FKs.

But, as I already said, have also a look at range partitioning. It is an easy (or even the easiest) way to improve the performance of queries in a DWH.

[tamilselvan]

12:24:48 SQLPLUS> SELECT count(*)
12:24:48 2 FROM quote a, enterprise_sales_det_fact b, orders c
12:24:48 3 WHERE quote_created_date between '01-OCT-2005' and '31-OCT-2005'
12:24:48 4 and a.quote_dim_sid = b.quote_dim_sid
12:24:48 5 AND c.order_dim_sid = b.order_dim_sid
12:24:48 6 /

Oracle has been here for more than 20 years where as COGNOS is not that old product. Certainly, COGNOS knows oracle data types (including date). I believe it will not generate a code such as "quote_created_date between '01-OCT-2005' and '31-OCT-2005' ". May be the developers are not aware of it. Or it could be configuration problem. Instead of identiffing the root cause, you are doing some thing which is not worth while.

Tamil

[slimdave]

Oracle has been here for more than 20 years where as COGNOS is not that old product. Certainly, COGNOS knows oracle data types (including date). I believe it will not generate a code such as "quote_created_date between '01-OCT-2005' and '31-OCT-2005' ". May be the developers are not aware of it. Or it could be configuration problem. Instead of identiffing the root cause, you are doing some thing which is not worth while.

Tamil

+1 for that -- DATE is an ANSI data type that Cognos does support, so this is a configuration issue with Cognos that has to be dealt with by the developers.