Re: HELP - Terrible query performance on newly partitioned table

Jim,

In my opinion OPTIMIZER_INDEX_COST_ADJ is not a bad hack, if it gives you the performance you want and it's a documented feature then use it. Another good parameter is OPTIMIZER_INDEX_CACHING which is similar but influences the optimizer as to the % of the index it considers to be in the buffer (Oracle default is 0). But be aware that both of these are global changes and will influence other code run against this database.

I'd suggest that you use the CREATE OUTLINE command to store a stabilised execution plan that will be used by the optimizer every time you sql is executed. Set your database parameters including the OPTIMIZER_INDEX_COST_ADJ and run the query with CREATE OUTLINE, then un-set the OPTIMIZER_INDEX_COST_ADJ parameter and start restart the dB with USE_STORED_OUTLINES=TRUE. This will mean you are not messing up any other query by playing with the optimizer settings but as the instance will use the stored plan will have your query run the way you want it every time! CREATE OUTLINE is covered in your PL/SQL guide and you should also check out the CREATE_STORED_OUTLINES and USE_STORED_OUTLINES parameters in your administrators guide.

Kind Regards

Fraser McCallum
MVP Oracle
www.brainbench.com

"Jim Harrington" <jharrington_at_accessdc.com> wrote in message news:u3ccl299alre8f_at_corp.supernews.com...
> 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 - 18:21:07 CST