Closed

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

Skills: Database Programming, Oracle, SQL

See more: sql stored procedure return table, sql xml stored procedure, return info stored procedure tsql, stored procedure dump phpmyadmin, windows applications create stored procedure page, sql database schema stored procedure, xml path stored procedure, sql stored procedure check expiry date, update stored procedure, t_sql stored procedure, stored procedure convert function, convert sql stored procedure oracle stored procedure, create stored procedure sap biztalk, convert stored procedure oracle procedure, stored procedure xml output net, stored procedure sql 2005, mssql stored procedure send mail

About the Employer:
( 0 reviews ) Aurora, United States

Project ID: #20468694

4 freelancers are bidding on average $30 for this job

fanninson45

INTRODUCTION I am an experienced Professional with Database Programming and Administration skills across MySQL, MS SQL and Business Intelligence Development. I have 4+ years’ experience with Database Programming and More

$20 USD in 7 days
(21 Reviews)
6.0
sreenivas2903

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 .

$50 USD in 3 days
(12 Reviews)
4.1
alielastal

Hi, After reading the SQL Statements, looks like it will take a deep test to ensure the performance. I will try to write another one if we have to, to finally reach to the highest performance. I have 5 year's in deve More

$30 USD in 3 days
(0 Reviews)
0.0
ranjanmohanty

I am an experienced Oracle Developer having good experience in Oracle database, SQL and PL/SQL and database performance tuning, I have vast industry exposure to solve this kind problem in PRODUCTION, UAT, DEV instance, More

$20 USD in 7 days
(0 Reviews)
0.0