How to set up a new connection in SQL Developer?
April 1, 2018
Show all

How can we predict whether a long running session is hang or just long running?

How can we predict whether a long running session is hang or just long running

How can we predict whether a long running session is hang or just long running

This is one of the common issues which we see in most of the production environments and there are multiple ways to identify it as we (Production Support team) can not keep looking at the sessions to see which one is running late.

There are two parts to it.

One, to know which one (tasks) is potentially running late and the other is to find out why and fix it.

Let’s talk about the first part, to identify which tasks.

Based on the previous run times of the workflows, you can come up with the average run time per the total number of records being processed and set an alert. Refer to this discussion for more details on how to set the alerts.

In certain projects there will be an alert set over certain period for example, no job should take more than an hour to complete. If there is a logic/increase in volumes the jobs should be split in parallels to process. This is a design aspect and should be incorporated with partitions or any other related logic.

Proactive Monitoring for PowerCenter (PMPC) is a tool from Informatica which can be configured based on certain conditions to show them as alerts. This needs a separate license.

Note: There are other possibilities based on the need and environment. The above are the common ones.

Let’s see the ‘how to find out and fix’ part of it.

If any task is running log, the first and the foremost thing is to see the session log entries. When was the last time the session log is updated with an activity?

Is there a standard message from the last ‘n’ number of minutes?

Is there any update to the number of records which are being read or processed or loaded in the session log?

Is there any change to the total number of records in the target file or relational table which is being updated by this task or workflow?

Is the task waiting for a file (either from an event wait task or a control file or a touch file) to proceed with the next steps?

There could also be a possibility of wrong conditions between session which would have missed the testing and the whole workflow is stuck but the overall status still shows as running.

Once you can answer the questions above or the one which is causing the issue, the next step is to fix it.

The fixing part is usually performance tuning if the cause is of high volumes. There could be other possibilities as well that are design specific and logic specific.