Performance Testing and OBIEE By Quontra Solutions

1
Email info_at_quontrasolutions.co.ukCont
act 020-3734-1498Website
www.quontrasolutions.co.uk

• Performance Testing and OBIEE
• By
• Quontra Solutions

2
Introduction

• Oracle BI specialist at Morrisons plc
• Big IT development programme at its early stages
  implementing OBIEE, OBIA, ORDM, all on Oracle 11g
  HP-UX

3
The aim of this presentation

• A Performance Tuning Methodology
• OBIEE techie stuff
• Learn from my mistakes!

4
What is performance testing all about?

• Response times
• Report
• ETL batch
• OLTP transaction
• System impact
• Resource usage
• Scalability

5
Why performance test?(Isnt testing just for
wimps?)

• Check that your system performs
• Are the users going to be happy?
• Baseline
• How fast is fast?
• How slow is slow?
• Validate system design
• Do it right, first time
• Capacity planning

6
Why performance test?

• Its never too late
• Youll never catch all your problems in
  pre-production testing. Thats why you need a
  reliable and efficient method for solving the
  problems that leak through your pre-production
  testing processes.

7
Why performance test?

• Because it makes you better at your job
• At the very least, your performance test plan
  will make you a more competent diagnostician (and
  clearer thinker) when it comes time to fix the
  performance problems that will inevitably occur
  during production operation.

8
Performance Testing What Why

• Quantifying response times
• System impact
• User expectations
• Problem diagnosis
• Design validation

9
Performance Testing - How?
Iterative approach
Timebox!
Do it right Dont fudge it
Evaluate design / config options
Do more testing
Redefine test
Do more testing
Be Methodical
10
Define build your test

• Define what are you going to test
• Aim of the test
• Scope
• Assumptions
• Specifics
• Data, environment, etc
• Build how are you going to test it
• OBIEE specific

• E.g.
• Check that the system performs
• Baseline performance
• Prove system capacity
• Validate system design

11
Consider your test scope
More components more complex more variables
larger margin of error
Fewer components easier to manage more
precise more efficient
12
OBIEE stack
Presentation Services
BI Server
Database
Excludes App/Web server presentation services
plug-in
13
OBIEE testing options
Load Testing tool (eg. LoadRunner, OATS)
User Stopwatch
Presentation Services
nqcmd
SQL Client
BI Server
Database
14
nqcmd

• Command nqcmd - a command line client which can
  issue SQL statements
• against either Oracle BI server
  or a variety
• of ODBC compliant backend
  databases.
• SYNOPSIS
• nqcmd OPTION...
• DESCRIPTION
• -dltdata source namegt
• -ultuser namegt
• -pltpasswordgt
• -sltsql input file namegt
• -oltoutput result file namegt
• -DltDelimitergt
• -Clt number of fetched rows by
  column-wise bindinggt
• -Rlt number of fetched rows by row-wise
  bindinggt
• -a (a flag to enable async processing)
• -f (a flag to enable to flush output
  file for each write)
• -H (a flag to enable to open/close a
  request handle for each query)
• -z (a flag to enable UTF8 instead of
  ACP)
• -utf16 (a flag to enable UTF16 instead
  of ACP)

15
nqcmd

• oracle_at_RNMVM01 setup . ./sa-init.sh
• oracle_at_RNMVM01 setup nqcmd
• --------------------------------------------------
  -----------------------------
• Oracle BI Server
• Copyright (c) 1997-2009 Oracle
  Corporation, All rights reserved
• --------------------------------------------------
  -----------------------------
• Give data source name RNMVM01
• Give user name Administrator
• Give password Administrator
• Table info
• Column info
• Data type info
• Foreign keys info
• Primary key info
• Key statistics info

16
nqcmd

• oracle_at_RNMVM01 perftest cat /data/perftest/lsql
  /test01.lsql
• SELECT "D0 Time"."T01 Per Name Week" saw_0 FROM
  "Sample Sales" WHERE ("D01 More Time
  Objects"."T31 Cal Week" BETWEEN 40 AND 53) AND
  ("D01 More Time Objects"."T35 Cal Year" 2007)
  ORDER BY saw_0
• oracle_at_RNMVM01 perftest . /app/oracle/product/o
  biee/setup/sa-init.sh
• oracle_at_RNMVM01 perftest nqcmd -d AnalyticsWeb
  -u Administrator -p Administrator -s
  /data/perftest/lsql/test01.lsql
• --------------------------------------------------
  -----------------------------
• Oracle BI Server
• Copyright (c) 1997-2009 Oracle
  Corporation, All rights reserved
• --------------------------------------------------
  -----------------------------
• Connection open with info
• 0State 01000 DataDirectODBC lib
  Application's WCHAR type must be UTF16, because
  odbc driver's unicode type is UTF16
• SELECT "D0 Time"."T01 Per Name Week" saw_0 FROM
  "Sample Sales" WHERE ("D01 More Time
  Objects"."T31 Cal Week" BETWEEN 40 AND 53) AND
  ("D01 More Time Objects"."T35 Cal Year" 2007)
  ORDER BY saw_0
• SELECT "D0 Time"."T01 Per Name Week" saw_0 FROM
  "Sample Sales" WHERE ("D01 More Time
  Objects"."T31 Cal Week" BETWEEN 40 AND 53) AND
  ("D01 More Time Objects"."T35 Cal Year" 2007)
  ORDER BY saw_0

17
nqcmd
Usage Tracking or NQQuery.log
BI Server
Logical SQL
Data
Logical SQL
Logical SQL
Test script
nqcmd
18
nqcmd
Master test script
Test script
nqcmd
BI Server
Data
Test script
nqcmd
Logical SQL
Test script
nqcmd
Test script
nqcmd
19
LoadRunnera.k.a. HP Performance Centre

• Simulates user interaction HTTP traffic
• Powerful, but can be difficult to set up
• Ajax complicates things
• Do you really need to use it?
• Tools
• Fiddler2
• FireBug
• Reference
• My Oracle Support Doc ID 496417.1
• http//rnm1978.wordpress.com/category/loadrunner

20
Defining your test - summary

• Be very clear what the aim of your test is
• You probably need to define multiple tests
• Different points on the OBIEE stack to interface
• Pick the most appropriate one
• Write everything down!

21
Measure
22
OBIEE measuring monitoring
PerfMon (Windows)
Enterprise Manager (Oracle)
Server metrics e.g. IO, CPU, Memory
Apache log
Oracle OS Watcher (unix)
OAS log
Usage Tracking
sawserver.log
Analytics log
Presentation Services
NQServer.log
Enterprise Manager
NQQuery.log
ASH, AWR, SQL Monitor
BI Server
Database
PerfMon (windows only)
systems management
jConsole etc
Enterprise Manager BI Management Pack
Presentation services
23
NQQuery.log

• Query Status Successful Completion
• Rows 1, bytes 96 retrieved from database query
  id ltlt10172gtgt
• Physical query response time 1 (seconds), id
  ltlt10172gtgt
• Rows 621, bytes 9246 retrieved from database
  query id ltlt10188gtgt
• Physical query response time 10 (seconds), id
  ltlt10188gtgt
• Physical Query Summary Stats Number of physical
  queries 2, Cumulative time 11, DB-connect time 0
  (seconds)
• Rows returned to Client 50
• Logical Query Summary Stats Elapsed time 14,
  Response time 12, Compilation time 2 (seconds)

24
Database metrics

• Oracle
• Enterprise Managers Performance functionality is
  fantastic
• For pure testing metrics capture you need to go
  to the tables
• VSQL_MONITOR, etc

25
Oracle SQL Monitor
26
Measure - summary

• Lots of different ways to measure
• Build measurement into your test plan
• Automate where possible
• Easier
• Less error

27
Analyse
28
Analysing the data
29
Analysing the data
30
Analysing the data
31
Analysing the data
32
Analysing the data
33
Analysing the data
Response time
1
1
9
3
2
10
2
1
2
3
Response time
1
1
1
2
2
2
3
3
9
10
Average (mean)
3.4
50th percentile (Median)
2
90th percentile
9.1
34
Recording data about the test
35
Extending Usage Tracking
OBIEE_REPLAY_STATEMENTS qt_ora_hash query_text saw
_path dashboard
S_NQ_ACCT START_TS ROW_COUNT
TOTAL_TIME_SEC NUM_DB_QUERY
QUERY_TEXT QUERY_SRC_CD
SAW_SRC_PATH SAW_DASHBOARD
OBIEE_REPLAY_STATS testid testenv qt_ora_hash star
t_ts response_time row_count db_query_cnt
36
Analyse
Iterative approach
Do it right Dont fudge it
Evaluate design / config options
Timebox!
37
Review
Iterative approach
Redefine test
Continue testing
Implement
38
Review
39
Implement
Iterative approach
40
Lessons Learnt

• You wont get your testing right first time
• Theres no shame in that
• Dont cook the books
• Better to redefine your test than invalidate its
  results
• Stick to the methodology
• Dont move the goalposts
• Very tempting to pick off the low-hanging fruit
  
• If you do, make sure you dont get indigestion
• Timebox
• Test your implementation!

41
How to approach performance testing

• Think clearly

42
Performance Testing OBIEE
Iterative approach
Do it right Dont fudge it
Evaluate design / config options
Do more testing
Redefine test
Do more testing
Be Methodical
43
Thank You!