Performance Testing and OBIEE Robin Moffatt WM Morrisons
Performance Testing and OBIEE Robin Moffatt, WM Morrisons plc http: //rnm 1978. wordpress. com
Introduction �Oracle BI specialist at Morrisons plc �Big IT development programme at its early stages implementing OBIEE, OBIA, ORDM, all on Oracle 11 g & 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
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 & build your test Measure Analyse Review Implement �Define – what are you going to test • Aim of the test • E. g. : • Check that the system performs • Scope • Baseline performance • Prove system capacity • Assumptions • Validate system design • Specifics • Data, environment, etc �Build – how are you going to test it OBIEE specific
Define Consider your test scope Measure Analyse Review Implement More Fewer components = more = easier complex to manage = more=variables more precise = larger = more margin efficient of error
Define Measure OBIEE stack Analyse Review Implement Report / Dashboard Rendered 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 Implement Load Testing tool d re de n Re Re (eg. Load. Runner, po rt OATS) User & /D Stopwatch as hb oa rd rt po re Presentation Services LS Q Da ta se L t nqcmd LSQL Client Physical SQL BI Server Da ta se t(s ) Physical SQL Database
Define Measure OBIEE testing options Analyse Review Implement nqcmd LSQL Physical SQL BI Server Da ta se t(s ) Database
Define Measure nqcmd Analyse Review Implement Usage Tracking or NQQuery. log Test script Logical SQL nqcmd BI Server Data
Define Measure nqcmd Analyse Review Test script nqcmd Test script Logical SQL Master test script Implement nqcmd BI Server Data
Define Load. Runner 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 Fiddler 2 Fire. Bug � Reference: My Oracle Support – Doc ID 496417. 1 http: //rnm 1978. wordpress. com/category/loadrunner Measure Analyse Review Implement
Define Measure Defining your test - summary Analyse Review Implement �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!
Define Measure Analyse Review Implement
Define Measure OBIEE measuring & monitoring Analyse Review Implement 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 Perf. Mon (windows only) NQServer. log Usage Tracking NQQuery. log Perf. Mon (Windows) Oracle OS Watcher (unix) Enterprise Manager ASH, AWR, SQL Monitor BI Server systems management Database j. Console etc Enterprise Manager BI Management Pack Presentation services
Define Measure Oracle SQL Monitor Analyse Review Implement
Define Measure - summary Analyse Review Implement �Lots of different ways to measure �Build measurement into your test plan Automate where possible ▪ Easier ▪ Less error
Define Measure Analyse Review Implement
Define Measure Analysing the data Analyse Review Implement
Define Measure Analysing the data Analyse Review Implement
Define Measure Analysing the data Analyse Review Implement 1 Average (mean) 1 3. 4 Response time 91 32 2 102 23 13 29 3 10 50 th percentile (Median) 2 90 th percentile 9. 1
Define Measure Recording data about the test Analyse Review Implement Dashboard Requests Logical SQL • ORA_HASH(QUERY_TEXT) Physical SQL • SQL IDs Execution plan • Execution plan hash id
Define Measure Extending Usage Tracking Analyse Review Implement S_NQ_ACCT START_TS ROW_COUNT TOTAL_TIME_SEC NUM_DB_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
Analyse Iterative approach Define Do it right Measure Evaluate design / config options Analyse Timebox! Review Implement Don’t “fudge it”
Review Iterative approach Define Measure Continue testing Redefine test Analyse Review Implement
Define Measure Review Analyse Review Implement
Implement Define Measure Analyse 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 Define Do it right Don’t “fudge it” Measure Evaluate design / config options Redefine test Be Methodical robin@moffatt. me · Analyse Do more testing Review Do more testing Implement http: //rnm 1978. wordpress. com · http: //twitter. com/rnm 1978
#EOF
- Slides: 35