ListOBIStats parses NQQuery log from Oracle's Business Intelligence Server and reports performance stats.
Introduction ListOBIStats provides an HTML as well a CSV file containing the summary performance stats from OBIEE (Oracle Business Intelligence Enterprise Edition) by parsing the nqquery.log generated by the BI server.
It collects the following information for each Logical SQL
- Logical SQL Hash ID
- Physical SQLs and their hash id
- Elapsed Time
- Number of rows fetched
- ADFQuery XML tag if the datasource is OTBI (Oracle Transactional Business Intelligence)
- A CSV file containing above data
Each Logical or Physical SQL is properly formatted and indented. They are written to separate files and are accessible from the HTML results in the browser.
If the datasource is an Oracle database, it is possible to automatically generate the Explain Plan.
Download source and execute the following command
python listobistats.py --help
Usage: python listobistats.py [options] $OBIEE_HOME/.../coreapplication_obis1/nqquery*.log
results.html contains the HTML output
For best results, your logical SQLs should contain an integer TEST ID, caching should be disabled and LOGLEVEL be set to 2. Here's an example:
SET VARIABLE PRODUCT_NAME='SPE', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='ZSP_LEADS', TEST=76: SELECT "Lead Facts"."# of Leads" Lead_Count,...
Options:
-h, --help show this help message and exit -d OUT_DIR, --out_dir=OUT_DIR store results in the specified output directory [default: listobistats]
-m MIN_ELAPSED_TIME, --min_elapsed_time=MIN_ELAPSED_TIME ignore queries that took less than the min elapsed time in seconds [default: 0]
-M MIN_TIME_PER_ROW, --min_time_per_row=MIN_TIME_PER_ROW ignore queries that took less than the min elapsed time per row in milliseconds [default: 0]
-i, --ignore_queries_with_no_results ignore queries that do not return any results [default: False]
-p, --explain_plan Generates explain Plan for physical sqls by executing them against oracle db [default: False]
-s SQLPLUS, --sqlplus=SQLPLUS full path to Sqlplus [default: /usr/local/redhat/OracleProd?/oracle10.2/bin/sqlplus] ORACLE_HOME should point to /usr/local/redhat/OracleProd?/oracle10.2 or equivalent
-c DB_CONNECTION_STRING, --db_connection_string=DB_CONNECTION_STRING db Connection string [default: hr/hr@localhost:1563/orcl]