UpdateLedgerBalanceJob is locking up inside a stored procedure

One of our jobs in ledger service that runs inside a stored procedure seems to be locked up, the job normally completes in 1/2hr and is still running for 6 1/2hrs. Stored Procedures : Begin PRC_UPDATE_LEDGER_BALANCE; End; Begin PRC_CALC_LGR_ACCT_TOTAL; End; The session that is running these procedures has been killed at the DB level begin [login to view URL]( sid => 46, serial => 6283); end; ORA-00031: session marked for kill ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 91 ------- ( its that sql. it is getting stuck running. used to take a half hour to run. Support SQL ID: CA1D372064F9D78B5F3B9DD6C958C1E11B628076SQL ID: bhkav9n9fu0ah Support Digest ID: CA1D372064F9D78B5F3B9DD6C958C1E11B628076Digest ID: bhkav9n9fu0ah it'd either be running as LGRADM or LGRAPP) this is the query that would return results if its working: Untitled select * from LGR_DAILY_LEDGER_ACCT_BALANCE where BALANCE_DATE >= '17-JUL-2019' ---------------- syntax ----- MERGE INTO LGR_DAILY_LEDGER_ACCT_BALANCE TGT USING ( SELECT SUM([login to view URL] * DECODE (PR.CREDIT_DEBIT_IND, 'C', -1, 'D', 1)) AMOUNT, TS.CLIENT_OFFERING_ID, TS.COUNTRY_CODE, TS.JURISDICTION_CODE, TS.OWNER_ID, LA.LEDGER_ACCOUNT_CD, TS.TRANS_STATE_EFFECTIVE_DATE NEWBAL_DATE FROM LGR_TRANSACTION_STATE TS JOIN LGR_TRANSACTION_POSTING_RULE PR ON TS.TRANSACTION_STATE_CD = PR.TRANSACTION_STATE_CD AND TS.TRANSACTION_TYPE_CD = PR.TRANSACTION_TYPE_CD JOIN LGR_LEDGER_ACCOUNT LA ON LA.LEDGER_ACCOUNT_CD = PR.LEDGER_ACCOUNT_FK WHERE TS.TRANS_STATE_EFFECTIVE_DATE=:B1 AND EXISTS ( SELECT 1 FROM LGR_LEDGER_ACCOUNT_RULE R WHERE R.TOTAL_AT_JURISDICTION_LEVEL = 1 AND R.CLIENT_OFFERING_ID = TS.CLIENT_OFFERING_ID AND R.COUNTRY_CODE = TS.COUNTRY_CODE AND R.LEDGER_ACCOUNT_CD = LA.LEDGER_ACCOUNT_CD) GROUP BY TS.CLIENT_OFFERING_ID, TS.COUNTRY_CODE, TS.JURISDICTION_CODE, TS.OWNER_ID, LA.LEDGER_ACCOUNT_CD, TS.TRANS_STATE_EFFECTIVE_DATE UNION