Tracking Workloads within Oracle E-Business Suite 11i

1. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd. larry.klein@hotsos.com

2. Learning Objectives • As a result of this presentation, you will be able to: • Understand how to measure database workload • Understand how to measure E-Business Suite 11i workload • Develop methods to analyze and report workload activity

3. Speaker’s Qualifications • Larry Klein • Consulting Lead, Hotsos Enterprises, Ltd. • Former Director, Oracle’s own Global Single Database Performance Team • 9 years Oracle Consulting performance consultant and director • 18 years IBM and Candle mainframeperformance engineer, consultant, instructor, team lead

4. Presentation Agenda • A Case Study • The “System” as a Factory • Measuring the Factory’s Activities • Collecting Workload Data in the Database • Collecting Workload Data in the Oracle E-Business Suite 11i • A “Measured” Case Study • Questions?

5. Client XYZ Company Custom Order Entry Application Database Server max’ed out Application not meeting needs of the business A Case Study • Tune Logical Reads • Identify/Trace Work • Measure and report progress • 5 week effort

6. A Case Study Client, “Good Work, but what does it mean to me???”

7. A Case Study • 7/8 server reduction • 5x more daily business thruput • 40x overall improvement“price per pound” • Met expected service levels • Client very happy Independent Business Metric

8. The “System” as a Factory • Workers (Users) • Forms • Batch Jobs • Widgets • Transactions • Assembly Line • Database “Costs” The notion is very powerful, to tie together business and system metrics

9. Measuring the 11i Factory Because my first job after college was as a conveyor engineer, let’s talk about the “Assembly line” then come back to the Workers and Widgets...

10. Measuring the Assembly Line • Logical/physical reads (LIO’s, PIO’s) • Bstat/Estat/Statspack too “aggregate” • Looking for transaction-level cost info • like v$session, v$sess_io join but persistent • consistent with LIO tracing/tuning work • help build “pie chart” of database (assembly line) usage The costs to support the Workers who make the Widgets

11. Measuring the Assembly Line “Audit Session” • Captures lnformation for Each and Every Session that connects to the database, regardless of the type of session... • Logical Reads • Physical Reads • Logical Writes • Login/Logout Times • Audit Session ID • …

12. Measuring the Assembly Line • Enabled in init.ora “audit_trail = true | DB” • Activated with SQL*Plus “audit session” • Deactivated with SQL*Plus “noaudit session” • When a session connects… • Oracle inserts row into SYS.AUD$ table • As the session runs… • Oracle maintains session data regardless in v$session, v$process, v$sess_io • When the session ends and disconnects… • Oracle updates row in SYS.AUD$ table from v$sess_io Audit Session Operating Characteristics

13. Run scripts today to find out about yesterday Typically select/join view “dba_audit_session” Measuring the Assembly Line

14. Measuring the Assembly Line What Questions Can We Ask? (and more!!!)

15. Useful to Decode Line Worker Data select to_char( trunc(timestamp), 'mm/dd Dy' ), count(*), sum(decode(decode(username,'APPS','APPS','OTHER'), 'APPS' ,logoff_lread,0)), sum(decode(decode(username,'APPS','APPS','OTHER'), 'OTHER',logoff_lread,0)), sum(logoff_lread), sum(decode(decode(username,'APPS','APPS','OTHER'), 'APPS' ,logoff_pread,0)), sum(decode(decode(username,'APPS','APPS','OTHER'), 'OTHER',logoff_pread,0)), sum(logoff_pread) from sys.dba_audit_session where timestamp >= to_date('&startdate‘) and timestamp < to_date('&enddate‘) + 1 and logoff_time is not null group by trunc(timestamp);

16. Measuring the Assembly Line • Not All Audited Sessions are APPS’ – you’ll be surprised by what Audit Session will reveal! • Sometimes, select count or sum(cost) of “OTHER” > 50% of total! • Some customers’ “Oracle Apps Performance Issue” have been a dominance of ... • Discoverer or other non-Apps reporting tools • Monitoring tools (OEM, Patrol, etc.) • Costly and/or non-persistent interfaces from other systems • DB Links • Cron-based scripts or 3rd party batch scheduler jobs • ...

17. Measuring the Assembly Line Common Objections to Using Audit Session • What about the overhead? • one row insert, one row update per session • perfect for an application like Oracle Apps with “long” sessions • “noise” compared to insert/update activity with order_lines_all, wf_item_activity_statuses and their indexes • “noise” compared to Sarbanes-Oxley auditing • not appropriate for a high volume, stateless, non-connection pool .com • What about Oracle Support and Development? • tacitly approved per Interoperability Support Note -> init.ora parm Support Note 216205.1 • hot SYS.AUD$ table in a RAC cluster??? • or, use a logoff trigger to do the same thing but without audit

18. Measuring the Assembly Line • SYS.AUD$ is in SYSTEM tablespace, consider moving it • “audit session” remains across instance restarts • “noaudit session” disables auditing without instance recycle • keep approx last 6 weeks of data online, purge weekly delete from sys.aud$ where timestamp# < sysdate - 42 • create index on SYS.AUD$ “timestamp#” column to improve reporting performance • advanced usage - trigger on audit (or logoff trigger) to capture more attributes • machine • program • module Managing Audit Session Data

19. Measuring the Assembly Line So what were the Daily Costs for the Workers to produce Widgets on the Assembly Line? Daily Workload (measured by audit statistics) LOGICAL PHYSICAL LOGICAL DATE COUNT MINUTES READS READS WRITES --------- ------- --------- --------------- ------------ -------------- 08/20 Tue 13008 114391 581,657,223 16,545,327 179,489,728 08/21 Wed 10989 330437 1,721,095,538 78,912,474 852,543,162 08/22 Thu 10413 179095 164,239,372 6,446,863 12,597,893 08/23 Fri 9008 270956 255,761,332 4,505,754 21,503,403 08/24 Sat 3195 156573 348,326,115 8,028,390 74,741,441 08/25 Sun 1054 64878 32,843,291 210,135 8,625,611 08/26 Mon 6292 182155 470,203,767 22,592,103 41,915,856 What happened on 08/21? - Drilldown to Detail!

20. Relating Assembly Line, Workers Ah Hah! The 21st was a “big” day because of several “large” runs of the BMCOIN module – need to investigate!!! Sessions Report - Concurrent Manager Large Jobs with OS User LOG/ ORACLE OS TOTAL PHY LREAD LOGICAL PREAD PHYSICAL LWRITE LOGICAL PROGRAM REQUEST DATE USERNAME USERNAME S LOGON LOGOFF MIN READ SEC READS SEC READS SEC WRITES NAME ID --------- -------- -------- - -------- -------- ----- ------ ------ ---------- ----- -------- ------ -------- ------------- -------- 08/21 Wed APPS applmgr L 15:01:13 15:28:51 28 28 36405 60358984 1298 2151746 34877 ######## BMCOIN 135439 APPS applmgr L 11:59:19 12:23:03 24 11 28823 41044557 2612 3719867 26146 ######## BMCOIN 135370 APPS applmgr L 14:33:21 14:56:23 23 17 29348 40559565 1717 2373287 26932 ######## BMCOIN 135424 APPS applmgr L 15:35:01 15:59:03 24 16 27862 40177212 1776 2561483 25811 ######## BMCOIN 135448 APPS applmgr L 16:51:44 17:14:00 22 18 30046 40141623 1696 2265690 27832 ######## BMCOIN 135516 APPS applmgr L 17:21:01 17:43:32 23 14 29710 40138081 2060 2782520 27522 ######## But Wait! This implies an ability to join Assembly Line (Database) and Worker (11i) Data!

21. Measuring Workers

22. Join Assembly Line, Workers • Online • Join from fnd_login_resp_forms.audsid to dba_audit_session.sessionid • Batch • Join from fnd_concurrent_requests.oracle_session_id to dba_audit_session.sessionid

23. Measuring Workers – Online 6i

24. Measuring Workers – Online SS

25. Measuring Workers - Batch

26. Recall, the “System” as a Factory • Workers (Users) • Forms • Batch Jobs • Widgets • Transactions • Assembly Line • Database “Costs”

27. Measuring Widgets • Identify Key Business Process Flows • Identify Important Transactions • Identify Related Base Tables • Create indexes on “who” creation_date • Create “widget scripts”

28. Measuring Widgets

29. Process to Measure the Factory • Run Scripts • Measure the Workers who “Clock in” • Measure the Widgets that come off the Line • Measure the Assembly Line Costs • Capture Output to Spreadsheet • Derive “Price per Pound” of Assembly Line Costs/Total Daily Widgets • Analyze, Resolve, Track

30. A “Measured” Case Study • E-Business Suite 11i customer • Can the “System” handle an upcoming upgrade? • No automated test scripts • Focus on current Prod activity from 2-4pm • Execute “Day in the Life” in upgraded ProdTest to mimic the Prod activity between 2pm and 4pm • selected users to log in, perform work, submit Concurrent Mgr jobs, reports • Determine if Day in Life (DIL) test overdrove, underdrove, or came close to Prod • Decide go-live go/no-go based on test “closeness” and system performance How “Close to Real” was the Stress Test?

31. Measuring D-I-L Test - Workers How much like Prod was my “Stress Test?” Hmmm – Day in Life used many more Conc Mgr Jobs runtime minutes

32. Measuring D-I-L Test - Widgets How much like Prod was my “Stress Test?” Hmmm – Day in Life created far fewer transaction base table rows

33. Measuring D-I-L – Assembly Line How much like Prod was my “Stress Test?” Hmmm – Day in Life consumed much higher read costs, especially as compared to total widgets

34. Comparing Stress Test to Prod

35. Comparing Stress Test to Prod Unscripted stress test users “underloaded” transaction processing and “overloaded” favorite, ugly reports

36. Summary • As a result of this presentation, you shouldknow how to: • Measure database workload • Measure E-Business Suite 11i workload • Develop methods to analyze and report workload activity

37. Q & A

38. Thank You!