DELETE and INSERT using OPENQUERY to Oracle require long delay between them

Question

I'm doing a DELETE followed by an INSERT on an OPENQUERY to Oracle. I have tried a straight DELETE with a WHERE as well as filtering with a JOIN. INSERT is just an INSERT OPENQUERY/SELECT. I have also tried a filtered UPDATE followed by INSERT on new rows. Since I can't do a MERGE on OPENQUERY, I must do two statements back-to-back.

DELETE, UPDATE and the INSERT work as expected, but only if I run them 10 minutes apart. Individually, they complete in less than one second. When run back-to-back, using either sp_executesql or SSMS, whether running them individually or in one script, they take 10 minutes exactly.

Talked to the Oracle DBA, doesn't ring any bells for him.

The queries themselves, as much as I would like to share them to help you help me, I am not free to do so. Please take my word for the fact that they both work as expected in less than one second when run 10 minutes apart and take 10 minutes when run together.

• Francesco Mantovani: You said that the query takes EXACTLY 10 minutes: does it fails or is that completed successfully? This is ringing the "deadlock" bell to me: sqlshack.com/what-is-a-sql-server-deadlock
• Metaphor: Successful. No deadlock. Exactly 10 minutes every time. With a WAITFOR DELAY '00:10:00' between statements, each takes less than a second.
• scott yu: can you turn on trace for this statement to see what excutation plan like? scott
• CaM: Can the Oracle DBA check active sessions / locks during the 1st query and following through to the 2nd? Maybe SQL isn't committing the transaction immediately for some reason? Anything in the Oracle server logs?

Answer 1

This turned out to be an issue with OPENQUERY and the related "inconsistent metadata" issue requiring OPTION (RECOMPILE). Switching to EXEC AT for write queries (UPDATE, INSERT and DELETE) resolved the throttling effect.