Siebel Noticed An Increase In Concurrency Of Type Cursor: Pin S Wait On X With SQLID 5b3pd28t72avt

(Doc ID 2276382.1)

Last updated on JUNE 13, 2017

Applies to:

Siebel Financial Services Call Center - Version 8.1.1.11.15 [IP2013] and later
Information in this document applies to any platform.

Symptoms

Noticed an increase in concurrency of type cursor: pin S wait on X with SQLID 5b3pd28t72avt when user did following:

1. Navigate to Home View
2. In My Orders applet, drill down to an Account Name

 

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:16:59 SELECT statement with ID: 1FA64578
SELECT
T39.CONFLICT_ID,
T39.LAST_UPD,
T39.CREATED,
T39.LAST_UPD_BY,
T39.CREATED_BY,
T39.MODIFICATION_NUM,
T39.ROW_ID,
T28.PAY_TYPE_CD,
T39.EAI_EXPRT_STAT_CD,
T39.HOLD_FLG,
T39.EAI_ORDER_NUM,
T39.PAY_OU_ID,
T7.LOC,
T39.ACCNT_ID,
T39.SRC_INVLOC_ID,
T39.SHIP_METH_CD,
T39.RTRN_ADDR_ID,
T39.SHIP_ADDR_ID,
T24.NAME,
T39.ORDER_TYPE_ID,
T39.STATUS_CD,
T39.PRIO_CD,
T39.DEST_INVLOC_ID,
T5.LAST_NAME,
T39.CONTACT_ID,
T5.FST_NAME,
T39.BILLABLE_FLG,
T39.BL_ADDR_ID,
T39.CARRIER_CD,
T39.X_VF_ORDER_TYPE,
T39.X_VF_ORDER_REASON,
T39.FULFLMNT_MODE_CD,
T39.BACKUP_ORDER_ID,
T39.FULFLMNT_CRIT_CD,
T20.RECV_TO_ADDR_ID,
T9.ZIPCODE,
T7.OU_TYPE_CD,
T34.CONTRACT_VIS_FLG,
T5.EMAIL_ADDR,
T39.CUSTOMER_ID,
T39.BILL_PROFILE_ID,
T18.NAME,
T39.DISCNT_RC_PCT,
T21.NAME,
T20.NAME,
T10.NAME,
T39.BL_OU_ID,
T39.BL_CON_ID,
T39.SHIP_OU_ID,
T39.SHIP_CON_ID,
T39.CURCY_CD,
T7.NAME,
T39.ORDER_NUM,
T39.ACCNT_ADDR_ID,
T39.TAX_EXEMPT_REASON,
T39.APPR_BY_POSTN_ID,
T39.TAX_EXEMPT_FLG,
T39.TAX_EXEMPT_NUM,
T39.TAX_PERCENT,
T39.PAYMENT_TERM_ID,
T39.SR_ID,
T39.QUOTE_ID,
T39.REQ_SHIP_DT,
T10.LOC,
T11.NAME,
T11.LOC,
T1.LOGIN,
T39.STATUS_DT,
T26.SH_ADDR_LINE_2,
T39.ORDER_CAT_CD,
T28.CCNUM_ENCRPKEY_REF,
T27.BU_ID,
T27.BU_ID,
T39.FREEZE_FLG,
T39.PAYTO_OU_ID,
T39.PAYTO_ADDR_ID,
T39.PAYTO_CON_ID,
T2.ZIPCODE,
T2.COUNTRY,
T2.CITY,
T2.STATE,
T39.AUTO_RECV_FLG,
T39.REVISION_DT,
T39.TAX_LIST_ID,
T39.LOY_MEMBER_ID,
T35.MEM_NUM,
T39.PR_PAYMENT_ID,
T19.ARRIVAL_DT,
T19.DEPARTURE_DT,
T39.CMPND_PROD_NUM,
T39.RTRN_REASON_CD,
T26.AVAIL_DT,
T26.ATP_STATUS_CD,
T26.ATP_STATUS_DT,
T34.NAME,
T39.BILL_ACCNT_ID,
T31.NAME,
T39.SERV_ACCNT_ID,
T33.NON_PRTCPT_FLG,
T39.CRDT_STATUS_CD,
T39.CRDT_COMMENTS,
T38.PRIMDL_ID,
T39.REV_NUM,
T26.SHIP_COMPLETE_FLG,
T26.GROSS_WGHT,
T26.GROSS_VOL,
T26.VOL_UOM_CD,
T39.RTRN_OU_ID,
T39.RTRN_CON_ID,
T26.SINGLE_SRC_FLG,
T26.RESERVE_TM_INTRVL,
T26.RSRV_TM_INTRVL_CD,
T39.AGREE_ID,
T7.OU_NUM,
T5.PR_PER_ADDR_ID,
T36.NAME,
T12.INTEGRATION_ID,
T15.NAME,
T15.LOC,
T7.MASTER_OU_ID,
T39.DISCNT_RC_AMT,
T39.STATUS_CHG_FLG,
T26.BL_ADDR_LINE_2,
T5.DFLT_ORDER_PROC_CD,
T7.PO_PAY_MAX_AMT,
T28.CC_NUM,
T20.INTEGRATION_ID,
T39.BU_ID,
T11.INTEGRATION_ID,
T5.WORK_PH_NUM,
T39.INTEGRATION_ID,
T14.LAST_NAME,
T14.FST_NAME,
T8.ZIPCODE,
T23.CITY,
T39.CMPNS_STATUS_CD,
T39.FRGHT_TERMS_INFO,
T39.CARRIER_PRIO_CD,
T39.DISCNT_AMT,
T39.TAX_AMT,
T23.COUNTRY,
T23.ZIPCODE,
T23.STATE,
T39.FRGHT_TERMS_CD,
T39.FRGHT_AMT,
T39.PRI_LST_ID,
T38.NAME,
T39.DISCNT_PERCENT,
T39.ACTIVE_FLG,
T26.BL_CITY,
T26.BL_COUNTRY,
T26.BL_ZIPCODE,
T26.BL_STATE,
T26.BL_ADDR,
T26.SH_ZIPCODE,
T26.SH_CITY,
T26.SH_COUNTRY,
T26.SH_STATE,
T26.SH_ADDR,
T39.ORDER_DT,
T39.ORDER_EXCH_DT,
T39.PR_POSTN_ID,
T39.FULFLMNT_STATUS_CD,
T39.ROOT_ORDER_ID,
T29.ATTRIB_56,
T29.ATTRIB_49,
T7.OU_TYPE_CD,
T4.ATTRIB_35,
T4.ATTRIB_40,
T7.PR_CON_ID,
T2.PROVINCE,
T29.ATTRIB_15,
T29.ATTRIB_16,
T29.ATTRIB_52,
T29.ATTRIB_36,
T39.X_VF_STATUS_REASON,
T39.X_VF_SERVICE_ID,
T39.X_VF_BUNDLE_ACTION,
T32.NAME,
T39.X_VF_BUNDLE_NAME,
T39.PREV_ORDER_REV_ID,
T7.NAME,
T39.APPLNT_GRP_ID,
T37.BNFT_PLAN_ITEM_ID,
T8.ADDR_NAME,
T5.FST_NAME,
T5.LAST_NAME,
T16.DEST_CITY,
T16.DEST_COUNTRY,
T13.NAME,
T16.DEST_LOC_ID,
T16.DEST_PRI_REGION,
T16.DEST_STATE,
T16.DEST_ZIPCODE,
T16.END_REQ_DELV_TS,
T16.END_REQ_PICK_TS,
T14.LAST_NAME,
T26.VOL_UOM_CD,
T26.WGHT_UOM_CD,
T16.FLEET_INTEG_ID,
T16.CURCY_CD,
T16.ORIGIN_CITY,
T16.ORIGIN_COUNTRY,
T6.NAME,
T16.ORIGIN_LOC_ID,
T16.ORIGIN_PRI_REGION,
T16.ORIGIN_STATE,
T16.ORIGIN_ZIPCODE,
T27.QUOTE_TYPE,
T16.SOLN_VALID_FLG,
T16.ST_REQ_DELV_TS,
T16.ST_REQ_PICK_TS,
T16.STATIC_PROFIT,
T16.SUBMIT_FULFIL_FLG,
T28.CCNUM_ENCRPKEY_REF,
T39.BLOCK_DLVRY_CD,
T29.ATTRIB_03,
T29.ATTRIB_04,
T16.HOLD_REASON_CD,
T16.LINE_OF_BUS_CD,
T16.MODE_CD,
T16.REJECT_REASON_CD,
T22.ADDR_LINE_2,
T22.ADDR,
T22.CITY,
T22.STATE,
T22.ZIPCODE,
T22.COUNTRY,
T25.ADDR,
T17.LAST_NAME,
T17.FST_NAME,
T3.ADDR_LINE_2,
T3.ZIPCODE,
T3.STATE,
T3.CITY,
T3.COUNTRY,
T3.ADDR,
T26.ROW_ID,
T26.PAR_ROW_ID,
T26.MODIFICATION_NUM,
T26.CREATED_BY,
T26.LAST_UPD_BY,
T26.CREATED,
T26.LAST_UPD,
T26.CONFLICT_ID,
T26.PAR_ROW_ID,
T16.ROW_ID,
T16.PAR_ROW_ID,
T16.MODIFICATION_NUM,
T16.CREATED_BY,
T16.LAST_UPD_BY,
T16.CREATED,
T16.LAST_UPD,
T16.CONFLICT_ID,
T16.PAR_ROW_ID,
T37.ROW_ID,
T37.PAR_ROW_ID,
T37.MODIFICATION_NUM,
T37.CREATED_BY,
T37.LAST_UPD_BY,
T37.CREATED,
T37.LAST_UPD,
T37.CONFLICT_ID,
T37.PAR_ROW_ID,
T29.ROW_ID,
T29.PAR_ROW_ID,
T29.MODIFICATION_NUM,
T29.CREATED_BY,
T29.LAST_UPD_BY,
T29.CREATED,
T29.LAST_UPD,
T29.CONFLICT_ID,
T29.PAR_ROW_ID,
T22.ROW_ID,
T25.ROW_ID,
T30.ROW_ID,
T3.ROW_ID,
:1
FROM
SIEBEL.S_USER T1,
SIEBEL.S_ADDR_PER T2,
SIEBEL.S_ADDR_PER T3,
SIEBEL.S_ORG_EXT_X T4,
SIEBEL.S_CONTACT T5,
SIEBEL.S_LOCATION T6,
SIEBEL.S_ORG_EXT T7,
SIEBEL.S_ADDR_PER T8,
SIEBEL.S_ADDR_PER T9,
SIEBEL.S_ORG_EXT T10,
SIEBEL.S_ORG_EXT T11,
SIEBEL.S_CONTACT T12,
SIEBEL.S_LOCATION T13,
SIEBEL.S_CONTACT T14,
SIEBEL.S_ORG_EXT T15,
SIEBEL.S_ORDER_FMX T16,
SIEBEL.S_CONTACT T17,
SIEBEL.S_INV_PROF T18,
SIEBEL.S_QUOTE_TNTX T19,
SIEBEL.S_INVLOC T20,
SIEBEL.S_INVLOC T21,
SIEBEL.S_ADDR_PER T22,
SIEBEL.S_ADDR_PER T23,
SIEBEL.S_ORDER_TYPE T24,
SIEBEL.S_ADDR_PER T25,
SIEBEL.S_ORDER_DTL T26,
SIEBEL.S_DOC_QUOTE T27,
SIEBEL.S_SRC_PAYMENT T28,
SIEBEL.S_ORDER_X T29,
SIEBEL.S_PARTY T30,
SIEBEL.S_ORG_EXT T31,
SIEBEL.S_PROD_INT T32,
SIEBEL.S_ORG_EXT_TNTX T33,
SIEBEL.S_ORG_EXT T34,
SIEBEL.S_LOY_MEMBER T35,
SIEBEL.S_DOC_AGREE T36,
SIEBEL.S_ORDER_PSX T37,
SIEBEL.S_PRI_LST T38,
SIEBEL.S_ORDER T39
WHERE
T7.PR_ADDR_ID = T2.ROW_ID (+) AND
T39.BL_OU_ID = T10.PAR_ROW_ID (+) AND
T39.BL_ADDR_ID = T8.ROW_ID (+) AND
T39.BL_CON_ID = T14.PAR_ROW_ID (+) AND
T39.BILL_ACCNT_ID = T34.PAR_ROW_ID (+) AND
T39.BILL_PROFILE_ID = T18.ROW_ID (+) AND
T39.CREATED_BY = T1.PAR_ROW_ID AND
T39.DEST_INVLOC_ID = T21.ROW_ID (+) AND
T16.DEST_LOC_ID = T13.ROW_ID (+) AND
T39.LOY_MEMBER_ID = T35.ROW_ID (+) AND
T39.ACCNT_ID = T7.PAR_ROW_ID (+) AND
T16.ORIGIN_LOC_ID = T6.ROW_ID (+) AND
T39.PRI_LST_ID = T38.ROW_ID (+) AND
T39.PR_PAYMENT_ID = T28.ROW_ID (+) AND
T27.BU_ID = T33.PAR_ROW_ID (+) AND
T39.QUOTE_ID = T27.ROW_ID (+) AND
T39.RTRN_OU_ID = T15.PAR_ROW_ID (+) AND
T39.RTRN_CON_ID = T12.PAR_ROW_ID (+) AND
T39.CONTACT_ID = T5.PAR_ROW_ID (+) AND
T39.AGREE_ID = T36.ROW_ID (+) AND
T39.ORDER_TYPE_ID = T24.ROW_ID AND
T39.QUOTE_ID = T19.PAR_ROW_ID (+) AND
T39.SERV_ACCNT_ID = T31.PAR_ROW_ID (+) AND
T20.RECV_TO_ADDR_ID = T9.ROW_ID (+) AND
T39.SHIP_OU_ID = T11.PAR_ROW_ID (+) AND
T39.SHIP_ADDR_ID = T23.ROW_ID (+) AND
T39.SRC_INVLOC_ID = T20.ROW_ID (+) AND
T39.ACCNT_ID = T4.PAR_ROW_ID (+) AND
T39.X_VF_BUNDLE_NAME = T32.ROW_ID (+) AND
T39.ROW_ID = T26.PAR_ROW_ID (+) AND
T39.ROW_ID = T16.PAR_ROW_ID (+) AND
T39.ROW_ID = T37.PAR_ROW_ID (+) AND
T39.ROW_ID = T29.PAR_ROW_ID (+) AND
T39.SHIP_ADDR_ID = T22.ROW_ID (+) AND
T39.RTRN_ADDR_ID = T25.ROW_ID (+) AND
T39.BL_CON_ID = T30.ROW_ID (+) AND
T39.BL_CON_ID = T17.PAR_ROW_ID (+) AND
T39.BL_ADDR_ID = T3.ROW_ID (+) AND
((T39.TEST_ORDER_FLG = 'N') AND
(T39.ORDER_CAT_CD = :2 AND T24.NAME != 'Bulk Request Template Order' AND T39.X_VF_ORDER_REASON != 'Template Order' AND T39.X_VF_ORDER_REASON != 'Port In Template Order' AND (T39.DESC_TEXT != 'RAP' OR T39.DESC_TEXT IS NULL))) AND
(T39.ACCNT_ID = :3)
ORDER BY
T39.ACCNT_ID DESC, T39.ORDER_DT DESC

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:16:59 Bind variable 1: eCommunicationsObjMgrUI_enu,SBLAPP2,24117656,MALGAL,00035bec59351308:21829,,Order Entry - Orders,Account Detail - Orders View

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:16:59 Bind variable 2: Sales

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:16:59 Bind variable 3: 1-QPT8VV5

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:16:59
***** SQL Statement Prepare Time for SQL Cursor with ID 1FA64578: 0.001 seconds *****

ObjMgrSqlLog SqlTag 2 00035bec59351308:21829 2017-06-07 12:16:59 Begin: Execute SqlObj 'Order Entry - Orders' at 37e16698 with SqlTag=eCommunicationsObjMgrUI_enu,SBLAPP2,24117656,MALGAL,00035bec59351308:21829,,Order Entry - Orders,Account Detail - Orders View

SQLParseAndExecute Bind Vars 4 00035bec59351308:21832 2017-06-07 12:16:59 1: eCommunicationsObjMgrUI_enu,SBLAPP2,24117656,MALGAL,00035bec59351308:21829,,Order Entry - Orders,Account Detail - Orders View

SQLParseAndExecute Bind Vars 4 00035bec59351308:21832 2017-06-07 12:16:59 2: Sales

SQLParseAndExecute Bind Vars 4 00035bec59351308:21832 2017-06-07 12:16:59 3: 1-QPT8VV5

ObjMgrSqlLog Detail 4 00035bec59351308:21829 2017-06-07 12:17:21
***** SQL Statement Execute Time for SQL Cursor with ID 1FA64578: 21.667 seconds *****

Changes

R e-built  a number of Siebel indexes in order to reduce fragmentation.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms