Re: How would you tune this ?

"Sebby" <sebastien.rigaud_at_b-rail.be> wrote in message news:5bc80fe2.0210162351.444ffbe2_at_posting.google.com...
> I have the following statement:
> > SELECT
> > SUM(A1.mMarketVal), SUM(A1.mIntPay), Min(A1.MarketValCur),
> > Max(A1.MarketValCur), Min(A1.IntPayCur), Max(A1.IntPayCur),
> > C3.seType, C3.Ref, C3.Purpose
> > FROM table1 A1, table2 A2, table3 C3
> > WHERE A2.lCntrId = 199334
> > AND A1.lAstId = A2.lAstId
> > AND A2.lCntrId = C3.lCntrId
> > GROUP BY C3.seType,
> > C3.Ref, C3.Purpose
> > /
>
> TABLE1 contains 182873 records
> " 2 " 165147 "
> " 3 " 41 "
>
> The schema is analyzed, all usable indexes are used (seen in
> EXPLAIN PLAN), Oracle 8.1.7.1 on Sun Solaris.
> The value that determines the final volume of figures to be
> processed is "A2.lCtnrId = 199334": there are 35 different
> values in this field, with unequal breakdown: the 1rst ones
> appear between 10 to 500 times, then you have 9 values
> appearing about 1200 to 1500 times, then 6 values between
> 3500 and 8500 times, then the last 3 values appearing 11500,
> 28000 and 81500 times in the table (but the overall COUNT
> of all joins in this query gives about 1500 to 2000 rows to
> process when executed with the value appearing 81500 times).
>
> Depending on the value used, this query takes 25-30 up to
> more than 60 seconds. I'd like to know whether there are
> means to improve it (with hints, of by playing with
> sort_area_size etc.).
>
> What would you recommend ?

I think that I would consider calculating histograms on table2. As for increasing sort area size etc, are you currently sorting on disk for this statement?

The normal recommendation would be to modify WHERE A2.lCntrId = 199334 to WHERE A2.lCntrId = :1 to eliminate reparsing however because of your scewed data I think this would be a poor step

Can you post the explain plan (and maybe statistics) for the query.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************