PeopleSoft: Properly Instrumented for Performance Tuning?

Transcription

1 PeopleSoft: Properly Instrumented for Performance Tuning? by David Kurtz, Go-Faster Consultancy Ltd. Since their takeover of PeopleSoft, Oracle has announced project Fusion, an initiative for a new generation of Oracle Applications building on the best of existing Oracle and PeopleSoft products. This is the second in a series of articles in which I will examine some aspects of PeopleTools, the proprietary technology developed by PeopleSoft, and speculate about Fusion. As a consultant who specialises in performance tuning, the recently introduced PeopleSoft Performance Monitor has been of particular interest to me. It has provided a method to collect various performance related metrics from the PeopleSoft technology without the need to resort to esoteric scripts. It enables the user to determine how much time was executing which part of the application, and in which tier of the technology. Since version 6 of the Oracle RDBMS, it has been possible to enable SQL*Trace (event 10046) on a session since. Additional code in the Oracle kernel writes information to a trace file about every SQL statement, parsed and executed, every fetch operation and execution plan, and how long each operation took. The trace can be enhanced to additionally record time spent waiting on an evergrowing list of database events, and the values of bind variables referenced in the SQL statements. Hence, the DBA can determine exactly what is going on and how long it is taking. As a young naïve DBA I used to believe that all you had to do to improve the performance of the database was measure the buffer cache hit ratio, and if it fell below 90% then it was time to add more memory to the block buffer cache. Later, I learnt that performance tuning is all about the time lost when a user waits for the application. SQL*Trace files can be processed with either Oracle s TKPROF, Trace Analyser or a third party profiler in order to identify the SQL operation, or database event that took the most time. However, over the years as multi-tier web-orientated architectures have become prevalent, applications spend less time in the database and more time executing code in an application server or java servlet (or both in the case of PeopleSoft). Therefore, I

2 increasingly see Oracle reporting significant quantities of time spent on the event SQL*Net message from client. DBAs often discarded this as a mere idle event, but the database might be idle because the middleware is busy, in which case this is time lost to a user that affects performance. It is ironic that as time-based analysis of performance issues has become generally recognised as the most (if not the only) effective technique, the tools that Oracle provide to investigate database performance issues have been made less effective in resolving system performance issues. PeopleSoft have instrumented every part of their Enterprise PeopleTools technology, from release As each operator action generates activity, this can be recorded as a number of transactions. These are recorded in the database. It is now possible to determine exactly which operator executes which piece of code and how long it took to execute. You can think of the PeopleSoft Performance Monitor as event for the application. PeopleSoft used its own existing PeopleTools technology to collect and store the performance data. A new servlet was added to the web server to receive performance metrics from the instrumented processes, and a new server process was added to the application server to store that data in the database. Each of the instrumented processes connect over HTTP to the monitoring servlet. Browser http / https & JavaScript) JavaScript) Monitoring System Web Web PIA PPMI Monitor Tuxedo Message Application DBMS APPQ DBMS PSAPPSRV SQL PSPPMSRV data data & meta- metadatdata Browser Screen Java Paint Script & JavaScript) JavaScript) http / https Web PIA Tuxedo Message Application APPQ PSAPPSRV PSMONITORSRV SQL DBMS data data & meta- metadatdata Monitored System Figure 1. PeopleTools Performance Monitor Architecture

3 Although a system can monitor itself, the recommended configuration is for one PeopleTools system to be configured to monitor another. Thus the overhead of storing performance data is not borne by the monitored system. To configure the Performance Monitor is merely necessary to give the monitored system the URL of the monitor servlet in the monitoring system. Performance Metrics are separated into Transactions and Events. Events are instantaneous metrics that can be sampled periodically, such as CPU and memory utilisation, or are collected in response to a certain conditions, such as the time-out of an ad-hoc query. Transactions are a part of the activity that occurs when a user does something in the application that causes an interaction with the middleware. Therefore, they also have a duration. One interaction will produce a hierarchy of many transactions; this is called a Performance Monitoring Unit (PMU). The Performance Monitor can be configured to sample PMUs across the system, say 1 in every 40, so that a picture of the whole system can be built up, without creating excessive load. It is possible to enable the Component Performance Trace, which collects all the PMUs for a user s session. The Performance Monitor is supplied with a number of analytic pages that perform queries on the performance data back from the database. For example, Figure 2 shows the system summary page from which you can drill into individual server processes.

4 Figure 2: System Performance Summary Page These are standard PeopleTools components that are developed in the usual way. The analytics delivered in PeopleTools 8.45 are considerably more sophisticated. However, since the takeover of PeopleSoft by Oracle this area is unlikely to develop further in PeopleTools, although, there is therefore nothing to prevent users from developing the own custom analytic pages. For example, the chart in Figure 3 is from a component that identifies the top 10 transactions by cumulative execution time. Clearly one transaction needs to be looked at closely. Figure 3: Part of Top Component Page

5 Figure 4 shows the analysis of a Component Performance Trace of the part of the application with the problematic transaction shown in Figure 3. It shows that during the trace 99.98% of the response time is waiting for SQL execution. This case is clearly a SQL problem. You can see the SQL statements and the values of the bind variables order by execution time, starting with the longest. Figure 4: Analysis of Component Performance Trace You have to cut and paste the SQL into a database tool, or just SQL*Plus, in order to get the execution plan. PeopleSoft doesn t do this for you because that is database platform specific (and of course you will probably be reviewing performance data on a different database). You can also look at PMUs in the trace. They can be presented as a hierarchy or tree (see Figure 5). The entries are listed in the order they executed. You can see how much time each transaction took, and what other transactions where a part of it.

6 Figure 5: Performance Monitoring Unit Tree You can then drill into individual transactions and see what they did, and what part of the application was executing, as shown in Figure 6. So if you need to change the code, you can find it easily. Figure 6: Performance Monitoring Transaction Context In conclusion, the PeopleTools Performance Monitor is extremely impressive. It is the more sophisticated that anything else I have seen or heard of. It could revolutionise analysis of functional and performance problems. (It could also but me out of business!) When an end-user reports a performance problem, it is

7 reasonable to ask them to enable the performance trace and then demonstrate their problem. The customer s in-house support team, who will necessarily be familiar with PeopleTools, can understand and analyse the information, determine whether there really is a problem, and if necessary make changes to the code. I ll leave you with two questions: will the Performance Monitor will be built into the Fusion product from day one, and if so will you have to pay extra for it? PeopleTools Performance Monitor is not a separately licensed option. If you have PeopleTools you have the Performance Monitor. David Kurtz is a performance specialist working Enterprise PeopleSoft applications and Oracle (www.go-faster.co.uk). He is the author of PeopleSoft for the Oracle DBA, published by Apress (www.psftdba.com). He is also the chair of UKOUG s UNIX SIG, and also deputy chair of the newly formed PeopleSoft Technology SIG.

PeopleSoft DDL & DDL Management by David Kurtz, Go-Faster Consultancy Ltd. Since their takeover of PeopleSoft, Oracle has announced project Fusion, an initiative for a new generation of Oracle Applications

PeopleTools Tables: The Application Repository in the Database by David Kurtz, Go-Faster Consultancy Ltd. Since their takeover of PeopleSoft, Oracle has announced project Fusion, an initiative for a new

Introduction AppDynamics for Databases Version 2.9.4 Page 1 Introduction to AppDynamics for Databases.................................... 3 Top Five Features of a Database Monitoring Tool.............................

Phire Architect Hardware and Software Requirements Copyright 2014, Phire. All rights reserved. The Programs (which include both the software and documentation) contain proprietary information; they are

PERFORMANCE TUNING FOR PEOPLESOFT APPLICATIONS 1.Introduction: It is a widely known fact that 80% of performance problems are a direct result of the to poor performance, such as server configuration, resource

Holistic Performance Analysis of J2EE Applications By Madhu Tanikella In order to identify and resolve performance problems of enterprise Java Applications and reduce the time-to-market, performance analysis

Transaction Monitoring Version 8.1.3 for AIX, Linux, and Windows Reference IBM Note Before using this information and the product it supports, read the information in Notices. This edition applies to V8.1.3

WAIT-TIME ANALYSIS METHOD: NEW BEST PRACTICE FOR APPLICATION PERFORMANCE MANAGEMENT INTRODUCTION TO WAIT-TIME METHODS Until very recently, tuning of IT application performance has been largely a guessing

White paper: Unlocking the potential of load testing to maximise ROI and reduce risk. Executive Summary Load testing can be used in a range of business scenarios to deliver numerous benefits. At its core,

Programa de Actualización Profesional ACTI Oracle Database 11g: SQL Tuning Workshop What you will learn This Oracle Database 11g SQL Tuning Workshop training is a DBA-centric course that teaches you how

Monitoring and Diagnosing Production Applications Using Oracle Application Diagnostics for Java An Oracle White Paper December 2007 Monitoring and Diagnosing Production Applications Using Oracle Application

Tier Architectures Kathleen Durant CS 3200 1 Supporting Architectures for DBMS Over the years there have been many different hardware configurations to support database systems Some are outdated others

Oracle University Contact Us: 1.800.529.0165 Oracle Database 12c: Performance Management and Tuning NEW Duration: 5 Days What you will learn In the Oracle Database 12c: Performance Management and Tuning

Response Time Analysis A Pragmatic Approach for Tuning and Optimizing Oracle Database Performance By Dean Richards Confio Software, a member of the SolarWinds family 4772 Walnut Street, Suite 100 Boulder,

Is your database application experiencing poor response time, scalability problems, and too many deadlocks or poor application performance? One or a combination of zparms, database design and application

An AppDynamics Business White Paper HOW MUCH REVENUE DOES IT GENERATE? Why Alerts Suck and Monitoring Solutions need to become Smarter I have yet to meet anyone in Dev or Ops who likes alerts. I ve also

Beyond Web Application Log Analysis using Apache TM Hadoop A Whitepaper by Orzota, Inc. 1 Web Applications As more and more software moves to a Software as a Service (SaaS) model, the web application has

Resource Monitoring During Performance Testing Experience Report by Johann du Plessis Introduction During a recent review of performance testing projects I completed over the past 8 years, one of the goals

1 of 5 1/17/2015 1:28 PM ORACLE DATABASE ADMINISTRATOR RESUME ORACLE DBA Resumes Please note that this is a not a Job Board - We are an I.T Staffing Company and we provide candidates on a Contract basis.

Chapter 1: Introduction to ArcGIS Server At a high level you can think of ArcGIS Server as software that helps you take your geographic information and make it available to others. This data can be distributed

Real Application Testing Dave Foster Master Principal Sales Consultant The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated

An Oracle White Paper February, 2015 Oracle Database In-Memory Advisor Best Practices Disclaimer The following is intended to outline our general product direction. It is intended for information purposes

Overview What is so cool about the SQL diagnostic manager Management Pack? The SQL diagnostic manager (SQLdm) Management Pack integrates key monitors and alerts used by SQL Server DBAs with Microsoft's

What Is Specific in Load Testing? Testing of multi-user applications under realistic and stress loads is really the only way to ensure appropriate performance and reliability in production. Load testing

Oracle University Contact Us: 1.800.529.0165 Oracle Database 12c: Performance Management and Tuning NEW Duration: 5 Days What you will learn In the Oracle Database 12c: Performance Management and Tuning

EVERYTHING A DBA SHOULD KNOW ABOUT TCPIP NETWORKS Chen (Gwen),HP Software-as-a-Service 1. TCP/IP Problems that DBAs Can Face In this paper I ll discuss some of the network problems that I ve encountered

Real Application Testing Fred Louis Oracle Enterprise Architect The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated

About the Author p. xv About the Technical Contributors p. xvi About the Technical Reviewers p. xvi Acknowledgments p. xix Preface p. xxiii About This Book p. xxiii How to Use This Book p. xxiv Appendices

Out of the Fire - Adding Layers of Protection When Deploying Oracle EBS to the Internet March 8, 2012 Stephen Kost Chief Technology Officer Integrigy Corporation Phil Reimann Director of Business Development

Oracle Database Monitoring for the Beginner Chris Grabowy cgrabowy@fcg.com First Consulting Group What is Database Monitoring? The monitoring of predefined events that generates a message or warning when