Can this sql restarts by itself
From: Pap <oracle.developer35_at_gmail.com>
Date: Thu, 14 Oct 2021 01:25:43 +0530
Message-ID: <CAEjw_fjOAvZfzix_WFE_+-qCr+=V1b-7r1SRXJpOyZGNwxk14A_at_mail.gmail.com>
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?
| Elapsed | Cpu | IO | Application | Concurrency | PL/SQL |
| 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 | |
| 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 |
Date: Thu, 14 Oct 2021 01:25:43 +0530
Message-ID: <CAEjw_fjOAvZfzix_WFE_+-qCr+=V1b-7r1SRXJpOyZGNwxk14A_at_mail.gmail.com>
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 : 241sPLSQL 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-lReceived on Wed Oct 13 2021 - 21:55:43 CEST