Hi
The problem orignally did show up with a SELECT against other tables that
were not partitioned, which I thought was relevant but it appears not as I
have seen this behaviour from INSERTs that query the same table being
inserted into. I do not know what else to look at in the dump file to prove
this, apart from the SQL statement that is dumped out.
The pattern of behaviour looks like this, the cursor is initially marked as
"VALID", the SELECT part of the INSERT runs and after some period of time
the cursor gets marked "INVALID_UNAUTH", then after the SELECT ends and the
INSERT is about to begin I think a check must be made on the cursor and
ORA-14403 is raised, then the cursor goes back to "VALID" as the whole
thing restarts. I assume there may be a rollback as well, but only for a
small amount of data. So the time it takes depends on how many
invalidations there are.
Sometimes a re-try suffers from a parallel downgrade, but the plan does not
change, at least the ones I have looked at have not. So parallel query is
being used as well, something else I should have noted,
Regards
Pete
On Mon, 28 Nov 2022 at 15:02, Jonathan Lewis <jlewisoracle@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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
