Usage Tracking - Need more precision in Date columns in S_NQ_ACCT

Mark.Thompson

• In S_NQ_ACCT, the START_TS column is defined with a datatype of DATE, which limits us to zero decimals of precision after the number of seconds (i.e. seconds are the lowest level of granularity for a DATE column). 
• A TIMESTAMP column can have up to 9 decimals of precision for seconds, with 6 being the default.
• I recreated the START_TS column as a TIMESTAMP in S_NQ_ACCT, and while Usage Tracking runs just fine, it still stores that column with zero decimals of precision after the seconds (e.g. 19-AUG-17 08.41.43.000000000 AM)
• This leads me to believe that Oracle must be converting the query starting time to HH:MI:SS format before storing it in the START_TS column, perhaps with a to_char() function.

If my conclusion above is correct, then this question is probably pointless, but I'll ask anyway:

Assuming that we redefine START_TS and END_TS as TIMESTAMP columns (which, as mentioned above, still works, and doesn't break anything), can anyone think of a way to record the full number of decimals of precision in the START_TS and END_TS columns in S_NQ_ACCT, so that we get something like 19-AUG-17 08.41.43.327654000 AM?

(A similar question could be asked about columns such as TOTAL_TIME_SEC, which are defined as NUMBER(10,0).  Recreating those columns with a NUMBER(10,3) datatype has no effect on the results.  We still see no decimal places, again leading me to believe that the result is either (a) being truncated or rounded prior to storage, or (b) is being computed from the DATE fields END_TS minus START_TS.  Either way, there are no decimals of precision.)

Syedsalmancs110

Your assumptions are quite right, usage tracking is a packaged functionality within OBIEE that is the reason we can leverage it by just following simple configuration steps and have no control over in what form data gets inserted into out of the box usage tracking tables, I am sure that if someone dig deep into what code controls usage tracking then it could be figured out but that is NOT A SUGGESTED PRACTICE.

But if you have valid use case/scenario which makes is mandatory to get Milliseconds details then I would suggest you to take this request up to Oracle and go through required channels.

P.S. Above is my understanding but if someone has a possible solution for same without disturbing any internal Oracle code(not supported methods) then be our guest