Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01

Library

Product

Contents

Index

Go to previous page Go to next page

12
Creating and Working with Tuning Sessions

This chapter describes how to create and work with tuning sessions after you have started Oracle Expert.

The topics in this chapter include:

Creating a Tuning Session

A tuning session is the framework within which Oracle Expert performs its tuning activities. Tuning sessions allow you to organize tuning activity and separate sessions within the same database. Oracle Expert provides the following ways to create a tuning session: manually or using the Tuning Session wizard.


Note:

Click Help to get page specific information on any of the Tuning Session Wizard pages. 


Creating a Tuning Session Using the Tuning Session Wizard

Oracle Expert offers an easy and quick way to create a new tuning session using the Tuning Session wizard. To activate the Tuning Session wizard, choose Tools=>Tuning Session Wizard.

The Tuning Session wizard automatically displays when you start Oracle Expert, unless you have disabled the automatic display.

Creating a Tuning Session Manually

To create a new tuning session, in the tree list click on the '+' to the left of the name of the database you want to tune, expand the tree list to display the Tuning Session folder, and perform one of the following:

Oracle Expert assigns a unique name to the new tuning session. You can either accept this name or provide a new name. Edit the tuning session name by clicking on the name and typing the new name. The new name must be 40 characters or fewer in length. Oracle Expert preserves the case (lowercase and uppercase) of alphabetic characters in the name.

If you receive a "table not found" error message, you may not have adequate privileges. Oracle Expert requires SELECT ANY TABLE privilege to collect the required information from the database to be tuned.

When logging in to the target database, Oracle Expert uses the preferred credentials provided by the console. If you manually created a service, the username and password supplied are used. The account specified must have the SELECT ANY TABLE privilege.


Note:

Be sure to give your tuning sessions descriptive names so that you can identify the type of tuning activity contained in the session. 


Setting the Scope of a Tuning Session

You must specify the scope of the tuning effort for each Oracle Expert tuning session. You do this by selecting the tuning scope or scopes you want Oracle Expert to address for the session. The tuning scopes you select determine the kinds of data that will be collected and the types of tuning recommendations Oracle Expert will make for the tuning session.

You can select one or more of the following tuning scopes:

You can select any combination of tuning scopes. They are not mutually exclusive. If you select all of the tuning scopes, this is called comprehensive tuning. During a comprehensive tuning session, Oracle Expert generates every tuning recommendation it is capable of making for a database. Comprehensive tuning is resource intensive and it generally takes a considerable amount of time to get results.

If you do not select all the tuning scopes, this is called focused tuning. During a focused tuning session, Oracle Expert generates tuning recommendations for the selected tuning scopes.

Select the Scope tab to display the Scope page of the Oracle Expert tuning session window, then select one or more tuning scopes for the tuning session.

Table 12-1 uses a set of tuning considerations to compare the three types of tuning. Use the table to determine if the type of tuning you want to do is practical (based on the time and resources you have to devote to the tuning session). Note that Oracle Expert allows you to perform as many or as few categories of tuning as you want at a given time. If you do not have the time or resources to tune all the categories you are interested in at once, you can tune those you have time for first, then tune the other categories when you have more time or resources.

Table 12-1 Comparison of Tuning Scopes
Tuning Considerations  Instance Optimization  Space Management  SQL Reuse  Data Access by Table  Data Access by Workload  Data Access Index Rebuild 

Amount of data to collect 

small 

med to large 

small to med 

small to large 

small to large 

small 

Time Required to collect data 

short 

med to long 

short to med 

short to long 

short to long 

short 

Impact of collection effort on database 

negligible 

low 

low 

low 

low 

low 

Manual effort required to collect of edit data 

low 

low 

low 

low to high 

low 

low 

Potential complexity of implementing tuning recommendations 

low 

low 

low 

low 

low 

low 

Potential gain from implementing tuning recommendations 

med 

med 

med 

med to high 

med to high 

med 

You can change the scope of a tuning session. For more information about changing the scope, see "Modifying a Tuning Session".

Instance Optimizations

When Check for Instance Optimizations is selected, you can tune instance parameters, which control the behavior of the database and certain configuration options, such as how the database will use memory resources on the system or how the parameters will handle various contention problems. Oracle Expert tunes the following categories, assuming that you collect the data Oracle Expert expects:

SQL Reuse Opportunities

When Check for SQL Reuse Opportunities is selected, Oracle Expert performs SQL statement matching when it analyzes the collected data.

During SQL reuse, Oracle Expert identifies similar statements that prevent SQL statements from being reused in the shared pool because of differences in case and/or spacing.

With SQL statement matching, Oracle Expert compares statements in the workload to determine if similar statements can be rewritten to eliminate redundancy. The Oracle Server maintains only one copy of a distinct SQL statement within the cache to maximize memory and minimize redundant parsing and validating. The Oracle Server does not consider statements to be identical unless they use identical spacing, punctuation, and case, and they match character by character. If Oracle Expert finds one or more statements that can be rewritten to increase cache efficiency, it makes these recommendations.

Space Management

When Check for Appropriate Space Management is selected, Oracle Expert evaluates the types and structures of tablespaces, the sizing and placing of schema objects, and the tablespace assignments of database users. If Oracle Expert determines that various guidelines are not being followed, then appropriate recommendations will be made.

Optimal Data Access

When Check for Optimal Data Access is selected, Oracle Expert ensures there is efficient access to data.

An access method is a strategy used to retrieve data from a database in the most optimal way. The Oracle Expert access method tuning rules address the Oracle cost-based optimizer, not the rule-based optimizer. One method that databases use to decrease data retrieval time is indexes. Oracle Expert offers three types of access method tuning:

Selecting Values for Tuning Session Characteristics

Oracle Expert Tuning Session Characteristics are displayed in the Tuning Session Characteristics section of the Scope page.

Tuning Session Characteristics provide useful information for tuning the database environment. Tuning Session Characteristics provide information about your database that cannot be collected from your database. For each Tuning Session Characteristic, select the most appropriate value for your database environment. To change the value of a Tuning Session Characteristic, click the down-arrow next to the current value, then select the new value from the list. Oracle Expert uses the Tuning Session Characteristic values you select to optimize its tuning recommendations for your database's specific environment.

Oracle Expert allows you to set the following Tuning Session Characteristics:

The Application Type tuning session characteristic gives Oracle Expert an indication about the type of workload used in the database environment. This allows Oracle Expert to optimize the database for the type of workload. The possible values are:

The Downtime Tolerance tuning session characteristic allows you to influence whether the system will bias its recommendations for recovery or performance. If the tolerance is large, Oracle Expert will optimize for performance. If the tolerance is small, Oracle Expert will optimize for recovery time. The possible values are:

The Peak Logical Write Rate tuning session characteristic give Oracle Expert an indication of maximum write transaction volume when actual transaction statistics have not been collected. This information is used to evaluate whether the server is configured to support the expected write transaction rate. The possible values are:

The Forms Application Used tuning session characteristic tells Oracle Expert whether forms applications are used within the database environment or not. Oracle Expert contains rules which are specific to forms applications, such as setting the minimum number of open cursors for the instance. Possible values are: Yes and No.

The Comprehensive Analysis tuning session characteristic tells Oracle Expert that there is a complete workload in the current database. A comprehensive analysis provides the most efficient tuning recommendations. If you chose not to perform a comprehensive analysis, the analysis will focus on a subset of SQL statements that are currently being accessed by the database repository.

Opening an Existing Tuning Session

You can open an existing tuning session by:

Modifying a Tuning Session

To change an active session's scope and the tuning session characteristic values, use the Scope page of the tuning session window. Enter data in the same manner as you did when you created the tuning session.

You can change the tuning scope to perform a different type of analysis. For example, you might initially have selected and performed focused instance tuning. After Oracle Expert has analyzed the data collected for the instance tuning session, you might decide to select a different tuning category.

You can also view and edit the rules and attributes associated with the tuning session by using the Edit pull-down menu.

Deleting a Tuning Session

You can delete a tuning session by clicking on the tuning session name in the tree list and choosing File=>Delete. This displays a dialog box that asks you to confirm the tuning session deletion. If you confirm the deletion, Oracle Expert deletes the tuning session and all the data in the repository that is associated with the tuning session.

Saving Tuning Session Data to a Log File

You can manually save current tuning session data to an Oracle Expert log file by choosing Help=>Save Current Session. (Note that tuning session information is automatically saved to a log file when unexpected errors are encountered.) This information can be sent to Oracle Customer Support for a comprehensive diagnosis of your session. Log files are saved in the <Oracle Home>\sysman\temp directory.


Note:

Log files are overwritten each time session data is saved. Be sure to move or rename log files that you wish to keep. 



Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index