| 1. Introduction: | | | | some specific items to focus on when evaluating |
| It is a widely known fact that 80% of performance | | | | database environment:o Make sure the database is |
| problems are a direct result of the poor performance, | | | | sized and configured correctlyo Make sure that the |
| such as server configuration, resource contention. | | | | hardware and O/S environments are set up |
| Assuming you have tuned your servers and followed | | | | correctlyo Verify that patch levels are currento Fix |
| the guidelines for your database server, application | | | | common SQL errorso Review documentation of |
| server, and web server, most of your performance | | | | known problems with PeopleSoft supplied codeo Be |
| problems can be addressed by tuning the PeopleSoft | | | | sure to check available patches from PeopleSoft that |
| Application. | | | | might address the problemo Review PeopleSoft |
| This article presents methodologies and techniques | | | | suggested kernel parameterso Set up the right |
| for optimizing the performance of PeopleSoft | | | | number of processeso Review the application server |
| applications. The methodologies that are discussed | | | | blocking for Long Running Querieso Make sure not to |
| are intended to provide useful tips that will help to | | | | undersize version 8 application server |
| better tune your PeopleSoft applications. These tips | | | | It is also recommended to continue to review these |
| focus on tuning several different aspects within a | | | | items on a periodic basis. |
| PeopleSoft environment ranging from servers to | | | | 6. Database Performance: |
| indexes. You will find some of these tips provide you | | | | The performance of an application depends on many |
| with a significant improvement in performance while | | | | factors. We will start with the overall general |
| others may not apply to your environment. | | | | approach to tuning SQL statements. We will then |
| 2. Server Performance: | | | | move to such areas as indexes, performance |
| In general, the approach to application tuning starts | | | | monitoring, queries, the Tempdb (Tempdb is often |
| by examining the consumption of resources. The | | | | referred to as plain "TEMP"), and, finally, servers and |
| entire system needs to be monitored to analyze | | | | memory allocation. |
| resource consumption on an individual component | | | | To understand the effect of tuning, we must |
| basis and as a whole. | | | | compare 'time in Oracle' with 'request wait time'. |
| The key to tuning servers in a PeopleSoft | | | | Request wait time is the time that a session is |
| environment is to implement a methodology to | | | | connected to Oracle, but not issuing SQL statements. |
| accurately capture as much information as possible | | | | In Oracle time shows the amount of time resolving a |
| without utilizing critical resources needed to serve the | | | | SQL statement once it has been submitted to Oracle |
| end-users. | | | | for execution. If time in Oracle is not significantly |
| Traditional tools used to measure utilizations impact | | | | smaller than the request wait time, then application |
| the system being measured and ultimately the | | | | tuning should be examined. Request wait time is |
| end-user experience. Commands like the following | | | | almost always much greater than in Oracle time, |
| provide snapshot data but not without an associated | | | | especially for on line users, because of think time. |
| cost. These tools can consume a significant amount | | | | One exception to this is for a batch job that |
| of resources so care should be taken when | | | | connects to Oracle and submits SQL statements, |
| executing them.a) df sizeb) iostat swapinfoc) ipcs | | | | then processes the returned data. A greater ratio of |
| timexd) netstat topf) ps uptimeg) sar vmstath) | | | | request wait to Oracle could indicate a loop in the |
| swapinfo also glance & gpm | | | | application outside of Oracle. |
| The goal of using these native commands is to | | | | This should be identified and eliminated before |
| identify, if and where, a bottleneck is in the server. Is | | | | continuing the performance analysis. |
| the problem in the CPU, I/O or memory? These | | | | The next step focuses on tuning the SQL |
| native tools provide indicators, but at the same time | | | | statements that use the most resources. To find the |
| could skew the results because of the overhead | | | | most resource consuming SQL statements, the |
| associated with them. Typically, additional third party | | | | scheduled collection approach can be used. The |
| tools are needed to complete the analysis. | | | | duration time is a commonly used criteria to locate |
| The last hurdle being faced in tuning the server is | | | | the offensive SQL statements. Other useful criteria |
| making timing decisions on when to upgrade the | | | | include the following wait states: I/O, row lock, table |
| hardware itself. To do this, much more information | | | | lock, shared pool, buffer, rollback segment, redo log |
| needs to be collected and stored in order to | | | | buffer, internal lock, log switch and clear, background |
| understand if an historical spike in resource utilization | | | | process, CPU, memory and I/O. For each offensive |
| was a one-time aberration or a regular occurrence | | | | SQL statement, the execution plan and database |
| building over time. The recommendation is to look at | | | | statistics are analyzed. The following statistics are |
| third party vendors for solutions that can collect key | | | | important: table and column selectivity, index |
| performance indicators while minimizing overhead on | | | | clustering factor, and storage parameters. First, all the |
| the system. The collected data can then be put in a | | | | joins of the SQL are considered. For each join, the |
| repository for detailed historical analysis. | | | | ordering of the tables is analyzed. It is of major |
| 3. Web Server Performance: | | | | importance to have the most selective filter condition |
| The release of PeopleSoft Pure Internet | | | | for the driving table. Then, the type of the join is |
| Architecture(TM) introduces new components to | | | | considered. If the join |
| PeopleSoft architecture--the web server and | | | | Represents a Nested Loop, forcing it into a hash join |
| application server. The application server is where | | | | can be advantageous under some conditions. |
| most shops struggle with appropriate sizing. Web | | | | The analysis stage usually results in several |
| servers are used for handling the end-user requests | | | | modification proposals, which are applied and tested |
| from a web browser to eliminate the administrative | | | | in sequence. Corrective actions include database |
| costs associated with loading software (fat clients) | | | | object changes and SQL changes. The typical |
| on individual desktops. The benefit is a significant | | | | database object changes are: index change, index |
| savings on software deployment costs, maintenance, | | | | rebuild and table reorganization. |
| and upgrades. While the shift from fat clients to thin | | | | The typical SQL changes are: replacing subquery with |
| lessens the administrative burden, it increases the | | | | a join, splitting a SQL into multiple SQLs, and inserting |
| need to ensure the web servers are finely tuned | | | | Oracle hints to direct the Optimizer to the right |
| since they will service a large number of clients. The | | | | execution plan. |
| requirement for these web servers to achieve | | | | 7. Indexes: |
| optimal performance is vital due to the mission | | | | Tuning indexes is another important factor in |
| critical-nature PeopleSoft plays in today's enterprise. | | | | improving performance in a PeopleSoft environment. |
| Recommendations for ensuring good performance | | | | Index maintenance is crucial to maintaining good |
| for web servers:o Ensure load balancing strategy is | | | | database performance. Statistics about data |
| soundo Implement a solution to verify and highlight | | | | distribution are maintained in each index. These |
| changes in traffic volumeso Closely monitor the | | | | statistics are used by the optimizer to decide which, |
| response times to verify that the strategy is | | | | if any, indexes to use. The statistics must also be |
| optimizing the web serverso Measure and review | | | | maintained so that the optimizer can continue to |
| historical patterns on server resource utilization (see | | | | make good decisions. Thus, procedures should be |
| server section above).o Increase the HEAP size to | | | | setup to update the statistics as often as is practical. |
| 200, 250, 300, or 380 MB for the web logic startup | | | | Keep in mind that objects that do not change, do |
| script. | | | | not need to have their statistics created again. If the |
| 4. Tuxedo Performance Management: | | | | object has not changed, the stats will be the same. |
| Tuxedo is additional middleware PeopleSoft utilizes to | | | | In this case, recreating the same statistics over again |
| manage the following Internet application server | | | | will waste resources. |
| services:o Component Processor--Responsible for | | | | Since PeopleSoft uses a lot of temp tables that are |
| executing PeopleSoft Components--the core | | | | loaded and then deleted, but not dropped, it is helpful |
| PeopleSoft application business logico Business | | | | to create the statistics when those tables are full of |
| Interlink Processor-- Responsible for managing the | | | | data. If the statistics are created when the table is |
| interactions with third-party systemso Application | | | | empty, the stats will reflect that fact. The Optimizer |
| Messaging Processor--Manages messages in a | | | | will not have correct information when it chooses an |
| PeopleSoft systemo User Interface | | | | access path. |
| Generator--Generates the user interface based on | | | | Periodically, indexes should be rebuilt to counter index |
| the Component or Query definition and generates | | | | fragmentation. An index creation script can be |
| the appropriate markup language (HTML, WML, or | | | | created via PeopleTools to drop and rebuild indexes. |
| XML) and scripting language (JavaScript, WMLScript) | | | | This procedure will eliminate index -wasted space on |
| based on the client accessing the applicationo | | | | blocks that are created as a result of Oracle logical |
| Security Manager--Authenticates end-users and | | | | deletes. This is only necessary on tables that are |
| manages their system access privilegeso Query | | | | changed often (inserts, updates or deletions). |
| Processor--Executes queries using the PeopleSoft | | | | Index scheme is also important to look at. The |
| Query toolo Application Engine--Executes PeopleSoft | | | | indexes in a standard PeopleSoft installation may not |
| Application Engine processeso Process | | | | be the most efficient ones for all installations. Closely |
| Scheduler--Executes reports and batch processes and | | | | examine data's pattern, distribution, and modify the |
| registers the reports in the Portal's Content Registryo | | | | indexes accordingly. For example, the index on |
| SQL Access Manager--Manages all interaction with the | | | | PS_VOUCHER (BUSINESS_UNIT, VOUCHER_ID) |
| relational DBMS via SQL | | | | could be changed to (VOUCHER_ID, |
| This Tuxedo middle tier is another critical and | | | | BUSINESS_UNIT) for an implementation with only a |
| influential component of performance. Similar to the | | | | few business units. Use ISQLW Query Options (Show |
| web server, what is needed is a way to see into the | | | | Query Plan and Show Stats I/O) to determine the |
| "black box" to further understand some of the key | | | | effectiveness of new indexes. However, be careful |
| performance metrics. | | | | to thoroughly test the new index scheme to find all |
| Some of the performance metrics to capture when | | | | of its ramifications. |
| analyzing tuxedo are:o Transaction volumes by | | | | 8. Queries: |
| domain, server, and applicationo Response time for | | | | It is a good idea to examine queries to try and fix a |
| each end-user requesto Tuxedo service generating a | | | | problem that is affecting the application. Query |
| poor performing SQL statemento Break down of | | | | analyzer can be used to see optimizer plans of slow |
| Tuxedo time by Service time and Queue timeo | | | | SQL statements. Choose "Query/Display Plan" to see |
| Identify problem origin - is it in tuxedo or the | | | | a graphical representation of a query plan. |
| database?o Response time comparisons for multiple | | | | Alternatively, by issuing a "set showplan_text on" |
| Tuxedo Server | | | | and running the statement will get a textual |
| Reports has shown that too often companies throw | | | | representation of the plan, showing indexes used, the |
| hardware at a Tuxedo performance problem when a | | | | order in which the tables were used, etc. |
| more effective solution can be as simple as adding | | | | When investigating queries, worktables created per |
| another domain to the existing server(s). This is due | | | | second should also be addressed. If a large number |
| to the fact that PeopleSoft and Tuxedo lack | | | | of work tables being are created per second (i.e. |
| management solutions that provide historical views of | | | | hundreds per second), this means that a large |
| performance. | | | | amount of sorting is occurring. This may not be a |
| 5. Application Performance: | | | | serious a problem, especially if it does not correspond |
| It is an accepted fact that 80% of application and | | | | with a large amount of I/O. |
| database problems reside in the application code. But, | | | | However, performance could be improved by tuning |
| there are other technical items to consider which | | | | the queries and indexes involved in the sorts and, |
| could influence the applications performance. Here are | | | | ideally, this will eliminate some sorting. |