Usage Tracking Issues

OK, this may all be fixed in 11g, but I'm still a 10g guy, 4 years after that hatched. Having said that, if it isn't fixed in 11g this would have to be considered a serious sign of being asleep at the switch. If it is fixed, then maybe 11g starts to look better to me, somewhat anyway.

So here's the set of problem in usage tracking, 10g. You have two choices when it comes to usage tracking: either direct inserts of usage information into a table (s_nq_acct) or usage tracking information written to files (a new file every X minutes, as determined by the parameters in NQSConfig.ini).

There are two big problems with direct insert. One, I've found, is that it's very temperamental and can stop working for either no apparent reason (in which case you'll probably get a bogus error message citing a bogus cause in the bi server log). Or it can stop working for a reason that shouldn't exist (such as you wanted a column to hold more characters so you expanded it).

The other big problem is that the QUERY_TEXT column is only varchar2(1024), which means that you're only going to record a fraction (maybe only a 10th!) of the entire logical query. (Again, expanding it pretty much shuts down direct insert). So this column doesn't give you the information you need to really understand whatever issues might have arisen when the query was executed because you can't see all of it and thus you can't duplicate it and examine the physical SQL that would have been generated.

So what happens if you take the second alternative and have the bi server write usage information to files (i.e. DIRECT_INSERT = NO in NQSConfig.ini)? Now you DO get all the logical SQL. However, the file doesn't contain all the other fields that would have been written in direct insert mode! Most importantly, QUERY_SRC_CD is null, always, instead of telling you whether a query was either a

GlobalFilter
Report
ValuePrompt
drill
Null (??)

Normally, if I'm looking at usage, I'm interested in counting only the "Report" queries. Without direct insert you can't differentiate -- you have to count everything. Not that the direct insert categorization is necessarily what you'd like if you had a choice (how about iBot being one of the query types?), but still it contains pretty essential information. Why isn't it written to the file? Why isn't what's written to the file (or directly to a table) configurable, for that matter?

At the moment I've adopted the file method rather than the direct insert method because a) direct insert stopped working after about 6 years of working, so I've got no real choice;  and b) I really would like the entire logical query text.

The consequence of this is that I periodically take all the usage files that have been generated and load them into a new table (S_NQ_ACCT_ADD) using SQLLDR. It's a bit of a pain, but it enables analysis of the usage data which otherwise, just sitting in a text file, is pretty much unusable.

A word of advice here: change the datatype of QUERY_TEXT in the new table to CLOB. Also increase the size of the NODE_ID column from varchar2(15) to varchar2(128), or otherwise loads will fail.

The pertinent section of the sqlldr control file then says:

APPEND INTO TABLE S_NQ_ACCT_ADD
      FIELDS TERMINATED BY ";"
  TRAILING NULLCOLS

  (
    USER_NAME CHAR(128) ,
REPOSITORY_NAME CHAR(128) ,
SUBJECT_AREA_NAME CHAR(128) ,
NODE_ID CHAR(128) ,
START_TS DATE "YYYY-MM-DD HH24:MI:SS",
START_DT DATE "YYYY-MM-DD",
START_HOUR_MIN ,
END_TS DATE "YYYY-MM-DD HH24:MI:SS",
END_DT DATE "YYYY-MM-DD",
END_HOUR_MIN ,
QUERY_TEXT char(40000),
SUCCESS_FLG ,
ROW_COUNT ,
TOTAL_TIME_SEC ,
COMPILE_TIME_SEC ,
NUM_DB_QUERY ,
CUM_DB_TIME_SEC ,
CUM_NUM_DB_ROW ,
CACHE_IND_FLG CHAR(1)
)