One of the frequent recommendations around performance in OBIEE that one hears is a blanket insistence on disabling the BI Server log. It is a line that is repeated by Oracle support, propogated in “Best Practice” guides, and repeated throughout blog posts on the subject. Antony Heljula did a talk on the subject at the recent RittmanMead BI Forum in Brighton, and I would like to echo and expand on it here.

The Myth:
If you are having performance problems in OBIEE, you should switch off BI Server logging

The arguments for:
Instinct would tell us that writing a log is going to take longer than not writing to a log
On a system with high user concurrency, we would expect to see contention for writing to the log file
Usage Tracking records report response times, so why do we also need the server logging
Log files will cause the disk to fill up, which left uncontrolled could cause system instability
The arguments against:
If you have performance problems in OBIEE, then you need logging in place to be able to trace and diagnose them. The BI Server log gives us vital information such as what physical SQL results from a logical query from the front end. If you turn off logging, you lose all visibility of query behaviour, timings, and row counts.
OBIEE writes lots of logs, more so now in 11g. Why only disable one of them? Why not all logs?
If a query takes 30 seconds to run, how much of that 30 seconds is actually going to be in log overhead? You disable logging and now your query runs in 29.999 seconds. It’s still slow, it’s still a performance problem – and now you don’t have the data available with which to diagnose the problem!
Usage Tracking doesn’t record the same level of detail around a query’s behaviour (response time profile, row counts) that the server log does.
By default, Usage Tracking chops off Logical SQL above 1024 characters in length.
Sometimes you need the log file to confirm that Usage Tracking is reporting correctly (especially in circumstances where report run times seem unusually high)
Error messages returned from the database are not captured in Usage Tracking
It Depends
To a point, I am being contrary in arguing this specific issue, but it is important with this and other broad-stroke pronouncements around performance that get regurgitating without context and caveats that they are understood. In particular, labelling it a “Best Practice” is a dangerous fallacy as it implies that it should be done without much further thought or consideration of its consequences.

If the NFR for a report’s performance is [sub]-second and it is not being met, then profiling of the end-to-end response time breakdown should be done, and it might be that it demonstrates that the logging is impeding performance. But the point is that it is proven rather than done blindly.

Further reading
Cary Millsap’s paper, Thinking Clearly About Performance, is an excellent starting point for developing an understanding of a logical and methodical approach to performance problem solving.

James Morle wrote an great blog post on the subject of “Best Practice” and why it is dangerous terminology, entitled “Right Practice”

When trying to login to BI server, we were getting a perpetual message :

Logging in... please wait.....Select * from table_namewas running for over 5 mins. Asked DBAs to do their magic, and sure enough, everything was back to normal. But why in the name of god, login would hang up if database is preoccupied? Why can’t BI log you in and show a blank page in you’re My Folders?Answer: Initialization blocks. Every time someone logs in, all session variables are populated, and till the results from BI comes back, BI would ask user to wait.

We checked the presentation server, BI server, memory, CPU, hard disk free space, temp files, you name it, all was good. Turns out, database server was the culprit. A simple query like :

OBIEE Logs are generated by the following components:

BI Presentation Server

BI Server

BI Cluster Controller

BI Javahost

BI Scheduler

BI Presentation Server log can be located from C:\OracleBIData\web\log\sawlog0.logThis log is useful for Presentation Server login issues and answers/dashboard related issues.You can increase the logging on this server to get a detailed logging to troubleshoot the SSO and integration issues by modifying the logconfig.xml file present in C:\OracleBIData\web\config folderBI Server logs are NQServer.log and NQQuery.log present under C:\OracleBI\server\Log folderNQServer.log consists of BI Server Start up issues and Subject areas loaded and the datasources connectedfrom within the RPD. If the NQSConfig.ini has errors or if RPD has version issues or if proper RPD is not loaded, the BI Server fails to start and this will be recorded in the NQServer.logNQQuery.log consits of BI Server query operations. We can also check if the result is coming from cache or straight from database. We can get the actual time to execute the query. We can get the physical and logical query from this log.BI Cluster Controller log can located from C:\OracleBI\server\Log folder. When the cluster is setup with OBIEE server nodes, the NQCluster.log file would provide the cluster nodes connectivity and status information. Along with the NQServer.log , NQCluster.log would be very useful for cluster related issues.BI Javahost can be located from C:\OracleBIData\web\log\javahost\jhost0.log. This is used for identifying the issues with the charts and graphs generated by Java from Answers/Dashboard reports.BI Scheduler can be located from C:\OracleBI\server\Log\NQScheduler.log. This is used for identifying the issues related to delivers/scheduler. To increase the logging on scheduler, set the debug flag to true in the scheduler job manager configuration window.