Performance Testing and OBIEE

Transcript

1. Performance Testing 

   and OBIEE
   Robin Moffatt, WM Morrisons plc http://rnm1978.wordpress.com
   

   View Slide

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
   

   View Slide

3. The aim of this presentation
   䡧A Performance Tuning Methodology
   䡧OBIEE techie stuff
   䡧Learn from my mistakes!
   

   View Slide

4. What is performance testing all about?
   䡧Response times
   ▪ Report
   ▪ ETL batch
   ▪ OLTP transaction
   䡧System impact
   ▪ Resource usage
   ▪ Scalability
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

18. Define
    Measure
    Analyse
    Review
    Implement
    

    View Slide

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)
    

    View Slide

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

    View Slide

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
    

    View Slide

22. Define
    Measure
    Analyse
    Review
    Implement
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

31. Define
    Measure
    Analyse
    Review
    Implement
    Implement
    Iterative
    approach
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

34. #EOF
    

    View Slide