Hi,
Point number 1.
Trace file output
15:42:41.961 10667 SQL 2L 13C 7866P Preparing: {CALL ttOptSetFlag('RowLock', 0)}
15:42:41.961 10668 SQL 3L 13C 7866P Executing: {CALL ttOptSetFlag('RowLock', 0)}
15:42:41.961 10669 SQL 2L 13C 7866P Preparing: {CALL ttOptSetFlag('TblLock', 1)}
15:42:41.961 10670 SQL 3L 13C 7866P Executing: {CALL ttOptSetFlag('TblLock', 1)}
15:44:11.013 246101 SQL 3L 1C 23864P Opening: SELECT SMST_SHORT_SELL_FLG, NVL(SM.SMST_INTRADAY_ALLWED_FLG, 'N'), NVL(SM.SMST_MAR_ALLOWD_FLG, 'N'), NVL(SM.SMST_T2_ALLOW_FLG, 'N'), NVL(SM.SMST_COVER_ALLOW_FLG, 'N'), DECODE(:B3 , :B2 , NVL(SM.SMST_RCBM_BASKET_ID, 'NO_BKT'), NVL(SMST_EBM_BASKET_ID, 'NO_BKT')), SM.SMST_ISIN_CODE, SM.SMST_OPEN_POSITION, SM.SMST_EXPOSURE_LIMIT FROM SECURITY_MASTER SM WHERE SMST_SECURITY_ID = :B1
15:44:11.013 246102 SQL 3L 10C 8041P Executing: update ttrep.reppeers set sendlsnhigh = :h, sendlsnlow = :l, reptableslsnhigh = :rh, reptableslsnlow = :rl, commit_timestamp = :ct, commit_seqnum = :cs, timesend = :t where replication_name = :rname and replication_owner = :rowner and tt_store_id = :mid and subscriber_id = :sid
15:44:11.013 246103 SQL 3L 1C 23864P execEnvGet: Allocated new env 8412790936 due to size reqd, nr=52, sdsz=34343
15:44:11.013 246104 SQL 3L 1C 23864P Opening: SELECT NVL(SUM(TONE_CURR_TRADE_QTY),0), NVL(SUM(TONE_BUY_ORD_QTY),0), NVL(SUM(TONE_SELL_ORD_QTY),0) FROM TRADE_ORD_NET_EXPOSURE WHERE TONE_ENTITY_ID = :B6 AND TONE_SECURITY_ID = :B5 AND TONE_EXCH_ID = :B4 AND TONE_ACC_TYPE = :B3 AND TONE_PRODUCT_ID = :B2 AND TONE_DATE = :B1
15:44:11.014 246105 SQL 3L 62C 23943P execEnvGet: Allocated new env 8413003648 due to size reqd, nr=47, sdsz=18633
15:44:11.014 246106 SQL 3L 62C 23943P Opening: select nvl(RQ.RQ_ORDER_NO,0) ,nvl(RQ.RQ_ORD_SERIAL_NO,0) ,trim(RQ.RQ_BUY_SELL_IND) ,trim(RQ.RQ_CLIENT_ID) ,to_number(trim(RQ.RQ_USER_ID)) ,trim(RQ.RQ_ENTITY_ID) ,trim(RQ.RQ_SECURITY_ID) ,trim(RQ.RQ_EXCH_ID) ,RQ.RQ_SEQ_NO ,nvl(RQ.RQ_D2C1_FLAG,'Y') ,nvl(RQ.RQ_FI_RETAIL_FLG,'L') ,nvl(RQ.RQ_OIB_INT_REF_ID,0) ,RQ.RQ_SOURCE_FLAG ,RQ_SESSION_TYPE ,RQ_PRODUCT_ID ,RQ_GROUP_ID ,RQ_HANDL_INST ,RQ_SETTLEMENT_TYPE ,RQ_CLIENT_SUB_TYPE ,RQ_ALGO_OI_NUM into :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20 from REQUEST_QUEUE RQ where (((RQ.RQ_EXCH_ORDER_NO=:b21 and RQ
15:44:11.014 246107 SQL 3L 1C 23864P Opening: SELECT RELD_LMT_FLG FROM RMS_ENTITY_LIMIT_DTLS WHERE RELD_EM_ENTITY_ID = :B2 AND RELD_SEGMENT_TYPE = 'C' AND RELD_EXM_EXCH_ID = 'ALL' AND RELD_ACC_TYPE = :B1
15:44:11.014 246108 SQL 3L 1C 23864P Opening: SELECT SEM_VAR_PERCENTAGE, SEM_EXCH_SPCL_MAR_PCT FROM SECURITY_EXCH_MAP S WHERE SEM_SMST_SECURITY_ID = :B2 AND SEM_EXM_EXCH_ID = :B1 AND SEM_STATUS = 'A' AND SEM_DERIVATIVE_FLG = 'N'
15:44:11.014 246109 SQL 3L 62C 23943P Opening: select OS_SEQ_NO into :b1 from ORDER_SEQ where OS_ORDER_NO=:b2
15:44:11.014 246110 SQL 3L 1C 23864P execEnvGet: Allocated new env 8412793368 due to size reqd, nr=108, sdsz=2400
15:44:11.014 246111 SQL 3L 1C 23864P Opening: SELECT SUM(NVL(TONE_TOT_NET_EXP, 0)), SUM(NVL(TONE_CURR_NET_EXP, 0)), SUM(NVL(TONE_BUY_ORD_QTY, 0)), SUM(NVL(TONE_SELL_ORD_QTY, 0)), SUM(NVL(TONE_CURR_TRADE_QTY, 0)), MAX(TONE_MARGIN_PCT), SUM(TONE_BUY_ORD_VAL), SUM(TONE_SELL_ORD_VAL), SUM(T.TONE_AVG_TRANS_PRICE), SUM(TONE_AVG_CUM_BUY_AMT), SUM(TONE_AVG_CUM_SELL_AMT), SUM(TONE_BUY_EXPOSURE), SUM(TONE_SELL_EXPOSURE), SUM(TONE_CURR_TRADE_VAL), SUM(TONE_BOOKED_PROFIT) FROM TRADE_ORD_NET_EXPOSURE T WHERE TONE_ENTITY_ID = :B7 AND TONE_EXCH_ID = :B6 AND TONE_SECURITY_ID = :B5 AND TONE_TCURR = :B4 AND TONE_ACC_TYPE = :B3 AND TONE_PRODUCT_ID = :B2 AND
15:44:11.014 246112 SQL 3L 62C 23943P execEnvGet: Allocated new env 8411217112 due to size reqd, nr=121, sdsz=5916
15:44:11.014 246113 SQL 3L 62C 23943P Opening: select ORD_ORDER_NO ,ORD_SERIAL_NO ,LTRIM(RTRIM(ORD_SEM_SMST_SECURITY_ID)) ,ORD_BTM_EMM_MKT_TYPE ,ORD_EXCH_ID ,LTRIM(RTRIM(ORD_EPM_EM_ENTITY_ID)) ,nvl(ORD_EXCH_ORDER_NO,0) ,nvl(ORD_CLIENT_ID,'0') ,ORD_BUY_SELL_IND ,to_char(ORD_ENTRY_DATE,'DD-MM-YYYY HH24:MI:SS') ,to_char(ORD_ORDER_TIME,'DD-MM-YYYY HH24:MI:SS') ,to_char(ORD_ORIGINAL_TIME,'DD-MM-YYYY HH24:MI:SS') ,ORD_QTY_ORIGINAL ,ORD_ORDER_PRICE ,nvl(ORD_TRIGGER_PRICE,0) ,ORD_DISC_QTY_FLG ,to_char(ORD_GTC_FLG) ,to_char(ORD_DAY_FLG) ,to_char(ORD_IOC_FLG) ,to_char(ORD_MIN_FILL_FLG) ,to_char(nvl(ORD_MKT_FLG,0)) ,to_char(ORD_STOP_LOSS_FLG) ,to_cha
15:44:11.014 246114 SQL 3L 1C 23864P Executing: UPDATE TRADE_ORD_NET_EXPOSURE SET TONE_BUY_ORD_QTY = TONE_BUY_ORD_QTY + :B17 , TONE_BUY_ORD_VAL = TONE_BUY_ORD_VAL + :B16 , TONE_SELL_ORD_QTY = TONE_SELL_ORD_QTY + :B15 , TONE_SELL_ORD_VAL = TONE_SELL_ORD_VAL + :B14 , TONE_CURR_NET_EXP = TONE_CURR_NET_EXP + :B13 , TONE_TOT_NET_EXP = TONE_TOT_NET_EXP + :B12 , TONE_TOT_BROKERAGE = TONE_TOT_BROKERAGE + :B11 , TONE_BUY_EXPOSURE = TONE_BUY_EXPOSURE + :B10 , TONE_SELL_EXPOSURE = TONE_SELL_EXPOSURE + :B9 WHERE TONE_ENTITY_ID = :B8 AND TONE_EXCH_ID = :B7 AND TONE_SECURITY_ID = :B6 AND TONE_TCURR = :B5 AND TONE_PRODUCT_ID = :B4 AND TONE_ACC_TYPE = :B3
15:44:11.014 246115 SQL 3L 1C 23864P execEnvGet: Allocated new env 8412792952 due to size reqd, nr=62, sdsz=1994
15:44:11.014 246116 SQL 3L 10C 8041P Executing: update ttrep.reppeers set sendlsnhigh = :h, sendlsnlow = :l, reptableslsnhigh = :rh, reptableslsnlow = :rl, commit_timestamp = :ct, commit_seqnum = :cs, timesend = :t where replication_name = :rname and replication_owner = :rowner and tt_store_id = :mid and subscriber_id = :sid
15:44:11.014 246117 SQL 3L 1C 23864P Executing: UPDATE RMS_ENTITY_LIMIT_DTLS R SET RELD_RTO_EXP = NVL(RELD_RTO_EXP, 0) + :B12 , RELD_NE_EXP = NVL(RELD_NE_EXP, 0) + :B11 , RELD_MAR_UTILIZATION = NVL(RELD_MAR_UTILIZATION, 0) + :B5 , RELD_BROKERAGE_AMT = NVL(RELD_BROKERAGE_AMT, 0) + :B10 , RELD_BUY_EXP = R.RELD_BUY_EXP + :B9 , RELD_SELL_EXP = R.RELD_SELL_EXP + :B8 , RELD_EMARGIN_UTILIZATION = RELD_EMARGIN_UTILIZATION + DECODE(:B7 , :B6 , :B5 , 0) WHERE RELD_EM_ENTITY_ID = :B4 AND R.RELD_EXM_EXCH_ID = :B3 AND R.RELD_SEGMENT_TYPE = 'E' AND R.RELD_ACC_TYPE = :B2 AND R.RELD_PROCESS_ID = :B1
15:44:11.014 246118 SQL 3L 1C 23864P execEnvGet: Allocated new env 8412793496 due to size reqd, nr=54, sdsz=2524
This content is displayed in my trace file
and o/p of ttxactadmin is
Program File Name: EquNseParent1
30486 0x139202b0 54.7002 Active Database 0x01312d0001312d00 IX 0
Command 8416780720 S 8416780720
Row BMUFVUAAAC2BwAALDe Xn 8416776768 DAIWAPRODV7.RMS_ENTITY_LIMIT_DTLS
Row BMUFVUAAABGEAAABgz Xn 8416766544 DAIWAPRODV7.RMS_ENTITY_LIMIT_DTLS
Table 12144968 IXn 8416766544 DAIWAPRODV7.RMS_ENTITY_LIMIT_DTLS
Row BMUFVUAAABPEAAAFhL Xn 8416493904 DAIWAPRODV7.TRADE_ORD_NET_EXPOSURE
Table 719136 IXn 8416493904 DAIWAPRODV7.TRADE_ORD_NET_EXPOSURE
Row BMUFVUAAACoFAAAIBt Xn 8413989000 DAIWAPRODV7.RMS_SOD_EOD_LOG
Table 721136 IXn 8413989000 DAIWAPRODV7.RMS_SOD_EOD_LOG
Command 8419805088 S 8419805088
Command 8419800256 S 8419800256
Command 8419759480 S 8419759480
Command 8412801112 S 8412801112
13 locks found for transaction 54.7002
As per my understanding of this " Table 12144968 IXn 8416766544" lock is that there is a table lock.
So we tried setting as per http://docs.oracle.com/cd/E11882_01/timesten.112/e21643/proced.htm#TTREF271
CALL ttOptSetFlag('RowLock', 1)
CALL ttOptSetFlag('TblLock', 0)
but result is still the same.
22878 0x9555770 151.643 Active Database 0x01312d0001312d00 IX 0
Row BMUFVUAAAAOJQAAJD2 Xn 6306707440 VSEVEN.RMS_ENTITY_LIMIT_DTLS
Table 12827064 IXn 6306707440 VSEVEN.RMS_ENTITY_LIMIT_DTLS
Row BMUFVUAAABbKwAAIDO Xn 6306693872 VSEVEN.TRADE_ORD_NET_EXPOSURE
Table 719232 IXn 6306693872 VSEVEN.TRADE_ORD_NET_EXPOSURE
Row BMUFVUAAABqBgAAGj1 Xn 6306573400 VSEVEN.RMS_SOD_EOD_LOG
Table 12826952 IXn 6306573400 VSEVEN.RMS_SOD_EOD_LOG
We are facing locking issues degrading our performance. What does ttoptesetflag('TblLock', 1) and ttoptesetflag('TblLock', 0) implies and why is it showing table level locks
Point number 2.
Also we run the procedure from backend it takes 1 ms for complete process, where as same when called from proc is taking more than 160 ms
Point number 3.
Also as seen in trace file there is frequent statement
"23864P execEnvGet: Allocated new env 8412790936 due to size reqd, nr=52, sdsz=34343"
what does it imply..?
Regards,
Yogita