PERFORMANCE MONITORING AND TUNING ORACLE E-BUSINESS SUITE FOR APPLICATIONS DBAS

1. PERFORMANCE MONITORING AND TUNING ORACLE E-BUSINESS SUITE FOR APPLICATIONS DBAS John Kanagaraj Cisco Systems Inc. Paper# 3604 – COLLABORATE 2010

2. Speaker’s Qualifications • IT Architect @ Cisco Systems Inc • Executive Editor SELECT Journal • Oracle ACE • Co-author Oracle Database 10g Insider Solutions • Technical Editor for various books • Published in many tech magazines • Frequent speaker – IOUG/OAUG/RUGs • Skilled in Oracle Database and Applications Tuning

3. SELECT: Call for Articles/Reviewers • The SELECT Journal is IOUG’s Technical Quarterly • Distributed to all IOUG members worldwide • Yearly Best Practices and Tips booklet • Submit an article or Review one! • Contact ‘select@ioug.org’

4. What this presentation is about • Approach to performance monitoring and tuning • Overview of end to end EBS performance • Tier based approach • Oracle Database 10g, 11g specifics for EBS • Oracle RAC considerations • Q & A

5. Audience survey • Audience profile – DBA/Application Admin • Version survey

6. EBS - Its all the same! (with some changes) • EBS employs well-understood technologies • Just in a different way using a strong F/work!  • E.g.: Gathering Table/Index statistics • EBS uses “Gather Schema Statistics” program • Calls FND_STATS.GATHER_TABLE_STATS • This is a PL/SQL wrapper that calls DBMS_STATS • DBMS_STATS gathers object statistics • In a very controlled manner – METHOD_OPT for e.g. • FND_HISTOGRAM_COLS – controls histograms • FND_EXCLUDE_TABLE_STATS – excludes tables

7. An Architectural overview

8. An approach to performance management • Before starting, ask the following questions: • What am I working towards? (Capacity? Issues?) • What is slow? (Conc programs slow, Forms ok) • When is it slow? (Time of day? Period end?) • Extent of slowness? (All users? All geographies?) • What is the Business priority? (Who gets the slice?) • What is the SLA? (What is the target to aim for?) • Create and agree Service Level Agreements • Understand and document Business/Process/Data flow (esp. Qtr/Yr end) Help stop CTD

9. Application Execution profile

10. Application Execution profile

11. Desktop Tier • Browser and JVM (Jinitiator, Sun JRE) • Runs the Applet client for Oracle Forms • Needs CPU and Memory • Guess which is more important? • Keep out pop-up blockers • Stop unnecessary services • Check OTN paper on PC Client Performance here

12. “Tech Stack” – Application tier • A number of technologies involved • Java-JRE-JVM, J2EE-OC4J, Apache/Web Servers • Networking, Load Balancers, Security layers • Performance stats not very obvious/missing • Skills do not normally lie in the DBA arena • Three areas to consider: • Latest level of patches/certifications ensure some testing and supportability • Provide adequate computing resources • Simple configuration settings can make a huge difference (positively and negatively)

13. An Architectural overview

14. Web Server tier • Caching makes a lot of difference • Cache static content as much as possible • Go easy on graphics – logos, etc. (size!) • Use parallel downloads if possible • Consider local accelerators such as Akamai • Consider network latency when designing/deploying OAF (or custom) pages • Chatty applications suffer most from latency • Use FireBug or Charles to debug

15. An Architectural overview

16. Forms tier • Forms applet requires direct connection (i.e. not connection pooled) • Forms suffers more directly from “chattiness” • Update a field => SELECT for UPDATE (traffic++) • Forms navigation triggers => validation traffic • LOVs adds to this problem; use good filters • Forms settings – Notes 384241.1, 138159.1 • Socket mode, Forms DCD, Cancel Query • Use Forms Trace: Notes 135389.1, 296559.1

17. JVM Troubleshooting & Tuning • Note: 362851.1 JVM setup Guidelines for EBS • Provide adequate CPU/Memory (Beware VMs!) • Impact of # of JVMs and settings of interest • Level and Verbosity of logging (switch off when done) • Heap settings: balance size versus GC overheads • Larger heaps may reduce OOM errors, but… • May mask issues – cursor leaks (track GC patterns) • Result in long GC pauses (application freezes) • Check full/partial GCs, parallel GC, no. of threads and type • Heap dumps will be required for Support • Use EM Application Server Control

18. Concurrent Manager tuning • Framework to submit, schedule, control and execute batch jobs and transaction processors • Needs good understanding of: • Underlying FND tables (monitor/troubleshoot/report) • Concept of queues, parameters such as number, type, width, runtime window, inclusion/exclusion, sleep times, cache and other settings (Manage CM) • CM Queues are basically “pipes” • Balance is key: Number/width vs. Complexity and Load

19. Concurrent Manager tuning • Configure Managers by priority and runtimes • Avoid queue backup! (breakout Standard Manager) • “Short(Long)/Critical”, “Short(Long)/Non-critical”, “Administrative” and “Period End” • Set up process to review all new conc. jobs • Purging Concurrent Managers • Relook at the defaults depending on request rate • Purge different reports by their individual rate • Consider APPLCSF log/out file system performance • Set Sleep seconds/Cache by manager type

20. Concurrent Manager tuning • Store/Analyze Request Completion times • Variation is key to pointing out problem areas • Could be caused by underlying inefficient SQL and/or varying parameters • Check unbounded inputs • Read “An Industrial Engineer’s Approach to Managing Oracle Databases" by Robyn Anderson Sands in the Q2 2009 issue of IOUG SELECT • Check Metalink Note: 1057802.1 Best Practices for Performance for Concurrent Managers • Non-SQL related Reports performance – Check Note 296559.1

21. Oracle Database tuning • Database performance is key to overall EBS performance • Database produces a large number of stats • First, follow the standards! • Strictly follow the mandatory Init.ora parameters • Make sure all objects are analyzed as per EBS approved methods • Don’t make any unapproved/unsupported changes • (Try to) keep up with all the DB patches

22. Oracle Database tuning • Next, provide adequate computing resources • CPU: Plan for peak processing (Qtr/Year End) as well as for growth and spikes in business • Memory: Same as CPU; Cater for increase in JVMs or Connection pool setting • I/O: Track I/O rates from AWR/Statspack, monitor redo, undo and TEMP write performance (RAID 1) • Depending on OS Memory Free, adjust the SGA_TARGET upward (watch for swapping) • Memory pools: This is the lower limit when SGA_TARGET is set

23. Tools: Oracle Database 10g • AWR – Performance Warehouse • Out of the box Workload Repository (7 day retention) • Active Session History: in memory, persisted • ADDM: Hourly snapshots automatically analyzed • High load SQLs and Statistics • Advisors: SQL Tuning, SQL Access, etc. • New views provide much better analysis • Tightly integrated to Oracle Enterprise Manager • Check IOUG repository for papers/articles Requires licenses (to even view using SQL!)

24. AWR Report: Deep dive Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ----------------------- ------------ ----------- ------ ------ ---------- db file sequential read 10,818,054 74,085 7 56.7 User I/O CPU time 20,605 15.8 gc buffer busy 2,086,824 12,810 6 9.8 Cluster db file scattered read 3,226,504 12,362 4 9.5 User I/O read by other session 879,441 4,312 5 3.3 User I/O • 1 Hour snap: 16 CPUs: 57,600 seconds available • Shared with another 10g database – used up the rest • OS CPU usage (sar: -u 100% CPU busy; -q: Large CPU queue; Wait I/O% high)

25. A process interacts with CPU

26. CPU load: Wait time inflation

27. AWR Report: Deeper dive Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ----------------------- ------------ ----------- ------ ------ ---------- db file sequential read 10,818,054 74,085 7 56.7 User I/O CPU time 20,605 15.8 gc buffer busy 2,086,824 12,810 6 9.8 Cluster db file scattered read 3,226,504 12,362 4 9.5 User I/O read by other session 879,441 4,312 5 3.3 User I/O • High CPU usage caused CPU starvation • Many processes in CPU Ready Queue • Resulting “I/O Wait Time” inflation

28. AWR Report: Deep dive SQL ordered by CPU Time DB/Inst: TSTDB/TSTDB1 Snaps: 1675-1676 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 11,733 15,203 1,525 7.69 52.2 4bdz6z8r4b2dj Module: icx.por.req.server.RequisitionAM SELECT PSSV.COUNTRY FROM PO_VENDORS PSV, PO_VENDOR_CONTACTS PSCV, PO_VENDOR_SITE S PSSV WHERE NVL (PSV.ENABLED_FLAG, 'Y') = 'Y' AND PSV.VENDOR_ID = PSSV.VENDOR_I D(+) AND ( PSV.VENDOR_TYPE_LOOKUP_CODE IS NULL OR PSV.VENDOR_TYPE_LOOKUP_CODE <> 'EMPLOYEE' ) AND (SYSDATE BETWEEN NVL (PSV.START_DATE_ACTIVE, SYSDATE - 1) AND

29. Tuning SQL • Standard code: • Search for SQL in Metalink • Possible performance bugs • Open a TAR  • SQL Tuning: • Traditional: Trace/TKPROF, Rewrite SQL/redesign • Check Stats fix (Histograms, TCF) • SQL Tuning Advisor: Deep dive in IOUG paper • SQL Outlines: Fix from the outside • If on 11g, look at SQL Plan Baselines

30. Looking into the past • Active Session History: Execution history • Introduced in 10g, expanded in 11g • 1 second sample in memory; 10th sample persisted • V$ACTIVE_SESSION_HISTORY in memory • DBA_HIST_ACTIVE_SESS_HISTORY in AWR • Bind variable capture • Values for bind variables captured/stored in AWR • 1st parse, then at 15 min interval • V$SQL_BIND_CAPTURE/DBA_HIST_SQLBIND

31. Object Statistics in EBS • “Gather Schema Statistics” => FND_STATS • DBMS_STATS gathers object statistics • History optionally stored (not required in 10g) • METHOD_OPT: Builds column list for histogram from FND_HISTOGRAM_COLS • See Metalink CKE Note: 358323.1 • 11i/R12 difference: Default INVALIDATE=YES • Register custom schemas to use this method • Be aware of bind peeking (solved in 11g?)

32. Oracle RAC in EBS Online Users: Forms, Self Service Each Concurrent manager connects to one specific node; Online users load balance to both nodes Web/Forms (online) and Concurrent Manager (batch) front-end servers F/E 2 F/E 1 F/E 3 Non EBS Queries Interconnect Services DB Node 1 DB Node 2 2 Node DB servers Discoverer (ad-hoc) Other I/Fs Interconnect ships Database blocks across nodes (Main overhead in RAC) Services direct non-EBS connections to one node (Node 2) Shared storage Connection fails over to Node 1 Primary connection to Node 2

33. RAC: AWR report Segments by CR Blocks Received DB/Inst: TSTDB/TSTDB2 Snaps: 26401-26402 -> Total CR Blocks Received: 127,334 -> Captured Segments account for 56.8% of Total CR   Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- APPLSYS APPLSYSD1 FND_CONCURRENT_REQUE TABLE 21,345 16.76 APPLSYS APPLSYSX1 FND_CONCURRENT_REQUE INDEX 9,357 7.35 XXAPP_O XXAPPD1 SYS_IOT_TOP_4005193 INDEX 7,771 6.10 XXAPP_O XXAPPD1 XXAPP_RPR_REPAIR_ORD TABLE 7,770 6.10 INV INVD1 MTL_MATERIAL_TRANSAC TABLE 6,716 5.27 ------------------------------------------------------------- • Too many Conc. Managers; very low Sleep_seconds • Expensive external queries: not using directed Services (Application partitioning)

34. Other factors in EBS • Application tuning: • Keep up with patches/certifications • Test/apply performance patches proactively  • See Note: 244040.1 for recommended patches • Develop/implement Archive/Purge Policy • WF Purging is key • Check/pursue purging for specific modules • If R12, check Purge Portal • Archiving provided by Oracle as well as 3rd party: HP, IBM – Buzzword is “ILM”

35. What we covered • Approach to performance monitoring and tuning • Overview of end to end EBS performance • Tier based approach • Oracle Database 10g, 11g specifics for EBS • Oracle RAC considerations • Q & A

36. Thank you  Please complete the Survey (Paper #3604) Link up with me on LinkedIn John Kanagaraj