Home -> Mailing Lists -> Oracle-L -> Re: Insert running long

Re: Insert running long

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 Apr 2020 19:39:09 +0000
Message-ID: <LNXP265MB15627753EAF3201737A6B9BEA5C60_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM> 











400 rows inserted per call, 1.5 seconds per call.
Now you need to decide if that's reasonable or not.



So, how many indexes have to be maintained as a row is inserted.
Will any (many) of the indexes find that 400 rows inserted turns into 400 different leaf blocks being updated.



How many buffer gets per row, disk_reads per row.



Is there any reason why index UM_DWH_NXT.XIF3MEMBER_BANK_BALANCE stands out so much - what does v$segstat tell you about the work done on that index in an interval. Does the size of the index look unreasonable large for the number of entries it should hold .... etc.




Regards


Jonathan Lewis






From: Bhavani Dhulipalla <bhavanidba6_at_gmail.com>
Sent: 02 April 2020 18:43


To: Jonathan Lewis


Cc: oracle-l_at_freelists.org


Subject: 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




01-APR-20 00:00:00 10862



02-APR-20 00:00:00 10964


[image.png]



On Thu, Apr 2, 2020 at 1:17 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Bhavani Dhulipalla <bhavanidba6_at_gmail.com<mailto:bhavanidba6_at_gmail.com>>
Sent: 02 April 2020 18:04


To: oracle-l_at_freelists.org<mailto: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>@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>@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










Received on Thu Apr 02 2020 - 21:39:09 CEST












Original text of this message