Re: Why Index full scan path not considered

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_at_gmail.com> 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_at_gmail.com> 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
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l