Re: Can this sql restarts by itself
Date: Thu, 14 Oct 2021 21:16:42 -0400
Message-ID: <6a1db304-d1d5-5a79-055c-e2ec7c05e009_at_gmail.com>
On 10/13/21 15:55, Pap wrote:
> Hello Listers, We have a 11.2.0.4 customer database executing plsql
> procedure. And below INSERT seems to keep coming with new sql_exec_id
> and sql_exec_start whereas it's not inside any looping kind of logic.
> So we were wondering why the same sqls execute so many times. As per
> dba_hist_active_sess_history it has already completed ~25+ executions
> as we see ~25different different sql_exec_id and sql_exec_start for
> all of those executions, each running for ~10minutes+ sequentially one
> after other, without any gap. But then I noticed the v$sql showing
> executions as 0 and even dba_hist_sqlstat also showing as 1 for this
> same sql_id. We do have a session running in parallel, dropping
> historical partitions from the same table to which this below query
> inserting data into, but this insert is happening into the live
> partition. So are we hitting any related bugs here? And if it's the
> same, sql is restarting and not really moving , is there any other way
> to confirm?
Pap, UPDATE can restart itself, that was described in Tom Kyte's book as
"write consistency". Since I cannot link to my bookshelf, I can give you
the link to AskTom.oracle.com:
https://asktom.oracle.com/pls/apex/asktom.search?tag=write-consistency
This is the original Tom, not the Oracle Masters. INSERT can probably restart itself as well as it can wait for ITL and when it finally receives an entry in the interested transaction list find out that the block no longer has sufficient room to accommodate the new row. In my opinion transaction will most likely restart and re-consume all the undo space. I confess that I have never seen such scenario. Tom didn't write about this scenario but I wouldn't consider it impossible. However, new execution id tells me that this is not a transaction restart. That is INSERT being re-executed by the application. That brings me to another question: does the table you're inserting into have the primary key constraint? If the transaction is trying to re-insert the same values over and over again, you should see ORA-0001
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 15 2021 - 03:16:42 CEST