Re: Adding Indexes drastically slows down App

Good, oracle assumes even distribution, which may not be correct.

also when doing histograms add parallel degree if avaialble CPUs are there.

In article <3b3214c1$0$15027$ed9e5944_at_reading.news.pipex.net>, "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
>"E Elliott" <emelliott_99_at_yahoo.com> wrote in message
>news:9c4485cf.0106210704.2d9bd801_at_posting.google.com...
>> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
 news:<3b31c3ef_at_news.iprimus.com.au>...
>> > Well, it's a hint, not a command. And the optimiser is a law unto
 itself,
>> > even on sunny Wednesdays, with a following wind, fingers crossed and
 copious
>> > quantities of Irish stout and shamrock juice.
>> >
>> > I'd love to see the explain plans! You must have some wildly skewed
 data in
>> > there for two pathetic indexes to make such a wild difference... and for
 an
>> > estimate statistics to be hopeless, too.
>> >
><snip>
>
>> This query is producing a report(crystal report!) the displays items
>> that have not been reconciled. Item is the biggest table in the
>> system
>
>Unless I've misread the plans then the optimiser prefers the index on serial
>number to the index on transaction date. What does the distribution of
>serial numbers look like. The optimiser will be assuming that they are
>evenly spread. I'm also a little suprised to see a serial number column as a
>varchar2 but not hugely.
>
>anyway my suggestions
>
>Collect histograms on the item table.
>EXECUTE DBMS_STATS.GATHER_TABLE_STATS
>('<USERID>','ITEM', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');
>
>Actually before doing that you could issue
>
>select item.serialnumber,count(*)
>from item
>group by serialnumber;
>
>just to see what the distribution is like if you don't know.
>
>HTH and I'm not barking up the wrong tree again
>
>
Received on Thu Jun 21 2001 - 16:22:09 CDT