Oracle Database Discussions

Long story short: what are my options, and primarily, why does cost go up after doing analyze...compute statistics??

I have a select that joins two tables, with one of the columns being non-nuique and having a non-unique index and yes, i know this is bad design. When I trace the statement oracle does a FTS on the table with the non-unique index instead of using the index.

SELECT *
FROM MEASURAND
INNER JOIN DELIVERY_POINT ON
MEASURAND.ID_DELIVERY_POINT_H = DELIVERY_POINT.ID_DELIVERY_POINT
WHERE DELIVERY_POINT.ID_GLO = 'rxxx.re.dlsh.320023';

CBO does a hash join then an FTS of measurand. I cannot at the moment attach the explain planfor the original (the plan below is after changing OPTIMIZER_MODE), but it costs 599 according to the plan.

I change OPTIMIZER_MODE=FIRST_ROWS_100 and run it again and get:

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=254 Card=100 Byt
es=33100)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MEASURAND' (Cost=2 Card=
1 Bytes=152)

2 1 NESTED LOOPS (Cost=254 Card=100 Bytes=33100)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DELIVERY_POINT' (Cos
t=56 Card=494 Bytes=88426)

4 3 INDEX (RANGE SCAN) OF 'DELIVERY_POINT_I01N' (NON-UNI
QUE) (Cost=3 Card=494)

5 2 INDEX (RANGE SCAN) OF 'MEASURAND_I01N' (NON-UNIQUE) (C
ost=1 Card=1)

Which is much better (even better if i use first_rows_10) but I do not really want to change the whole instance for one rogue statement. I cannot change the code directly as it is autogenerated by a framework called BOF, so I cannot add a hint to use the index. When I first started investigating this, I could add an +INDEX hint and it would report in the plan that the index had been used and the cost was then very low, like 63 or something, but since implementing these changes, when I now try to add the hint, the cost is over 1000, even when I try to simulate the old parameter by changing the session to use OPTIMIZER_MODE=CHOOSE (default for our systems).

I cannot use histograms as the column is in the join and not the where clause, and I cannot guarantee that bind variables are not used.

I could not get stored outlines to work...I created a stored outline with the index hint included, but my current session did not seem to want to use it. Cost was the same even when issueing "alter session set use_stored_outlines=my_category;", but I don't know if I needed to enable outlines in the instance first.

One other thing to note is that when using CHOOSE optimizer, I can delete all stats on all objects involved and performance goes up as it uses RBO. I cannot use RBO as we use function indexes heavily, but when I then create new stats with ANALYZE COMPUTE STATISTICS on each object referenced, then performance goes worse for each analyzed object!! I analyze tables first and cost goes up slightly, then I analyze one index and cost goes up a bit again, and it keeps going up for each object I analyze! I have even tried to alter table move/alter index rebuild, but it does not influence the CBO to choose the index over FTS.

Lastly, I only have one customer so far with this issue. All other sites use the index in the explain plan.

Just one addition: I have tried altering optimizer_index_cost_adj=20 to make the CBO more willing to use the index. I needed to set it to 20 to get CBO to use it which was quite a dramatic change. This solution, and optimizer_mode, whilst solving my problem, are too all-encompassing, as solutions go, and would negatively affect other apps running in the same instance. If I could get a more fine-grained solution (maybe some help in getting outlines to work), then I would be happy.

Message was edited by:
Andrew Henry