Performance Testing and OBIEE By
Quontra Solutions Email : info@quontrasolutions.com Contact : 404-900-9988 Website : www.quontrasolutions.com
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
The aim of this presentation
A Performance Tuning Methodology
OBIEE techie stuff
Learn from my mistakes!
What is performance testing all about?
Response times Report ETL batch OLTP transaction
System impact Resource usage Scalability
Why performance test? (Isn’t 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
Why performance test?
It’s never too late “You’ll never catch all your problems in pre-
production testing. That’s why you need a reliable and efficient method for solving the problems that leak through your pre-production testing processes.”
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.”
Performance Testing – What & Why
Quantifying response times System impact
User expectations
Problem diagnosis
Design validation
Performance Testing - How? Iterative approach
Define Do it right Timebox! Evaluate design / config options
Redefine test
Be Methodical
Measure
Don’t “fudge it”
Analyse
Do more testing
Review
Do more testing
Implement
Define Measure
Define & build your test
Define – what are you going to test • Aim of the test • Scope • Assumptions • Specifics
• E.g. : • Check that the system performs • Baseline performance • Prove system capacity • Validate system design
• Data, environment, etc
Build – how are you going to test it OBIEE specific
Analyse Review Implemen t
Define Measure
Consider your test scope
Analyse Review Implemen t
More Fewer components components = more = easier complex to manage = more=variables more precise = larger = more margin efficient of error
Define Measure
OBIEE stack
Analyse Review Implemen t
Report / Dashboard Rendere d report
Presentation Services
Data set
Logical SQL
BI Server
Physical SQL statement(s)
Data set(s) Excludes App/Web server & presentation services plug-in
Database
Define Measure
OBIEE testing options
Analyse Review Implemen t
Load Testing tool (eg. LoadRunner, OATS)
User & Stopwatch
nd Re ed er r po re t
Presentation Services
LS QL Da t
as
et
nqcmd
LSQL
BI Server
Physical SQL
Da ta
se t
(s)
SQL Client
Physical SQL
Database
Define Measure
nqcmd
Analyse Review Implemen t
Usage Tracking or NQQuery.log
Logical SQL
Logical SQL
Logical SQL Test script
nqcmd
BI Server
Data
Define Measure
nqcmd
Analyse Review Implemen t
Test script
nqcmd
Test script
nqcmd
Master test script Test Test script script
Logical SQL
nqcmd nqcmd
BI Server
Data
LoadRunner
a.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
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!
Measure
OBIEE measuring & monitoring Enterprise Manager (Oracle)
Apache log
Web Server
OAS log
App Server
Analytics log
Presentation Services plug-in
Server metrics e.g. : IO, CPU, Memory
sawserver.log
Presentation Services PerfMon (windows only)
NQServer.log
Usage Tracking
NQQuery.log
PerfMon (Windows) Oracle OS Watcher (unix)
Enterprise Manager ASH, AWR, SQL Monitor
BI Server systems management
Database
jConsole etc Enterprise Manager
BI Management Pack
Presentation services
Oracle SQL Monitor
Measure - summary
Lots of different ways to measure
Build measurement into your test plan Automate where possible Easier Less error
Analyse
Analysing the data
Analysing the data
Analysing the data
Analysing the data
Analysing the data
Analysing the data 1
Average (mean)
1
3.4
Response time
91 32 2 102 23 13 29 3 10
50th percentile (Median) 2 90th percentile 9.1
Recording data about the test
Extending Usage Tracking
S_NQ_ACCT START_TS
OBIEE_REPLAY_STATEMENT S qt_ora_hash query_text saw_path dashboard
ROW_COUNT TOTAL_TIME_SEC NUM_DB_QUERY QUERY_TEXT QUERY_SRC_CD SAW_SRC_PATH SAW_DASHBOARD
OBIEE_REPLAY_STAT S testid testenv qt_ora_hash start_ts response_time row_count db_query_cnt
Analyse Iterative approach Do it right
Don’t “fudge it”
Evaluate design / config options
Timebox!
Review Iterative approach Redefine test Continue testing
Implement
Review
Implement Iterative approach
Lessons Learnt
You won’t get your testing right first time There’s no shame in that Don’t cook the books Better to redefine your test than invalidate its results
Stick to the methodology Don’t move the goalposts Very tempting to pick off the “low-hanging fruit” If you do, make sure you don’t get indigestion…
Timebox
Test your implementation!
Performance Testing OBIEE Iterative approach Do it right
Don’t “fudge it”
Evaluate design / config options Do more testing Redefine test
Be Methodical
Do more testing
Thank You!