tag:blogger.com,1999:blog-5124641802818980374.post1321632859260574067..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: DML Operations On Partitioned Tables Can Restart On InvalidationUnknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5124641802818980374.post-27713864928248589832023-02-14T13:55:31.209+01:002023-02-14T13:55:31.209+01:00I have experienced this with 11.2.0.4. The only wa...I have experienced this with 11.2.0.4. The only way I found to fix it was to insert first into a Global Temporary table and then into the target table. My experiments and a discussion on Oracle-L lead me to believe that using a partioned table is the significant feature plus the database being "busy", although the example in this fine post shows that there is probably more than way to trip over this issue. When I turned on trace for event 14403 I found that it occurred for even the most simple "INSERT ... VALUES ..." statement, but in those cases the retry only happened once.<br />William's comment about this re-producing in release 19 is interesting and a bit concerning that Oracle appear to see this issue as "just the way it works".Peter Hitchmannoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-65080097467383732982020-08-08T19:00:40.277+02:002020-08-08T19:00:40.277+02:00Also, it didn't seem to need the condition &qu...Also, it didn't seem to need the condition "The DML statement hasn't touched yet one of the partitions". In our case, it was an INSERT with a 9-way join that normally takes around 15 minutes to load several million rows. Most of the tables are partitioned. Any partition/subpartition DDL on any partition of any of the 10 tables caused the v$sql.object_status to change immediately from VALID to INVALID_UNAUTH until the internal rollback completed (which could easily take an hour, depending on how far it had got before being invalidated), when it would go back to VALID and sql_exec_id and sql_exec_start would be reset.William Robertsonhttps://www.blogger.com/profile/15587739711173542698noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-39057625722031608922020-08-08T18:50:07.776+02:002020-08-08T18:50:07.776+02:00Yes it still happens in 12.2 and 19.0, regardless ...Yes it still happens in 12.2 and 19.0, regardless of the CURSOR_INVALIDATION setting or equivalent "deferred invalidation" clause.William Robertsonhttps://www.blogger.com/profile/15587739711173542698noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-68043225009287492052019-08-20T17:59:37.730+02:002019-08-20T17:59:37.730+02:00Hi Dominic,
good point. I don't have too much...Hi Dominic,<br /><br />good point. I don't have too much experience in that regard with 12.2 and later, so, yes, I agree, in theory the feature should reduce the impact - but can't say anything about real-life experience, so time will tell when more clients are on 12.2+.<br /><br />Kind regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-87156747296760388822019-08-20T17:34:44.173+02:002019-08-20T17:34:44.173+02:00Hi Randolf,
Presumably 12.2 fine-grained cursor i...Hi Randolf,<br /><br />Presumably 12.2 fine-grained cursor invalidation might reduce some instances of this issue if the partition that was the target of the DDL was not relevant to the cursor in question?<br />https://blogs.oracle.com/optimizer/fine-grained-cursor-invalidation<br /><br />Cheers,<br />DominicDominic Brookshttp://orastory.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-69218176900839745882016-01-18T01:40:33.049+01:002016-01-18T01:40:33.049+01:00FYI...I can reproduce this behaviour on a 10.2.0.3...FYI...I can reproduce this behaviour on a 10.2.0.3 database<br /><br />Cheers<br />FairlieFairlie Regohttps://www.blogger.com/profile/12472975937585205288noreply@blogger.com