Oracle Enterprise Manager Oracle Expert User's Guide

Using Oracle Expert Effectively

Defining the Appropriate Tuning Scope
Complete and Accurate Data
Iterative Tuning With Oracle Expert
Taking Advantage of Rules

There are four major areas to carefully consider to best use Oracle Expert. They are:

defining the appropriate scope for the tuning session
ensuring that the tuning session data is complete and accurate
using Oracle Expert over time
taking advantage of rules

By following the guidelines presented in this chapter, you will minimize the hardware resources and the amount of time required for Oracle Expert to complete an evaluation.

Defining the Appropriate Tuning Scope

Oracle Expert provides you with the ability to selectively refine the scope of a tuning session. The tuning process consists of selecting both the appropriate tuning scope and collecting the appropriate subset of information about the database environment.

The tuning scope helps you to direct Oracle Expert into specific problem areas. This saves you time both in the amount of information to be collected and the time required to complete the analysis. For large or complex database environments, this means you get results in minutes rather than hours or days.

For example, performing data access analysis for a particular schema rather than a table in that schema results in large performance differences. Oracle Expert eliminates SQL statements from consideration that are not relevant to the currently selected tuning focus. Many more statements can be eliminated early in the evaluation process when the data access tuning focus has been specified at a table level, so less work is required than if a complete schema is tuned.

The tuning scope determines the options which are available to you from the Collection page. Focused tuning eliminates the need to collect certain types of information, which saves time.

Complete and Accurate Data

The recommendations generated by Oracle Expert are only as good as the information you provide. The more complete and accurate the data you supply, the better the recommendations will be, since Oracle Expert will not have to make as many assumptions.

For example, if you are performing instance parameter tuning, you should supply accurate system information. System information includes details such as the amount of physical memory and different resource uses for the system. The instance tuning rules use this information during the evaluation of many of the parameters.

Another area which impacts the evaluation process is the control parameters. Specific rules within the Oracle Expert knowledge base are dependent on control parameter settings. For example, the type of database environment you specify, either DSS, OLTP, or BATCH, has a large influence on the evaluation process. If you allow this particular control parameter to default to OLTP and your database environment is primarily DSS, certain features which optimize performance for DSS environments will not be recommended.

It is also important to ensure that the information available in the Oracle Expert repository is up-to-date. Changes to the database such as server upgrades, or adding an index to a table, influences the evaluation process. Providing up-to-date information will result in up-to-date recommendations.

Iterative Tuning With Oracle Expert

Oracle Expert maintains historical information about your database environment within its repository. Successive iterations of a tuning session provides additional performance optimizations over time. These successive iterations are also useful for proactive tuning of the environment as resources and workloads fluctuate.

Iterative tuning is particularly useful for instance parameter tuning. Oracle Expert provides two mechanisms for collecting statistics about the instance. These are:

single snapshot
duration-based statistics collection

The single snapshot mechanism uses the information from a single snapshot of the Oracle Server's dynamic performance tables. This is only a subset of the information available from the duration-based mechanism. In addition, the single snapshot approach cannot handle situations where the dynamic performance tables overflow the space allocated for them.

The duration-based snapshot mechanism uses the differences between two snapshots from the Oracle Server's dynamic performance tables. Multiple instance statistics records can be collected using this mechanism for a user-selected time period and duration. This approach collects additional statistics that are not available from the single snapshot approach.

To effectively use Oracle Expert's instance parameter rules, you should collect instance statistics using the duration-based snapshot mechanism for time periods when the database is heavily used. This is the period when potential problems and resource bottlenecks will be most evident. In addition, the Oracle Expert rules are implemented to be conservative when making recommendations which result in resource reductions. Resource reduction rules require a minimum of 100 instance statistics samples to be collected before the rules are considered to be valid. This can be changed by setting the "Samples for statistical significance" common rule.

Another advantage of performing iterative tuning with Oracle Expert over time is that as factors within the database environment, such as server upgrades or hardware changes, are made Oracle Expert will automatically identify any dependencies that should be adjusted.

Taking Advantage of Rules

The Oracle Expert rule mechanism allows advanced users to have ultimate control over the analysis process. Rules can be used to:

override the Oracle Expert recommendations
limit the evaluation of specified objects within the database environment
restrict or disable other rules
constrain the range of certain Oracle Expert recommendations

For example, the "Percent physical memory available" rule controls the amount of physical memory on a system which can be used for a database instance. By default, the "Percent physical memory available" rule is 80 percent of the total memory available. If the user knows that the system is required to support multiple instances, the "Percent physical memory available" rule can be instantiated for the system and adjusted to a lower value of 40 percent. The subsequent analysis would ensure that no more than 40 percent of the total memory on the system was used for the instance.

Rules reside with the object which they impact. The "Percent physical memory available" rule resides with the system object, since it impacts the amount of total memory available to the instance on the system. The "Maxmimum sorted indexes per table" rule resides with the table, since it impacts the number of indexes allowed on the table.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.