1 / 57

Bart Drewes

Bart Drewes. Product Manager VERITAS Software. Inside the Black Box. Tuning Oracle E-Business Suite Applications. Agenda. Introduction/ERP Overview Success Criteria for ERP Performance Management Some Real World Experiences Starting Point to ERP Tuning Closing Summary.

wendell
Download Presentation

Bart Drewes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bart Drewes Product ManagerVERITAS Software

  2. Inside the Black Box Tuning Oracle E-Business Suite Applications

  3. Agenda • Introduction/ERP Overview • Success Criteria for ERP Performance Management • Some Real World Experiences • Starting Point to ERP Tuning • Closing Summary

  4. Why ERP Applications? • Shorten the time and expense (theoretically!) required to deliver high quality business solutions • Buy (vs. build) functionality whenever and wherever possible • Avoid the expense and risk of building (and supporting) applications from scratch • Allow focus on core business functions • Minimize efforts which dilute core competency • Maintain competitiveness through employment of latest technology

  5. ERP Application Characteristics • Wide-ranging core functionality that can be customized to meet specific customer requirements • Support for multiple platforms to accommodate today’s heterogeneous computing environments • Modular design to enable customers to “mix & match” application components to fit their functional business requirements

  6. A Need for Performance • Because ERP solutions are typically implemented to automate mission-critical core business functions, the requirement for high performance and throughput must be met • E-business modules further increase the exposure of poor performance • ERP-based applications inherently tend to be the most transaction – and throughput – intensive of your entire portfolio

  7. Built-in Performance? • Well… Unfortunately, the requirement of high performance cannot be explicitly designed into the application as a core functional attribute • But why not…? The problem stems from the fact that each customer environment is inherently different – different customizations, usage patterns, data volumes, etc.

  8. Performance Limitations from ERP Design • Wide-ranging, customizable core functionality • Each customer/site will customize uniquely • Support for multiple platforms • Tuning is often platform- and release-specific • Modular design in support of “mix & match” • Modular application components • Modular architectures

  9. Application Storage Database Complex, Multi-tier Architectures Web Server URLHTMLJPEGGIFJSP JSPEJBSERVLETJDBCSQL USER SQLINDEXTABLEDATAFILELVMI/O I/OCHANNELLUNDISK “The More Tiers, the More Tears!”

  10. Common Perception on ERP Tuning • It’s no use • Vendors do not allow • How can you tune black boxes? • Changes cause more problems • No way to measure performance gain • Would never catch up with dynamic business changes • Just follow the vendor’s best practice guidelines

  11. Success Factors - Basic ERP Performance Management • Holistic view of application performance behavior • Ability to measure end-to-end response time, and segment into application tiers • Continuous performance monitoring with little overhead • Visibility to application structure • Correlation of information across application tiers • Ability to track true user activities across tiers • Ability to track application module performance across tiers • In-context drill down for root cause analysis • Quick identification of performance bottleneck

  12. Success Factors - Advanced ERP Performance Management • Capture of historical performance data to enable: • Analysis of problems in the past • Baseline behavior for exception analysis • Trend analysis • Capacity planning • Building of Application knowledge base • Ability to do proactive performance management • Simulation for performance impact from changes • Understanding of application usage patterns in relation to business requirement changes

  13. INFORM INSIGHT Where is the problem? Alerting Do I or will I have a performance problem? PerformanceWarehouse Reporting INDEPTH How is performance tracking over time? How do I fix the problem? VERITAS i3 Client Network Web Servers App Servers DB Servers Storage

  14. Oracle Financials 11i running on a 4-tier architecture Two Form Servers balanced by the Forms Metric Server Eight worldwide offices are connected to the system Case Study The System Oracle Database Server Web Clients Internet Web Server Form Servers

  15. StartPoint shows the system architecture. It provides us with a dashboard view of the system’s health Each sphere represents one tier of the architecture. The atmosphere represents the alerts that relate to the tier We detect a performance alert in the Oracle Application server 16

  16. ‘Top Activity’ alert is part of the ‘Performance’ alert metric set. It indicates that an OA activity response time has breached the performance threshold

  17. We were able to drill down ‘in context’ to find the problematic Form that breached the response time. We found the Transaction form This form exceeded the response time that we set for it by 15% of its normal response time

  18. We are looking at the Oracle Applications Savvy in the Oracle Apps tier We see most of the processing time is in the Oracle Database tier We have the database users that called the Transaction form

  19. We are in the Oracle Workspace This displays the Oracle Activity associated with the Transaction Form SQL Statements associated with Transaction Form. One SQL statement has high response time

  20. We selected the most time consuming SQL statement and looked at it using the Over Time view It has increased dramatically. Let us drill down in context on this statement

  21. We have drilled down and are able to see the Form, the Users, the longest running statements We see this statement is the eight longest running statement over the long term

  22. The response time of this SQL statement has increased only recently

  23. Let us look at the most recent Execution Plan

  24. We uncover that the table RA_CUSTOMER_TRX_ALL has recently grown significantly. This table is accessed by the long running query

  25. The Over Time graph verifies this

  26. These are the columns in the Index

  27. The number of Distinct values have not changed although the table size has significantly increased

  28. Find the SQL statement that inserted values in the RA_CUSTOMER_TRX_ALL table

  29. Look at the behavior of the Insert statement over time. This statement consumed major I/O resources on the 31st August

  30. To find what activated the Insert statement we associate it to the Forms. It show us that it is the RAXTRX.exe (Autoinvoice Import Program)

  31. To improve the situation we use the SmarTune feature and receive index recommendations

  32. Simulate Changes check the affect of adding the index, both positive or negative for all statements

  33. Verify the changes by looking at the response time statistics of the original problematic statement

  34. The Over Time graph verifies that the response time has returned to the normal value after the index implementation

  35. The Over Time graph verifies that the response time has returned to the normal value after the index implementation

  36. Alerts are gone

  37. Case Study • Detect • Identify an upsurge in form response time • Find • Problem is associated with slowed down performance of the database • Focus • An SQL statement has deteriorated in performance due to increases in table size • Improve • A suitable index was introduced • Verify • The index has improved SQL time, and consequently the performance of the problematic form

  38. Starting Point To ERP Tuning • Majority of performance challenges end up in database tier • Start measuring and collecting performance indicators ASAP • Set reasonable targets based on measurable performance indicators • Identify top 10 tuning targets (by user, by business transaction, or other application entity)

  39. Large Return Recognizable Through Database Tuning - Oracle as Example • Object Design and Maintenance • Query Optimization • Object statistics • Indexing • Environmental/Configuration Considerations • Lifecycle Issues

  40. Object Design and Maintenance — DOs • Review base table and index growth - monitor extents and fragmentation - frequently; preferably weekly • If possible, anticipate (and plan for) occasional rebuilds of your database (after lots of testing) • Consider a larger database block size - 8K is a good nominal value for large sites/implementations • Make sure your busy tables and indexes are load-balanced for optimal I/O (try to isolate large transaction-intensive objects in their own tablespaces) • Try to educate your users on the importance of workload management - distribute log files and teach them how to interpret them

  41. Object Design and Maintenance — DOs (II) • Pay special attention to temporary and interface tables - monitor their size very closely, and truncate them as often as possible to reclaim space and reset the high water mark in the header block • This can have a dramatic effect on full table scans! • Monitor your index usage patterns (more on this later)

  42. Object Design and Maintenance — DON’Ts • Never accept the default storage parameters • Diligent review should be standard policy • Don’t expect “linear” growth • Data volume • Transaction volume • Server capacity • Users’ expertise • User demand on performance

  43. Query Optimization • Cost-based optimization is prevalent • Tables must be ANALYZEd periodically to maintain accurate data distribution statistics • Check standard ANALYZE utility scripts packaged with ERP administration function to ensure sufficient sample size for ESTIMATE option • Histogram for certain tables may be critical for success • Track explain plan change over time from object statistics changes

  44. Indexing – Most Power Tuning Key • Heavy indexing is a common design feature – • Result from implicitly “generic” design approach • Monitor usage • Evaluate search efficiency • Informed decision to drop indexes • Informed decision to add appropriate indexes • Can’t change SQL text from standard ERP modules -- but can influence optimizer with proper index design

  45. Indexing (II) • All “standard” tuning practices with regard to indexes still apply in ERP environment: • Try to enhance clustering whenever and wherever possible (via reloads or CLUSTER creation) • Re-organize indexes as frequently as is feasible • Capitalize on larger block size (as previously mentioned) to reduce b-tree depth • Physically separate indexes from base table - separate spindles, not just LVs • Keep your statistics as current as possible to enhance the effectiveness of CBO hints

More Related