Re: HELP - Terrible query performance on newly partitioned table

Herman,

Thanks for the advise. I tried the second two of your suggestions (the ones I can try by myself) and the query parses much, much faster (it was taking over a minuite). Unfortunately, the execution plan has not changed significantly and the run time has not changed.

I suspect that statistics all around would fix execution speed as well, but my I'm worried about the time required to calc the statistics. I did calced full statistics for the ACCOUNT table (1.7 Million rows) and it took about 1/2 hour. This table will change less that 1% per day, how often would you say that statistics would need to be re-calculated?

Also, I came accross the following in the Oracle doc:

alter session set OPTIMIZER_INDEX_COST_ADJ = 10;

which basically tells the optimizer to only count 10% of the cost of using an index when calculating the cost of a path using indexes. When I did this, all the indexes get hit and the execution speed is great.

Is the above a bad hack? Management wants to hear about solutions that take no time and the index cost adjustment cost no time.

Thanks,

    Jim H.

"Herman de Boer" <h.de.boer_at_itcg.nl> wrote in message news:bde5777e.0201041038.25109ddc_at_posting.google.com...
> Hello Jim,
>
> a few things worth mentioning.
> 1. Partitioned tables will always force the CBO te be used.
> Using the RULE hint will not change that behaviour.
> 2 The CBO, in this case, because of the CHOOSE setting, will try to
> optimize ALL_ROWS, which might not be desirable.
> This favours full table scans / hash joins above index scans.
> Also, without statistics, the CBO usually produces awsome inefficient
> plans.
> 3 The difference between selecting 'count(*)' and '*' is that there is
> probably no need for table access in order to retrieve columns like
> DEB_BRANCH_CODE, much more can be done be reading solely the index.
>
> My advice:
> 1. Analyze all tables involved. My experience is that
> 'analyze table <table_name. estimate statistics for table for all
indexes
> for all columns' will do (thus using default sample size).
> 2. Use FIRST_ROWS hint, as it will tend to use indexes on the joined
tables.
> If that does not give the desired plan, add USE_NL hints on the joined
> tables - for all tables involved.
> 3. Use an additional ORDERED hint, if parsing time is (too) long.
> It might also help in forcing the USE_NL hints to work.
>
> Kind Regards,
>
> Herman de Boer
> IT Consultancy Group bv
> the Netherlands
Received on Fri Jan 04 2002 - 16:52:20 CST