June 11, 2010

Scripts to extract information from OBIEE NQQuery.log

Filed under: hack, nqcmd, obiee, unix — rmoff @ 09:02

Here are a couple of little unix scripts that I wrote whilst developing my performance testing OBIEE method.

They’re nothing particularly special, but may save you the couple of minutes it’d take to write them ๐Ÿ™‚

Note that some of this data is available from Usage Tracking and where it is I’d recommend getting it from there, databases generally being easier to reliably and repeatably query than a transient log file.

Extracting Logical SQL from NQQuery.log

First is how to extract logical SQL statements from NQQuery.log. This is useful if you want to build up a set of files to replay as a test load against OBIEE:

  1. Use grep to extract just the logical SQL statements
    grep SAW_SRC NQQuery.log > NQQuery.lsql_statements.txt
  2. If desired, eliminate duplicates from the file
    	sort -u NQQuery.lsql_statements.txt > NQQuery.lsql_statements.deduped.txt
  3. Use split to the lsql statements into separate files:
    split -l 1 NQQuery.lsql_statements.txt replay.

    This creates a set of files with a replay. prefix and xx suffix, eg.

    • replay.aa
    • replay.ab
    • etc

Extracting query metrics from NQQuery.log

The next snippet will parse the end of the NQQuery.log and output query execution details:

  • Number of database queries
  • How many rows were returned
  • How long it took
# get_nq_stats.sh
# https://rnm1978.wordpress.com
# Outputs query details of the most recently executed query on BI Server
# Make sure OBIEE_HOME environment variable is set, or update this script to hardcode its location
# Usage 
#     get_nq_stats.sh <testref>
# Examples:
#   Append to file: 
#     get_nq_stats.sh testrep01 >> nq_stats.csv
#   Output to screen:
#     get_nq_stats.sh testrep01
tail -n12 $OBIEE_HOME/server/Log/NQQuery.log|awk -v ref=$1 'BEGIN {physical=""
} {
if ($8=="physical") {gsub(/,/,"",$10) ;physical= $10}
if ($2=="Rows" ) {rows= $6}
if ($2=="Logical") {gsub(/,/,"",$8) ; elapsed= $8}
# print "DB Queries,Rows,Elapsed sec"
print ref "," physical "," rows "," elapsed

The usage for this is on an isolated sequential test environment where you run one BI query, then run this against NQQuery.log, then another query, then this against NQQuery.log etc. Each time you call this script you use a reference (that of the BI Query you’ve just run), and this will be output along with the data from NQQuery.log.
If you call this script and pipe the output to append to a CSV file you can build up a file that looks like this:

Report reference,DB Queries,Rows,Elapsed sec

OBIEE Replay

These snippets form part of a set of Unix and Oracle scripts that I’ve developed under the title OBIEE Replay. The idea of it is to build a harness through which Logical SQL statements can be run against the BI Server and various metrics collected, all in a repeatable manner.
As and when I get time, I plan to post these scripts up here, so watch this space… ๐Ÿ™‚



  1. Hy Thanks Robin,

    I’m not a big guru of unix script (I’m a big zero) and I learn a little bit.

    But when you say that only a few of this informations can be found in usage tracking, I think that you can get them all.

    From the usage tracking table:
    Logical Query:QUERY_TEXT
    How long it took: CUM_DB_TIME_SEC
    How many rows were returned:CUM_NUM_DB_ROW
    Number of database queries:NUM_DB_QUERY

    No ?

    Cheers and good week-end

    Comment by Nicolas GERARD — June 11, 2010 @ 09:27

    • Hey Nico,

      Good point, so maybe these scripts are just another way to skin a cat ๐Ÿ™‚

      Sometimes it’s easier to work with a plain text file that database query, I think that’s probably why I’ve got these snippets stored up.

      The bit that NQQuery.log does have and Usage Tracking doesn’t is the SQL of DB queries fired and the response times/row count of these — and I can’t find my scripts that extracted that info now.

      Comment by rnm1978 — June 11, 2010 @ 09:59

      • Yes, you’re right. We miss a little bit the physical/database query that OBIEE send. In 11g, for sure ๐Ÿ˜‰

        Comment by Nicolas GERARD — June 11, 2010 @ 10:04

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.

%d bloggers like this: