![Page 1: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/1.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Tracking Workloads within Oracle E-Business Suite 11i
Larry Klein
Hotsos Enterprises, [email protected]
![Page 2: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/2.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 3: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/3.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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 mainframe
performance engineer, consultant, instructor, team lead
![Page 4: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/4.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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?
![Page 5: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/5.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
A Case Study
• Client XYZ Company• Custom Order Entry
Application• Database Server
max’ed out• Application not
meeting needs of the business
Challenge Approach
• Tune Logical Reads• Identify/Trace Work• Measure and report
progress• 5 week effort
![Page 6: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/6.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
A Case Study
Client, “Good Work, but what does it mean to me???”
![Page 7: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/7.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
Daily Order Volume
0
1000
2000
3000
4000
5000
6000
Aug-05 Sep-05
Independent Business Metric
![Page 8: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/8.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 9: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/9.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring the 11i Factory
Element How to Measure
Workers• Online Forms• Conc Mgr Jobs
Select count(*), trunc(...date...) from fnd... group by trunc...
Widgets• Headers, lines table rows
Select count(*), trunc(creation_date) from base_table group by trunc...
Assembly line Select ??? from ???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...
![Page 10: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/10.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
• 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
Measuring the Assembly Line
The costs to support the Workers who make the Widgets
![Page 11: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/11.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
•…
Measuring the Assembly Line
“Audit Session”
![Page 12: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/12.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 13: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/13.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring the Assembly Line• Run scripts today to find out about yesterday• Typically select/join view “dba_audit_session”
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
ACTION_NAME VARCHAR2(27)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
SESSIONID NUMBER
RETURNCODE NUMBER
CLIENT_ID VARCHAR2(64)
SESSION_CPU NUMBER
![Page 14: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/14.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring the Assembly Line
What are the daily summary trends in overall workload activity?
select to_char(timestamp, ‘mm/dd Dy’), sum(logoff_lread), sum(logoff_pread)
from dba_audit_session
group by to_char(timestamp, ‘mm/dd Dy’);
What individual workloads on given day(s) each used more than 1,000,000 LIO’s?
select …
from dba_audit_session
where timestamp >= to_date(‘&startdate’)
and timestamp < to_date(‘&enddate’) + 1
and logoff_lread >= &lio_threshold;
What Questions Can We Ask? (and more!!!)
![Page 15: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/15.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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);
![Page 16: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/16.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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– ...
![Page 17: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/17.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring the Assembly Line
• 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
Common Objections to Using Audit Session
![Page 18: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/18.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 19: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/19.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring 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!
So what were the Daily Costs for the Workers to produce Widgets on the Assembly Line?
![Page 20: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/20.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Relating Assembly Line, Workers
Sessions Report - Concurrent Manager Large Jobs with OS User
LOG/ ORACLE OS TOTAL PHY LREAD LOGICAL PREAD PHYSICAL LWRITE LOGICAL PROGRAM REQUESTDATE 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 ########
Ah Hah! The 21st was a “big” day because of several “large” runs of the BMCOIN module – need to
investigate!!!
But Wait! This implies an ability to join Assembly Line (Database) and Worker (11i) Data!
![Page 21: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/21.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Workers
Worker Table(s) Note(s)
Online
Forms 6i
Fnd_logins
Fnd_login_responsibilities
Fnd_login_resp_forms
• \Navigate-Profile-System-Site Sign-on: Audit Level = Form
• Purge FNDSCPRG
Online Self Service
ICX_Sessions • Purge $ICX_TOP/sql/ICXDLTMP.sql???
Batch
Conc
Mgr
Fnd_concurrent_requests • Purge FNDCPPUR
![Page 22: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/22.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 23: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/23.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Workers – Online 6i
Worker Query
Online
Forms 6i
select to_char(start_time, 'mm/dd/rr') trans_date,
count(*) session_count,
count(distinct user_id) user_count
from applsys.fnd_logins
where start_time >= to_date('&startdate')
and start_time < to_date('&enddate') + 1
group by to_char(start_time, 'mm/dd/rr');
![Page 24: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/24.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Workers – Online SS
Worker Query
Online
Self Service
select to_char(creation_date, 'mm/dd/rr') trans_date,
count(*) session_count,
count(distinct user_id) user_count
from icx.icx_sessions
where creation_date >= to_date('&startdate')
and creation_date < to_date('&enddate') + 1
group by to_char(creation_date, 'mm/dd/rr');
![Page 25: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/25.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Workers - Batch
Worker QueryBatch
Conc Mgr
select trunc(r.actual_start_date) actual_start_date,
count(*) job_count,
count(*) - count(r.actual_completion_date) error_count,
sum( r.actual_completion_date - r.actual_start_date ) * 24 * 60 run_min,
sum( r.actual_start_date - r.requested_start_date ) * 24 * 60 wait_min
from applsys.fnd_concurrent_requests r
where
r.actual_start_date >= to_date('&startdate')
and r.actual_start_date < to_date('&enddate') + 1
and r.actual_completion_date is not null
group by trunc(r.actual_start_date);
![Page 26: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/26.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Recall, the “System” as a Factory
Workers (Users)
• Forms
• Batch Jobs
Widgets• Transactions
Assembly Line
• Database “Costs”
![Page 27: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/27.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Widgets
• Identify Key Business Process Flows
• Identify Important Transactions
• Identify Related Base Tables
• Create indexes on “who” creation_date
• Create “widget scripts”
![Page 28: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/28.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring Widgets
How many new order lines today?
select to_char(creation_date, 'mm/dd/rr') trans_date,
count(*) count
from ont.oe_order_lines_all
where creation_date >= to_date('&startdate')
and creation_date < to_date('&enddate') + 1
group by to_char(creation_date, 'mm/dd/rr');
How many new GL_JE_LINES today?
select to_char(creation_date, 'mm/dd/rr') trans_date,
count(*) count
from gl.gl_je_lines
where creation_date >= to_date('&startdate')
and creation_date < to_date('&enddate') + 1
group by to_char(creation_date, 'mm/dd/rr');
![Page 29: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/29.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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
![Page 30: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/30.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
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?
![Page 31: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/31.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring D-I-L Test - Workers
Workers Conc Jobs
Workers Online Forms
Workers Online SServe
Workers Total
CountRun Mins
Count Distinct Count Distinct Count
Prod2-4pm
2546 397 3134 294 417 214 9650
Day in Life
1990 1663 2669 162 329 122 6628
How much like Prod was my “Stress Test?”
Hmmm – Day in Life used many more Conc Mgr Jobs runtime minutes
![Page 32: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/32.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring D-I-L Test - Widgets
FinancialsSupply Chain
Projects Total
GL_JE_ Lines
new rows ... OE_Lines new rows ...
PA_Expend_Lines new rows ... ...
Prod2-4pm
41670 1162 596 55982
Day in Life
10496 841 63 12212
How much like Prod was my “Stress Test?”
Hmmm – Day in Life created far fewer transaction base table rows
![Page 33: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/33.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Measuring D-I-L – Assembly Line
Logical Reads (millions)
Physical Reads (millions)
Total Logical Reads/ Widgets
APPS OTHER APPS OTHER “Price per Pound”
Prod2-4pm
486 8 11 1 8824
Day in Life
1038 2 17 0 85162
How much like Prod was my “Stress Test?”
Hmmm – Day in Life consumed much higher read costs, especially as compared to total
widgets
![Page 34: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/34.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Comparing Stress Test to Prod
Workers • Day in Life used many more Conc Mgr Jobs runtime minutes
• DIL online user load 80% of Prod• DIL batch job run minutes 418% over Prod
Widgets • Day in Life created far fewer transaction base table rows
• DIL transaction “widgets” 21% of Prod
Assembly Line
• Day in Life consumed much higher read costs
• DIL logical reads (CPU cost) 213% of Prod• DIL physical reads (Disk cost) 150% of Prod
![Page 35: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/35.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Comparing Stress Test to Prod
“Price per Pound” Ratio
• Day in Life consumed much higher read costs, especially as compared to total widgets
• DIL 965% “more inefficient” than Prod
Unscripted stress test users “underloaded” transaction processing and
“overloaded” favorite, ugly reports
![Page 36: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/36.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Summary• As a result of this presentation, you should
know how to:– Measure database workload– Measure E-Business Suite 11i workload– Develop methods to analyze and report
workload activity
![Page 37: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/37.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Q & A
![Page 38: Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd. Tracking Workloads within Oracle E-Business Suite 11i Larry Klein Hotsos Enterprises, Ltd](https://reader037.vdocuments.us/reader037/viewer/2022110207/56649d1e5503460f949f22ff/html5/thumbnails/38.jpg)
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Thank You!