Release 1.4.0
A53653_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Expert User's Guide Release 1.4.0 A53653_01 |
|
This chapter presents four major areas for you to consider as you seek to maximize the benefits provided by Oracle Expert. These areas include:
By following the guidelines presented in this chapter, you will minimize the hardware resources and the time required for Oracle Expert to complete an evaluation.
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 reduces both 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 from consideration SQL statements 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 that are available to you from the Collection page. Focused tuning eliminates the need to collect certain types of information, which saves time.
The recommendations generated by Oracle Expert are only as good as the information you provide. The more complete and accurate the data you supply is, the better the recommendations will be, because 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 that affects 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 that 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, influence the evaluation process. Providing up-to-date information will result in up-to-date recommendations.
Oracle Expert maintains historical information about your database environment within its repository. Successive iterations of a tuning session provide additional performance optimizations over time. These successive iterations are also useful for tuning 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:
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 that 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 changes within the database environment, such as server upgrades or hardware changes, are made Oracle Expert will automatically identify any dependencies that occur.
The Oracle Expert rule mechanism allows advanced users to have ultimate control over the analysis process. Rules can be used to:
For example, the "Percent physical memory available" rule controls the amount of physical memory on a system that 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 must 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 that they affect. The "Percent physical memory available" rule resides with the system object, because it affects the amount of total memory available to the instance on the system. The "Maximum sorted indexes per table" rule resides with the table, because it affects the number of indexes allowed on the table.
