It looks like you might be able to disable the index ffs path for delete by
setting fix_control 17908541 off (0) - but that wasn't the thing I
originally had in mind with changing the costing mechanism.
Regards
Jonathan Lewis
On Thu, 9 Jun 2022 at 22:29, Jonathan Lewis <jlewisoracle@xxxxxxxxx> wrote:
1) Delete following an index fast full scan was a path that became
available some time in 12c, see:
https://jonathanlewis.wordpress.com/2014/06/19/delete-costs/
2) You stats say you must be doing something like the site mentioned in
the article. Your table reports 14,000 blocks and no rows, your index
report blevel=2 and no rows and (because of the way Oracle calculates the
leaf block count) Oracle doesn't know how many allocated blocks there are
in the index, but I'd guess there might be a couple of hundred.
You could set (and lock) some index stats to give (particularly the
leaf_blocks) to let Oracle know the index space is physically quite large,
even though it is empty. Or you could find a moment to move the table
online while there's no data in it so that it shrinks and gets zero stats
and takes zero space. The former might be the safer option.
(I think there's a fix-control you can set (or maybe a parameter) to tell
Oracle to use the segment size to cost the index fast full scan, and that
would be another valid workaround.)
Regards
Jonathan Lewis
On Thu, 9 Jun 2022 at 20:24, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, We have a delete query with a difference in plan after we
moved to 19C. It runs hundreds of thousands of times in a day. It was using
unique scan path on 11.2 and was running faster i.e. ~.0001 seconds per
execution but when we moved to 19C it's now going for an index fast full
scan path and taking ~.05 seconds/execution.
We fetched the 10053 trace for both OFE 19.1 and OFE 11.2. What we see
is , in 11.2 the best cost comes to be 2.000464 i.e the index unique scan
path. An optimizer evaluated table scan vs index unique scan path but
there was no index FFS path cost evaluation. In the 19c trace we are
seeing the new "index FFS" access path evaluation and that cost endup as
2.000000 and thus chosen as the best path in 19c.
So my question is,
1)If the index ffs path was not possible for delete statements and was
introduced in 19c?
2)To handle such a scenario if hinting the query is the only option?
DELETE FROM TAB1 WHERE CMP_ID = :b1 AND EP_DT =
to_date(:b2,'DD-MON-YYYY');
Here an unique index TAB1_PK exists on column cmp_id, EP_DT.
11G 10053 trace:-
https://gist.github.com/oracle9999/ba46c45d4c227a7ed7f56be21c26ccfc
19C 10053 trace:-
https://gist.github.com/oracle9999/9edb233ced8bd5c6b908f5c7c7a1dc70
