1 / 20

BW Know-How Call : Performance Tuning

BW Know-How Call : Performance Tuning. dial-in phone numbers! U.S. Toll-free: (877) 260-8900 International: (612) 338-1917 Passcode: “BW”. OLTP Systems Application Development and performance tuning separated Performance tuning by basis experts . Performance Tuning. BW

gerald
Download Presentation

BW Know-How Call : Performance Tuning

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. BW Know-How Call : Performance Tuning dial-in phone numbers! U.S. Toll-free: (877) 260-8900 International: (612) 338-1917 Passcode: “BW”

  2. OLTP Systems • Application Development and performance tuning separated • Performance tuning by basis experts Performance Tuning • BW • Performance Tuning as holistic process over application design and database configuration Database Database Performance Tuning Performance Tuning in BW OLTP • Performance Tuning Application BW Application

  3. Contents Checklists OLAP Performance Layers Further Information

  4. Performance Guidelines Checklists OLAP Performance Layers Further Information

  5. Checklist – Query and Web Performance – Overview ST03 / Technical Content Which component contributes most? OLAP Database Frontend • Data Model • Query Definition • Aggregates • OLAP Cache • Pre-Calculated Web Templates • Compressing • Indices • DB Statistics • DB and basis (Buffer) Parameter • Network • WAN and BEx • Client Hardware • VBA / Java • Documents • Formatting • ODBO / 3rd party • Data Model • Query Definition (including OLAP features) • Aggregates • OLAP Cache • Virtual Key Figures / Characteristics • Authorizations Check these points SQL Trace (ST05) RSRV RSRT, RSRTRACE RSRT, RSRTRACE SQL Trace (ST05) ABAP Trace (SE30) IEMON RSRT, RSRTRACE Tools

  6. Checklist – Data Load Performance – Overview 1 – Technical Content, Data Load Monitor Which component contributes most? Extraction Transfer Load Into PSA • Customer Exits • Resource Utilization • Load Balancing • Data Package Size • Indices on tables • Flat File format • Content vs. generic extractor • I/O Contention • PSA Partition Size • Resource Contraint • CPU / Memory Bottleneck • Network • Application Buffer Synchronization Check these points Extractor Checker (RSA3), ABAP Trace (SE30), SQL Trace (ST05) SM50 SQL Trace (ST05) OS Monitor (ST06) Tools OS Monitor (ST06) DB Monitor (ST04)

  7. Checklist – Data Load Performance – Overview 2 – Technical Content, Data Load Monitor Which component contributes most? Transfer Rules Load Into Data Targets Update Rules ODS Objects Master Data InfoCubes • Buffering Number Ranges • Change Run • Roll-up • Change Run • Compression • Indices • Load Master Data before Transaction Data • Buffering Number Ranges • Transformation Rules / ABAP Coding • Transformation Library Formulas • Parallel ODS activation • Unique Data Records • Flag BEx Reporting • Indices Check these points Debugger within Monitor ABAP Trace (SE30), SQL Trace (ST05) Tools SQL Trace (ST05)

  8. Performance Guidelines Checklists OLAP Performance Layers Further Information

  9. OLAP Performance Layers • Performance Layers • For specific Scenarios Reuse Performance Offline Analysis Pre-Calculation OLAP Cache Aggregates InfoCubes

  10. Aggregates – 1 – • Aggregate Definition • Materialization of aggregated subsets of InfoCube fact table data • Independent structures where summary data is stored within separate, transparent InfoCubes • Transparency: Users do not notice if aggregate is hit or not • Improved query performance by reducing the amount of data to be read from DB • Aggregates can be created • Only on top of basic InfoCubes • For dimension characteristics • For navigational attributes • On hierarchy levels • Using time-dependent navigational attributes (as of BW 3.x) • Using hierarchy levels where the structure is time-dependent (as of BW 3.x) • Aggregates can improve query performance considerably, but keep in mind that they also impact the load performance.

  11. Aggregate Flow with aggregate Aggregates – 2 – Example • Example for flow with and without aggregates Flow without aggregate Selection Database OLAP Engine InfoCube Number of records read on the database Records transferred to BW instance after being summarized on the database

  12. OLAP Cache – 1 – • Caching in BW 2.x • One Local Cache for each session • No cache access across sessions • No possibility to set size of cache • Caching in BW 3.x  OLAP Cache • Additional global cache which is accessible from all sessions • OLAP Cache is part of application buffer (Imp/Exp SHM) and can also be stored in a DB table or a file • The Cache stores query results and navigation statuses as highly compressed cluster data • Query Cache is used for equal queries or subsets of cached queries • Benefits: OLAP Cache reduces workload on database and application server

  13. Buffer small by default! OLAP Cache – 2 – • OLAP Cache • OLAP Cache size can be adjusted • (In 3.x there is also an adjustable size for the local cache) • Cache can be switched off entirely, for InfoCubes and for individual queries • Local Cache is used, when OLAP Cache is inactive. • Specific instance profile parameters: rsdb/esm/buffersize_kb 4096 (kB) Size of exp/imp SHM buffer rsdb/esm/max_objects 2000 Max. number of objects in the buffer rsdb/esm/large_object_size 8192 (byte)Estimation for the size of the largest object rsdb/esm/mutex_n 0 Number of mutexes in Exp/Imp SHM buffer

  14. Pre-Calculated Web Templates – 1 – • Pre-Calculated Web Templates • Pre-calculation is a set of techniques where you can distribute the workload of running the report to off-peak hours, and have the report result set ready for very fast access to the data • Data Pre-Calculation or HTML Pre-Calculation • HTML Pre-Calculation can be used for Offline Analysis • Benefits • Fast response time • System workload shifted to off-peak hours • Re-use data that goes to many queries • Reports also available offline

  15. Pre-Calculated Web Templates – 2 – • Scenario • Supports “Newspaper” scenario • Quick access to many reports • Reports are requested by many users • Static reporting, usually little navigation requirements • Active Reporting Authorisations • Restrictions • No invalidation when new data is loaded • Restricted navigation: only filtering possibles

  16. Pre-Calculated Web Templates – 3 – Access Modes SAP GUI Web Browser DATA MODE http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=new NEW STORED HYBRID STATIC STATIC_HYBRID BEx Web Service Doc Web Service WebDAV Handler BW Server Reporting Agent Web Framework Content Management Framework OLAP Engine Log Web Application Server InfoProvider RA Data Storage CMF Repository

  17. Pre-Calculated Web Templates – 4 – Access Modes SAP GUI Web Browser DATA MODE http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=stored NEW STORED HYBRID STATIC STATIC_HYBRID BEx Web Service Doc Web Service WebDAV Handler BW Server Reporting Agent Web Framework Content Management Framework OLAP Engine Log Web Application Server InfoProvider RA Data Storage CMF Repository

  18. Pre-Calculated Web Templates – 5 – Access Modes SAP GUI Web Browser DATA MODE http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=static http://myserver:myport/sap/bw/doc/tmpl/fldtmpl/“cmfpath/report.htm“ NEW STORED HYBRID STATIC STATIC_HYBRID BEx Web Service Doc Web Service WebDAV Handler BW Server Reporting Agent Web Framework Content Management Framework OLAP Engine Log Web Application Server InfoProvider RA Data Storage CMF Repository

  19. Performance Guidelines Checklists OLAP Performance Layers Further Information

  20. Further Information • Find Additional Information • BW Performance Page in SAP Service Marketplace • Alias BW  Performance • SEM-BPS Performance Page in SAP Service Marketplace • Alias SEM  Media Library  Help To Use SEM  SEM-BPS Performance • KHNC on SEM Performance in 2002

More Related