Re: Insert running long

Hi Jonathan

it seems like 400 is the array size and I see that query got executed many more times today than previous . Each execution is taking 1.5 sec avg

bdhulipa_at_ebisprd2>select 21984327/54964 from dual;399.976839

bdhulipa_at_ebisprd2>select 3769805/9425 from dual; 399.97931

select trunc(cast(BEGIN_INTERVAL_TIME as date)),sum(executions_delta) from
dba_hist_snapshot sn
, dba_hist_sqlstat st
WHERE
sn.snap_id = st.snap_id
AND sn.dbid = st.dbid
AND sn.instance_number = st.instance_number AND sql_id = 'aqs7g5aj9s44d'
GROUP BY
trunc(cast(BEGIN_INTERVAL_TIME as date)) order by 1
/Date SUM(EXECUTIONS_DELTA)

------------------ ---------------------
03-MAR-20 00:00:00 10367
04-MAR-20 00:00:00 10479
05-MAR-20 00:00:00 10461
06-MAR-20 00:00:00 10434
07-MAR-20 00:00:00 10418
10-MAR-20 00:00:00 10405
11-MAR-20 00:00:00 10379
12-MAR-20 00:00:00 10294
13-MAR-20 00:00:00 10280
14-MAR-20 00:00:00 10307
17-MAR-20 00:00:00 10354
18-MAR-20 00:00:00 10400
19-MAR-20 00:00:00 10429
20-MAR-20 00:00:00 10481
21-MAR-20 00:00:00 10541
24-MAR-20 00:00:00 10606
25-MAR-20 00:00:00 10640
26-MAR-20 00:00:00 10643
27-MAR-20 00:00:00 10674
28-MAR-20 00:00:00 10717
31-MAR-20 00:00:00 10807

On Thu, Apr 2, 2020 at 1:17 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> If you want to check whether that statement is using single row processing
> or array processing then select executions, and rows_processed from v$sql
> for the SQL_ID.
> Do it a couple of times over a few minutes and the deltas should allow you
> to work out the arraysize (with a little error, possibly).
>
> Regards
> Jonathan Lewis
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Bhavani Dhulipalla <bhavanidba6_at_gmail.com>
> Sent: 02 April 2020 18:04
> To: oracle-l_at_freelists.org
> Subject: Insert running long
>
> Hello
>
>
>
> DB Version 11.2.0.4 OS Linux
>
>
> one of the insert process is running from 6 hours today and getting
> executed so many times - Each Execution is roughly taking a sec -
>
> its plain insert -I suspect it is is being done in pl/sql loop row by row
> processing -
>
> How can I validate it ? ask for the pl/sql procedure and check the code -
>
> this is the insert query -
>
>
> INSERT INTO MEMBER_BANK_BAL(MEMBER_BANK_BAL_ID,MEM
>
> BER_BANK_ID,INTERNAL_ACCT_ID,MEMBER_BANK_BAL_AMT,A
>
> CCRUED_INT_AMT,FROM_DT,TO_DT,CREATE_TS,LAST_UPDT_T
>
> S,RT_RECRD_NUM,RT_TIER_BAL,BATCH_JOB_EXECUTION_ID)
>
> VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10
>
> , :11, :12)
>
>
> Wait events:
>
>
> bdhulipa_at_ebisprd2>_at_ash/ashtop event2 sql_id='aqs7g5aj9s44d' sysdate-4/24
> sysdate
>
>
> Total Distinct
>
> Seconds AAS %This EVENT2 FIRST_SEEN LAST_SEEN Execs Seen
>
> --------- ------- ------- ------------------------------------------
> ------------------- ------------------- ----------
>
> 14192 1.0 99% | db file sequential read 2020-04-02 04:40:04 2020-04-02
> 08:40:03 6352
>
> 149 .0 1% | ON CPU 2020-04-02 04:41:51 2020-04-02 08:39:30 149
>
> 2 .0 0% | enq: FB - contention [mode=6] 2020-04-02 04:51:41 2020-04-02
> 07:34:04 2
>
> 2 .0 0% | gc current multi block request 2020-04-02 08:15:22 2020-04-02
> 08:15:29 2
>
> 1 .0 0% | gc current block 2-way 2020-04-02 07:44:19 2020-04-02 07:44:19 1
>
> 1 .0 0% | gc current grant 2-way 2020-04-02 06:32:56 2020-04-02 06:32:56 1
>
> 1 .0 0% | local write wait 2020-04-02 08:37:47 2020-04-02 08:37:47 1
>
>
> It seems like db file sequential reads are generating from Index
>
>
> bdhulipa_at_ebisprd2>_at_ash/ashtop objt "sql_id='aqs7g5aj9s44d' and event2='db
> file sequential read'" sysdate-4/24 sysdate
>
>
> Total Distinct
>
> Seconds AAS %This OBJT FIRST_SEEN LAST_SEEN Execs Seen
>
> --------- ------- -------
> -------------------------------------------------- -------------------
> ------------------- ----------
>
> 13854 1.0 100% | UM_DWH_NXT.XIF3MEMBER_BANK_BALANCE [INDEX] 2020-04-02
> 05:24:16 2020-04-02 09:19:46 8054
>
> 4 .0 0% | UM_DWH_NXT.XIF5MEMBER_BANK_BALANCE [INDEX] 2020-04-02 06:22:53
> 2020-04-02 07:06:51 4
>
> 3 .0 0% | UM_DWH_NXT.XPKMEMBER_BANK_BALANCE [INDEX] 2020-04-02 07:12:04
> 2020-04-02 07:26:29 3
>
>
>
> Thanks
>
> Bhavani
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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




image/png attachment: image.png