We had run the aggregate scripts provided by the summary adviser on Times Ten Database. It has created range indexes on some columns from the Aggregate tables. Even with the aggregate tables, The OBIEE report performance is still not good. We started to debug the Query generated by OBIEE and found that some indexes were missing on the Join conditions and also filter columns used in the query. So I went ahead and created range indexes on the missing columns and join conditions. But the Times Ten database is not picking the manually created indexes. Also it just picks the Unique Range indexes created during the aggregates creation.
1. Why does not Times Ten pick the manually created indexes even those columns are being used in the join conditions and filters.
2. Whenever we have a metric in the query (Some aggregation Sum(), Max() etc.) the query is taking more time ~20 seconds to return data, Where as if remove the metric from the query the query response time is significantly low. ~3 seconds.
Did you update optimiser statistics for all tables on which you created indexes after you created them? If you did not then there is a high possibility that the new indexes will not be used. Also, how did you determine what new indexes are needed? ANd finally, what TimesTen release are you using?
For Exalytics, if supported and certified by the other components right now we would recommend using TimesTen 126.96.36.199.1. In addition, the best way to determine the optimal indexes for BI queries is to use the TimesTen Index Advisor. Please consult the details of the builtin procedures ttIndexAdviceCaptureStart, ttIndexAdviceCaptureEnd, ttIndexAdviceCaptureOutput and so on in the TimesTen Database Reference.