Performance Testing 
 and OBIEE Robin Moffatt, WM Morrisons plc http://rnm1978.wordpress.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.” — Cary Millsap - Thinking Clearly About Performance 

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.” — Cary Millsap - Thinking Clearly About Performance 

Define Measure Analyse Review Implement Timebox! Performance Testing - How? Evaluate design / config options Do it right Don’t “fudge it” Do more testing Iterative approach Be Methodical Redefine test Do more testing 

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 Defi ne Meas ure Anal yse Revi ew Impl eme nt •E.g. : •Check that the system performs •Baseline performance •Prove system capacity •Validate system design 

Consider your test scope More components = more complex = more variables = larger margin of error Fewer components = easier to manage = more precise = more efficient Defi ne Meas ure Anal yse Revi ew Impl eme nt 

OBIEE stack Database Presentation Services BI Server Report / Dashboard Logical SQL Physical SQL statement(s) Data set(s) Data set Rendered report Excludes App/Web server & presentation services plug-in Defi ne Meas ure Anal yse Revi ew Impl eme nt 

OBIEE testing options Database Presentation Services BI Server nqcmd SQL Client LSQ L Physical SQL Data set(s) Data set Rendered report LSQL Physical SQL User & Stopwatch Load Testing tool (eg. LoadRunner, OATS) Defi ne Meas ure Anal yse Revi ew Impl eme nt Report / Dashboard 

OBIEE testing options Database BI Server nqcmd Physical SQL Data set(s) LSQL Defi ne Meas ure Anal yse Revi ew Impl eme nt 

nqcmd Defi ne Meas ure Anal yse Revi ew Impl eme nt Usage Tracking or NQQuery.log Test script BI Server Data nqcmd Logical SQL Logical SQL Logical SQL 

Master test script nqcmd Defi ne Meas ure Anal yse Revi ew Impl eme nt Test script BI Server Data nqcmd Logical SQL Test script nqcmd Test script nqcmd Test script nqcmd 

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 Defi ne Meas ure Anal yse Revi ew Impl eme nt 

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! Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Define Measure Analyse Review Implement 

OBIEE measuring & monitoring Database Presentation Services BI Server Presentation Services plug-in App Server Web Server Apache log OAS log Analytics log sawserver.log NQServer.log NQQuery.log systems management Enterprise Manager BI Management Pack Usage Tracking PerfMon (windows only) jConsole etc Presentation services Enterprise Manager ASH, AWR, SQL Monitor Defi ne Meas ure Anal yse Revi ew Impl eme nt Server metrics e.g. : IO, CPU, Memory PerfMon (Windows) Oracle OS Watcher (unix) Enterprise Manager (Oracle) 

Oracle SQL Monitor Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Measure - summary 䡧Lots of different ways to measure 䡧Build measurement into your test plan ▪ Automate where possible ▪ Easier ▪ Less error Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Define Measure Analyse Review Implement 

Analysing the data Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Analysing the data Defi ne Meas ure Anal yse Revi ew Impl eme nt 

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 Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Recording data about the test Defi ne Meas ure Anal yse Revi ew Impl eme nt Dashboard Requests Logical SQL • ORA_HASH(QUERY_TEXT) Physical SQL • SQL IDs Execution plan • Execution plan hash id 

Extending Usage Tracking 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_STATEMENTS qt_ora_hash query_text saw_path dashboard OBIEE_REPLAY_STATS testid testenv qt_ora_hash start_ts response_time row_count db_query_cnt Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Define Measure Analyse Review Implement Analyse Evaluate design / config options Do it right Don’t “fudge it” Iterative approach Timebox! 

Define Measure Analyse Review Implement Review Iterative approach Redefine test Continue testing Implement 

Review Defi ne Meas ure Anal yse Revi ew Impl eme nt 

Define Measure Analyse Review Implement 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! 

Define Measure Analyse Review Implement Performance Testing OBIEE Evaluate design / config options Do it right Don’t “fudge it” Do more testing Iterative approach Be Methodical Redefine test Do more testing [email protected] · http://rnm1978.wordpress.com · http://twitter.com/rnm1978 

#EOF