Re: ORA-14403 cursor invalidation detected after getting DML partition lock - lots of retries

If this is insert as select are you certain from the 14403 dump that the problem comes from the table you're insert into rather than one of the tables you're selecting from? If the cursor becomes invalid but the insert completes hours or days later that sounds like invalidation producing a change in execution plan that does something ridiculous like forgetting partition elimination or using stats from an empty partition.

Regards
Jonathan Lewis

On Mon, 28 Nov 2022 at 14:39, Peter Hitchman <pjhoraclel_at_gmail.com> wrote:

> Hello Jonathan,
> You are correct, it is an interval partitioned table, partitioned on a
> timestamp column, which I should have said. However the partitions are
> created yearly, so most of the time inserts do not create new partitions
> (which I know from reading your blog is a DML operation).
>
> Originally I thought this was a SQL tuning issue (so I could scatch my CTD
> itch) , then I thought it was something to do with ORA-1551 but then I also
> came across ORA-14403 and setting event 14403 proved it (from MOS
> 2057107.1).
>
> This system has a lot of "INSERT ... SELECT..." operations and Oracle
> seems to be picking on a few and one in partitcular to invalidate the
> cursor. I have been able to reproduce this in development and I only got
> the INSERT to work after I drastically reduced the database workload. In
> production I have seen cursors invalidated and then after some time (which
> can be anything from 8 hours to 4 days), the INSERT completes.
>
> This is a mature system, obviously no longer on a supported Oracle DB
> release, which started off life as release 11.1.0.7. What I also don't know
> is how long this has been going on for.
>
> Regards
> Pete
>
> On Mon, 28 Nov 2022 at 12:52, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Just a guess that might fit your description: is it interval
>> partitioning? If so an insert that required a new partition to exist would
>> not show any DDL but might trigger the 14403.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Mon, 28 Nov 2022 at 12:15, Peter Hitchman <pjhoraclel_at_gmail.com>
>> wrote:
>>
>>> Hi
>>> Oracle Database EE 11.2.0.4.0
>>>
>>> This database started displaying odd behaviour with SQL insert
>>> statements driven by a select statement, inserting into a partitioned
>>> table, being run many times. Eventually I dscovered ORA-14403. As far as I
>>> can tell the "re-try" is caused by cursor invalidation, but I do not know
>>> why the cursor is being marked as invalid. There is no DDL taking place
>>> against the partitoned table or statistics gathering going on. The insert
>>> statement is run and the cursor starts off as being"VALID" and then at some
>>> un-predictable time it gets marked as"INVALID_UNAUTH", once the session is
>>> ready to insert the ORA-14403 error gets raised and handled internally.
>>> Clearing the cursor out of the shared_pool or flushing the shared_pool
>>> completely has no impact.
>>>
>>> The best reference I have found (outside of MOS) is this blog post by
>>> Randolph Geist :-
>>>
>>> https://oracle-randolf.blogspot.com/2016/01/dml-operations-on-partitioned-tables.html
>>>
>>> My situation is not exactly the same, since I have confimed no DDL is
>>> taking place and no stats are being gathered.
>>>
>>> Any suggestions as to why this is happening?
>>>
>>> Regards
>>> Pete
>>>
>>
>
> --
> Regards
>
> Pete
>

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