Skip to main content

I was hoping someone could perhaps give me some pointers on why this issue is occuring and a possible resolution

 

Issue – Active Separate API causes 100% RAM utilisation of the database server

IFSAPP9 Oracle 12C Server 2012R2 – 4vCPUs 32GB vRAM

 

When a user saves a record in Active work order after assigning a person id, the issue occurs, where the session hangs for the user.  This does not happen consistently, at the moment it's occasional, random and has only occurred 5+ times in the last 3 months or so.

 

Once the session hangs I can observe the session process from a SQL query and the only option is to terminate the process to free up the RAM using orakill or such.

 

The Active seperate module continues to consume Ram over 10 - 30 minutes untill all the RAM has been consumed and the server then grinds to a halt.

I’ve not seen that but it smells like some kind of bug to me.  What UPD are you running?  

It could also potentially be situational related, whereby at the time that the activity is run which locks the user’s session something else is also happening e.g. high RAM load at that time, DB activities, or jobs running in that same WO area.  That combination may trigger the failure resulting in the runaway activity that you see.  Again, could be tied into an instability/bug in the code getting into some kind of loop at that point.

What is your ‘normal’ RAM consumption?  If you’re not far from maxing out normally you might want to consider an increase to see if that helps.

Did you look in the Application Server logs?  You may see something in there that will indicate timeouts or repeated attempts to do something, resulting in the initial lock situation.

Nick

Hi,

Since you stated that it's occasional, have you noticed this RAM 100% hit only when a certain background job runs?

Also, are there any blocking sessions that get created through Active Separate API?


Regards,​​​​​​​

Nadeesh

@NickPorter Thanks, Nick. We are running on UPD17 which I believe was the last one for APPs9. I still need to check in more detail what is happening in the background but it becomes difficult as if it's not caught in time the system becomes almost unusable and SQL queries take forever. Normal RAM consumption is about 60% of the total ram. Thanks for the tip, I will trawl the app logs and see if I can spot anything else

 

@NadeeshHerath  I have noticed but will have a look. How can I check for blocking sessions?

A certain background job causing this is highly likely.

 

You can use the below query to determine blocking sessions,

SELECT t.sid,t.serial#,t.inst_id,t.blocking_instance,t.blocking_session,t.status,t.OSUSER,t.machine,t.program,t.client_info
FROM SYS.GV_$SESSION t
WHERE t.BLOCKING_SESSION IS NOT NULL;
 

Please log in as SYS if you do not have access to the GV_$Session table.

You can also check for blocking sessions in PLSQL Developer by following the below steps,

Tools > Sessions > Locked Sessions

 

Hope it helps.

Terms & ConditionsCookie settings