OBIEE 11g Server unresponsive

Question

Hello,

We are on Linux based:

Oracle Business Intelligence 11.1.1.7.150120

Our production server became unresponsive. No one could hit the /analytics url, our sysadmin couldn't even ssh/putty into it. Our monitoring tool Opmanager showed 100% CPU.

Eventually we were able to ssh into the server using putty and saw that the cpu load average was showing as 90+ and some of the key processes like nqserver, sawserver were showing 100% cpu

Long story, short, since we couldn't have production server down, we ended up killing these processes and then stopped and restarted the whole OBI stack (its a simple single host environment, no clustering etc.).

The question now is how to go about finding out why this happened and what can be done to mitigate this scenario.

Any pointers are welcome. We have captured key log files after shutdown. Planning to open an SR as well.

Thanks,

Manish

[Deleted User] · Answer

ManishHathi wrote:one of them fessed up saying they a detail level analysis for 3 yrs worth of data without any filters. Ok, so we now know how it happened.

That's so relatable it hurts

ManishHathi wrote:The next question is, at a system level, how can we prevent a single session to monopolize the OBIEE system? Note that the issue was not on the database server, only on the OBI server.BI Publisher has the memory guard, the database has resource manager, is there a configuration on the Analytics side which would prevent this from happening? (Yes, I am aware of user training, design analysis that doesn't allow huge data ranges etc. but occasionally, users with the best of intentions can inadvertently still do this type of behavior)

Query limits, time-outs etc.

ManishHathi · Answer

Yes, we already have the memory guard configured for BI Publisher.

I did some old fashioned sleuthing. Talked to various power users and one of them fessed up saying they a detail level analysis for 3 yrs worth of data without any filters. Ok, so we now know how it happened.

The next question is, at a system level, how can we prevent a single session to monopolize the OBIEE system? Note that the issue was not on the database server, only on the OBI server.

BI Publisher has the memory guard, the database has resource manager, is there a configuration on the Analytics side which would prevent this from happening? (Yes, I am aware of user training, design analysis that doesn't allow huge data ranges etc. but occasionally, users with the best of intentions can inadvertently still do this type of behavior)

Thanks,

Manish

FPonte · Answer

Hi ManishHathi.

Hard to know without information from the Log.

One possibility. I faced similar issues with this and the culprit was a BI Publisher report. The data model generated a massive result basic killed the server.

You may find some information about this in the logs.

The workaround to this was to apply the BI Publisher Memory Guard.

You will find more information here.

https://docs.oracle.com/cd/E29542_01/bi.1111/e22255/mem_guard.htm#BIPAD4850