Re: CBO calculates lower cost, but runs slower...

Paul Moore wrote:
>
> On Sat, 17 Nov 2001 01:55:36 +0100, vwu_at_anacomp.com (Vance Wu) wrote:
>
> >Did you ANALYZE your table(s), it really help to improve performance
> >when you set to CBO, on top of SQL statement tuning, it provides
> >statistics information to the CBO optimizer.
> >
> > ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;
>
> [plus some other similar comments from a few others]
>
> Sorry, I didn't cover all of the details as well as I should have. I had
> been having a very frustrating time with Oracle over this one, and I
> skipped a few points.
>
> 1. Yes, I did analyze the tables. Not with COMPUTE, as the tables are
> big (gigabytes) but with ESTIMATE at about 15%. I tried a number of
> values, and 15% seems perfectly OK.
>
> 2. The stats are fresh - I get this behaviour immediately after doing
> the analyze.
>
> 3. The data is skewed, but calculating histograms doesn't affect the
> problem - which is not that the costing is wrong, but that *changes* in
> the costing don't match changes in the elapsed time. I know this - I
> tried histograms at various levels.
>
> 4. There are many queries experiencing the problem - it is *not* a
> tuning problem for a single query, but a database-wide issue.
>
> 5. The database is not tuned for the RBO. It's not been tuned at all :-(
> I am the DBA, not an application developer - as such I have limited
> understanding of the application characteristics. Sadly, there is no
> serious application support available, so I'm the best that my customer
> has got...
>
> 6. Even if I wanted to tune the particular query, I couldn't, as it is
> generated by a reporting tool (Business Objects) which doesn't allow the
> user to tune the query.
>
> Just to reiterate my basic issue, in isolation from particular queries:
>
> If I have two execution plans, with differing costs as calculated by the
> CBO (at the same time, so the data or stats haven't changed), is it
> reasonable to assume that the plan with the lowest cost is the one which
> should run in the shortest execution time?
>
> >But, make sure you rebuild your table's indexes before you analyze it,
> >otherwise the execution can go very bad (speaking from my experience),
> >if later on you find out the performance is getting worst, you can
> >remove the statistics information by command:
> >
> > ANALYZE TABLE TABLE_NAME DELETE STATISTICS;
>
> Can you explain why you need to rebuild indexes? I can believe that
> badly organised indexes could be less effective than well-organised
> ones, but I'm not sure I see why the CBO would be more likely to use the
> bad indexes than the RBO.
>
> Thanks,
> Paul.

Hi Paul,

Given your circumstances, you could always try FIRST_ROWS for the entire database. I know that this is taking a hammer solution (to a problem that is not necessarily a nail!) but FIRST_ROWS often keeps the users happier at the cost of overall database performance. They see the first screen of their Discoverer (or whatever) result set earlier and think that life's grand...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."