Question
American Express
American Express
US
American Express
Posted: Dec 13, 2016
Last activity: Dec 15, 2016
Last activity: 15 Dec 2016 11:03 EST
Closed
INITRANS value(to allow multiple concurrent sessions to update the same data blocks ) for the Pega OOTB WORK and PR_SYS_LOCKS tables (current value 1 in our application)
Hi Team,
Could you please suggest the preferrable INITRANS value(to allow multiple concurrent sessions to update the same data blocks ) for the Pega OOTB WORK and PR_SYS_LOCKS tables.
Posted: 9 years ago
PEGA
Pegasystems Inc.
US
This type of question is best answered by your DBA after performing some analysis. The best approach is to consider the number of ITL Locks occurring (see this blog post http://blog.itpub.net/38267/viewspace-703721/).
Also, I've seen this discussion in regards to another issue :
"For small tables like pr_sys_locks, which involve frequent DML statements, Oracle suggests to create table with initrans more than 1 and index with initrans more than 2. This is to avoid multiple session blocking issue to wait for data(allocation) on the same data block.The exact number to choose for initrans should be decided by the estimation of concurrent user sessions of the application.
This type of question is best answered by your DBA after performing some analysis. The best approach is to consider the number of ITL Locks occurring (see this blog post http://blog.itpub.net/38267/viewspace-703721/).
Also, I've seen this discussion in regards to another issue :
"For small tables like pr_sys_locks, which involve frequent DML statements, Oracle suggests to create table with initrans more than 1 and index with initrans more than 2. This is to avoid multiple session blocking issue to wait for data(allocation) on the same data block.The exact number to choose for initrans should be decided by the estimation of concurrent user sessions of the application.
There may be other tables like sys queue, which could be considered for this setting too.
create table pr_sys_locks ......initrans 10;
ALTER TABLE pr_sys_locks initrans 10;
create index pr_sys_locks_pk ...initrans 10;"
Finally, here is a PDN article suggesting INITrans of 10 for another table in Pega.
https://community.pega.com/support/support-articles/stored-procedure-sppcdatauniqueid-performing-poorly