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:
- Use grep to extract just the logical SQL statements[sourcecode language=“bash”] grep SAW_SRC NQQuery.log > NQQuery.lsql_statements.txt[/sourcecode]
- If desired, eliminate duplicates from the file[sourcecode language=“bash”] sort -u NQQuery.lsql_statements.txt > NQQuery.lsql_statements.deduped.txt[/sourcecode]
- Use split to the lsql statements into separate files: [sourcecode language=“bash”]split -l 1 NQQuery.lsql_statements.txt replay.[/sourcecode] This creates a set of files with a replay. prefix and xx suffix, eg.
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
[sourcecode language=“bash”] # get_nq_stats.sh # http://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
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… :-)