Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance Issue: Insert Statement

GeetaMAug 21 2012 — edited Aug 22 2012

Hi

I have a insert query from the user saying that thestatement is taking took long than it supposed to be -

 

INSERT INTO ***_IVR_METRIC_RULE_DLY_BLD(TIME_KEY,***_IVR_APPLICATION_KEY,LINE_OF_BUSINESS_KEY,***_IDENTITY_SITE_KEY,SITE_KEY,***_IVR_DNIS_KEY,***_LANGUAGE_KEY,IVR_METRIC_RULE_KEY,METRIC_VALUE,CREATE_DT,LAST_UPDATE_DT)  VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)

When checked backend found below sid and sql for the XXX user,
I believe that the select statement is making the insert statement to go slow....correct me if I am wrong. -

SET LINES 200
set pages 999
select inst_id||' - '||sid||' - '||serial#||' -'||SQL_ID||' - '||osuser||' - '||username||' - '||machine||' - '||event||' - '||status||' - '||to_char(logon_time,'dd:mm:yyyy hh24:mi:ss')         
from gv$session where username='***'order by logon_time,status;
 
INST_ID||'-'||SID||'-'||SERIAL#||'-'||SQL_ID||'-'||OSUSER||'-'||USERNAME||'-'||MACHINE||'-'||EVENT||'-'||STATUS||'-'||TO_CHAR(LOGON_TIME,'DD:MM:YYYYHH24:MI:SS')
-------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - 1492 - 4451 -aa3xyybh8b0ca - infa - *** - catlmsxp364.corp.***.com - SQL*Net message from client - INACTIVE - 20:08:2012 12:15:00
5 - 1418 - 39665 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execute Reply - ACTIVE - 20:08:2012 12:15:00
5 - 337 - 49881 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq Credit: send blkd - ACTIVE - 20:08:2012 13:09:43
5 - 585 - 29985 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 20:08:2012 13:09:43
5 - 203 - 36173 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq Credit: send blkd - ACTIVE - 20:08:2012 13:09:43
5 - 17 - 48571 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq Credit: send blkd - ACTIVE - 20:08:2012 13:09:43
5 - 781 - 54385 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 20:08:2012 13:09:43
5 - 909 - 49479 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 20:08:2012 13:09:43
5 - 841 - 22273 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq Credit: send blkd - ACTIVE - 20:08:2012 13:09:43
5 - 709 - 19819 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 20:08:2012 13:09:43
5 - 855 - 30047 - - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:38
5 - 78 - 3831 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:38
5 - 920 - 6971 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:38
5 - 968 - 4383 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:38
5 - 1354 - 35699 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:44
5 - 589 - 8609 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:44
5 - 1232 - 49581 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - PX Deq: Execution Msg - INACTIVE - 21:08:2012 08:12:44
5 - 660 - 19875 -4yzvn9swwv1xm - infa - *** - catlmsxp364.corp.***.com - cell smart table scan - ACTIVE - 21:08:2012 08:12:44
 
18 rows selected.
 
 
 
SQL> SELECT SQL_TEXT FROM GV$SQLAREA WHERE SQL_ID='aa3xyybh8b0ca';
 
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ***_IVR_METRIC_RULE_DLY_BLD(TIME_KEY,***_IVR_APPLICATION_KEY,LINE_OF_BUSINESS_KEY,***_IDENTITY_SITE_KEY,SITE_KEY,***_IVR_DNIS_KEY,***_LANGUAGE_KEY,IVR_METRIC_RULE_KEY,METRIC_VALUE,CREA
TE_DT,LAST_UPDATE_DT)  VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
 
 
SQL> SELECT SQL_TEXT FROM GV$SQLAREA WHERE SQL_ID='4yzvn9swwv1xm';
 
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT TRUNC ( TIME_KEY ) , A.***_IVR_APPLICATION_KEY , LINE_OF_BUSINESS_KEY , ***_IDENTITY_SITE_KEY , SITE_KEY , ***_IVR_DNIS_KEY , ***_LANGUAGE_KEY , COUNT ( * ) FROM  ***_IVR_BLD A , ***_IVR_A
PPLICATION_DIM WHERE A.***_IVR_APPLICATION_KEY = ***_IVR_APPLICATION_DIM.***_IVR_APPLICATION_KEY AND ***_IVR_APPLICATION_DIM.APP_ID = 1 AND ( ( A.NODE ='CF0280_EnterpriseServicability_HT' AND UPPE
R(RESULT) ='OK' AND NOT EXISTS ( SELECT 1 FROM  ***_IVR_BLD C WHERE A.UCID =C.UCID AND A.SESSION_ID =C.SESSION_ID AND A.***_REC_INDEX<C.***_REC_INDEX AND c.node IN ( 'CF0480_IdentifierReturned_BC',
'CF0430_PINExists_BC') ) ) OR ( NODE IN ('CF0530_CustomerAuthentication_HT', 'CF0510_CustomerAuthentication_HT', 'CF0460_CustomerAuthentication_HT') AND UPPER(RESULT) ='OK' AND EXISTS ( SELECT 1 FROM
  ***_IVR_DETAIL_STG b WHERE b.ucid = a.ucid AND b.session_id = a.session_id AND b.***_rec_index = a.***_rec_index AND data_field = 'IsAuthenticated' AND data_value ='true' ) ) ) GROUP BY TRUNC ( T

Regards
GeetaM

Edited by: GeetaM on Aug 22, 2012 1:09 PM

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 19 2012

Added on Aug 21 2012

#general-database-discussions, #performance

6 comments

209 views