RSS

OBIEE Query LOGLEVEL

08 Oct

To analyze or diagnose OBIEE report performance we need to see its log files.These diagnostics will be shown to us depends on the LOGLEVEL we set for the user.

The following Oracle note gives us a clear understanding of each LOGLEVEL we set for the users.

Query Logging Levels:

Logging Level Information That Is Logged
Level 0 No logging.
Level 1 Logs the SQL statement issued from the client application. Also logs the following: 

  • Physical Query Response Time — The time for a query to be processed in the back-end database.
  • Number of physical queries — The number of queries that are processed by the back-end database.
  • Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).
  • DB-Connect time — The time taken to connect to the back-end database.
  • Query cache processing — The time taken to process the logical query from the cache.
  • Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time.
  • Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking.
  • Compilation time — The time taken to compile the logical query.
  • For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.
Level 2 Logs everything logged in Level 1.  

Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.

Level 3 Logs everything logged in Level 2.  

Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails.

Do not select this level without the assistance of Oracle Support Services.

Level 4 Logs everything logged in Level 3.  

Additionally, logs the query execution plan. Do not select this level without the assistance of Oracle Support Services.

Level 5 Logs everything logged in Level 4. 

Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Oracle Support Services.

Level 6 and 7 Not used.

 

 

Thanks,

Shiva

Advertisements
 
3 Comments

Posted by on October 8, 2010 in OBI EE, OBIEE 11g

 

3 responses to “OBIEE Query LOGLEVEL

  1. Kishore

    October 31, 2010 at 7:46 am

    Hi Shiva- The posting is pretty informative.Please keep posting and share your knowledge.This would be of immense help to us.

    i have a situation in our environment .

    Lately we had the OBIEE performance issue.

    There is a query (with lot of joins ) for which the end users are reporting performance issues.
    There are 4 levels in the report for that single query when the end users run the report.
    The same query runs fine for 3 and 4 levels in seconds but when the end users select level 1 and 2 ,the query take more than 1-2 hours .

    When we checked with the respective OBIEE team,they say that it is a performance problem with the database optimiser .However the same query works fine for the reports (3 and 4).

    When we asked for the queries ,they say that they cant get which queries are running slow from OBIEE .

    when asked ,they say that hints also cannot be added in obiee .

    They obiee team states that the whole reason is the optimiser in the oracle database is changing the plans .
    but the DBA says that the tables used in the query are collected statistics every weekend .

    Have you come across a similar situation in your experience ?
    what can be done to narrow down the performance bottleneck in these situations ?

    any inputs from you will be appreciable.

    Thanks
    Kishore

     
  2. MNicholas

    March 8, 2011 at 8:25 pm

    Where do you set this LOGLEVEL?

     
  3. Ignacio

    March 31, 2011 at 2:40 pm

    Hi all,

    Someone might advance the resolution of these issues?
    I also need to implement this in my client.

    Regards.

    Ignacio

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: