Maximizing the Performance of the Oracle E-Business Suite

1
Maximizing the Performance of the Oracle
E-Business Suite
Session id 40191

• Ahmed Alomari
• Applications Performance Group
• Oracle Corporation

2
Agenda

• Architecture
• Application Services Tier
• Forms
• Reports
• Concurrent Manager
• Apache
• Mod PL/SQL
• Jserv/JVM
• Portal
• Discoverer

3
Agenda (continued)

• Network Performance
• Data Server Tier
• 9iR2 New Features
• New tablespace model
• Application Tuning
• Q A

4
Architecture
Portal
Real Application Clusters (RAC)
Servlet Engine
JDBC (Thin)
Java Server Pages (JSP)
http
Discoverer
Web Listener
PL/SQL Gateway
TNS (Net8)
Data Server Tier
Reports
Clients
Forms
Concurrent Manager
Application Tier
5
Application Services Tier

• Forms
• Deploy with socket mode for internal users
• connectModesocket (appsweb.cfg)
• Enable Forms Dead Client Detection
• Value specified in minutes
• Terminates f60webmx processes for dead clients.
• FORMS60_TIMEOUT10
• Disable Forms Abnormal Termination Handler
• FORMS60_CATCHTERM0

6
Application Services Tier

• Forms
• Upgrade to Forms Patchset 10 or higher
• Forms patchset 7 (6.0.8.16.X) introduced a
  regression which affects scalability (bug
  2269913).
• Results in Forms generating invalid SQL.
• Fixed in Forms patchset 10 (6.0.8.19.X).
• Refer to MetaLink document 125767.1 for Forms
  patchset upgrades.
• Disable Cancel Query
• Set the Profile FND Enable Cancel Query to No.

7
Application Services Tier

• Forms
• Ensure users are optimally utilizing the
  professional Forms interfaces.
• Avoid Blind queries
• Provide selective criteria in Find windows and
  LOVs.
• Avoid opening and closing forms across
  transactions.
• Users which need to switch responsibility in
  order to complete a business transaction should
  instead keep the relevant Forms open.

8
Application Services Tier

• Forms (Tracing)
• If the form is slow, or a specific flow in the
  Form is slow, first generate a complete SQL trace
  using the Trace menu in Help-Diagnostics.
• If the SQL trace does not account for the
  majority of the response time experienced by the
  user, then generate a Forms Runtime Diagnostics
  (FRD) Trace.

9
Application Services Tier

• Forms (Tracing SQL Trace)

10
Application Services Tier

• Forms (Tracing FRD Trace)
• Logon to Applications 11i with the following
  parameters appended to the URL
  playrecordcollectlog/tmp/oeform.trc
• Navigate through the flows which you would like
  to trace.
• Exit from Oracle Applications completely.
• Review the trace file from the application server
  (/tmp/oeform.trc).

11
Application Services Tier

• Reports
• Ensure users provide selective parameters to the
  standard Reports.
• Review the Report output
• Number of rows returned to the report.
• Number of report pages.
• SQL Trace can be generated by enabling the
  Enable Trace flag in the Concurrent Program
  Definition Form.

12
Application Services Tier

• Reports (Tracing)
• If the SQL trace for the report does not account
  for the elapsed time of the request, then
  generate a Reports trace by appending tracing
  parameters the to command line.
• ar60run TRACEFILEltfilegt TRACE_OPTS(lttrace
  optionsgt) TRACEMODEtrace_replacetrace_append
• The following are the trace options
• TRACE_ALL log all possible trace information
  in the trace file. (DEFAULT)
• TRACE_APP log trace information on all the
  report objects in the trace file.
• TRACE_BRK list breakpoints in the trace file.
• TRACE_ERR list error messages and warnings in
  the trace file.
• TRACE_PLS log trace information on all the
  PL/SQL objects in the trace file.
• TRACE_PRF log performance statistics in the
  trace file.
• TRACE_SQL log trace information on all the
  SQL in the trace file.
• TRACE_TMS enter a timestamp for each entry in
  the trace file.

13
Application Services Tier

• Reports (Trace Output)
• Report /u02/appl/fnd/11.5.0/reports/US/FNDS
  CURS.rdf
• Logged onto server
• Username
• . . . . . . . .
• 235957 APP ... ( Generic Graphical Object
  B_SECURITY_GROUP_NAME1
• 235957 APP ... ) Generic Graphical Object
  B_SECURITY_GROUP_NAME1
• 235957 APP ... ( Generic Graphical Object
  B_USER_NAME1
• . . . . . . . .
• -------------------------
  ------------
• Report Builder Profiler
  statistics
• -------------------------
  ------------
• TOTAL ELAPSED Time 2243.04
  seconds
• Reports Time 1196.62 seconds
  (53.34 of TOTAL)

14
Application Services Tier

• Concurrent Manager
• Avoid enabling an excessive number of standard or
  specialized managers.
• Use specialization rules and work shifts to bind
  specific jobs to specific time windows.
• Helps avoid scheduling resource intensive batch
  requests during peak activity.
• For jobs which spawn parallel workers such as
  Auto Invoice or Payroll, set the sleep time of
  the Conflict Resolution Manager (CRM) to null
  (i.e. 10 seconds). The default value is 60
  seconds.

15
Application Services Tier

• Concurrent Manager
• Database Resource Manager Integration
• Define Resource Consumer Groups and associate
  specific concurrent programs with a resource
  group.
• Allows an administrator to constrain the amount
  of CPU resources used by a concurrent program.
• Resource Groups can also be defined for online
  users using the profile FND Resource Consumer
  Group.

16
Application Services Tier

• Concurrent Manager
• Transaction Managers
• Used for Inventory Transactions as well as other
  synchronous online processing.
• Ensure enough transaction managers exist to
  service the request load.
• Set the profile ConcurrentWait for Available
  TM to 1 (second).
• Set TPINV Transaction processing mode to
  On-line processing for small inventory requests
  from the UI.
• Set the sleep time on the transaction managers to
  a low number (lt 5 seconds) for high volume
  requests.
• Transaction Managers can also be traced.

17
Application Services Tier

• Concurrent Manager (Enabling Trace)

18
Application Services Tier

• Apache
• On UNIX, Apache is process based (httpd), and
  mods such as mod PL/SQL run within the process
  address space of the httpd processes.
• On Windows, Apache is multi-threaded
  (Apache.exe).
• Tune the number of processes and number of
  clients (httpds.conf)
• MinSpareServers 5
• MaxSpareServers 10
• StartServers 5
• MaxClients 512

19
Application Services Tier

• Apache
• Minimize levels of logging (httpds.conf)
• LogLevel warn
• SSLLogLevel warn
• Enable Caching of non-HTML resources including
  images, style sheets, and Java script.
• Caches content in the browser cache.
• Reduces network round-trips (non SSL)
• Reduces network bandwidth utilization (SSL)
• Included in AutoConfig templates in 11.5.8.

20
Application Services Tier

• Apache
• Enable Caching of non-HTML resources (httpd.conf
  or apps.conf)

ltDirectory "ltphysical_path_corresponding_to_the
_alias_/OA_HTML/gt"gt enable the generation
of the Expires header for files under /OA_HTML/
ExpiresActive On expire images one
month after last client access
ExpiresByType image/gif "access plus 1 month"
expire stylesheets one week after the last
client access ExpiresByType text/css
"access plus 1 weeks" expire javascript
libraries one day after the last client access
ExpiresByType text/javascript "access plus 1
days ExpiresByType application/x-javascript
"access plus 1 day" lt/Directorygt
21
Application Services Tier

• Apache
• Review Apache Access Log file to ensure images
  are being satisfied from the browser cache.
• Http code 200 (request for document)
• Http code 304 (request for time stamp)

130.35.127.106 - - 23/Oct/2002190021 -0700
"GET /OA_MEDIA/FNDINVDT.gif HTTP/1.1" 200
821 130.35.127.106 - - 25/Oct/2002143823
-0700 "GET /OA_MEDIA/FNDINVDT.gif HTTP/1.1" 304
- 130.35.127.106 - - 25/Oct/2002143823 -0700
"GET /OA_MEDIA/FNDWATHS.gif HTTP/1.1" 200
190 130.35.127.106 - - 25/Oct/2002143823
-0700 "GET /OA_MEDIA/FNDREDPT.gif HTTP/1.1" 200
70 130.35.127.106 - - 25/Oct/2002143923
-0700 "GET /OA_HTML/OA.jsp?page/oracle/apps/icx/
por/rcv/pages/ReceivingHomePageOAHPICXP OR_MENU
OASFICXPOR_RCV_HOME_PAGEdbcap107fam_ipdev11ila
nguage_codeUStransactionid70A5819F04C0F411
HTTP/1.1" 200 14 130.35.127.106 - -
25/Oct/2002143934 -0700 "GET
/servlets/PoolMonitor?jvm HTTP/1.1" 200
7954 130.35.127.106 - - 25/Oct/2002144103
-0700 "GET /OA_HTML/US/ICXINDEX_ipdev11i.htm
HTTP/1.1" 304 - 130.35.127.106 - -
25/Oct/2002144103 -0700 "GET
/OA_MEDIA/logo.gif HTTP/1.1" 304 - 130.35.127.106
- - 25/Oct/2002144103 -0700 "GET
/OA_MEDIA/appslogo.gif HTTP/1.1" 304
- 130.35.127.106 - - 25/Oct/2002144103 -0700
"GET /OA_MEDIA/FNDJLFRL.gif HTTP/1.1" 304
- 130.35.127.106 - - 25/Oct/2002144103 -0700
"GET /OA_MEDIA/FNDINVDT.gif HTTP/1.1" 304
- 130.35.127.106 - - 25/Oct/2002144103 -0700
"GET /OA_MEDIA/FNDJLFRR.gif HTTP/1.1" 304 -
22
Application Services Tier

• Apache Mod PL/SQL
• Configure a dedicated mod PL/SQL Listener
• Improves performance and scalability
• Significantly reduces overall number of
  sessions/connections.
• Reduces latency of web requests.
• Improves cursor sharing.
• Documented in Oracle 9i Application Server Using
  the PL/SQL Gateway Release 1 (v1.0.2.2)
• http//technet.oracle.com/docs/products/ias/doc_li
  brary/1022doc_otn/apps.102/a90099/apptroub.htm634
  180

23
Application Services Tier

• Apache Mod PL/SQL (dedicated listener)

1. For the main Listener running on Port 7000,
edit the file IAS_HOME/Apache/modplsql/cfg/plsql.
conf as follows Disable the mod PL/SQL service
from the main listener by commenting out the
lines between the two Location parameters as
follows ltLocation /plsgt

SetHandler pls_handler
Order
deny,allow
Allow from all

lt/Locationgt Comment out the
following line as follows LoadModule
plsql_module /d1/ias/Apache/modplsql/bin/modplsql.
so
24
Application Services Tier

• Apache Mod PL/SQL (dedicated listener)

2. Configure the main listener to forward all
mod_plsql requests to the dedicated mod PL/SQL
listener by adding the following line
ProxyPass /pls/ http//sechost.us.oracle.com888
8/pls/ For the dedicated mod PL/SQL Listener
running on Port 8888, configure each DAD to
override the default CGI environment variables in
order to allow redirects. Edit the file
IAS_HOME/Apache/modplsql/cfg/wdbsvr.app and add
the following line for each DAD
cgi_env_listSERVER_NAMEmainhost.us.oracle.com,
SERVER_PORT7000,HOSTmainhost.us.oracle.com7000

25
Application Services Tier

• Apache Jserv / JVM
• Minimize Jserv logging
• logtrue
• log.channel.warningtrue
• log.file/d1/ias/Apache/Jserv/logs/jserv_7000.log
• Disable auto reload in production environments
• autoreload.classesfalse
• autoreload.filefalse
• Use Jserv Auto Load Balancing
• Configure Multiple Zones.
• Provides higher availability and improves
  scalability.

26
Application Services Tier

• Apache Jserv / JVM
• Upgrade to the latest JDK (1.3.1_09).
• Do not disable hotspot or the JIT (i.e.
  DCOMPILERNONE or Xint).
• Enable verbose GC in order to optimally tune the
  heap sizes based on the GC traffic.
• Minor Collections
• Major Collections
• Elapsed Time of GC

27
Application Services Tier

• Apache Jserv / JVM
• Enable Verbose GC Output
• Overload wrapper.bin with a shell wrapper in
  order to redirect stdout to a file.

Existing wrapper.bin/d13/jdk/jdk1.3.1/bin/java
Change To Wrapper.bin/d13/scripts/java.sh java.
sh /d13/jdk/jdk1.3.1/bin/java
-verbosegc gtgt /d2/logs/java.log
28
Application Services Tier

• Apache Jserv / JVM (Verbose GC Output)

/d2/logs/java.log GC 30460K-gt1369K(510848K),
0.1135695 secs Full GC 15135K-gt1686K(510848K),
0.2700469 secs GC 32123K-gt2131K(510848K),
0.0203634 secs GC 32595K-gt2130K(510848K),
0.0113639 secs GC 32593K-gt2171K(510848K),
0.0129179 secs GC 32635K-gt2419K(510848K),
0.0567306 secs GC 32881K-gt3157K(510848K),
0.2906981 secs GC 33620K-gt3197K(510848K),
0.0320023 secs GC 33661K-gt3218K(510848K),
0.0103013 secs GC 33674K-gt3309K(510848K),
0.0487887 secs GC 33769K-gt3532K(510848K),
0.0531514 secs GC 33983K-gt3784K(510848K),
0.0552549 secs GC 34248K-gt4056K(510848K),
0.0624969 secs GC 34520K-gt4404K(510848K),
0.0555575 secs GC 34868K-gt4828K(510848K),
0.0587044 secs GC 35292K-gt5242K(510848K),
0.0945290 secs
29
Application Services Tier

• Apache Jserv / JVM
• Utilize the verbose GC output to tune the JVM
  heaps (-Xmx and Xms) accordingly.
• Review the frequency of collections, especially
  major collections (i.e. Full GC).
• Start with
• -Xms256M and Xmx512M
• -XXNewSize60M -XXMaxNewSize120M

30
Application Services Tier

• Apache Jserv / JVM
• Process Identification (VSESSION)
• Specify the property DCLIENT_PROCESSID in the
  JVM startup shell script.

Existing wrapper.bin/d13/jdk1.3.1/bin/java Chan
ge To Wrapper.bin/d13/scripts/java.sh java.sh
!/bin/sh /d13/jdk1.3.1/bin/java
-verbosegc -DCLIENT_PROCESSID gtgt
/d2/logs/java.log
31
Application Services Tier

• Apache Jserv / JVM
• Process Identification (VSESSION)
• Allows you to map the JDBC session from vsession
  to a particular JVM process.

SID MACHINE PROCESS
MODULE LOGON ---- --------------------
----- ------------ ------------------
----------------- 41 aptier1.us.oracle.com
28806 JDBC Thin Client 09/07/03
232643 42 aptier2.us.oracle.com 1723
JDBC Thin Client 09/07/03 232701 43
aptier3.us.oracle.com 3201 JDBC Thin
Client 09/07/03 232815 44
aptier1.us.oracle.com 28807 JDBC Thin
Client 09/07/03 232917 . . . . . . . . . . .
. . . aptier1apps_a-gt ps -ef grep
28806 apps_a 28806 28561 0 232639 pts/20
000 /bin/sh ./java.sh apps_a 28807 28806 53
232640 pts/20 855 /jdk1.3.1_09/bin/../bin/spa
rc/native_threads/java
32
Application Services Tier

• Java Server Pages (JSPs)
• Precompile the JSPs to avoid dynamic compilation.
• Users experience poor performance for the initial
  page loads.
• Potential deadlocks if multiple users attempt to
  compile the same JSP.
• Potential JVM death due to OutOfMemoryException
  during concurrent compilation.
• MetaLink Document 215268.1 provides the
  instructions and the patch reference for an
  automated script to perform the precompilation.
• ojspCompile Script

33
Application Services Tier

• Java Server Pages (JSPs)
• Use a separate JVM to perform the JSP compilation.

root.properties servlet.oracle.jsp.JspServlet
.initArgstranslate_paramstrue,
unsafe_reloadfalse, page_repository_root/app
l_top/115/common/html/jsp/pagecache,
alias_translationtrue,developer_modefalse,
javaccmd/usr/jdk131/bin/javac,send_errortrue
34
Application Services Tier

• Portal Tuning
• Tune the number of content fetcher threads for
  PPE (poolSize). Default is 25.
• Adjust the fetch request timeout (requesttime).
• Adjust the fetch connection request timeout
  (stall).

zone.properties servlet.page.initArgspoolSiz
e50 servlet.page.initArgsrequesttime200
servlet.page.initArgsstall100
35
Application Services Tier

• Portal Tuning
• Ensure that the mod PL/SQL cache is enabled and
  sized appropriately

APACHE_TOP/modplsql/cfg/cache.cfg PLSQL
Cache enabledyes total_size100000000 cleanup_si
ze75000000 cleanup_interval86400 Cookie
Cache enabledyes total_size25000000 cleanup_siz
e15000000 cleanup_interval86400 max_size 0
36
Application Services Tier

• Discoverer 4i Viewer Tuning
• Disable Query Prediction by setting QPPEnable0
  in pref.txt.
• Set ObjectsAlwaysAccessible1 to avoid extra
  workbook SQL validation, and related dictionary
  SQL.
• Set the Query Governor option Limit retrieved
  query data to to 100 rows.
• Set the option After opening a worksheet to
  Dont run query.
• Ensure Custom workbooks define mandatory
  parameters.

37
Network Performance

• Network performance is often neglected.
• Networks with poor latency or low bandwidth
  impacts user response time.
• Can also impact concurrent programs if the
  network between the application tier and data
  server tier is not optimal.
• A direct switch should be used between the
  application tier and data server tier
• Latency should ideally be 2-3ms per round-trip.

38
Network Performance

• Use the ping command to determine the latency of
  requests. Vary the packet size to determine the
  average latency. Do not use the default ping
  packet size of 32 bytes.

D\gtping ap626sun.us.oracle.com -l 2048 Pinging
ap626sun.us.oracle.com 139.185.128.27 with 2048
bytes of data Reply from 139.185.128.27
bytes2048 time371ms TTL252 Reply from
139.185.128.27 bytes2048 time330ms
TTL252 Reply from 139.185.128.27 bytes2048
time361ms TTL252 Reply from 139.185.128.27
bytes2048 time360ms TTL252 D\gtping
ap626sun.us.oracle.com -l 512 Pinging
ap626sun.us.oracle.com 139.185.128.27 with 512
bytes of data Reply from 139.185.128.27
bytes512 time231ms TTL252 Reply from
139.185.128.27 bytes512 time210ms
TTL252 Reply from 139.185.128.27 bytes512
time231ms TTL252 Reply from 139.185.128.27
bytes512 time220ms TTL252
39
Network Performance

• Use the ping command to determine the latency
  between the application server and the data
  server. Use a packet size of 2K since this the
  default size for SQLNet traffic.

aptierhostapplmgr-2-gt ping -s dbtierhost
2048 PING dbtierhost.us.oracle.com 2048 data
bytes 2056 bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq0. time1. ms 2056
bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq1. time1. ms 2056
bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq2. time1. ms 2056
bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq3. time1. ms 2056
bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq4. time1. ms 2056
bytes from dbtierhost.us.oracle.com
(144.25.76.250) icmp_seq5. time1. Ms ----
dbtierhost.us.oracle.com PING Statistics---- 6
packets transmitted, 6 packets received, 0
packet loss round-trip (ms) min/avg/max 1/1/1
40
Network Performance

• Measure the sustained bandwidth on both the
  client network (i.e. desktops) as well as the
  application-tiers.
• Use traceroute utility to determine number of
  hops and impact on latency.
• Monitor network statistics including packet
  rates, error rates, collisions, etc.. using tools
  such as netstat i.

41
Data Server Tier

• Refer to the MetaLink document 216205.1 Database
  Initialization Parameters and Configuration for
  Oracle Applications 11i.
• Ensure mandatory parameters are set correctly.
• Optimally configure the buffer cache and shared
  pool as per the workload and the number of users
• typical configuration for 2,000 Apps users
• db_block_buffers800,000
• shared_pool_size2000M
• A poorly sized buffer cache results in excessive
  buffer gets and physical I/O.
• A poorly sized shared pool results in library
  cache and shared pool latch contention due to
  reloads and lack of space.

42
Data Server Tier

• Quick I/O improves performance and scalability by
  simulating raw devices, and avoiding file system
  cache traffic for the DB files.
• Use locally managed temp files (uniform) for the
  temporary tablespace.
• Install Stats Pack and use spauto.sql to automate
  hourly snapshots.
• Stats Pack reports can be used to trend
  transaction rates and business flows.

43
Data Server Tier

• Stats Pack Report (Instance Summary)

STATSPACK report for DB Name DB Id
Instance Inst Num Release Cluster
Host ------------ ----------- ------------
-------- ----------- ------- ------------ GSIAP
317772662 gsi1ap 1 9.2.0.1.0
YES agsidbs1 Snap Id Snap
Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
------------------- Begin Snap 503400 11-Nov-02
110001 .0 End Snap 503405
11-Nov-02 160004 .0 Elapsed
300.05 (mins) Cache Sizes
(end) Buffer
Cache 5,313M Std Block Size
8K Shared Pool Size 2,864M
Log Buffer 1,024K . . . . . . . . . . . .
44
Data Server Tier

• Stats Pack Report (Instance Summary)

Load Profile
Per Second Per Transaction
---------------
--------------- Redo size
462,767.56 26,266.16
Logical reads 54,537.81
3,095.50 Block changes
3,237.12 183.74
Physical reads 2,403.22
136.40 Physical writes
272.19 15.45
User calls 518.96
29.46 Parses
223.29 12.67 Hard
parses 1.26
0.07 Sorts
199.15 11.30
Logons 1.85
0.11 Executes
1,566.48 88.91
Transactions 17.62 Blocks
changed per Read 5.94 Recursive Call
88.46 Rollback per transaction 3.18
Rows per Sort 102.78 . . . . . . . . . . . .
45
Data Server Tier

• Stats Pack Report (Instance Summary)

Instance Efficiency Percentages (Target
100)
Buffer Nowait 99.90 Redo
NoWait 100.00 Buffer Hit
96.03 In-memory Sort 99.96
Library Hit 99.87 Soft Parse
99.44 Execute to Parse 85.75
Latch Hit 99.82 Parse CPU to Parse Elapsd
69.57 Non-Parse CPU 97.34 Shared
Pool Statistics Begin End
------ ------
Memory Usage 90.55 76.14 SQL with
executionsgt1 66.70 75.37 Memory for SQL
w/execgt1 60.86 71.08 Top 5 Timed
Events
Total Event
Waits
Time (s) Ela Time --------------------------------
------------ ------------ ----------- -------- db
file sequential read
16,187,270 134,274 31.12 CPU time

110,764 25.67 global cache cr request
21,410,811 66,129 15.33 db
file scattered read
3,184,765 23,571 5.46 KJC Wait for msg
sends to complete 10,722,532
20,446 4.74
46
Data Server Tier

• Stats Pack Report (Wait Events)

Avg
Total Wait wait
Waits Event Waits
Timeouts Time (s) (ms) /txn --------------
-------------- ------------ ---------- ----------
------ -------- db file sequential read
16,187,270 0 134,274 8
51.0 global cache cr request 21,410,811
56,241 66,129 3 67.5 db file
scattered read 3,184,765 0
23,571 7 10.0 KJC Wait for msg sends to
c 10,722,532 194,069 20,446 2
33.8 row cache lock 74,583
5,842 17,796 239 0.2 wait for
unread message on b 73,977 4,624
13,054 176 0.2 SQLNet more data to
client 6,021,483 0 8,873 1
19.0 enqueue
114,204 19,898 7,427 65
0.4 buffer busy global CR 516,382
18 4,735 9 1.6 buffer busy
waits 434,054 192
3,857 9 1.4 direct path read
1,239,648 0 3,226 3
3.9 inactive session 1,774
1,774 1,784 1006 0.0 latch free
242,171 19,203 1,694
7 0.8 db file parallel read
121,670 0 1,280 11
0.4 direct path write 1,270,008
0 1,110 1 4.0 log file sync
604,002 244 1,097
2 1.9 SQLNet message from dblink
20,365 0 1,080 53 0.1
47
Data Server Tier

• Review Latch Free waits in the Stats Pack
  reports.
• Latch contention is often a symptom due to a
  legitimate problem such as non-sharable SQL,
  sub-optimal SQL which performs full table or full
  index scans, dynamic object creation/removal,
  etc..
• Review the latch Statistics section of the Stats
  Pack report to determine the hot latches.
• Enable tracing for a few of the sessions waiting
  on the latch as well as the holder to determine
  the actual cause.

48
Data Server Tier

• Stats Pack Report (Expensive SQL)

CPU Elapsd Physical Reads Executions
Reads per Exec Total Time (s) Time (s) Hash
Value --------------- ------------ --------------
------ -------- --------- ----------
3,413,015 1 3,413,015.0 7.9
1970.29 6716.03 976039043 Module
ALECDC SELECT distinct ooha.order_number ,
fu.user_name , rac.cu stomer_name ,
to_char(wctc.last_update_date, 'DD-MON-YY
hh24mi ') , DECODE(SUBSTR(wctc.comments, 1,
21), 'NOTE This Order-Line', wctc.process_flag
' manually scre ened',
DECODE(wctc.process_flag ,'101','101 host
u 2,229,632 1 2,229,632.0
5.2 2115.49 35448.22 1280227044 Module
ALECDC select distinct pv.segment1 ,
nvl(pv.vendor_name_a lt, pv.vendor_name) ,
ai.invoice_num , hr.name ,
'ap_gsiap_rpts_us_at_oracle.com' from
po_vendors pv , ap_invoices_all ai ,
ap_holds_all ah , hr_organi zation_units hr
where ai.payment_status_flag'''Y'
and 1,990,002 1 1,990,002.0
4.6 2321.34 23934.41 4063073269 Module
OKILOADDR BEGIN OKI_REFRESH_PUB.REFRESH_ADDRS(
errbuf, rc ) END
49
Data Server Tier

• Monitor CPU and Memory Utilization
• Monitor paging
• Monitor I/O statistics, including service times.
• Review Stats Pack reports and correlate top SQL
  to the business flows.
• Review expensive custom SQL

50
Data Server Tier

• Gathering Statistics
• Do not gather statistics excessively on entire
  schemas or the entire database such as nightly or
  weekly.
• Do not gather statistics on permanent objects
  during peak intervals.
• Gathering statistics invalidates cursors
• Gathering statistics requires dictionary and
  object level locks.
• Plans are not likely to change if the data
  distribution has not changed.

51
Data Server Tier

• Gathering Statistics
• For tables which are growing at a rapid rate,
  gather statistics only on those tables.
• Use only FND_STATS or the Gather Schema and
  Gather Table Statistics Concurrent Programs
• Do NOT USE the analyze nor dbms_stats command
  directly. It is not supported, and results in
  sub-optimal plans.
• Review the table and index statistics for the
  objects which appear in the top SQL section of
  Stats Pack.

52
Data Server Tier

• Verifying the Statistics

SQLgt set serveroutput on SQLgt
exec apps.fnd_stats.verify_stats
('ONT','OE_ORDER_LINES_ALL')

Table
OE_ORDER_LINES_ALL

last analyzed sample_size
num_rows blocks 10-28-2002 2148
2607722 26077220 2385380
Index name last analyzed
num_rows LB DK LB/key DB/key
CF -----------------------------------------------
--------------------------------------------------
--- OE_ORDER_LINES_N1 10-28-2002
2110 25576780 91790 263415 1 29
7652470 OE_ORDER_LINES_N10 10-28-2002
2110 19823770 75010 256561 1 27
7130580 . . . . . . . . . . OE_ORDER_LINES_N2
10-28-2002 2110 25728720 107860
1470664 1 12 18595490 OE_ORDER_LINES_N
3 10-28-2002 2110 26092490 80870
10468 7 2184 22866190 OE_ORDER_LINES_N
5 10-28-2002 2110 0 0
0 0 0 0 OE_ORDER_LINES_N6
10-28-2002 2110 155669 592 152764
1 1 79814 OE_ORDER_LINES_N7
10-28-2002 2110 82116 310 5030
1 6 34641 OE_ORDER_LINES_N9
10-28-2002 2110 2185597 8988 1795102 1
1 1467499 OE_ORDER_LINES_U1
10-28-2002 2110 26056680 85380 26056680 1
1 15242720 -----------------------------
--------------------------------------------------
--------------------- Histogram Stats Schema
Table Name Status
last analyzed Column Name ONT
OE_ORDER_LINES_ALL present
28-10-2002 2148 OPEN_FLAG
53
Data Server Tier

• 9iR2 New Features
• Auto memory manager
• Pga_aggregate_target can be set to tune the PGA
  for the entire instance.
• Automatically tunes hash area and sort area sizes
• Returns unused memory to the OS.
• System Managed Undo
• No more ORA-1555s!!!
• Set undo_retention to the length of the longest
  running request.
• Auto Segment Tablespace Management
• No need to manually set Freelist, freelist
  groups, pctused. A bitmap is used to
  automatically scale the segment related
  parameters.

54
Data Server Tier

• 9iR2 New Features
• PL/SQL Native Compilation
• Improves PL/SQL execution performance.
• Improves scalability by removing a lot of stress
  off of the shared pool.

55
Data Server Tier

• New Tablespace Model for the eBusiness Suite
• Consolidate the number of tablespaces from 400
  tablespaces to 10.
• Optimizes performance via the wide stripe model.
• Ideal for customers migrating to RAC or migrating
  to raw devices.
• Uniform extents, locally managed
• No more fragmentation!
• Migration script can be used to migrate a schema
  at a time.

56
Data Server Tier

• New Tablespace Model for the eBusiness Suite
• Objects are classified by object type and access
  patterns
• Transaction tables
• Transaction indexes
• Reference/SEED
• Interface
• Summary Management
• Archive
• NoLogging
• Queue
• Media
• Temporary
• Undo
• System
• Total 12 Tablespaces

57
Data Server Tier

• Email apps_tsmig_ww_at_oracle.com for information on
  the migration guide and utility.
• Migration timings (complete migration)
• 1.2 Terabyte customer DB
• 20 hours
• New size of DB after migration (850 GB).
• 250 GB customer DB
• 4 hours
• New size of DB after migration (150 GB).

58
Application Tuning

• Review the Metalink note Recommended Performance
  Patches for the Oracle E-Business Suite,
  244040.1.
• Recommended performance patches for all the
  modules are consolidated in this note.

59
Application Tuning

• Workflow
• When starting background engines via Concurrent
  Manager, set the Process Stuck parameter to No.
• Start a separate background engine to handle
  stuck processes with a very low frequency such
  as once or twice a day.
• Use deferred activities to improve online
  response times and facilitate asynchronous
  processing for flows such as Scheduling, PO
  Document Approval, etc..

60
Application Tuning

• Order Management
• OM Pack H contains numerous performance fixes
  including Pricing performance fixes.
• OM Bulk Import (Pack H)
• 20,000 lines per hour per worker.
• Ensure that the profile OM Debug Level is set
  to zero (0).
• Ensure that the profile QP Debug is set to
  N.

61
Application Tuning

• Payroll
• Tune the number of threads, chunk size and the
  buffer sizes (pay_action_parameters)
• THREADS25
• CHUNK_SIZE 20
• BAL BUFFER SIZE550
• EE BUFFER SIZE 550
• RR BUFFER SIZE 550
• COST BUFFER SIZE550
• Set threads to 1.5-2.0 times the number of data
  server processors.
• Set LOW_VOLUMEN to enable optimal plans.

62
Application Tuning

• OA Framework Applications
• Upgrade to 5.7 if running an earlier release of
  OA Framework.
• Refer to MetaLink document 139863.1 (Configuring
  and Troubleshooting the Self Service Framework
  with Oracle Applications).
• Pool Monitor can be used to monitor the resource
  utilization
• Application Module Pool
• View Objects
• Memory Utilization
• JVM Properties
• https//lthostnamegt/servlet/OAAppModPoolMonitor

63
Application Tuning

• OA Framework Applications (Pool Monitor)

64
Application Tuning

• OA Framework Applications (Pool Monitor)

65
Application Tuning (OA)

• Tracing Self-Service Applications
• Set profile FND Diagnostics to Yes at user
  level
• Login to Self Service as the above user
• Click on Diagnostics icon at the top of page
• Select Set Trace Level and click Go
• Select the desired trace level and click Save
• Perform the activity that you want to trace
• Exit application

66
Application Tuning (OA)
67
Application Tuning

• AOLJ Connection Pool (dbc configuration)
• FND_TOP/secure/lthostname_sidgt.dbc
• Number of DB connections used by the Java based
  Self-Service Applications. FND_JDBC_MAX_CONNECTIO
  NS 300
• Tune FND_JDBC_MAX_CONNECTIONS as per the amount
  of transaction concurrency (per JVM).
• AOLJ automatically decays idle connections and
  resizes the pool.
• Disable sanity checks in production environments
• FND_JDBC_USABLE_CHECKfalse
• FND_JDBC_PLSQL_RESETfalse

68
Application Tuning

• AOLJ Connection Pool (dbc configuration)
• Patch 2566387 allows the use of service names in
  the configuration
• New dbc parameter APPS_JDBC_URL
• Used in place of DB_HOST, DB_NAME, DB_PORT
• Useful for automatic connection load balancing

APPS_JDBC_URLjdbcoraclethin_at_(DESCRIPTION(ADDR
ESS_LIST(LOAD_BALANCEON)(ADDRESS(PROTOCOLTCP)(
HOSTap201ops)(PORT1526))(ADDRESS(PROTOCOLTCP)(
HOSTap202ops)(PORT1526)))(CONNECT_DATA(SERVICE_
NAMEopsperf)))
69
Application Tuning

• Connection Pool Status (AoljDbcPoolStatus.jsp)

70
Application Tuning

• Connection Pool Status

71
Reminder please complete the OracleWorld
online session surveyThank you.