Deadlock occurred in HFM database during month end data loads
We are facing an issues with dead locks in database.the below of observations we identified in oracle DBA Side.
1.There have been a spike in the incidence of deadlocks (caused by application queries) during the time 5pm to 6pm AEST.
2. The cpu utilization has reached 100% and incurred lot of wait times in the database during the time 4pm to 6pm AEST.
3. The concurrent active sessions peaked from 0 to 150 during a short period of time between 4pm to 6pm.
Following query has used most of the cpu resources during 4:30pm to 5:30pm.
+++++++++++
User: ABC
Query:
SELECT *
FROM APPHFM_DCE_1_2012
where lEntity = :"SYS_B_0" AND (lValue = :"SYS_B_1" or lValue = :"SYS_B_2") AND dTimestamp >= :"SYS_B_3" ORDER BY lValue,lAccount,lICP,lCustom1,lCustom2,lCustom3,lCustom4.------->
0