Lock Contention On BAL_GRP_T Table During BPEL Commit Customer Flow

(Doc ID 1296427.1)

Last updated on SEPTEMBER 02, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 and later
Information in this document applies to any platform.
Checked for relevance on 13-Mar-2013.
***Checked for relevance on 13-Oct-2014***
***Checked for relevance on 17-Oct-2016***

Goal

Table locks on bal_grp_t table and locks observed in database on bal_grp_t while executing BPEL Commit Customer Flow. These impact performance of operations.

How to reduce the impact from these locks?

Following data has been collected, while locks observed on DB :

Explain Plan:

SQL_ID a01fzrrsn0mm8
--------------------
select poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t where bal_grp_t.poid_ID0 =
:1 for update of bal_grp_t.poid_id0

Plan hash value: 1810282781
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| 1 | FOR UPDATE | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BAL_GRP_T | 1 | 27 | 1 (0)| 00:00:01 |
| 3 | INDEX UNIQUE SCAN | I_BALANCE_GROUP__ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------


Stack Trace of dm_oracle_aia

PROD1:/brm123x/obrm_7.4/opt/portal/7.4/apps/pin_rel#pstack 20487
20487: /brm123x/obrm_7.4/opt/portal/7.4/bin/dm_oracle_aia

---------- lwpid : 7455591 ---------------

0: 60000000c042fd30 : _poll_sys() + 0x30 (/usr/lib/hpux32/libc.so.1)
1: 60000000c04450c0 : poll() + 0xe0 (/usr/lib/hpux32/libc.so.1)
2: 60000000ed35b280 : ntevpque() + 0xd0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
3: 60000000ed34d0e0 : ntevqone() + 0xa0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
4: 60000000ed1d1520 : nsevwait() + 0x3580 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
5: 60000000ed1e7e50 : nsevbrecv() + 0x12e0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
6: 60000000ed3650a0 : nioqrc() + 0x1d00 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
7: 60000000eda7f960 : ttcdrv() + 0x6f00 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
8: 60000000ed3af730 : nioqwa() + 0xd0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
9: 60000000eca49190 : upirtrc() + 0xf50 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
10: 60000000ecd83380 : kpurcsc() + 0x100 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
11: 60000000eccbdd80 : kpuexec() + 0x1b20 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
12: 60000000eca5bdb0 : OCIStmtExecute() + 0x50 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
13: 60000000d2aaeb00 : ExecuteSQLStmt() + 0xa0 (/brm123x/obrm_7.4/opt/portal/7.4/sys/dm_oracle_aia/dm_oracle11g.so)
14: 60000000d2ab08c0 : PINStmtExecute() + 0x120 (/brm123x/obrm_7.4/opt/portal/7.4/sys/dm_oracle_aia/dm_oracle11g.so)
15: 60000000d2ad1d80 : do_sql_select() + 0x5f0 (/brm123x/obrm_7.4/opt/portal/7.4/sys/dm_oracle_aia/dm_oracle11g.so)
16: 60000000d2b3c870 : op_rflds() + 0x810 (/brm123x/obrm_7.4/opt/portal/7.4/sys/dm_oracle_aia/dm_oracle11g.so)
17: 60000000d2ac2f90 : dm_if_process_op() + 0x1f00 (/brm123x/obrm_7.4/opt/portal/7.4/sys/dm_oracle_aia/dm_oracle11g.so)
18: 0000000004009620 : (unknown) () (unknown)
19: 0000000004008390 : (unknown) () (unknown)
20: 000000000400a670 : (unknown) () (unknown)
21: 000000000401c520 : (unknown) () (unknown)
22: 0000000004020a30 : (unknown) () (unknown)
23: 60000000c0030c90 : main_opd_entry() + 0x50 (/usr/lib/hpux32/dld.so)

------  lwpid : 7455662 -----

0: 60000000c042fd30 : _poll_sys() + 0x30 (/usr/lib/hpux32/libc.so.1)
1: 60000000c04450c0 : poll() + 0xe0 (/usr/lib/hpux32/libc.so.1)
2: 60000000ed35b280 : ntevpque() + 0xd0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
3: 60000000ed34d0e0 : ntevqone() + 0xa0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
4: 60000000ed1d1520 : nsevwait() + 0x3580 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
5: 60000000eda9e4e0 : kpcerecv() + 0x4e0 (/app/oracle/product/11.1.0/client_1/lib32/libclntsh.so.11.1)
6: 60000000c00f6a60 : __pthread_bound_body() + 0x190 (/usr/lib/hpux32/libpthread.so.1)


Log snippet

     op_trans_pol_open return flist
# number of field entries allocated 20, used 1
0 PIN_FLD_POID POID [0] 0.0.0.1 -1 0
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 cm_child.c(115):1006 1:BRM01:UnknownProgramName:0:pool-2-thread-75:12:1297346 144:0
cm_pcm_op_call_stack
Seconds Type Opcode (flags, notes)
0.000000000 Enter PCM_OP_TRANS_OPEN (0x10000)
0.000035556 .Enter PCM_OP_TRANS_OPEN (0x10000)
0.004589486 .Exit PCM_OP_TRANS_OPEN (0x10000) - from DM 0.0.0.1
0.004591849 .Enter PCM_OP_TRANS_POL_OPEN (0x10000)
0.004636010 .Exit PCM_OP_TRANS_POL_OPEN (0x10000)
0.004661799 Exit PCM_OP_TRANS_OPEN (0x10000)
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 cm_child.c(115):4396 1:BRM01:UnknownProgramName:0:pool-2-thread-75:70:1297346 144:0
XXX CMAP: op_custom() past op_decode, opcode: PCM_OP_CUST_MODIFY_PROFILE
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 fm_cust_profile.c:813 1:BRM01:UnknownProgramName:0:pool-2-thread-75:70:129734 6144:0
     op_cust_modify_profile input flist
# number of field entries allocated 20, used 3
0 PIN_FLD_POID POID [0] 0.0.0.1 /profile/serv_extrating 14761372411 0
0 PIN_FLD_NAME STR [0] "THREE_FREE"
0 PIN_FLD_PROFILES ARRAY [0] allocated 20, used 2
1     PIN_FLD_INHERITED_INFO SUBSTRUCT [0] allocated 20, used 3
2         PIN_FLD_DATA_ARRAY ARRAY [*] NULL array ptr
2         PIN_FLD_DATA_ARRAY ARRAY [0] allocated 20, used 4
3             PIN_FLD_VALUE STR [0] "97333213036"
3             PIN_FLD_NAME STR [0] "NUMBER"
3             PIN_FLD_VALID_TO TSTAMP [0] (0) <null>
3             PIN_FLD_VALID_FROM TSTAMP [0] (0) <null>
2         PIN_FLD_DATA_ARRAY ARRAY [1] allocated 20, used 4
3             PIN_FLD_VALUE STR [0] "97333498434"
3             PIN_FLD_NAME STR [0] "NUMBER"
3             PIN_FLD_VALID_TO TSTAMP [0] (0) <null>
3             PIN_FLD_VALID_FROM TSTAMP [0] (0) <null>
1     PIN_FLD_PROFILE_OBJ POID [0] 0.0.0.1 /profile/serv_extrating 14761372411 0
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 cm_utils_trans.c(14):87 1:BRM01:UnknownProgramName:0:pool-2-thread-75:70:1297 346144:0
fm_utils_trans_open entering
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 cm_utils_trans.c(14):169 1:BRM01:UnknownProgramName:0:pool-2-thread-75:70:129 7346144:0
fm_utils_trans_open returning
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 fm_cust_pol_prep_profile.c:161 1:BRM01:UnknownProgramName:0:pool-2-thread-75: 70:1297346144:0
fm_cust_pol_prep_profile return flist
# number of field entries allocated 20, used 2
0 PIN_FLD_POID POID [0] 0.0.0.1 /profile/serv_extrating 14761372411 0
0 PIN_FLD_INHERITED_INFO SUBSTRUCT [0] allocated 20, used 3
1     PIN_FLD_DATA_ARRAY ARRAY [*] NULL array ptr
1     PIN_FLD_DATA_ARRAY ARRAY [0] allocated 20, used 4
2         PIN_FLD_VALUE STR [0] "97333213036"
2         PIN_FLD_NAME STR [0] "NUMBER"
2         PIN_FLD_VALID_TO TSTAMP [0] (0) <null>
2         PIN_FLD_VALID_FROM TSTAMP [0] (0) <null>
1     PIN_FLD_DATA_ARRAY ARRAY [1] allocated 20, used 4
2         PIN_FLD_VALUE STR [0] "97333498434"
2         PIN_FLD_NAME STR [0] "NUMBER"
2         PIN_FLD_VALID_TO TSTAMP [0] (0) <null>
2         PIN_FLD_VALID_FROM TSTAMP [0] (0) <null>
D Thu Feb 10 16:55:44 2011 BRM01 cm_aia:23802 fm_cust_pol_valid_profile.c:525 1:BRM01:UnknownProgramName:0:pool-2-thread-75 :70:1297346144:0    

Solution

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