How to override the log level settings directly in Answers?
Level of query logging for users should be set in the Oracle BI Administration Tool in Security Manager, but we can override the session variable to turn on logging (or change to a higher level of logging) for a particular analysis.
This can be sometimes useful as in production environments logging level is usually set to 0 (no logging). Changing it to second logging level (LOGLEVEL=2) would fetch both the logical and physical SQL.
To change the logging level set in Admin Tool directly in Answers go to Advanced tab when editing the analysis and in Advanced SQL Clauses add below statement to the Prefix field:
SET VARIABLE LOGLEVEL=2;
When the report will be executed, the into the log file. To check the query you can go to Administration / Session Management / Manage Sessions or check the NQquery.log file on the OBIEE server. The default path to NQquery.log file is:
ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/
For the detailed information on query log and logging levels you can refer to the Oracle Documentation on Managing the Query Log: https://docs.oracle.com/cd/E14571_01/bi.1111/e10541/logging.htm#BIESG178.