Bart Drewes

1
(No Transcript)
2
Bart Drewes

• Product ManagerVERITAS Software

3
Inside the Black Box

• Tuning Oracle E-Business Suite Applications

4
Agenda

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

5
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

6
ERP Application Characteristics

• Wide-ranging core functionality that can be
  customized to meet specific customer
  requirements
• Support for multiple platforms to accommodate
  todays heterogeneous computing environments
• Modular design to enable customers to mix
  match application components to fit their
  functional business requirements

7
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

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

9
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

10
Complex, Multi-tier Architectures
Web Server
URLHTMLJPEGGIFJSP
JSPEJBSERVLETJDBCSQL
USER
SQLINDEXTABLEDATAFILELVMI/O
I/OCHANNELLUNDISK
The More Tiers, the More
Tears!
11
Common Perception on ERP Tuning

• Its 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 vendors best practice guidelines

12
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

13
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

14
VERITAS i3
Client
Network
Web Servers
App Servers
DB Servers
Storage
15
Case Study
The System

• 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

Oracle Database Server
Web Clients
Internet
Web Server
Form Servers
16
StartPoint shows the system architecture. It
provides us with a dashboard view of the systems
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
17
Top Activity alert is part of the Performance
alert metric set. It indicates that an OA
activity response time has breached the
performance threshold
18
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
19
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
20
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
21
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
22
(No Transcript)
23
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
24
The response time of this SQL statement has
increased only recently
25
Let us look at the most recent Execution Plan
26
We uncover that the table RA_CUSTOMER_TRX_ALL has
recently grown significantly. This table is
accessed by the long running query
27
The Over Time graph verifies this
28
(No Transcript)
29
(No Transcript)
30
These are the columns in the Index
31
The number of Distinct values have not changed
although the table size has significantly
increased
32
Find the SQL statement that inserted values in
the RA_CUSTOMER_TRX_ALL table
33
(No Transcript)
34
Look at the behavior of the Insert statement over
time. This statement consumed major I/O resources
on the 31st August
35
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)
36
To improve the situation we use the SmarTune
feature and receive index recommendations
37
Simulate Changes check the affect of adding the
index, both positive or negative for all
statements
38
Verify the changes by looking at the response
time statistics of the original problematic
statement
39
The Over Time graph verifies that the response
time has returned to the normal value after the
index implementation
40
The Over Time graph verifies that the response
time has returned to the normal value after the
index implementation
41
Alerts are gone
42
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

43
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)

44
Large Return Recognizable Through Database Tuning
- Oracle as Example

• Object Design and Maintenance
• Query Optimization
• Object statistics
• Indexing
• Environmental/Configuration Considerations
• Lifecycle Issues

45
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

46
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)

47
Object Design and Maintenance DONTs

• Never accept the default storage parameters
• Diligent review should be standard policy
• Dont expect linear growth
• Data volume
• Transaction volume
• Server capacity
• Users expertise
• User demand on performance

48
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

49
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
• Cant change SQL text from standard ERP modules
  -- but can influence optimizer with proper index
  design

50
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

51
Environmental/Configuration Considerations

• Dont under-instance, i.e., make sure you have
  enough Apps. database instances to effectively
  manage your entire lifecycle (more on this later)
• If possible, try to maintain pseudo-equivalence
  between instances, i.e., make your test instances
  as close to production as possible, ideally with
  on-demand or short latency refresh capabilities
• Size your systems with absolutely as much
  physical memory as you can afford - and then
  immediately budget for more!

52
Environmental/Configuration Considerations (II)

• Try to keep ad-hoc and OLAP reporting queries off
  your production instance consider a
  short-latency reporting instance, preferably on a
  separate machine
• Pin to Win! - Design a package pinning strategy
• Adjust the PCTFREE parameter for static data,
  such as financial data for closed accounting
  periods
• For tables which are subjected to high concurrent
  INSERT activity, consider building them with more
  than one free list
• Tables with high UPDATE activity may benefit from
  a higher INITRANS value, although this does
  increase block-level overhead

53
Environmental/Configuration Considerations (III)

• Some influencing INIT.ORA parameters
• CURSOR_SPACE_FOR_TIME - when set to TRUE,
  prevents private SQL statements in the library
  cache from being de-allocated until application
  cursor is closed use to enhance on-line
  transaction performance, bear in mind that you
  will probably want to increase the size of the
  shared pool library cache and the number of open
  cursors (watch out for ORA-01000 errors)
• DB_FILE_MULTIBLOCK_READ_COUNT - set to a minimum
  of 32 this will greatly enhance the performance
  of full table scans (remember that the maximum
  for this value is OS-dependent, and calculated as
  OS maximum IO size/database block size)

54
Lifecycle Issues

• Formalize your change management policies and
  practices before you go live some bureaucracy is
  not necessarily a bad thing...
• Make sure you have a very thorough code/design
  review with your implementation consultants
  before they walk out the door - document
  everything! - and dont forget database object
  parameters
• If you are going to do any post-production
  customizations, make sure you have at least 3
  (preferably full-sized) instances for migration -
  development, test, and QA
• Need a separate instance specifically for
  validating patches

55
Lifecycle Issues (II)

• Remember - new/changed code will impact your
  maintenance schedule as well (i.e., defrags,
  rebuilds, extent monitoring, etc.)
• Tricks of the Trade - you might be able to
  justify the additional capacity required by your
  full-scale development/testing database(s) as
  part of a disaster recovery strategy

56

QUESTIONS
ANSWERS
57
(No Transcript)