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 17:16:33 +0000
Message-ID: <LNXP265MB1562958E8581068D05C3148CA5C60_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM> 







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>@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
Received on Thu Apr 02 2020 - 19:16:33 CEST












Original text of this message