I should also have said that I wouldn't collect stats on the table and its
indexes until I have some idea of what was wrong. It is highly likely that
there is a statistical problem, but it's best to know what it is before
trying to fix it - especially since it probably involves at most one index
and a couple of columns in the table.
Regards
Jonathan Lewis
On Fri, 3 Nov 2023 at 11:13, Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx>
wrote:
I think swapping stats is not a good idea since the data is different -
things like ORG_ID at least - which will impact estimates with different
inputs in different DB. Changing num_distinct/num_rows/similar things might
be OK.
It's best to gather stats on the table and indexes and see if the plan
changes - perhaps the index/table stats are not in sync and causing this
bug.
Also I find it funny that lines 15-17 are not even executed because of the
conditional FILTER operation, yet causing misestimates and wrong choice of
the join method.
On Fri, Nov 3, 2023 at 1:02 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
There are times when I get to feel really smug!
Maybe you could swap some details of the stats on that table, its indexes
and the column in the critical predicates with Amit to help identify why
the silly cardinality estimate has appeared.
Regards (and thanks)
Jonathan Lewis
On Fri, 3 Nov 2023 at 08:23, Holvoet Jo <J.Holvoet@xxxxxx> wrote:
I can find that exact same sql_id in our Oracle EBS db as well, and our
execution plan is basically doing what Jonathan says : a NL join between
VW_NSO_1 and a unique index scan on PO_LINE_LOCATIONS_U1 instead of your
hash join with a full table scan. We end up with a cost of 6 for the update
and execution times in the centisecond range (although here it is hardly
ever executed – at most once every few days).
Regards
Jo
--
Regards
Timur Akhmadeev
