Performance Testing and OBIEE

Performance Testing and OBIEE

2bded62396ea66c84bd10e91c718dea9?s=128

Robin Moffatt

May 20, 2010
Tweet

Transcript

  1. 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
  2. 4.

    What is performance testing all about? 䡧Response times ▪ Report

    ▪ ETL batch ▪ OLTP transaction 䡧System impact ▪ Resource usage ▪ Scalability
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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)
  17. 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
  18. 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
  19. 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
  20. 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
  21. 28.

    Define Measure Analyse Review Implement Analyse Evaluate design / config

    options Do it right Don’t “fudge it” Iterative approach Timebox!
  22. 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!
  23. 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
  24. 34.