Re: Can this sql restarts by itself

Hi,
we hit the situation similar to what you described once ago on 11.2.0.4 either. I found the description of the problem by Randolf Geist

https://oracle-randolf.blogspot.com/2016/01/dml-operations-on-partitioned-tables.html

• shortly - yes , the dml operations can (at least - could on 11.2.0.4 , haven't tested it on recent versions) restart, if their target is partitioned table and the cursor gets invalidated (for any reason, e.g. - statistic gathering can be one of the reasons).

Regards

Maxim

On Wed, Oct 13, 2021 at 9:56 PM 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