Transcript Performance Metrics: What to Measure in the PeopleSoft 8
Performance Metrics:
What to Measure in the PeopleSoft 8 Environment
David Kurtz Go-Faster Consultancy Ltd.
www.go-faster.co.uk
Go-Faster Consultancy Ltd.
1
• What is Performance Tuning?
• • On-Line Batch • • What has been achieved?
Questions?
Go-Faster Consultancy Ltd.
Agenda
2
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) will be available on the Tech SIG page on Customer Connection and on – www.go-faster.co.uk
Go-Faster Consultancy Ltd.
3
• DBA – Independent consultant – Performance tuning – UK Oracle User Group • Unix SIG Chair • www.ukoug.org
Go-Faster Consultancy Ltd.
Who am I?
4
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.
Go-Faster Consultancy Ltd.
5
Aphorism
• Performance Tuning is a search for lost time.
Go-Faster Consultancy Ltd.
6
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
Go-Faster Consultancy Ltd.
7
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
Go-Faster Consultancy Ltd.
8
PIA (On-Line) Performance
• • • • • Architecture – brief overview Sources of Metrics Graphical Techniques Tracing Techniques Go-Faster Consultancy Ltd.
9
It isn’t always the database!
• Understand architecture – Web front end – Network – Application server – Database • What can you measure?
Go-Faster Consultancy Ltd.
10
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) SQL
Go-Faster Consultancy Ltd.
DBMS (application data & meta data
11
Sources of Metrics (1)
• Browser – Proxy Server – 3rd Party software • Local agent on PC • Web Server – Access Log Go-Faster Consultancy Ltd.
12
Sources of Metrics (2)
• • BEA/Tuxedo – Service Trace – TMADMIN Oracle Database – SQL_Trace – PSFT Application Server Trace Go-Faster Consultancy Ltd.
13
Hint
• • All the data collected on a time base.
Therefore, make sure that the clocks on all machines are synchronised at least with each other.
– PC clocks are particularly liable to drift.
Go-Faster Consultancy Ltd.
14
Sources of Metrics
Proxy Server Access Log Webserver Access Log http / https Java Servlet (presentation logic) Tuxedo Message Tuxedo Application Server (application logic) SQL DBMS (application data & meta-data
Go-Faster Consultancy Ltd.
15
Web/Proxy Access Log
• Standard format for access log – – www.w3.org/pub/WWW/TR/logfile.html
Apache is different Go-Faster Consultancy Ltd.
16
Web/Proxy Access Log
• Fields – Date, Time (finish not start) – Time Taken • accurate to OS time units (1/100 th s or 1/1000 th s) • only 1s on Apache – Message size bytes – Client IP addresses & DNS names – URI stem & Query Go-Faster Consultancy Ltd.
17
How to Enable WebLogic Trace
#weblogic.properties
#required
weblogic.httpd.enableLogFile=true weblogic.httpd.logFileName=access.log
weblogic.httpd.logFileFormat=extended
#recommended weblogic.httpd.logRotationType=date weblogic.httpd.logRotationPeriodMins=1440 weblogic.httpd.logRotationBeginTime=11-24-2000-00:00:00 Go-Faster Consultancy Ltd.
18
Sample WebLogic Access Log
#Version:1.0
#Fields: date time time-taken bytes c-ip c-dns cs-method sc-status cs uri-stem cs-uri-query 2002-03-15 21:51:03 0.981
8057 10.0.0.2 go-faster-1 GET 200 /servlets/iclientservlet/peoplesoft8/signon.html
2002-03-15 GET 2002-03-15 GET 21:51:03 0.11
200 2103 10.0.0.2 go-faster-1 /peoplesoft8/signin.css
21:51:04 0.05
200 1186 10.0.0.2 go-faster-1 /peoplesoft8/images/secondaryPSFTLogo.gif
2002-03-15 21:51:10 3.414
8101 10.0.0.2 go-faster-1 POST 200 /servlets/iclientservlet/peoplesoft8/ ICType=Panel&Menu=ADMINISTER_WORKFORCE_(GBL)&Market=GBL&PanelGro upName=PERSONAL_DATA&RL=&target=main2 Go-Faster Consultancy Ltd.
19
Apache Access Log
#httpd.conf
LogFormat "%{%Y.%m.%d %H:%M:%S}t|%T|%B|%u|%h|%{User Agent}i|%>s|%m|%U|%q" monitoring CustomLog logs/access.log monitoring Go-Faster Consultancy Ltd.
20
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 Go-Faster Consultancy Ltd.
21
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)?
• Indicate possible bandwidth problems.
Go-Faster Consultancy Ltd.
22
Download Bandwidth
#version: 1.0
#Fields: date time time-taken bytes c-ip c-dns cs-method sc-status cs-uri-stem cs-uri-query 2002-09-10 11:29:52 141.93.249.74
0.359 15832 141.93.249.74
GET 200 /XXXXXXXX/images/topPSFTLogo.gif
Go-Faster Consultancy Ltd.
23
Sources of Metrics
Tuxedo Service Trace http / https Java Servlet (presentation logic) Tuxedo Message Tuxedo Application Server (application logic) SQL DBMS (application data & meta-data
Go-Faster Consultancy Ltd.
24
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 Go-Faster Consultancy Ltd.
25
Edit psappsrv.cfg
• • Add a new variable to the trace section Thus service trace can be enabled/disabled from configuration file • New variable will appear in the configuration dialogue [Trace] TuxedoServiceTrace=-r Go-Faster Consultancy Ltd.
26
Edit psappsrv.ubx
• Change every CLOPT entry in psappsrv.ubx. • File name is set manually. – Same as queue name RQADDR="APPQ" CLOPT="{$Trace\TuxedoServiceTrace} -e {LOGDIR}{FS}APPQ.stderr {$PSAPPSRV\Spawn Server} -s@..{FS}psappsrv.lst -- -C {CFGFILE} D {$Domain Settings\Domain ID} -S PSAPPSRV" Go-Faster Consultancy Ltd.
27
Resultant psappsrv.ubb
• When domain is configured .ubb file is generated. All variables are fully resolved.
CLOPT=”-r -e D:\ps\hr8\appserv\hr8d\LOGS\APPQ.stderr
-p 1,600:3,10 -s@..\psappsrv.lst s@..\psqcksrv.lst -sICQuery sSqlQuery:SqlRequest -- -C psappsrv.cfg -D HR8D -S PSAPPSRV" Go-Faster Consultancy Ltd.
28
Sample Tuxedo Service Trace
• Each service called to domain is logged SERVICE PID SDATE STIME EDATE ETIME ------ -- ---- ---- ---- ---- @ICScript 390 1014717320 4586495 1014717322 4588397 @ICScript 390 1014717322 4588467 1014717322 4588537 @ICScript 390 1014717322 4588557 1014717323 4589259 @ICScript 390 1014717332 4598021 1014717332 4598171 @ICScript 390 1014717355 4621745 1014717361 4627554 @ICScript 390 1014717365 4631329 1014717366 4632511 @ICScript 390 1014717375 4641944 1014717377 4643306 @ICPanel 390 1014717380 4646761 1014717381 4647783 @ICPanel 390 1014717388 4654202 1014717391 4657567 @ICPanel 390 1014717396 4662614 1014717401 4667271 Go-Faster Consultancy Ltd.
29
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/ 100 th s or 1/1000 th s) Hence calculate accurate service duration Time on Server - not queuing Go-Faster Consultancy Ltd.
30
Txrpt Utility
SERVICE SUMMARY REPORT SVCNAME 9a-10a Num/Avg --------------- ------- ICScript 8/1.43 ICPanel 6/3.47 JavaMgrGetObj 1/0.04 GetCertificate 1/0.73 --------------- ------ TOTALS 16/2.06 TOTALS Num/Avg ------ 8/1.43
6/3.47
1/0.04
1/0.73
------ 16/2.06
Go-Faster Consultancy Ltd.
31
Sources of Metrics
Tuxedo tmadmin script http / https Java Servlet (presentation logic) Tuxedo Message Tuxedo Application Server (application logic) SQL DBMS (application data & meta-data
Go-Faster Consultancy Ltd.
32
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) Go-Faster Consultancy Ltd.
33
PQ
• • • What queues in domain How many servers on queue How many requests queued – (How much work on queue) Prog Name Queue Name # Serve Wk Queued # Queued Ave. Len Machine -------- ------------------- -------- ------- ------- ------ JSL.exe 00095.00200 1 JREPSVR.exe 00094.00250 1 0 - GO-FASTER+ 0 - GO-FASTER+ PSSAMSRV.exe SAMQ 1 BBL.exe 54455 1 WSL.exe 00001.00020 1 PSAPPSRV.exe APPQ 1 0 - GO-FASTER+ 0 - GO-FASTER+ 0 - GO-FASTER+ 0 - GO-FASTER+ Go-Faster Consultancy Ltd.
34
PSR
• • • What servers exist?
How many requests have they handled?
What are they doing right now?
Prog Name Queue Name Grp Name ID RqDone Load Done Current Service -------- --------- ------- -- ------ --------- -------------- BBL.exe 54455 GO-FAST+ 0 3 150 ( IDLE ) PSAPPSRV.exe APPQ APPSRV 1 9 450 ICPanel PSSAMSRV.exe SAMQ APPSRV 100 0 0 ( IDLE ) WSL.exe 00001.00020 BASE 20 0 0 ( IDLE ) JSL.exe 00095.00200 JSLGRP 200 0 0 ( IDLE ) JREPSVR.exe 00094.00250 JREPGRP 250 5 250 ( IDLE ) Go-Faster Consultancy Ltd.
35
PCLT
• • • Who is connected?
Are they doing something?
When did they connect?
LMID User Name Client Name Time Status Bgn/Cmmt/Abrt --------------- --------------- --------------- -------- ------- ------------ GO-FASTER-1 NT WSH 0:21:14 IDLE 0/0/0 GO-FASTER-1 NT JSH 0:21:13 IDLE 0/0/0 GO-FASTER-1 NT tmadmin 0:00:00 IDLE 0/0/0 GO-FASTER-1 PS JavaClient 0:02:30 BUSY/W 0/0/0 Go-Faster Consultancy Ltd.
36
Sources of Metrics
Oracle SQL*Trace http / https Java Servlet (presentation logic) Tuxedo Message Tuxedo Application Server (application logic) SQL DBMS (application data & meta-data
Go-Faster Consultancy Ltd.
37
Oracle SQL Trace
• • • • • Tuning tool Session trace Format it with TKPROF Find long running SQL statements Total SQL Time for session call count cpu elapsed disk query current rows ------- ----- -------- ---------- ---------- ---------- --------- --------- Parse 72289 173.06 175.45 7 14654 94 0 Execute 77140 64.02 67.37 205495 766343 170611 78630 Fetch 50599 33.20 34.28 17302 971624 80146 143218 ------- ----- -------- ---------- ---------- ---------- --------- --------- total 200028 270.28 277.10 222804 1752621 250851 221848 Go-Faster Consultancy Ltd.
38
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(
39
Proxy Server Access Log Webserver Access Log
Sources of Metrics
Tuxedo tmadmin script Tuxedo Service Trace Oracle SQL*Trace http / https Java Servlet (presentation logic) Tuxedo Message Tuxedo Application Server (application logic) SQL DBMS (application data & meta-data
Go-Faster Consultancy Ltd.
40
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 Go-Faster Consultancy Ltd.
41
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 ,queue ,etime ":stime/100" ”’APPQ’" ":etime/100") Go-Faster Consultancy Ltd.
42
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 Go-Faster Consultancy Ltd.
43
Example: CPU Utilisation
Go-Faster Consultancy Ltd.
44
Example: Apache Servlet Times
Go-Faster Consultancy Ltd.
45
Longest http conversations
Go-Faster Consultancy Ltd.
46
Example: Tuxedo Service Time
Go-Faster Consultancy Ltd.
47
tmadmin - PCLT - Connect Client Sessions
Go-Faster Consultancy Ltd.
48
tmadmin - PCLT - Busy/Wait Users
Go-Faster Consultancy Ltd.
49
tmadmin - PQ - Queue Length
Go-Faster Consultancy Ltd.
50
tmadmin - PSR - print server processes
Go-Faster Consultancy Ltd.
51
Relating different metrics produces new information
Go-Faster Consultancy Ltd.
52
• • Sources of Metrics Graph
Batch Performance
Go-Faster Consultancy Ltd.
53
Sources of Metrics (1)
• Process Scheduler Request Table – PSPRCSRQST • • need to archive this table maintain history in another table with on DELETE trigger Go-Faster Consultancy Ltd.
54
Graphical Analysis
Go-Faster Consultancy Ltd.
55
Sources of Metrics(2)
• Application Engine Steps – PS_BAT_% tables – AE_TRACE=1024+128 • In Process Scheduler configuration (psprcs.cfg) – Report to database and/or trace file • Cobol Statement – Report to trace file • SQL Trace Go-Faster Consultancy Ltd.
56
Top AE Statements
AE PC % Cum % % Cum % Stmt Execute SQL SQL PC SQL SQL Total Total Rank Statement ID Count Time Time Time Time Time Time Time ---- ---------------------------------------- -------- --------- -------- -------- ----- ------ ----- ----- 1 GP_PMT_PREP.CA000.CF000.S 13 11655.56 0.00 0.00 12.8 12.8 19.2 19.2
2 PeopleCode.Rowset.Fill GP_RSLT_ACUM 4725754 10065.02 0.00 0.00 11.1 23.9 16.6 35.9
3 GP_PMT_PREP.FA000.FH000.S 15 9828.60 0.00 0.00 10.8 34.8 16.2 52.1
4 GP_PMT_PREP.FA000.FC000.S 15 8556.60 0.00 0.00 9.4 44.2 14.1 66.2
5 GP_PMT_PREP.FA000.FG000.S 15 8553.51 0.00 0.00 9.4 53.6 14.1 80.3
6 GP_PMT_PREP.FA000.FB000.S 15 8322.51 0.00 0.00 9.2 62.8 13.7 94.1
7 PeopleCode.Rowset.Fill GP_RSLT_PIN 3177786 6906.78 0.00 0.00 7.6 70.4 11.4 105.5
8 PeopleCode.CNuK_ 4725754 4696.51 0.00 0.00 5.2 75.6 7.8 113.2
9 PeopleCode.CDfF0_PPNw 3409180 2392.33 0.00 0.00 2.6 78.2 3.9 117.2
… Go-Faster Consultancy Ltd.
57
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 cpu elapsed disk query current rows ------- ----- -------- ---------- ---------- ---------- --------- --------- Parse 36 0.02 0.01 0 0 0 0 Execute 36 55.09 86.80 0 2973787 3 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ----- -------- ---------- ---------- ---------- --------- --------- total 72 55.11 86.81 0 2973787 3 1 Go-Faster Consultancy Ltd.
58
• • Process Scheduler Status Change On-Connect
Triggers
• • • Enable Trace Archiving data during purge routines Altering session parameters Go-Faster Consultancy Ltd.
59
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')) Go-Faster Consultancy Ltd.
60
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); Go-Faster Consultancy Ltd.
61
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'); Go-Faster Consultancy Ltd.
62
Further sources of Information
• PSFT Customer Connection Red Papers – 8.4 PIA, Power Users Go-Faster Consultancy Ltd.
63
• 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)
Aphorism
Go-Faster Consultancy Ltd.
64
Questions?
Go-Faster Consultancy Ltd.
65
Aphorism
• You know my methods, apply them.
–
A Study in Scarlet, Arthur Conan-Doyle
Go-Faster Consultancy Ltd.
66
Performance Metrics:
What to Measure in the PeopleSoft 8 Environment
David Kurtz Go-Faster Consultancy Ltd.
www.go-faster.co.uk
Go-Faster Consultancy Ltd.
67