Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

The Myth : Usage Tracking Measures For Tracking The Performance Of Report Retrieval Time

Posted by sranka on June 22, 2010

Hi All,

Took some time off from the blog and other thing to take care of some family priorities, and now back to reality of OBIEE world.  I had been on a Ops call for more than 7 days, issue was very typical, during this debug I explored Usage Tracking in detail.  Following are the detail, this was taken from Doc ID 973090.1]

TOTAL_TIME_SEC is not a good measure of the time it takes from user submittal to retrieval completion because OBI has a zero footprint client, and Usage Tracking is only able to account for the time from when a request enters the OBI Presentation Server to the time that OBIPS releases the results to the client. The Time spent between UI and OBIPS is not accounted for in Usage Tracking.TOTAL_TIME_SEC is the time that the OBI server spent working on a query.This includes the clock time spent waiting for queries to complete. If multiple physical queries are running in parallel, the time reported is how long OBI is spent waiting for the database.This basically is the run time of the longest running query. The total time of all physical queries spawned by a single logical query is reported in CUM_DB_TIME_SEC. This is not part of TOTAL_TIME_SEC. Time spent waiting for resources is not included in TOTAL_TIME_SEC.Note also that START_TS and END_TS have nothing at all to do with TOTAL_TIME_SEC.

START_TS is the time when a user submits a query.This could be when they hit the Results tab in Answers or when they select a dashboard page.
END_TS is when the results are returned to the client.The difference between START_TS and END_TS also includes any time spent waiting for resources, such as waiting for a free connection. In the example where the TOTAL_TIME_SEC is 0 but the difference between START_TS and END_TS is 14 minutes, the 14 minutes could have been spent waiting.

If it is evident that a report is delayed, it is because it is waiting for a connection pool connection, a dbgateway thread or a server thread.
Connection pool connections are set in the .rpd. Dbgateway and server threads are set in nqsconfig.ini. Connection pooling is also handled by nqsserver, not sawserver. TOTAL_TIME_SEC is the time clocked once the request reaches nqsserver. Any wait for connections would happen before the request reached nqsserver. These would be reflected in the difference between START_TS and END_TS as mentioned above.

If you decide that server thread and dbgateway thread need to be adjusted, it is recommended that you get a site review from professional services.
Oracle recommends that unless you are experiencing a specific problem, that the settings be left at default.Problems involving sessions or thread ranges set too small would involve requests being queued. There are several settings that could come into play here including thread ranges and connections from connection pools.  Normally performance monitor counters would be set up to diagnose what setting is likely to be the bottleneck.As for the stack sizes, again you would need to be experiencing some sort of problem for Oracle to recommend a change.

Hope This Help

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Advertisements

One Response to “The Myth : Usage Tracking Measures For Tracking The Performance Of Report Retrieval Time”

  1. rnm1978 said

    Hi Sunil,

    Interesting article. Are you sure about this statement though?

    “Usage Tracking is only able to account for the time from when a request enters the OBI Presentation Server to the time that OBIPS releases the results to the client”

    I would have said that it is correct if you put ‘BI Server’ in place of ‘OBI Presentation Server’. Usage Tracking is on the BI Server only, I don’t think it can account for PS at all.
    I’ve written a bit about response times out of PS here: http://rnm1978.wordpress.com/2010/06/14/measuring-real-user-response-times-for-obiee/

    Cheers, Robin.

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: