Re: Oracle Performance

In article <1998040702133101.WAA17454_at_ladder01.news.aol.com>,  

eweisscns_at_aol.com (Eweisscns) wrote:
>
> 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
>

I don't know whether you didn't mention analizing the dimension tables because they are not or you just missed it. Try it first.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Apr 07 1998 - 00:00:00 CDT