PeopleSoft Performance Tuning Using OEM
PeopleSoft Admins or PeopleSoft DBAs or Oracle DBAs know the importance of finding, analyzing, and correcting resource-intensive SQL statements. Those shops which run PeopleSoft on Oracle can make use of Oracle Enterprise Manager (OEM) in isolating and analyzing troublesome SQL statements. Using OEM, a DBA can quickly discover the SQL statements most responsible for performance degradation and then drill down further to tune them.
OEM is pretty robust tool and is usually cnsidered as a database management tool. However, it is very useful as a real-time performance analysis tool that can assist the DBA in tuning a SQL. You can even make use of the OEM to manage all of the databases in the enterprise from the OEM console.
This post assumes that you already have OEM installed and if you don’t have that – please get that first.
The following steps illustrate how you can make use of OEM to do performance tuning in PeopleSoft.
1. Find the “Big Ones”
SQLs can cause a variety of issues and based on the issues you face in your application, you take a call, which is worst. A SQL may run for long while another SQL may take subseconds to run but it might have a high frequency. A SQL, which runs for long is easy to identify using the OEM Top Sessions tool. This tool is extremely useful in discovering “who’s doing what” in the database at any point in time. Up to 230 Sessions are displayed, sorted by resource usage i.e., CPU or disk I/O. The sessions can be sorted by TOTAL CPU (or DISK I/O), or by DELTA CPU (or DISK I/O).
Sessions that are using the RDBMS at a particular moment are considered “ACTIVE.” In large OLTP applications, the majority of sessions should be inactive, because most queries are rapidly answered. Thus, a session that is ACTIVE for more than a few seconds must be a large consumer of resources (CPU or file i/o). Note, however, that users who are “blocked” will also be marked as active. If needed, the Lock Manager tool may be used to find the blocker.
2: Show Explain Plan for Each Statement
Once you’ve retrieved the SQL using the OEM Top Session tool, double-click on the session of interest, then choose the cursors menu selection and select the Show Explain Plan option to display the Explain Plan in a hierarchy. Note that some versions of OEM include the column Expected Rows, which is valid only when the Cost Based Optimizer is used.
3. Get Statistics on Tables and Indexes Used
Using the Explain Plan output from the Top Sessions tool, note all tables and indexes used in the sql statement. Then, determine the following:
- Approximate size (rows) for each table listed
- Definitions of each index used
Make use of the Oracle Schema Manager tool to browse through the objects in any schema. Schema Manager easily finds the index in question, and show the columns in the index.
4. Determine Selectivity of Each Index Used
Full table scans are not always bad. For example, using a non-selective index to access a table is often worse than a full table scan because of the time and disk I/O wasted in resolving useless index entries. This situation is very common with the Rule Based Optimizer, which cannot take into account index selectivity when choosing indexes. With the Rule Based Optimizer, preference is first given to unique indexes, and then to composite indexes for which each indexed column is in the ‘where’ clause of the SQL statement.
Thus, it is important to know the “spread” of values in a particular column, so that good index candidates may be selected. For instance, a rough idea for column “DEPT” is obtained with the following:
select employee_number, employee_name, dept, division from tablename
where employee_number > 12000 and dept <> ‘D002’
group by DEPT;
Note the columns used in the ‘where’ clause of the sql statement. Each column mentioned in the where clause is a candidate for an index, either single or composite. If any of the ‘where’ columns has a large spread of data, consider using a new index defined for this column.
If the column only has a small proportion of distinct values, then the index is considered non-selective, and is not a good candidate for an index. The classic case is an index on gender, which would be a terrible index candidate. In the above example, dept and division are considered to be poor index candidates.
5. Look at Composite Indexes
Ideally, create a composite index comprising the most selective columns. In general, the optimizer will avoid using calls to several single indexes (the AND-EQUAL operation), as a single composite index will be more efficient.
If all the columns in the where clause are included in a composite index, there is still an opportunity for performance gain. Consider a new composite index that includes (at the end) the fields in the SELECT portion of the query. In this scenario, the index is sufficient to obtain ALL the data needed with NO table access. Remember, though, that only the ‘where’ columns in the sql statement are used by the optimizer in selecting which index to use.
6: Identify the Best Driving Table for each Nested Loop Join
A Nested Loop join is the best way to join tables, but this is where the SQL performance starts to degrade and this is the one which will need most of your time analyzing the SQL and tuning it.
The driving table is the table listed in the Explain Plan that shows the first direct access–i.e., reading the table through either ROWID or a full table scan. Using OEM, this will generally be the table shown first within each loop in the Explain Plan hierarchy.
Whenever the nested loop operation is used, it is critical that the driving table be chosen correctly. Efficient Nested Loops joins require that the smallest driving set be positioned at the beginning of the joins. In other words, try to reduce the rows returned at the earliest opportunity.
If only one table is constrained by a ‘where’ clause, the optimizer will tend to make the constrained table the driving table. This is logical, because the ‘where’ condition reduces the size of the returned row set.
All other things being equal, the Rules Based Optimizer will choose the last table listed in the from clause as the driving table. Especially when the Rule Based Optimizer is used, a big improvement may be obtained simply through altering the order of tables.
To instruct the optimizer to use a different driving table, consider the use of hints. For example, the hint USE_NL (table_name) is used to specify a nested-loops join. Another hint, one that is frequently used in conjunction with the USE_NL hint, is ORDERED. This hint causes the join order to follow the order of tables listed in the from clause.
Whenever the nested loop operation is used, it is critical that the driving table be chosen correctly. Efficient Nested Loops joins require that the smallest driving set be positioned
7. Rerun Query and Confirm New Explain Plan
After making appropriate changes (like adding a new index or changing the SQL), rerun the query and review the new Explain Plan. Once again, the OEM Top Sessions tool may be used to obtain the Explain Plan, as well as monitor CPU or disk consumption. Once a particular sql statement is properly tuned, proceed to the next “big ones” and repeat the process.
References and Suggested Reads:
Oracle Database 11g Release 2 Performance Tuning Tips & Techniques (Oracle Press)
