Performance Tuning for PeopleSoft Administrators David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk.

Slide 1Performance Tuning for PeopleSoft Administrators David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk Slide 2Who Am I? Oracle Database Specialist –Independent consultant Performance tuning –PeopleSoft ERP –Oracle RDBMS Book –www.psftdba.comwww.psftdba.com Oak Table UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 2 Slide 3It depends… The answer to all questions should be deemed to be implicitly prefixed it depends if they are not already explicitly so prefixed. The reasons for this include, but are not limited to: –There may be exceptions where the answer is either false or not completely true –There may be exceptions to the exceptions and so on ad infinitum. –The question may not explicitly scope all the conditions upon which the answer depends UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 3 Slide 4Not Completely True UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 4 Slide 5Not Adequately Specified 342 8 -173 8 =147 8 UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 5 342 -173 =147 Slide 6Performance Performance is exactly what the user perceives it to be. No more, no less. Performance is poor when the users perception does not match their expectation. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 6 Slide 7Data, data, data, I cannot make bricks without clay. You dont need fancy monitoring software to tell you that a system is performing poorly. That is what users are for. But you do need to record and monitor metrics to provide diagnostic data UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 7 Slide 8Performance Tuning is a search for lost time. Detection is, or ought to be, an exact science. It should be treated in the same cold and unemotional manner. The Sign of Four, Arthur Conan-Doyle UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 8 Slide 9We do not use guesswork Say rather... we balance probabilities and choose the most likely. It is the scientific use of the imagination, but we have always some material basis on which to start our speculation. The Hound of the Baskervilles, Arthur Conan-Doyle UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 9 Slide 10PEOPLETOOLS PERFORMANCE MONITOR UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 10 Slide 11Performance Monitor Part of PeopleTools –PeopleTools to monitor PeopleTools Since PeopleTools 8.44 –Fully instrumented –Including a timed-event interface for the component processor Event 10046 for the application –Useful PeopleBook –No separate licence UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 11 Slide 12UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 12 Web Server Web Server Application Server (application logic) Application Server (application logic) APPQ PSAPPSRV DBMS (application data & meta-data DBMS (application data & meta-data SQL Tuxedo Message http / https Browser (presentation & JavaScript) Browser (presentation & JavaScript) Monitoring System PIA Servlet PSPPMSRV Monitored System Web Server (presentation logic) Web Server (presentation logic) PIA Servlet DBMS (application data & meta-data DBMS (application data & meta-data Tuxedo Message http / https Browser (presentation & JavaScript) Browser (presentation & JavaScript) Screen Paint Java Script Application Server (application logic) Application Server (application logic) APPQ PSAPPSRV PSMONITORSRV SQL PPMI Servlet 0345f1003x Monitor Servlet Slide 13Performance Monitor Metrics Transactions –User activities in PIA that cause communications with application server –Sampled –Enabled to form a trace PSPMTRANSHIST Events –Periodic samples –Usually initiated by monitoring agents –eg. CPU, Tuxedo counters PSPMEVENTHIST UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 13 Slide 14UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 14 Web Server (presentation logic) Web Server (presentation logic) Servlet Thread Application Server (application logic) Application Server (application logic) APPQ PSAPPSRV Browser (presentation & JavaScript) Browser (presentation & JavaScript) DBMS (application data & meta-data DBMS (application data & meta-data SQL Tuxedo Message http / https Web Server Access Log Tuxedo Service Trace Oracle SQL*Trace Screen Paint Java Script PeopleTools Trace 101: PIA Request 115: Jolt Time 400: Tuxedo Service 401: ICPanel 400: PeopleCode only 406/407/408: SQL Exec Slide 15Analytics: System Performance UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 15 Slide 16Analytics: Top Components UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 16 Slide 17Performance Trace Generates a group of PMUs for activity in a user session –Choose an ID to identify records later –Verbose Includes SQL Significant overhead. Dont use as default. Trace only. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 17 Slide 18Performance Monitor Transactions User activity in PIA Performance Monitoring Unit –Hierarchy of transactions Similar to Oracle event 10046 trace –recursive actions UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 18 Slide 19SQL in Verbose Trace UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 19 Slide 20How much data? Control sampling –Proportion of transactions collected Depends upon activity on system On busy self-service system as little as 1 in 1000 –It depends! You will have to decide for yourself. –Event sampling frequency For each agent 5 minutes – 15 minutes –Depends on whether you want to be able to see short-lived behaviours. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 20 Slide 21UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 21 Slide 22Performance Tuning the Performance Monitor Always configure self- monitoring of the monitoring database –So you know it works –So you can work out why the analytics are slow! Performance Tuning the Performance Monitor Archive Process –http://blog.psftdba.com/200 8/05/performance-tuning- performance-monitor.htmlhttp://blog.psftdba.com/200 8/05/performance-tuning- performance-monitor.html Additional indexes –http://blog.psftdba.com/200 6/04/performance-tuning- performance-monitor.htmlhttp://blog.psftdba.com/200 6/04/performance-tuning- performance-monitor.html –http://blog.psftdba.com/200 8/12/poor-performance-of- pspmsessionsvw-view.htmlhttp://blog.psftdba.com/200 8/12/poor-performance-of- pspmsessionsvw-view.html UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 22 Slide 23Performance Monitor Data Delivered analytics will only get you so far –Take time to understand the data model –Write your own analytics Data stored in –PSPMTRANSHIST –PSPMEVENTHIST Archived data in –PSPMTRANSARCH –PSPMEVNTARCH UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 23 Slide 24Custom Analytics with Excel Write a query to extract the data –Create ODBC source using Oracles ODBC driver –Use MSQuery to extract the data directly via into Excel workbook Possibly directly into a pivot table Chart the data When you update the data the chart will also update. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 24 Slide 25Application Server Processes by Service over a week UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 25 Slide 26Performance Monitor Further Reading Performance Monitor PeopleBook PeopleSoft Performance Monitor Red Paper –Doc ID: 747510.1 PeopleSoft for the Oracle DBA –Chapter 10 Practical Guidance on the Use of PeopleSoft Performance Monitor –www.go-faster.co.uk/Practical_PPM.pptwww.go-faster.co.uk/Practical_PPM.ppt –www.go-faster.co.uk/Practical_PPM_2009.pptwww.go-faster.co.uk/Practical_PPM_2009.ppt UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 26 Slide 27PEOPLETOOLS INSTRUMENTATION FOR ORACLE DATABASE UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 27 Slide 28Oracle DBMS: Module & Action Enterprise Manager / Grid Control Active Session Hstory (ASH) Oracle Extended Trace –From 10g set trace on Module/Action Oracle Resource Manager UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 28 Slide 29Instrumentation for Oracle Database up to PeopleTools 8.49 DBMS_APPLICATION_INFO –Sets a Session Attribute CLIENT_INFO since PT7.53 –Useful for auditing with database triggers Module and Action not set –Module defaults to program name UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 29 Slide 30Oracle DBMS: Module & Action Oracle + PeopleSoft instrumentation lets you work out database response time in application. Challenge: relating a piece of SQL to a step in a PeopleSoft process –Identification Comments in code UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 30 Slide 31PeopleTools 8.50 Instrumentation for Oracle Database MODULE and ACTION –On-Line Module/Action –Component/Page, Query Name, Subscription Message Client ID = Operator ID –Scheduled Processes But only sets ACTION to PRCSNAME UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 31 Slide 32Module & Action in OEM UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 32 Slide 33Custom PeopleTools Instrumentation Trigger on PSPRCSRQST –Set Module to PRCSNAME –Set Acton to Process Instance Or Operator ID & Run Control ID http://www.go-faster.co.uk/scripts/psftapi.sql –Override PeopleTools 8.50 instrumentation Module = program, Action = Process Name UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 33 Slide 34UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 34 Slide 35PeopleTools 8.52 Instrumentation for Oracle Database Application Engine in PeopleTools 8.52 –Module = PSAE. program name. session ID –Action = Program. Section. Step Overrides custom trigger ASH data for specific process instance using PSPRCSQUE.SESSION_ID_NUM UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 35 Slide 36Oracle RDBMS:Active Session History Samples active sessions every second Circular buffer in memory –v$active_Session_history –It should hold about 1 hour of data 1 in 10 samples stored in database –DBA_HIST_ACTIVE_SESS_HISTORY –Flushed out during AWR snapshot UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 36 Slide 37Licensing ASH is a part of the Diagnostics Pack –only available with Enterprise Edition of Oracle database. –Thats means it costs money. –I dont like it either, but that is how it is! UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 37 Slide 38ASH in OEM You can run ASH reports via EM UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 38 Slide 39Example ASH Report These processes were responsible for 86% of total DB activity Average 14.8 active sessions (out 32 processes) If I go on I get SQL statements But I dont get execution plans. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 39 Slide 40What does ASH retain? Most of the columns are on v$session –Session Session ID and serial, query coordinator –Wait event id, name and parameters –SQL SQL_ID, plan hash, opcode Plan line numbers from 11g –Object object, file and block numbers row numbers from 11g –Application module, action, client_id … UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 40 Slide 41Active Session History Query ASH repository directly –DBA_HIST_ACTIVE_SESS_HISTORY Profile DB Time by –Module / Action –SQL_ID –SQL Plan Hash Value (if lots of different literals) UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 41 Slide 42Background Reading Sifting through the ASHes, Graham Wood –http://www.oracle.com/technology/products/manageability/database/pdf/t wp03/PPT_active_session_history.pdfhttp://www.oracle.com/technology/products/manageability/database/pdf/t wp03/PPT_active_session_history.pdf The ASHes of (DB) Time, Graham Wood –UKOUG 2009 http://www.ukoug.org/lib/show_document.jsp?id=11472 –Video of presentation at MOW2010 http://www.oaktable.net/media/mow2010-graham-wood-ashes-time-part1 http://www.oaktable.net/media/mow2010-graham-wood-ashes-time-part-2 Doug Burns Oracle Blog –http://oracledoug.com/serendipity/index.php?/plugin/tag/ASHhttp://oracledoug.com/serendipity/index.php?/plugin/tag/ASH Introduction to DBMS_XPLAN –http://www.go-faster.co.uk/Intro_DBMS_XPLAN.ppthttp://www.go-faster.co.uk/Intro_DBMS_XPLAN.ppt UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 42 Slide 43Further Reading Practical use of Active Session History With examples drawn from PeopleSoft –http://www.go-faster.co.uk/ukougpres.htm #Practical_ASH.ppthttp://www.go-faster.co.uk/ukougpres.htm #Practical_ASH.ppt –http://www.go-faster.co.uk/Practical_ASH.pdfhttp://www.go-faster.co.uk/Practical_ASH.pdf UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 43 Slide 44PEOPLETOOLS TRACES UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 44 Slide 45Traces PeopleTools SQL Trace Profiler –PSTraceViewer Unsupported –TraceMagic Doc 1470578.1 Application Server Log –LogFence=4 Diagnostic –Large Files –Difficult to extract performance metrics –Timing Inaccuracies –Incomplete –Measurement Intrusion UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 45 Slide 46Batch Timings Reports Application Engine and COBOL –Time spent in each step –AE can write this to database –Can get negative numbers Only ever one, can get around this with some arithmetic. Fixed in PT8.53? UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 46 Slide 47Web Server Access Log Logs every request –Large files –Time taken for every request –Client IP address But it could just be a network component not the actual client –Oracle Doc 662319.1 UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 47 Slide 48There are only two kinds of performance problems You are working too hard. –Consuming resource CPU Disk Memory You are being prevented from working –Queuing Database Locking Tuxedo Queuing CPU overload … –Waiting for somebody else working too hard! UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 48 Slide 49It is generally better to queue higher up the stack. –The impact of queuing in lower tiers can propagate across the system through mechanisms in higher tiers. –Protect lower tiers for overload by correct configuration But do explain this to DB/OS/disk admins! UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 49 Monitored System Web Server (presentation logic) Web Server (presentation logic) PIA Servlet DBMS (application data & meta-data DBMS (application data & meta-data Tuxedo Message http / https Browser (presentation & JavaScript) Browser (presentation & JavaScript) Screen Paint Java Script Application Server (application logic) Application Server (application logic) APPQ PSAPPSRV SQL 0345f1003x Slide 50Good Queuing! While it is best not to have to queue at all –better to queue on the APPQ in the application server domain, than run out of CPU and queue on the operating system run queue. Use Process Scheduler configuration –limit the number of concurrent processes –prioritise processing of queued processes This can result in increased system throughput UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 50 Slide 51COST-BASED OPTIMIZER STATISTICS UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 51 Slide 52Cost-Based Optimizer Statistics Performance of SQL is a significant aspect of any OLTP system –PeopleSoft is not an exception to this rule. All SQL databases* use volumetric statistics to make SQL Optimizer determine the best execution plan. You need to get your statistics right *at least the ones on which PeopleSoft is certified! But I am only going to talk about Oracle RDBMS UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 52 Slide 53How should you collect Optimizer Statistics in Oracle? Maria Colgan and others – http://blogs.oracle.com/optimizer/http://blogs.oracle.com/optimizer/ –Oracle Database Optimizer: An Insiders View of How the Optimizer Works (Wednesday) –Oracle Database Optimizer: Harnessing the Power of Optimizer Hints (Tuesday) Carlos Sierra –How to Create in 5 Minutes a SQL Tuning Test Case Using SQLTXPLAIN (Mon) –How to Improve SQL Performance with the New SQL Health Check Tool (Tuesday – actually now!) Jonathan Lewis - http://jonathanlewis.wordpress.com/http://jonathanlewis.wordpress.com/ –Creating Test Cases (Monday) –B-Tree Indexes (Mon) Christian Antognini - http://www.antognini.ch/blog/http://www.antognini.ch/blog/ –How the Query Optimizer Learns from Its Mistakes (Tuesday – also now!) Peter Brink –Cardinality Feedback UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 53 Slide 54How should you collect Optimizer Statistics in Oracle? Tell the Optimizer the truth about your data. –Or at least as much of the truth as it needs to make the right answer. –Making it handle too much of the truth can be prejudicial without being probative. Data in Table has a lifecycle –Create / Change / Delete data, –Collect Statistics –Use it UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 54 Slide 55What does Oracle do by default? Oracle collects statistics in the maintenance window –10pm-6am weekdays, and weekends Collects statistics on stale objects –Automatically determines optimal sample size for collecting statistics –Automatically determines whether data is sufficiently skewed to require histograms. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 55 Slide 56Implications for PeopleSoft of Default Oracle Behaviour PeopleSoft is parse intensive. –Literal values in dynamic SQL –Non-use of ReUseStatement in Application Engine –%ProcessInstance, %Oprid and similar macros always resolve to literals –Temporary Record corresponds to many tables. –Non-sharable SQL Skewed Data –Default values to avoid NULL 0, single space, 1 st January 1900. Excessive histogram generation –Processing histograms adds to both statistics collection and parse overhead. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 56 Slide 57New Optimizer Features in 11g Table Preferences –Default dbms_stats options at table level CASCADE DEGREE ESTIMATE_PERCENT –However, there are new features that only work properly with automatic sample size. GRANULARITY INCREMENTAL METHOD_OPT –Can control histogram collection STALE_PERCENT –Control when statistics are refreshed –Use these, and you can safely use standard, default approaches to maintaining statistics with PeopleSoft. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 57 Slide 58%UpdateStats macro DDL Model calls dbms_stats.gather_table_statistics –In 11g, can remove most parameters and use table preferences to control the behaviour of DBMS_STATS. –This applies to whenever statistics are collected. There is still a case for intercepting calls from %UpdateStats to dbms_stats with custom PL/SQL package. –Some processes make excessive use of %UpdateStats eg. TL_TIMEADMIN –Statistics collection can be significant overhead during batch process –Sometimes a process may call %UpdateStats on a permanent table. If that table is large then incremental difference small. Might only want to collect statistics if sufficient change, –ie statistics are STALE. –Fine grain control over %UpdateStats can be very useful. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 58 Slide 59Warning: Statistics History NB: Default Oracle Statistics History Retention policy is 31 days. –Global policy – can specify at table level –Every %UpdateStats call generates history –http://blog.psftdba.com/2009/06/oracle-10g- statistics-history-retention.htmlhttp://blog.psftdba.com/2009/06/oracle-10g- statistics-history-retention.html UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 59 Slide 60pscbo_stats – 1322888.1 It is an attempt to solve a genuine problem –It has continued to evolve since its initial release However, it is a 10g solution. –It does not attempt to manage 11g table preferences –Uses excessively high sample sizes –Too many buckets for histograms –Fails to recognise all PS objects are NOPARALLEL –No partition support –11g Incremental Statics Synopses on partitioned objects only work properly on default sample size Based upon my experience of PeopleSoft on Oracle: –I think better effects can be achieved with better choices for histograms and numbers of buckets and default sample size. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 60 Slide 61GFCPSSTATS11 Package Hold table preferences on meta-data table keyed on RECNAME –DDL Trigger to set table preferences as table created –DML Trigger to set table preferences when metadata changed. DDL model also calls GFCPSSTATS11 package –Via meta-data, can suppress call to dbms_stats –Or only collect statistics if stale. So, no PeopleSoft application code change required. –Force collection of locked statistics –Catch exception when attempt to update locked statistics –http://blog.psftdba.com/2012/09/maintaining-optimizer-statistics-on.htmlhttp://blog.psftdba.com/2012/09/maintaining-optimizer-statistics-on.html UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 61 Slide 62Temporary Records No point collecting statistics during maintenance window. Truncated at start of AE –But Truncate doesnt clear statistics!!! Can only collect accurate statistics during process –%UpdateStats macro Recommendation: –Lock and Delete Optimizer Statistics –OPTIMIZER_DYMANIC_SAMPLING=4 UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 62 Slide 63Global Temporary Tables? Application Engine –Temporary table instances –State Records COBOL –Regular Table Record Consider using Oracle Global Temporary Table –Bot cant do this for restartable AEs UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 63 Slide 64MOS Document References SQLT – [ID 215187.1] Useful for detailed performance analyses of individual SQL statements OS Watcher Black Box – [ID 301137.1] Automates collection of OS-Level performance and diagnostic metrics Extensible scripting can include application- specific information. (stay tuned) UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 64 Slide 65Further Reading http://blog.psftdba.com www.go-faster.co.uk E-ORA Advice for the PeopleSoft Oracle DBA [ID 1445965.1] –Although I have some reservations about some of the content! UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 65 Slide 66TUXEDO APPLICATION SERVER SIZING UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 66 Slide 67Application Server Sizing How many server processes should I configure? –It is very hard to answer that question with a simply numeric answer. –Not too many. –Not too few. –It depends! UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 67 Slide 68Application Server Sizing Too Few? –Queuing –All servers busy Too Many? –Run out of memory/CPU Paging to disk Queue on run queue –>= 10 per queue IPC Queue Contention –Overload database causing Application Server to back up UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 68 Slide 69Detecting Application Server Queuing Queuing not reported in PPM due to bug Look instead at –application server process service status Event 302 –spawning. Following chart derived from this data Interrogate Tuxedo domain directly with tmadmin CLI with shell/batch script. –See http://www.go- faster.co.uk/scripts/tux mon.ziphttp://www.go- faster.co.uk/scripts/tux mon.zip UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 69 Slide 70Application Server Processes by Service over a week UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 70 Slide 71Application Server Spawning Spawning for enabled when Min servers Slide 72Recycle Count PSAPPSRV –Historically, the delivered value has varied between 1000 and 10000 in different versions of PeopleTools –It used to be used as a coarse method of controlling memory consumption of application server processes –More effective to use MaxCacheMemory >= 500Mb PSQRYSRV –Entire query results sets copied into memory –A lower recycle count can be used to release memory Particularly on Windows Further Reading –Document ID 1457385.1 UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 72 Slide 73Application Server Sizing Avoid creating things that you dont need ! –PSSAMSRV – Only one or two are ever needed. –PSQCKSRV – Only used by for 3-tier Application Designer sessions (and then only optional) Ensure enough connections are available –Turning off Jolt Pooling will require more JSHs Understand Application Server Memory Usage –My Oracle Support - Document ID 1457385.1 UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 73 Slide 74Avoiding Contention Tuxedo Queues Contention on IPC queues occurring if messages on queue and not all servers busy Recommendation –No more than 10 server processes/queue –Create multiple queues in a single domain Rather than multiple domains each with a single queue –Need to manually set Bulletin Board sizing parameters in psappsrv.ubx file. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 74 Slide 75Unix IPC Message Sizing (This affects windows too!) PeopleSoft Messages to Tuxedo typically around 100-120Kb –application dependent – so you need to measure this for your system –Default message size 64Kb (except AIX) –Messages larger than max message size (default ¾ queue size) written to and read from disk –Messages that ¾ fill queue also written to disk UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 75 Slide 76UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 76 Slide 77Unix IPC Message Sizing General Recommendation –Set queue size 256Kb Although, this will consume more shared memory. ipcs shows queues that Tuxedo PrintQueue doesnt! –Set maximum message size 128Kb Large PS/Query results will still ping to disk and there is nothing you can do about it UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 77 Slide 78Application Server – Integration Broker Considerations (Asynch) Isolate PUB/SUB processes by dedicating one or more domains to IB. DO NOT allow multiple domains to concurrently process the same service operations –For domain failover, use IB failover groups –For further isolation, use dedicated dispatchers –To Scale, use slave or template slave dispatchers PeopleSoft Integration Performance and Tuning for PeopleTools 8.50 [ID 1169053.1] Slide 79Integration Broker Monitoring Integration Broker Service Operations Monitor IB Profiling – runtime performance detail for both Synch and Asynch operations –Enable at both the Integration Gateway and Application Server –Navigation: PeopleTools->Integration Broker- >Service Operations Monitor->Statistics UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 79 Slide 80Further Reading PeopleSoft for the Oracle DBA –Chapter 13 Tuxedo Internals This presentation from 2002 is based on PT7.5x –http://www.go-faster.co.uk/bea_internals.ppshttp://www.go-faster.co.uk/bea_internals.pps UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 80 Slide 81WEB SERVER SIZING & CONFIGURATION UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 81 Slide 82Web Server Characteristics Primary purpose is serialization/deserialization to build and serve presentation to browsers Maintains user session state Tuning mostly involves correctly sizing the JVM. Scale horizontally by adding web instances UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 82 Slide 83Web Server Sizing Tuning goal is to properly size JVMs, allowing for enough user sessions without extensive GC pauses. JVM size is set with Java start-up options (-Xmx, -Xmn) Try to keep JVM size between 512M and 2048M –depends on which JRE Watch out for native heap –-Xss to reduce thread stack size if necessary Native heap limitations largely a non-issue in PT 8.51+ –64-bit addressing Use verbose GC logging to monitor the Garbage Collector UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 83 Slide 84Web Server Logging GC logging (-verboseGC –XX:+PrintGCDetails -XX:+PrintGCTimeStamps) –Provides timing and frequency of Java VM Garbage Collection cycles –Useful for evaluating Java Heap sizing under load. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 84 Slide 85Web Server Configuration Timeouts can drastically affect Web Server performance and scalability Web Profile Cache settings and Timeouts are important –Saved States, State Discard Interval, Home Page Stale Interval Consider a dedicated Web Instance for Integration Broker Gateway –Things like session affinity and Http keepalives dont matter as much as for the IB Gateway. UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 85 Slide 86Further Reading Web/Network Infrastructure –E-PIA: Red Paper On Implementing Clustering And High Availability For PeopleSoft [ID 612096.1] UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 86 Slide 87Conclusion It depends… UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 87 Slide 88QUESTIONS? Nullius in verba UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 88 Slide 89You know my methods, apply them. A Study in Scarlet, Arthur Conan-Doyle UKOUG2012 - Performance Tuning for PeopleSoft Administrators ©2012 www.go-faster.co.uk 89