Re: why does CBO not use available indices

If Oracle is making a big mistake with the plan, then there are a couple of GUESSES I could make:

1. You have skewed data and need a histogram
2. Your sort_area_size is unsuitable
3. Your db_file_multiblock_read_count is unsuitable

Since you think a nested loop is the correct strategy you might try modifying the parameter

    optimizer_index_caching

• values around 80 are often 'realistic' for OLTP systems.

You don't need to fiddle with
optimizer_max_permutations to 'fix'
a two-table join.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


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


"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