Question is: Why isn’t
Oracle choosing the right table table to drive:
Answer:
·
Oracle uses
CBO [ cost based optimizer] to choose the correct plan out of many
combination [number of combinations increases with the number of objects –
tables/indexes]
·
CBO uses statistics
on the objects to choose the optimal plan.
·
In our transforms ,
we use T_ tables [ temporary table] which gets loaded and truncated
everyday before the transforms run. [ so there is no point in collecting
statistics on the empty table via the scheduled stats collection job.]
·
Infact , we ensure
that we don’t collect stats on the T_ table because Dynamic
Sampling which we use on our transform to estimate correct statsitics on
the T_ table on the fly will not work , if CBO sees stats collect on the
table which will anyway be an empty table when stats collection job would have
run.
·
In most cases
dynamic sampling [ by which oracle dynamically chooses some blocks and
estimates stats] works.
·
However, it remote
case like the one which happened this time, it did not work as it might
have picked wrong representative blocks to sample and got the histograms wrong.
·
In that case
HINTS along with dynamic sampling might work fine.
|
Reason for
running long:Choosing the
wrong driving table D_MP_LEGAL_ENTITY_MAP instead of
T_CHANGED_DIGITAL_TRANSACTIONS [highlighted in Red],
This happens on T_
tables which get truncated everyday and hence no stats are collected on these
tables.
|
|
Fix: use dynamic sampling or hints or both. [
the bottomline is we should not be using D_MP_LEGAL_ENTITY_MAP as the driving
table.]
|
|
HINT
USED: no_swap_join_input(
|
Comments