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

(Still) No Silver Bullets : OBIEE 12c Performance in the Real World

Robin Moffatt
February 02, 2017

(Still) No Silver Bullets : OBIEE 12c Performance in the Real World

Are you involved in the design and development of OBIEE systems and want to know the best way to go about ensuring good performance? Maybe you've an existing OBIEE system with performance "challenges" that you need to diagnose?
This presentation looks at the practical elements of diagnosing the causes of performance issues in OBIEE, and discusses good practices to observe when developing new systems. It includes discussion of OBIEE 12c and with additional emphasis on analysis of Usage Tracking data for the accurate profiling and diagnosis of issues.

Robin Moffatt

February 02, 2017
Tweet

More Decks by Robin Moffatt

Other Decks in Technology

Transcript

  1. [email protected] www.rittmanmead.com @rittmanmead 1
    (Still) No Silver Bullets :

    OBIEE 12c Performance in the Real World
    Robin Moffatt, Rittman Mead
    BIWA Summit 2017
    speakerdeck.com/rmoff/

    View full-size slide

  2. [email protected] www.rittmanmead.com @rittmanmead
    Robin Moffatt
    2
    • Head of R&D, Rittman Mead

    • Previously OBIEE/DW developer at large UK
    retailer
    • Previously SQL Server DBA, Business Objects, 

    DB2, COBOL….
    • Oracle ACE

    • Frequent blogger : http://ritt.md/rmoff and
    http://rmoff.net

    • Twitter: @rmoff

    • IRC: rmoff / #obihackers / freenode

    View full-size slide

  3. [email protected] www.rittmanmead.com @rittmanmead
    Rittman Mead
    3
    • Oracle Gold Partner with offices in the
    UK and USA

    • 70+ staff delivering Oracle BI, DW, Big
    Data and Advanced Analytics projects

    • Significant web presence with the
    Rittman Mead Blog (http://
    www.rittmanmead.com)

    • Hadoop R&D lab for “dogfooding”
    solutions developed for customers

    View full-size slide

  4. [email protected] www.rittmanmead.com @rittmanmead
    OBIEE Performance in the Real World
    4
    • Do It Right, First Time

    - What Makes A Performant OBIEE
    System?
    • If It’s Not Done Right, Know How To
    Figure Out What Is Broke

    - Practical elements of diagnosing the
    causes of performance issues
    - Methodical analysis - “nose to tail”

    View full-size slide

  5. [email protected] www.rittmanmead.com @rittmanmead
    The Requirement
    5

    View full-size slide

  6. [email protected] www.rittmanmead.com @rittmanmead
    The Problem
    6
    • Lots of moving parts and complexity
    •Overall solution crosses disciplines and job roles
    ‣ OBIEE developer
    ‣ Database developer
    ‣ DBA
    ‣ Server Admin
    ‣ Network
    ‣ SAN
    ‣ LDAP
    ‣ etc

    View full-size slide

  7. [email protected] www.rittmanmead.com @rittmanmead
    The Additional Problem
    7

    View full-size slide

  8. [email protected] www.rittmanmead.com @rittmanmead
    Performance Beyond the “Best Practices”
    8
    - Tear down the reliance on “Best
    Practice”, but with a viable,
    better, alternative instead.

    View full-size slide

  9. [email protected] www.rittmanmead.com @rittmanmead
    The Only Answer
    9
    EVIDENCE-BASED 

    DESIGN AND DIAGNOSTICS

    View full-size slide

  10. [email protected] www.rittmanmead.com @rittmanmead
    Time Profile
    10
    • To understand why is it slow, we first must understand
    where is it slow

    • Approach championed by Cary Millsap / Method-R

    - “Thinking Clearly About Performance” (2010)
    Performance improvement is proportional to how much a
    program uses the thing you improved.
    — Amdahl’s Law

    View full-size slide

  11. [email protected] www.rittmanmead.com @rittmanmead
    The OBIEE stack
    Request
    User
    Response
    BI Server
    BI Server
    Presentation
    Services
    Managed
    Server
    Managed
    Server
    JavaHost
    Presentation
    Services
    Web Browser
    Web Browser
    BI Plug-in
    BI Plug-in
    DWH
    [ ... ]
    [ ... ]
    DWH
    [ ... ]
    [ ... ]
    WebLogic Server OBIEE system components Data Source(s)
    Network
    Network

    View full-size slide

  12. [email protected] www.rittmanmead.com @rittmanmead
    Time Profile in Action
    12
    • End-user reports a response time of 40 seconds.

    • How can we make it faster? 

    ‣ Where did the time get spent?
    Response
    BI Server
    Managed Server
    Presentation
    Services
    Web Browser BI Plug-in
    User WebLogic Server OBIEE system components
    DB Query 1
    25 seconds
    DB Query 2
    5 seconds
    Data
    Processing
    10 seconds
    Page Generation
    5 seconds
    DWH
    Data Source(s)
    DWH
    Time, seconds
    Component

    View full-size slide

  13. [email protected] www.rittmanmead.com @rittmanmead
    Time Profile in Action
    13
    Step Action
    Response
    Time (s)
    %
    1 Physical SQL 1 execute on DB 25.00 62.5
    2 Physical SQL 2 execute on DB [5.00] —
    3 BI Server does work on DB results 10.00 25.0
    4 Presentation Services generates page 5.00 12.5
    Total 40.00 100.0
    DB Query 1
    DB Query 2
    Data Processing
    Page Generation
    Time profile shows clearly :
    1.Improve performance of Query 1
    2.Push work into single query

    View full-size slide

  14. [email protected] www.rittmanmead.com @rittmanmead
    Deep Dive into BI Server Time from obis1-query.log
    14
    Elapsed
    Response
    Physical
    Physical Query x
    Physical Query 1 BI Server
    processing
    (federation,
    calculation,
    aggregation)
    Send to client, wait for
    acknowledgement
    Eg. Presentation Services
    generating pivot tables, table
    scrolling/paging, etc.
    Compilation
    DB Connect
    Logical Query Summary
    Stats: [...] Compilation
    time c (seconds)
    Physical query response
    time p1 (seconds)
    Physical query response
    time px (seconds)
    Logical Query Summary Stats: Elapsed time e
    Logical Query Summary Stats: [...] Response time r
    Physical Query Summary Stats:
    [...] DB-connect time d (seconds) r - d - c - max(p) e - r

    View full-size slide

  15. [email protected] www.rittmanmead.com @rittmanmead
    Request
    Response
    Iterative Approach
    15
    • Be sure to account for all time, end-to-end (nose to tail)

    • Expand time profile to focus on where the time is going in
    particular

    • In reality, time profile based on BI Server alone will help in a
    lot of cases
    BI Server
    Managed Server
    Presentation
    Services
    Web Browser BI Plug-in
    User WebLogic Server OBIEE system components
    DWH
    Data Source(s)
    DWH
    Network
    Network
    obis1-query.log
    sawlog.log EM Active Reports, V$SQL, etc
    Browser tools WLS logs

    View full-size slide

  16. [email protected] www.rittmanmead.com @rittmanmead
    Digging Deeper with DMS Metrics
    16
    • OBIEE supports Fusion Middleware’s Dynamic Monitoring
    System (DMS)

    • Once we have found WHERE the time has gone, DMS
    metrics are one way to help us find out WHY

    • Hundreds of low-level metrics, ranging from the obvious 

    (BI Server Total Sessions) 

    to the less obvious
    (Peak_DXEParameterizedImpl_Count)

    View full-size slide

  17. [email protected] www.rittmanmead.com @rittmanmead
    Accessing DMS Metrics
    17
    •DMS Spy

    •WLST

    •EM FMC

    •EM12c

    •obi-metrics-agent
    http://ritt.md/oma-intro

    View full-size slide

  18. [email protected] www.rittmanmead.com @rittmanmead
    Full Stack Performance Analysis for OBIEE
    18

    View full-size slide

  19. [email protected] www.rittmanmead.com @rittmanmead
    Tracing OBIEE Performance into the Oracle Database
    19
    •OBIEE sets the ACTION for queries sent to Oracle DB

    •Stores this as PHYSICAL_HASH_ID in Usage Tracking

    •Use Usage Tracking to correlate recent database activity
    with OBIEE Dashboards and Users

    •Generate long-term OBIEE performance analysis against
    AWR reports

    •Highlight “heavy” reports that use lots of I/O or CPU
    http://ritt.md/obi-cp

    View full-size slide

  20. [email protected] www.rittmanmead.com @rittmanmead 20

    View full-size slide

  21. [email protected] www.rittmanmead.com @rittmanmead 21

    View full-size slide

  22. [email protected] www.rittmanmead.com @rittmanmead
    Response time
    Number of concurrent users
    22
    •Fix Performance problems at root cause; adding capacity alone is generally not sensible
    •Improve the performance of an underlying Capacity problem and you might offset the need to add
    any additional capacity at all.
    Bad
    Good
    Is your problem Performance or Capacity?
    •Performance: Response time is slow for one user
    •Capacity: Response time degrades as user concurrency increases

    View full-size slide

  23. [email protected] www.rittmanmead.com @rittmanmead
    Performance Diagnostics Approach
    23
    • Performance

    ‣ Build a time profile
    ‣ Don’t know where to start? Try Usage Tracking or obis1-query.log
    - Long running queries
    - Logical queries returning lots of data to the client
    - Logical queries returning lots of data from the DB but small % to the client
    - Logical queries requiring lots of physical queries
    • Capacity

    ‣ Examine capacity metrics 

    (OS, DMS) over time
    ‣ Correlate with reported problems
    Queuing DMS metrics:
    Oracle BI DB Connection Pool/* -> Current Queued Requests
    Oracle BI PS Chart Engine -> Current Charts Queued
    Oracle BI PS Query Cache -> Current Queued Queries
    Oracle BI PS Thread Pools/* -> Current Jobs Queued
    Oracle BI Thread Pool/* -> Current Queued Requests

    View full-size slide

  24. [email protected] www.rittmanmead.com @rittmanmead
    So What Does “Good” Look Like?
    24

    View full-size slide

  25. [email protected] www.rittmanmead.com @rittmanmead
    This Is Not “Best Practice”
    25
    •There is no one right way : It Depends
    The only “best practice” you should be
    using all the time is “Use Your Brain”.
    — Steven Robbins / Tom Kyte

    View full-size slide

  26. [email protected] www.rittmanmead.com @rittmanmead 26
    ★Don’t Do It
    ★Do It Only Once
    ★Do It Less Often
    ★Do It More Efficiently
    (h/t Greg Rahn)
    The Bucket List of Performance Gains

    View full-size slide

  27. [email protected] www.rittmanmead.com @rittmanmead
    ‘Database Pushdown’ (Do It More Efficiently)
    27
    •Reduce the amount of work/data handled further up the
    stack

    • Federation across sources can be a challenge

    - c.f. BI Server caching (http://ritt.md/bi-cache)
    BI Server
    Managed Server
    Presentation Services
    Web Browser
    BI Plug-in
    DWH
    [ ... ]
    User WebLogic Server OBIEE system components Data Source(s)
    Network
    Network

    View full-size slide

  28. [email protected] www.rittmanmead.com @rittmanmead
    Data Mashups in OBIEE 12c (Do It More Efficiently)
    28
    •External Subject Areas (XSA) introduced in OBIEE 12c

    •By default data is stored in flat file on disk

    •Use the database-backed XSA Cache for performance

    • More information:

    • http://ritt.md/obiee12c-xsa-dss
    • Doc ID 2087801.1
    BI Server
    BI Presentation
    Server
    Metadata
    (RPD)
    Metadata
    Datasets
    Managed

    Server
    Data Set Service
    Rest
    API
    XLSX files
    etc
    RDBMS
    RDBMS

    View full-size slide

  29. [email protected] www.rittmanmead.com @rittmanmead
    Export to Excel?
    29

    View full-size slide

  30. [email protected] www.rittmanmead.com @rittmanmead 30

    View full-size slide

  31. [email protected] www.rittmanmead.com @rittmanmead
    Feeding the Excel Monster (Don’t Do It/Do It More Efficiently)
    31
    • What’s being done with the data once it’s in Excel?

    - Could it be done in OBIEE instead?
    • Alternatives to Export from Analysis/Dashboard:

    ‣ Oracle have specific recommendations (DocID 1558070.1 p.13)
    - Favour CSV export over Excel
    - Favour BI Publisher export over OBIEE Analysis Export
    - Use Logical SQL against BI Server’s 

    ODBC/JDBC interface directly
    - Dump direct from the database

    View full-size slide

  32. [email protected] www.rittmanmead.com @rittmanmead
    Don’t Do It / Do It Less Often
    32
    • “Filter Early”

    ‣ Table Prompt != Filter

    • Report by Exception

    • Make sure Dashboards have 

    default/mandatory prompts

    (http://ritt.md/obi-prompts)

    • Be smart about Dashboard and Analysis design - don’t cram everything into one page

    - Less clutter : Better user experience & Better performance
    Yes, please
    do….

    View full-size slide

  33. [email protected] www.rittmanmead.com @rittmanmead
    Not forgetting …
    33
    • Do It More Efficiently

    - Aggregation

    • Do It Less Often

    - BI Server caching (http://ritt.md/bi-cache)
    •Database optimisation (partitioning, indexing, parallelism, statistics, etc)

    •Balanced Hardware configuration

    View full-size slide

  34. [email protected] www.rittmanmead.com @rittmanmead
    Rittman Mead OBIEE Performance Analytics Service
    34
    •Understand your existing situation
    - Performance Analytics Report
    •Fix and monitor performance problems

    - Performance Analytics Dashboards
    •Learn Optimal Design and Performance Troubleshooting
    - Training from the OBIEE Performance Experts

    View full-size slide

  35. [email protected] www.rittmanmead.com @rittmanmead
    Performance Analytics Report
    35
    •Empirical performance
    assessment based on Usage
    Tracking data

    •Quantify overall performance
    profile of OBIEE

    •Identify key optimisation
    candidates and efficiency
    opportunities

    View full-size slide

  36. [email protected] www.rittmanmead.com @rittmanmead
    Performance Analytics Dashboards
    36
    •Monitor and Troubleshoot
    performance problems

    •Interactive dashboards for
    rapid analysis

    •Holistic view of OBIEE in one
    place
    - Response Times
    - Cache usage
    - Temporary file usage
    - Database metrics
    (ASH)
    - OS metrics

    View full-size slide

  37. [email protected] www.rittmanmead.com @rittmanmead 37
    email

    [email protected]
    web

    http://ritt.md/rmoff
    http://rmoff.net
    twitter

    @rmoff
    irc

    rmoff @ #obihackers
    #EOF
    http://ritt.md/pa
    http://ritt.md/obi-performance

    http://speakerdeck.com/rmoff

    View full-size slide

  38. [email protected] www.rittmanmead.com @rittmanmead
    References & Further Reading
    38
    •Cary Millsap - “Thinking Clearly About
    Performance”

    ‣ http://queue.acm.org/detail.cfm?id=1854041

    •OBIEE Performance Analytics: Analysing the
    Impact of Suboptimal Report Design

    ‣ https://community.oracle.com/docs/DOC-993649

    •All You Ever Wanted to Know About OBIEE
    Performance…but were too afraid to ask

    ‣ http://ritt.md/obi-performance

    •obi-metrics-agent

    ‣ http://ritt.md/oma-intro

    •Greg Rahn - “The Core Performance
    Fundamentals Of Oracle Data Warehousing –
    Balanced Hardware Configuration”

    ‣ http://wp.me/p3cJT-by

    •Oracle documentation - “Oracle® Database 2
    Day + Data Warehousing Guide - Balanced
    Hardware Configuration”

    ‣ E25555-03

    View full-size slide