Question on update statement with high concurrency
Dear Experts,
We are on 12.1.0.2
update USERS set COUNTRY_CODE=:1 , CREATED_BY=:2 , DIP_COUNTER=:3 ,
EMAIL_VERIFICATION=:4 , FORGOT_PASSWORD_EXPIRE=:5 ,
FORGOT_PASSWORD_TOKEN=:6 , ISDONOR=:7 , LAST_LOGIN_TIME=:8 ,
MOBILE_NUMBER=:9 , REGISTRATION_EMAIL_LINK=:10 , REQ_CHANNEL=:11 ,
SESSION_ID=:12 , SMS_VERIFICATION=:13 , UPDATED_BY=:14 ,
UPDATED_TIME=:15 , USER_NAME=:16 , USER_PASSWORD=:17 , USER_STATUS=:18
, WATCH_LIST_FLAG=:19 where USER_ID=:20
Plan hash value: 2268081486
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | USERS | | | | |
|* 2 | INDEX UNIQUE SCAN| USERS_PK | 1 | 209 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("USER_ID"=:20)
Recommendation provide
1. Increase Initrans USERS 50 and index 51 and higher value for PCTFREE 10 to 40.
0