Performance Testing and OBIEE Robin Moffatt WM Morrisons

  • Slides: 35
Download presentation
Performance Testing and OBIEE Robin Moffatt, WM Morrisons plc http: //rnm 1978. wordpress. com

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

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

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

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

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

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

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

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 /

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

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

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

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

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

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

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

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 –

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

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 Analyse Review Implement

Define Measure OBIEE measuring & monitoring Analyse Review Implement Enterprise Manager (Oracle) Apache log

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 Oracle SQL Monitor Analyse Review Implement

Define Measure - summary Analyse Review Implement �Lots of different ways to measure �Build

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 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

Define Measure Analysing the data Analyse Review Implement

Define Measure Analysing the data Analyse Review Implement 1 Average (mean) 1 3. 4

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

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

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

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

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

Define Measure Review Analyse Review Implement

Define Measure Review Analyse Review Implement

Implement Define Measure Analyse Review Implement Iterative approach

Implement Define Measure Analyse Review Implement Iterative approach

Lessons Learnt � You won’t get your testing right first time There’s no shame

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

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

#EOF

#EOF