Packaged Application Tuning - Go
Download
Report
Transcript Packaged Application Tuning - Go
Packaged Application Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Packaged Application Tuning
•
•
•
•
Who am I?
What is Tuning?
What is a Packaged Application?
What techniques could you use?
• Experimental Session
– Are you, the membership, interested in
discussing Application specific issues?
Rules of Engagement
• If you can’t hear me - say so now!
• Feel free to ask on-topic questions during
the presentation.
• This presentation (with notes) is available
on the conference website (after the
conference) and on
– www.go-faster.co.uk
Who am I?
• DBA
– Independent consultant
– Performance tuning
• PeopleSoft
– UKOUG Unix SIG Chair
Aphorism
• Performance is exactly what the user
perceives it to be. No more, no less.
• Poor performance is when the user’s
perception does not match their expectation.
Aphorism
• Performance Tuning is a search for lost
time.
Aphorism
• When you have eliminated the impossible,
whatever remains, however improbable,
must be the truth.
– A Study in Scarlet, Arthur Conan-Doyle
• It is a capital mistake to theorise before one
has data.
The Memoirs of Sherlock Holmes, Arthur Conan-Doyle
Aphorism
• Detection is, or ought to be, an exact
science. It should be be treated in the same
cold and unemotional manner.
– The Sign of Four, Arthur Conan-Doyle
What is a Packaged Application
• Bought in from a software vendor.
• You didn’t have any say in how it was
developed.
• You cannot (easily) change the code.
• You may not even be able to see the code.
• It may be designed to run on other
databases as well as Oracle.
What Packaged Applications are
there?
• ERP
– Oracle, SAP, PeopleSoft
– Baan, J D Edwards ...
• CRM
– and Siebel
• What others?
It isn’t always the database!
• Understand architecture
–
–
–
–
Web front end
Network
Application server
Database
• What can you measure?
PeopleSoft Internet Architecture
• PIA is 4-tier architecture
– Each component can contribute to response
time
– Need to measure each component
http /
https
Java
Servlet
(presentation
logic)
Tuxedo
Message
Tuxedo
Application
Server
(application
logic)
DBMS
SQL
(application
data & metadata
Sources of Metrics
• Browser
– Proxy Server
– 3rd Party software
• Local agent on PC
• Web Server
– Access Log
Sources of Metrics (2)
• BEA/Tuxedo
– Service Trace
– TMADMIN
• Oracle Database
– SQL_Trace
– PSFT Application Server Trace
Sources of Metrics
Proxy Server
Access Log
Webserver
Access Log
http /
https
Java
Servlet
(presentation
logic)
Tuxedo
tmadmin
script
Tuxedo
Message
Oracle
SQL*Trace
Tuxedo
Service
Trace
Tuxedo
Application
Server
(application
logic)
DBMS
SQL
(application data
& meta-data
Web/Proxy Access Log
• Standard format for access log
– www.w3.org/pub/WWW/TR/logfile.html
– Apache is different
Web/Proxy Access Log
• Fields
– Date, Time
– Time Taken
• accurate to OS time units (1/100ths or 1/1000ths)
• only 1s on Apache
– Message bytes
– IP addresses & DNS names
– URI stem & Query
Apache Access Log
#httpd.conf
LogFormat "%{%Y.%m.%d
%H:%M:%S}t|%T|%B|%u|%h|%{UserAgent}i|%>s|%m|%U|%q" monitoring
CustomLog logs/access.log monitoring
Sample Apache Access Log
2002.02.26 09:57:06|0|275|-|127.0.0.1|Mozilla/4.0
(compatible; MSIE 6.0; Windows NT 4.0;
Q312461)|200|GET|/peoplesoft8/cache/PT_NEXTTAB_ENG_1
.gif|
2002.02.26 09:57:17|0|31847|-|127.0.0.1|Mozilla/4.0
(compatible; MSIE 6.0; Windows NT 4.0;
Q312461)|200|POST|/servlets/iclientservlet|?ICType=P
anel&Menu=ADMINISTER_WORKFORCE_(GBL)&Market=GBL&Pane
lGroupName=JOB_DATA
2002.02.26 09:57:26|0|30959|-|127.0.0.1|Mozilla/4.0
(compatible; MSIE 6.0; Windows NT 4.0;
Q312461)|200|POST|/servlets/iclientservlet|?ICType=P
anel&Menu=ADMINISTER_WORKFORCE_(GBL)&Market=GBL&Pane
lGroupName=JOB_DATA
What does the access log tell us?
•
•
•
•
A date and time for every request
Who made the request?
How long did it take to serve?
What was requested?
– Which component (panel group)?
BEA/Tuxedo Service Trace
• Edit psappsrv.ubx file
• CLOPT=“-r -e APPQ.stderr…”
– -r enables trace to stderr file
– -e qualifies name of stderr file
Sample Tuxedo Service Trace
• Each service called to domain is logged
SERVICE
------@ICScript
@ICScript
@ICScript
@ICScript
@ICScript
@ICScript
@ICScript
@ICPanel
@ICPanel
@ICPanel
PID
--390
390
390
390
390
390
390
390
390
390
SDATE
----1014717320
1014717322
1014717322
1014717332
1014717355
1014717365
1014717375
1014717380
1014717388
1014717396
STIME
----4586495
4588467
4588557
4598021
4621745
4631329
4641944
4646761
4654202
4662614
EDATE
----1014717322
1014717322
1014717323
1014717332
1014717361
1014717366
1014717377
1014717381
1014717391
1014717401
ETIME
----4588397
4588537
4589259
4598171
4627554
4632511
4643306
4647783
4657567
4667271
Tuxedo Service Trace
• Service - Name of Service
• Pid - An OS PID of listener or handler
• Sdate/Edate - Start/End Date
– Seconds since 00:00hrs GMT 1st January 1970.
• Stime/Etime
– Start/End Time in OS time units (1/ 100ths or 1/1000ths)
– Hence calculate accurate service duration
– Time on Server - not queuing
BEA/Tuxedo TMADMIN Utility
• BEA Interactive command line interface
• Can be included in scripts
– -r for read only in monitoring scripts
• pq (printqueue)
• psr (printserver)
• pclt (printclient)
PQ
• What queues in domain
• How many servers on queue
• How many requests queued
– (How much work on queue)
Prog Name
--------JSL.exe
JREPSVR.exe
PSSAMSRV.exe
BBL.exe
WSL.exe
PSAPPSRV.exe
Queue Name # Serve Wk Queued
------------------- --------00095.00200
1
00094.00250
1
SAMQ
1
54455
1
00001.00020
1
APPQ
1
-
# Queued
-------0
0
0
0
0
0
Ave. Len
--------
Machine
------GO-FASTER+
GO-FASTER+
GO-FASTER+
GO-FASTER+
GO-FASTER+
GO-FASTER+
PSR
• What servers exist?
• How many requests have they handled?
• What are they doing right now?
Prog Name
--------BBL.exe
PSAPPSRV.exe
PSSAMSRV.exe
WSL.exe
JSL.exe
JREPSVR.exe
Queue Name
---------54455
APPQ
SAMQ
00001.00020
00095.00200
00094.00250
Grp Name
-------GO-FAST+
APPSRV
APPSRV
BASE
JSLGRP
JREPGRP
ID RqDone Load Done Current Service
-- ------ --------- --------------0
3
150 ( IDLE )
1
9
450 ICPanel
100
0
0 ( IDLE )
20
0
0 ( IDLE )
200
0
0 ( IDLE )
250
5
250 ( IDLE )
PCLT
• Who is connected?
• Are they doing something?
• When did they connect?
LMID
--------------GO-FASTER-1
GO-FASTER-1
GO-FASTER-1
GO-FASTER-1
User Name
--------------NT
NT
NT
PS
Client Name
Time
--------------- -------WSH
0:21:14
JSH
0:21:13
tmadmin
0:00:00
JavaClient
0:02:30
Status
------IDLE
IDLE
IDLE
BUSY/W
Bgn/Cmmt/Abrt
------------0/0/0
0/0/0
0/0/0
0/0/0
Oracle SQL Trace
•
•
•
•
•
Tuning tool
Session trace
Format it with TKPROF
Find long running SQL statements
Total SQL Time for session
call
count
------- -----Parse
72289
Execute 77140
Fetch
50599
------- -----total
200028
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------173.06
175.45
7
14654
94
64.02
67.37
205495
766343
170611
33.20
34.28
17302
971624
80146
-------- ---------- ---------- ---------- ---------270.28
277.10
222804
1752621
250851
rows
---------0
78630
143218
---------221848
Small Application Servers
• Create small application with just a single
handler process
• Enable trace on that process
• Trace sample transaction (only one user)
sys.dbms_system.set_sql_trace_in_session(
<sid>,<serial>,TRUE);
Sources of Metrics
Proxy Server
Access Log
Webserver
Access Log
http /
https
Java
Servlet
(presentation
logic)
Tuxedo
tmadmin
script
Tuxedo
Message
Oracle
SQL*Trace
Tuxedo
Service
Trace
Tuxedo
Application
Server
(application
logic)
DBMS
SQL
(application data
& meta-data
Graphical Analysis
• Don’t drown in numbers
– Draw a picture
• Load Data into Database
– SQL_Loader (Oracle 9i External Table)
• Process Data
– Matching, Aggregation
• Oracle Analytic Function
• Load data into Excel
– Draw Graph
Sample SQL*Loader File
LOAD DATA
INFILE 'APPQ.stderr'
REPLACE
INTO TABLE txrpt
WHEN (1) = '@'
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(service
"substr(:service,2)" -- remove leading @
,pid
,stimestamp
":stimestamp/86400+2/24+TO_DATE('01011970','DDMMYYYY')
"
,stime
":stime/100"
,queue
”’APPQ’"
,etime
":etime/100")
Tips about Graphs
• Connect Excel directly to a database
• Aggregate data carefully
– Too little and excel draws it badly because it adds a black border
around an area
– Too much and you lose details
• Pivot tables are useful
– but Excel97 buggy
• Scatters and Trendlines
– joining dots can obscure data
– Excel supports 32000 points per series
Example: CPU Utilisation
Example: Apache Servlet Times
Longest http conversations
Example: Tuxedo Service Time
tmadmin - PCLT - Connect Client
Sessions
tmadmin - PCLT - Busy/Wait
Users
tmadmin - PQ - Queue Length
tmadmin - PSR - print server
processes
Relating different metrics
produces new information
Batch Performance
• Sources of Metrics
• Graph
Sources of Metrics
• Process Scheduler Request Table
– every scheduled process recorded on this table
– start and end time
• Application Traces
• SQL Trace
Graphical Analysis
Oracle SQL*Trace & TKPROF
• Enable SQL Trace on batch process
• Process trace file with TKPROF
– report Top n statements
– sort by
• Parse/Fetch/Execute
• CPU/Elapsed Time
– statement timings/execution plan
call
count
------- -----Parse
36
Execute
36
Fetch
0
------- -----total
72
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.02
0.01
0
0
0
55.09
86.80
0
2973787
3
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------55.11
86.81
0
2973787
3
rows
---------0
1
0
---------1
Triggers
• Process Scheduler
Status Change
• On-Connect
• Enable Trace
• Archiving data during
purge routines
• Altering session
parameters
Process Scheduler
CREATE OR REPLACE trigger
sysadm.set_trace
before update of runstatus on
sysadm.psprcsrqst
for each row when
(new.runstatus = 7 and old.runstatus != 7
and new.prcstype IN('Application
Engine','COBOL SQL',
'SQR Process','SQR Report','SQR
Report For WF Delivery'))
On-Connect Triggers
create or replace trigger
sysadm.psqrysrv_session_trigger
after logon on sysadm.schema
declare
l_program VARCHAR(64);
begin
SELECT program INTO l_program FROM v$session
WHERE sid IN(SELECT sid FROM v$mystat WHERE rownum =
1);
IF substr(l_program,1,8) = 'PSQRYSRV' OR
INSTR(UPPER(l_program),'PSNVS') > 0 THEN
sys.dbms_session.set_sql_trace(true);
...
For example…
sys.dbms_session.set_sql_trace(true);
ALTER SESSION SET EVENTS '10053 trace name
context forever, level 1’;
ALTER SESSION SET EVENTS '10128 trace name
context forever, level 2’;
ALTER SESSION SET optimizer_mode=FIRST_ROWS;
ALTER SESSION SET optimizer_index_caching=100;
ALTER SESSION SET optimizer_index_cost_adj=1;
ALTER SESSION SET sort_area_size = 100000;
sys.dbms_application_info.set_module('nVision','p
sqrysrv_session_trigger fired');
Aphorism
• The problems change from
release to release, but the
methods by which they are
investigated remain the
same.
– Jonathan Lewis (author of Practical Oracle 8i)
Questions?
Aphorism
• You know my methods, apply them.
– A Study in Scarlet, Arthur Conan-Doyle
Packaged Application Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk