Re: CBO stupid !!!

In article <3574feb1.5563033_at_read.news.global.net.uk>,   boulkenospam_at_globalnet.co.uk (Keith Boulton) wrote:
>
> On Sat, 30 May 1998 21:44:51 +0100, "David" <darussell_at_msn.com> wrote:
>
> >Many of the reasons for Full Table scans are caused because of the lack of
> >Histograms in 7.2, I would recommend 7.3.x which include many new plan
> >options, STAR, HASH JOIN and HISTOGRAMS.......
> >
> >Regards
> >David Russell
> >
>
> In most of the applications I've worked on, I don't think histograms
> would help because processing usually involves bind variables and to
> quote from the concepts manual:
>
> Histograms are not useful for columns with the following
> characteristics:
> * All predicates on the column use bind variables
> ...
>
> I believe hash join is only really useful if one of the tables is very
> small as it involves full table scans of the tables involved in the
> join. The only times I've seen a hash join in an access path, the
> statement has been too slow.
>
> A star plan seems to apply only in very limited circumstances.
>
> I suspect the main reason for inappropriate full table scans and join
> orders is that the default optimizer goal is maximum throughput.
>
> The point is that use of an index where a full table scan would be
> more efficient or use of a nested loops operation in place of a
> sort-merge or hash join results in a query that runs maybe 3 or four
> times slower. Use of a full table scan or a hash join operation where
> it is inappropriate can result in a thousand-fold increase in the time
> taken. The first_rows hint almost always avoids a full-table scan or
> sort-merge operation.
>
>

This is a part of the Oracle Server Tuning Manual (c) Oracle Corporation.

When to Create Indexes
...
This guideline is based on these assumptions:

Rows with the same value for the column on which the query is based are uniformly distributed throughout the data blocks allocated to the table. Rows in the table are randomly ordered with respect to the column on which the query is based.
The table contains a relatively small number of columns. Most queries on the table have relatively simple WHERE clauses. The cache hit ratio is low and there is no operating system cache. If these assumptions do not describe the data in your table and the queries that access it, the percentage of the table's rows selected under which an index is helpful may increase to as much as 25%.

End of Oracle Server Tunig Manual.

I think, that the cbo thinks like the manual. In addition to this, the most people reduces the cost for full table scans (large db_multi_block_read_count, sort_area_size and hash_area_size). So the optimizer will use full table scans with the corresponding plans (sort merge and hash). In many cases this will greatly reduce the response time.

Unfortunately, the other side of this, is the inappropiate use of full table scans. Setting the optimizer_goal to first_rows often shows no success. You can use hints to force the optimizer to perform index scans. Fortunately, it's enough to specify (select /*+ index(table) */ * from ...). So you don't need to specify the index directly by name. So optimizer can choose the index later.

There's unfortunately no way so that the cbo works alone.

I'm also interested in solutions of this problem.

Andreas Prusch

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 04 1998 - 02:34:17 CDT