Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Performance Testing and OBIEE

Performance Testing and OBIEE

Robin Moffatt

May 20, 2010
Tweet

More Decks by Robin Moffatt

Other Decks in Technology

Transcript

  1. Performance Testing 
 and OBIEE Robin Moffatt, WM Morrisons plc

    http://rnm1978.wordpress.com
  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?
 (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
  6. 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
  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.” — Cary Millsap - Thinking Clearly About Performance
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. OBIEE testing options Database BI Server nqcmd Physical SQL Data

    set(s) LSQL Defi ne Meas ure Anal yse Revi ew Impl eme nt
  14. 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
  15. 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
  16. 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
  17. 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
  18. Define Measure Analyse Review Implement

  19. 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)
  20. Oracle SQL Monitor Defi ne Meas ure Anal yse Revi

    ew Impl eme nt
  21. 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
  22. Define Measure Analyse Review Implement

  23. Analysing the data Defi ne Meas ure Anal yse Revi

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

    ew Impl eme nt
  25. 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
  26. 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
  27. 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
  28. Define Measure Analyse Review Implement Analyse Evaluate design / config

    options Do it right Don’t “fudge it” Iterative approach Timebox!
  29. Define Measure Analyse Review Implement Review Iterative approach Redefine test

    Continue testing Implement
  30. Review Defi ne Meas ure Anal yse Revi ew Impl

    eme nt
  31. Define Measure Analyse Review Implement Implement Iterative approach

  32. 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!
  33. 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 robin@moffatt.me · http://rnm1978.wordpress.com · http://twitter.com/rnm1978
  34. #EOF