(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.

Transcript

1. info@rittmanmead.com 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/
2. info@rittmanmead.com 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
3. info@rittmanmead.com 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
4. info@rittmanmead.com 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”
5. info@rittmanmead.com www.rittmanmead.com @rittmanmead The Requirement 5
6. info@rittmanmead.com 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
7. info@rittmanmead.com www.rittmanmead.com @rittmanmead The Additional Problem 7
8. info@rittmanmead.com www.rittmanmead.com @rittmanmead Performance Beyond the “Best Practices” 8 - Tear down the reliance on “Best Practice”, but with a viable, better, alternative instead.
9. info@rittmanmead.com www.rittmanmead.com @rittmanmead The Only Answer 9 EVIDENCE-BASED 
 DESIGN AND DIAGNOSTICS
10. info@rittmanmead.com 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
11. info@rittmanmead.com 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
12. info@rittmanmead.com 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
13. info@rittmanmead.com 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
14. info@rittmanmead.com 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
15. info@rittmanmead.com 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
16. info@rittmanmead.com 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)
17. info@rittmanmead.com www.rittmanmead.com @rittmanmead Accessing DMS Metrics 17 •DMS Spy •WLST •EM FMC •EM12c •obi-metrics-agent http://ritt.md/oma-intro
18. info@rittmanmead.com www.rittmanmead.com @rittmanmead Full Stack Performance Analysis for OBIEE 18
19. info@rittmanmead.com 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
20. info@rittmanmead.com www.rittmanmead.com @rittmanmead 20
21. info@rittmanmead.com www.rittmanmead.com @rittmanmead 21
22. info@rittmanmead.com 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
23. info@rittmanmead.com 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
24. info@rittmanmead.com www.rittmanmead.com @rittmanmead So What Does “Good” Look Like? 24
25. info@rittmanmead.com 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
26. info@rittmanmead.com 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
27. info@rittmanmead.com 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
28. info@rittmanmead.com 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
29. info@rittmanmead.com www.rittmanmead.com @rittmanmead Export to Excel? 29
30. info@rittmanmead.com www.rittmanmead.com @rittmanmead 30
31. info@rittmanmead.com 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
32. info@rittmanmead.com 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….
33. info@rittmanmead.com 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
34. info@rittmanmead.com 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
35. info@rittmanmead.com 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
36. info@rittmanmead.com 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
37. info@rittmanmead.com www.rittmanmead.com @rittmanmead 37 email
 robin@rittmanmead.com 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
38. info@rittmanmead.com 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