1 / 36

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

PERFORMANCE MONITORING AND TUNING ORACLE E-BUSINESS SUITE FOR APPLICATIONS DBAS. John Kanagaraj Cisco Systems Inc. Paper# 3604 – COLLABORATE 2010. Speaker’s Qualifications. IT Architect @ Cisco Systems Inc Executive Editor SELECT Journal Oracle ACE

mheise
Télécharger la présentation

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

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

More Related