Re: Can this sql restarts by itself

 I have experienced a similar situation and also it's well noted in Jonathan's blog below regarding how a UPDATE having a full scan in its execution path can restart itself. But if i remember correctly the sql monitor used to be the same just that the Execs column used to increase pointing to increase in number of tablescans. But here in your case , you have noted a whole new set of sql_exec_ids(which mean new sql monitors altogether), so I am not sure if an INSERT can show symptoms when its historical partitions are being dropped . In an ideal scenario a partition with all local indexes should not cause any issue for other DMLS which are happening on other partitions even at the same time. But yes the executions count should have been increased to more than 1, in gv$sql and dba_hst_sqlstat considering your query ran for 10+ minutes each time/iteration before it changes the sql_exec_id. From your plan it seems INSERT as a SELECT query, and that to operate in parallel, so not sure if that can cause such a thing. Others can comment.

https://jonathanlewis.wordpress.com/2019/09/10/update-restarts/

Regards
Lok

On Thu, Oct 14, 2021 at 1:26 AM Pap <oracle.developer35_at_gmail.com> 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?
>
> The sql monitor looks something like below
>
> Global Information
> ------------------------------
> Status : EXECUTING
> Instance ID : 1
> SQL Execution ID : 16777240
> Execution Started : 10/13/2021 11:55:14
> First Refresh Time : 10/13/2021 11:55:17
> Last Refresh Time : 10/13/2021 11:59:13
> Duration : 241s
> PLSQL Entry Ids (Object/Subprogram) : 161298,9
> PLSQL Current Ids (Object/Subprogram) : 161298,15
>
> Global Stats
>
> =========================================================================================================================
> | Elapsed | Cpu | IO | Application | Concurrency | PL/SQL |
> Buffer | Read | Read | Write | Write | Cell |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) |
> Gets | Reqs | Bytes | Reqs | Bytes | Offload |
>
> =========================================================================================================================
> | 243 | 53 | 190 | 0.08 | 0.00 | 0.36 |
> 5M | 42591 | 39GB | 163 | 19MB | |
>
> =========================================================================================================================
>
> SQL Plan Monitoring Details (Plan Hash Value=3677945390)
>
> ==============================================================================================================================================================================================================================================
> | Id | Operation | Name
> | Rows | Cost | Time | Start | Execs | Rows | Read | Read |
> Write | Write | Mem | Temp | Activity | Activity Detail
> | Progress |
> | | |
> | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
> Reqs | Bytes | | | (%) | (# samples)
> | |
>
> ==============================================================================================================================================================================================================================================
> | 0 | INSERT STATEMENT |
> | | | | | 1 | | | |
> | | | | |
> | |
> | 1 | LOAD TABLE CONVENTIONAL |
> | | | | | 1 | | | |
> | | | | |
> | |
> | 2 | SEQUENCE | TAB1_SEQ
> | | | | | 1 | | | |
> | | | | |
> | |
> | 3 | PX COORDINATOR FORCED SERIAL |
> | | | | | 1 | | | |
> | | | | |
> | |
> | 4 | PX SEND QC (RANDOM) | :TQ10001
> | 3314 | 110K | | | 1 | | | |
> | | | | |
> | |
> | 5 | HASH JOIN |
> | 3314 | 110K | 231 | +7 | 1 | 0 | | |
> 154 | 18MB | 2M | 21M | |
> | |
> | 6 | PX BLOCK ITERATOR |
> | 8262 | 110K | 231 | +7 | 1 | 158K | | |
> | | | | |
> | |
> | -> 7 | TABLE ACCESS STORAGE FULL | TAB2
> | 8262 | 110K | 239 | +2 | 13 | 158K | 39391 | 38GB |
> | | | | 99.58 | Cpu (42)
> | 100% |
> | | |
> | | | | | | | | |
> | | | | | cell multiblock physical read (196)
> | |
> | 8 | BUFFER SORT |
> | | | | | | | | |
> | | | | |
> | |
> | 9 | PX RECEIVE |
> | 60 | 1 | | | | | | |
> | | | | |
> | |
> | 10 | PX SEND BROADCAST | :TQ10000
> | 60 | 1 | | | | | | |
> | | | | |
> | |
> | 11 | INDEX SKIP SCAN | IDX_TAB3
> | 60 | 1 | | | | | | |
> | | | | |
> | |
> ==============================================================================================================================================================================================================================================
>
>
>

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