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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

  18. Define
    Measure
    Analyse
    Review
    Implement

    View full-size 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 full-size slide

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

    View full-size 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 full-size slide

  22. Define
    Measure
    Analyse
    Review
    Implement

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  31. Define
    Measure
    Analyse
    Review
    Implement
    Implement
    Iterative
    approach

    View full-size 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 full-size 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
    [email protected] · http://rnm1978.wordpress.com · http://twitter.com/rnm1978

    View full-size slide