Re: large table optimizer suggestions

"mark thomas" <mark_at_immermail.com> wrote in message news:1124909293.822391.285440_at_g49g2000cwa.googlegroups.com...
>I have a table with 4e9 rows, size about 80 bytes/row, partitioned by
> time slices (daily), each slice containing about 10e6 rows. One column
> has a local bitmap index. There are typically 10-1000 rows matching a
> given column value in each partition.
>
> Some of my queries are multi-partition aggregates. Obviously, parallel
> full scans are the right way to do this. Other queries are searching a
> single partition for rows matching a single value from the indexed
> column. Obviously, the index is useful here (typically about 100 times
> faster than a single partition FTS)
>
> I have global stats at table, partition, and indexed column level. I
> think the optimizer should be smart enpough to use parallel FTS on
> aggregate queries, and use the index on the other queries. But no such
> luck. It chooses FTS for the indexed column query, unless I hint the
> SQL.
>
> Oracle Corp's geniuses suggested I 'alter table noparallel' Sure
> enough, then the index gets used, but then the multi-part aggregate
> queries run single-threaded, which obviously is a killer.
>
> Am I missing something obvious here? Isn't this like a textbook case
> of a data warehousing situation? Shouldn't the optimizer know how to
> do this correctly?
>

Which version of Oracle, and what
degree of parallelism is the table ?

Are you using cpu costing method
(system statistics) or traditional I/O
costing, which will depend on the
db_file_multiblock_read_count ?
If the latter, what is the dbf_mbrc.
If the former, what are the system
statistics.

As an example of the sort of thing that
causes problems: Assume you are running
parallel 8, with db_file_multiblock_read_count =32 because it is a DW system.

The cost of scanning one partition of 10,000 blocks will be approximately

    10,000 / 16.507 / 8 = 75
(16.407 because the optimizer scales your 32 down, 8 because that's what 9i does with DOP).

Assume your number of distinct values is 2,000 - so Oracle assumes a query will fetch 500 by bitmap index. The cost assumes that 20% of the rows will be scattered, and 80% will be well-packed (See K Gopalakrishnan - The
Oracle Wait Interface). Cost is:

    100 + 4 = 104

(100 from the 100 scattered rows, one per block, four from the 500 packed rows at 100 per block).

Since 104 > 75, the tablescan is more desirable.

Adjust input figures as necessary - and you may see the same on your system.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005