Oracle Performance

From: Eweisscns <eweisscns_at_aol.com>
Date: 1998/04/07
Message-ID: <1998040702133101.WAA17454@ladder01.news.aol.com>#1/1

Fellow Oracle Users

If anyone out there can make a useful suggestion for a performance problem, I would appreciate it. We are working with a simple star schema, in which the fact table primary key is composed of prod_code, channel_code, month, and geo_code. The dimension tables contain:

Product: prod_code, prod_desc
Channel: channel_code, channel_desc
Time: month, quarter, year
Geography: geo_code, geo_desc, state, etc.

The fact table contains about half a million rows. The largest of the dimension tables contains 100,000 rows. The following query runs in about 30 seconds:

select prod_code, channel_code, month, geo_code, sum(sales) from fact_table
group by prod_code, channel_code, month, geo_code;

On the other hand, this next query runs almost forever without returning a result:

select p.prod_desc, c.channel_desc, t.month, g.geo_desc, sum(f.sales) from product p, channel c, time t, geography g, fact_table f where p.prod_code = f.prod_code

and c.channel_code = f.channel_code
and t.month = f.month
and g.geo_code = f.geo_code

group by p.prod_desc, c.channel_desc, t.month, g.geo_desc;

All key columns are indexed, most using bitmap indexing. The fact table and all its indexes have been analyzed. The optimizer ignores the indexing whether the optimizer goal is set to Cost or First_Rows. Results are returned in a more reasonable time if the scope is narrowed, say by specifying one product or one geography. But the performance on an unrestricted query is much poorer than I am used to getting.

We recently increased the memory allocations for sort space and disk cache, but I don't know if the amount is sufficient. I am not a DBA, but I'm saddled with the problem, so any suggestions would be helpful.

Regards

Eric Received on Tue Apr 07 1998 - 00:00:00 CDT