Re: Oracle Performance

In article <6gd8g7$4kh$1_at_nnrp1.dejanews.com>,   mdaskalo_at_tlogica.com wrote:
>
> 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
>

I would advise you

1. To run EXPLAIN for your SQL and check if you have FULL table access on your dimension tables. If the answer is YES - thats an explanation of poor performance. Check the order of table accesses : the fact table must appear at the beginning as the first accessed table.
2. If FULL exists - check if you ANALYZED these tables as well and if you have any indexes ( primary keys ) there. You need ordinary ( Btree ) indexes for your dimension tables.
3. If fact table is NOT the firs one, then use following HINT SELECT /*+ ORDERED */ p.prod_desc, c.channel_desc, t.month, g.geo_desc, sum(f.sales) from fact_table f, product p, channel c, time t, geography g 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;

  The fact_table MUST appear first in FROM clause.

 Michael.

-----== 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