OBIEE - Which Usage Tracking time measure should be used to gauge the performance of SQL retrieval time ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

2 - Measures

2.1 - TOTAL_TIME_SEC

TOTAL_TIME_SEC is the same as Response time in the query log. It is the response time for the execution of a logical request in the gateway. There are several times from the query log that are included in response time (TOTAL_TIME_SEC) that are not tracked in the usage tracking logs.

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.

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.

Advertising

2.2 - START_TS and END_TS

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 BI Server Client submits a query.

For Presentation Service, this could happen when you hit the Results tab in Answers or when you 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.

3 - CUM_DB_TIME_SEC

The CUM_DB_TIME_SEC column sometimes show a value greater than TOTAL_TIME_SEC.

CUM_DB_TIME_SEC is the cumulative time of all of the queries sent to the backend database. These queries run in parallel so the cumulative query time will be equal to or greater than the total time connected to the database. Say for example that a logical request spawns 4 physical SQL statements sent to the database and that the query times for 3 of the queries are 10 seconds and one is 15 seconds. Since the queries run in parallel, nqsserver will only be connected to the backend database for 15 seconds. CUM_DB_TIME_SEC will show 45 seconds.

4 - Waiting Time and parallelism

Not true !

TOTAL_TIME_SEC = END_TS - START_TS
TOTAL_TIME_SEC = COMPILE_TIME_SEC + CUM_DB_TIME_SEC 

The reasons why the calculations provided above do not add up are because:

  • The processes are carried out in parallel and is very much dependent on the load on the BI Server and database performance.
  • If all connections are full then the query would join a queue and wait.

This dependent on the load and configuration of the execution so it won't add up.

  • Under normal circumstances the process will take place parallel and engenders server based operations that are light or intensive.

For instance, if 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 SQL is delayed, it is because it is waiting for:

Connection pooling is also handled by nqsserver, not sawserver.

Advertising

5 - Documentation / Reference

dat/obiee/obis/usage_tracking_time_column.txt · Last modified: 2017/03/30 13:42 by gerardnico