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

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. Received on Mon Nov 19 2001 - 07:01:29 CST