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
[login to view URL](
sid => 46,
serial => 6283);
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:
select * from LGR_DAILY_LEDGER_ACCT_BALANCE where BALANCE_DATE >= '17-JUL-2019'
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
4 freelancers are bidding on average $30 for this job
I am expertise in sql queries and query tuning . if you are interested ping me i will fix your issue. we will get rid of this merge and do the things .