Performance Tuning Using Oracle Enterprise Manager

PERFORMANCE TUNING USING ORACLE ENTERPRISE MANAGER

OVERVIEW

Experienced Oracle DBAs know that large performance improvements require finding, analyzing, and correcting resource-intensive SQL statements. Oracle Enterprise Manager (OEM) can assist the DBA in isolating and analyzing troublesome SQL statements. Using OEM, the DBA can quickly discover the SQL statements most responsible for performance degradation, then consider tuning improvements. Although other components of the information system should be reviewed, tuning problem SQL statements usually yields the biggest “bang for the buck.”

Although OEM is usually thought of as a database management tool, it is very useful as a real-time performance analysis tool that can assist the DBA in tuning SQL. A particular strength of OEM is the ability to manage all of the databases in the enterprise from the OEM console.

OEM actually comprises a suite of windows tools that assist the DBA in critical functions such as space management, user administration, locks, etc. Each tool is launched from the OEM console, shown in Figure 1. To activate any OEM took, the database of interest is highlighted, then the desired tool is activated. As shown in Figure 1, numerous databases, across many servers, may conveniently be analyzed by the DBA using OEM.

At Pacific Bell Mobile Services in Pleasanton, California, OEM was extensively used to identify bottlenecks in the Customer Care system. The Customer Care program is a large OLTP application that manages information for hundreds of thousands of PCS users throughout California. Approximately 500 service agents are connected simultaneously to the version 7.3.2 RDBMS, which maintains customer information for all customers in the PCS system. Obviously, process bottlenecks in such a system have huge consequences.

The following steps illustrate a method for using OEM in the tuning process:

1: FIND THE “HEAVY HITTERS”

The troublesome SQL statements are easily identified 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). As shown in Figure 2, the FILE I/O delta option is a good choice to find sessions consuming huge amounts of resources--such as batch jobs that should not be running during peak business hours.

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.

In the Top Sessions display shown in Figure 3, the user “VKRYSOV” indicates a session using large amounts of CPU. In this particular case, the user has consumed 85,525 ms of CPU time since the last screen refresh. This user is a good candidate for further review. He is easily located because OEM provides USERNAME, OSUSER, MACHINE, and PROGRAM details.

2: OBTAIN THE EXPLAIN PLAN FOR EACH SQL STATEMENT

Using OEM, the active sql statement for any session is easily retrieved. Once again, use the Top Session tool. Simply double-click on the session of interest, then choose the cursors menu selection. Selecting the Show Explain Plan option will then display the Explain Plan in a hierarchy, as shown in Figure 4

In the example shown in Figure 4, multiple tables are used in successive nested loops operations. 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

OEM can once again be used--this time to obtain index definitions. TheOracle Schema Manager tool is used to browse through the objects in any schema. As shown in Figure 5, this tool easily finds the index in question, and show the columns in the index. In the example shown in Figure 5, the highlighted index is defined to be an index on column INSERT_DATE.

4: DETERMINE SELECTIVITY OF EACH INDEX USED

Proper sql tuning is not simply a matter of avoiding full table scans. 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 DEPT, count (*) from tablename

where rownum < 5000

group by DEPT;

Note the columns used in the ‘where’ clause of the sql statement. Each 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.

5: CONSIDER 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: DETERMINE BEST DRIVING TABLE FOR EACH NESTED LOOP JOIN

A Nested Loop join is the most common way that Oracle joins tables. 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. For example, in Figure 4, the driving table is thus OPERATIONS_DETAIL.

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.

7: RERUN QUERY AND CONFIRM NEW EXPLAIN PLAN

After making appropriate changes, 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 "heavy hitter" and repeat the process.

CONCLUSION

Oracle Enterprise Manager can assist the DBA in quickly finding and tuning the “problem” statements in a database. In addition, it can be used as a tuning aid for all databases in an enterprise, and can quickly identify the big resource-consumers on a system. The various tools in OEM thus constitute a "toolkit" for achieving excellent performance from critical database applications.

REFERENCES

Harrison, Guy, “Oracle SQL High Performance Tuning.” 1996, Prentice Hall.

Aronoff, Eyal, Loney, Kevin, and Noorali, Sonawalla, “Advanced Oracle Tuning and Administration.” 1997, Osborne McGraw-Hill.

BIO

Chris Lawson is a member of the Northern California Oracle Users Group, and a certified ORACLE7 DBA. He is a Senior DBA Consultant for MAXIM Group in the San Francisco Bay area, where he specializes in application performance tuning. He may be reached at 510-829-7586. His fax number is 510-829-7496. Email: