Re: why does CBO not use available indices

Couple of questions and suggestions:

• What Oracle version?
• What are the settings on optimizer_index_caching, db_file_multiblock_read_count and sort_area_size?
• Try not gathering histogram (i.e. don't use "for all indexed columns" unless you think queries benefit from it)
• Use dbms_stats.gather_table_stats(ownname=>'<owner>',tabname=>'<table>',cascade=>true)
• When all else fails, look into stored outlines (you should be using bind variables for this ...).

Anurag

"Anke Heinrich" <anke.heinrich_at_marconi.com> wrote in message news:9042145d.0310060903.4ed4e354_at_posting.google.com...
> Hi,
>
> I have a problem getting the cost based optimizer to use the available
> indices instead of performing a full table scan. Using hints is not
> possible as I can't change the source code at the moment. I've heard
> about outlines, but I'm not sure if this works for us.
>
> I don't have much experience with query optimization or export/import
> of outlines, so any help from you is greatly appreciated. The facts
> follow:
>
> SQL statement:
>
> select * from TP, ETP
> where ( TP.FK_RESOURCE_ETP = ETP.ID ) and ( ETP.FOREIGN_ID = ? )
> order by ETP.FK_CONTAINER_EMLSN, ETP.FOREIGN_TYPE, ETP.FOREIGN_LABEL
>
> best select strategy:
> 1) search for entries in ETP with given FOREIGN_ID
> using index ETP_FOREIGN_ID on ETP (FOREIGN_ID) which is not unique,
> but very selective (usually only one match)
> 2) join the results from ETP with TP.FK_RESOURCE_ETP = ETP.ID
> using unique index TP_KEY_RESOURCE on TP (FK_RESOURCE_ETP)
> 3) sort results
>
> table statistics:
> have been calculated using
> analyze table TP compute statistics
> for all indexes for all indexed columns;
> analyze table ETP compute statistics
> for all indexes for all indexed columns;
>
> optimizer parameter:
> optimizer_index_cost_adj=10
> optimizer_max_permutations=1000
>
> we've had a good execution plan when both tables had around 400
> entries:
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (ORDER BY)
> 2 1 NESTED LOOPS
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
> 4 3 INDEX (RANGE SCAN) OF 'ETP_FOREIGN_ID' (NON-UNIQUE)
> 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'TP'
> 6 5 INDEX (UNIQUE SCAN) OF 'TP_KEY_RESOURCE' (UNIQUE)
>
> with around 35000 entries (and updated statistics) the execution plan
> now is:
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (ORDER BY)
> 2 1 MERGE JOIN
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
> 4 3 INDEX (FULL SCAN) OF 'ETP_ID' (UNIQUE)
> 5 2 SORT (JOIN)
> 6 5 TABLE ACCESS (FULL) OF 'TP'
>
> we tried out the hint which produced the preferred execution plan, but
> as I said we can't change the source code, so this is not a real
> option:
>
> /*+ USE_NL(ETP TP) INDEX(ETP ETP_FOREIGN_ID) INDEX(TP
> TP_KEY_RESOURCE) */
>
> performance difference is 0.04 with good old plan, 0.46 with new one
> :-(
>
> Is there a way to force the optimizer to use a specific plan for
> dynamically generated statements without changing source code?
>
> Are there any other options left?
>
> Thanks in advance,
> Anke
Received on Mon Oct 06 2003 - 17:35:21 CDT