copyright and confidential (c) 2005 hotsos enterprises, ltd. tracking workloads within oracle...
TRANSCRIPT
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Tracking Workloads within Oracle E-Business Suite 11i
Larry Klein
Hotsos Enterprises, [email protected]
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
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
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?
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
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
A Case Study
Client, “Good Work, but what does it mean to me???”
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
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
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...
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
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”
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
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
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!!!)
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);
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– ...
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
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
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?
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!
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
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
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');
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');
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);
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”
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”
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');
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
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?
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
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
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
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
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
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
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Q & A
Copyright and Confidential (c) 2005 Hotsos Enterprises, Ltd.
Thank You!